Pivot Chart question

[Trevor Greene]

Hello,

I have some data based on companies.

The data is the following.... Each day, (using date), I have a new
Profit/Loss number based on a stock.

I have a list of companies about 100 long, and so far 6 dates worth of
P&L's. I am trying to set up a pivot chart to automatically create
line graphs for each company which I will filter, to see the trend in
P&L's. The problem is I am unable to draw the lines so the X
variables are displayed as dates, the Y as the Value of P&L, and have
it individually make a simple line graph for each company.

Any suggestions? I really don't want to have to write a line graph
each time for each company 100+ times, A filter would be 1000x
easier.

Thanks!

 

[Luke M]

Assuming your data has headers of Date, Company Name, and P/L:

Select your data, go to Data, PivotTable. Select a PivotChart, then hit
'Finish'.

Place Date into the Category Field area. Place Company Name into the series
field area. Place P/L into the Data Items area.

Finally, right-click on chart, change type to line. You can now use the
series filter to choose which companies you want to look at.

 

[Trevor Greene]

Assuming your data has headers of Date, Company Name, and P/L:

Select your data, go to Data, PivotTable. Select a PivotChart, then hit
'Finish'.

Place Date into the Category Field area. Place Company Name into the series
field area. Place P/L into the Data Items area.

Finally, right-click on chart, change type to line. You can now use the
series filter to choose which companies you want to look at.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I wouldn't exactly Say I have headers--

I have a header for symbol. To lay it out, it looks like this...

Symbol 7/9/2009 7/10/2009
AAA 25 15
BBB 50 22
CCC 30 13

So i have a header for Symbol, But my date is technically my header
for P&L.

 

[Shane Devenshire]

Hi,

If that is your data layout you need to change the way the raw data is set
up the pivot table will not handle the dates across the top correctly.

1. Select the raw data and press Alt+D, P
2. Choose Multiple Consolidation Ranges, Next, Next,
3. Highlight all your data and click Add, Finish
4. At the bottom right corner of the pivot table double-click the Grand Total
5. On the new sheet delete the Page1 column and change the heading titles to
Symbol, Date, and Amount (or whatever)
6. Using your standard method create a pivot chart from this data.

 

[Trevor Greene]

Hi,

If that is your data layout you need to change the way the raw data is set
up the pivot table will not handle the dates across the top correctly.

1. Select the raw data and press Alt+D, P
2. Choose Multiple Consolidation Ranges, Next, Next,
3. Highlight all your data and click Add, Finish
4. At the bottom right corner of the pivot table double-click the Grand Total
5. On the new sheet delete the Page1 column and change the heading titlesto
Symbol, Date, and Amount (or whatever)
6. Using your standard method create a pivot chart from this data.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

That last suggestion definitely helped, But i am still struggling on
getting the chart to design a line for each company individually over
the 5-6 dates vs. the line of ALL companies over the course of one
day.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
All done according to Shane.
http://www.mediafire.com/file/mjug3dlktzt/07_20_09.xlsm
Will not convert to 2003.

 

[Trevor Greene]

Excel 2007 PivotTable
All done according to Shane.http://www.mediafire.com/file/mjug3dlktzt/07_20_09.xlsm
Will not convert to 2003.

That helped very much. Thank you everybody.