Forum Discussion
creating a graph with the same data for multiple companies
how do you create a graph that has the same data over time for multiple companies?
I want to plot these ratios over time and see them for different companies in one place
eg CAPEI, Enterprise Value etc are constant column names but the ID will change and when i use a graph the ID becomes the x axis or the ID and date in sequence - both of which are not what i want.
this is a sample of the bigger data set
thank you in advance
Tina
| ID# | date | CAPEI | enterprise value | Price/Operating Earnings | Price/Operating Earnings |
| 5750 | 31/1/2010 | -0.62 | -2.82 | -0.29 | -0.29 |
| 5750 | 28/2/2010 | -0.662 | -2.82 | -0.309 | -0.309 |
| 5750 | 31/3/2010 | -0.882 | -3.571 | -10.538 | -10.538 |
| 5750 | 30/4/2010 | -1.442 | -3.571 | -17.224 | -17.224 |
| 5750 | 31/5/2010 | -1.255 | -3.571 | -14.995 | -14.995 |
| 5750 | 30/6/2010 | -0.68 | -7.795 | -13.589 | -13.589 |
| 5750 | 31/7/2010 | -0.807 | -7.795 | -16.137 | -16.137 |
| 5750 | 31/8/2010 | -0.632 | -7.795 | -12.629 | -12.629 |
| 5750 | 30/9/2010 | -0.672 | -11.158 | -18.643 | -18.643 |
| 5750 | 31/10/2010 | -0.609 | -11.158 | -16.888 | -16.888 |
| 5750 | 30/11/2010 | -0.634 | -11.158 | -17.6 | -17.6 |
| 5750 | 31/12/2010 | -0.559 | -16.87 | -1.054 | -1.054 |
| 6216 | 31/1/2010 | 16.171 | 8.109 | 11.592 | 11.773 |
| 6216 | 28/2/2010 | 17.184 | 7.867 | 13.242 | 13.426 |
| 6216 | 31/3/2010 | 20.014 | 7.867 | 15.423 | 15.637 |
| 6216 | 30/4/2010 | 21.431 | 7.867 | 16.457 | 16.685 |
| 6216 | 31/5/2010 | 17.901 | 8.835 | 15.64 | 15.878 |
| 6216 | 30/6/2010 | 15.621 | 8.835 | 13.648 | 13.856 |
| 6216 | 31/7/2010 | 15.766 | 8.835 | 13.697 | 13.905 |
| 6216 | 31/8/2010 | 14.639 | 8.908 | 13.654 | 13.872 |
| 6216 | 30/9/2010 | 16.335 | 8.908 | 15.236 | 15.48 |
| 6216 | 31/10/2010 | 16.297 | 8.908 | 15.201 | 15.444 |
3 Replies
Have you tried using a Pivot Table to initially summarize the data, and then have a Pivot Graph developed directly from that Pivot Table?
I added a column for month to what is otherwise your table, and just quickly let Excel summarize the CAPE values in this attached Pivot Table and adjoining graph. Is this the kind of thing you're wanting to do?
The Pivot Table will work for many columns and many ID#s. I used just the ones you provided to illustrate, not to limit.
And one good thing, you can't break the data by playing around with the settings for the Pivot Table...
If you want more help, you'll need to upload a more complete sampling (or the full data set, so long as there's nothing confidential in it)....and I or someone else can show you a more complete example. It would also help if you can describe more fully the kind of graphing you want---i just let Excel pick the first graph that is supplied.....
