Graphing A Time Series OF Stocks

[carl]

My data looks like so:

Stock TradePrice TradeVolume ExecutionDateTime
EFG 5.2 8 2:40:04 PM
EFG 5.5 8 2:40:02 PM
EFG 5.1 10 2:40:01 PM
EFG 5.3 10 2:40:01 PM
EFG 5.1 10 2:40:00 PM
EFG 5.1 10 2:40:00 PM
ABC 1.5 4 2:31:17 PM
ABC 1.6 4 2:31:16 PM
ABC 1.7 2 2:30:51 PM


I am trying to create one graph with price on the Y and time on the X for
all my stocks (in this example just 2 stocks).

Is it possible (I've tried to use the chart wizard with no success).

Thank you in advance.

 

[Tushar Mehta]

Your data are perfectly organized for a PivotTable (and PivotChart).
Select Data | PivotTable and PivotChart report... (the name has changed
with different versions of XL).

Put the ExecutionDateTime as the row field, the Stock as the column
field and the TradePrice as the data field. By default, XL will use
'Sum of TradePrice' as the data field, but that is OK -- as long as you
do not have multiple trades for the same stock at the same time.

Of course, once you create your basic table and chart, you can
experiment with the layout to see if something else is more appealing
to you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[carl]

thanks. is there a way to handle the case where I have multiple prices at the
same time ? This occurs frequently.

 

[Tushar Mehta]

You have multiple prices for the same stock at the same time?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[carl]

yes. i run an exchange. a given order can be split into multiple trades all
taking place at the same time and same price.

 

[Jon Peltier]

Use the Average of Trade Price option, not the default Sum (why Sum is
default, I'll never figure out). Then drag another copy of the Trade
Price field to the Row Area. Here's how the pivot table looks:

Average of TradePrice Stock
ExecutionDateTime TradePrice ABC EFG
2:30:51 PM 1.7 1.7
2:31:16 PM 1.6 1.6
2:31:17 PM 1.5 1.5
2:40:00 PM 5.1 5.1
2:40:01 PM 5.1 5.1
5.3 5.3
2:40:02 PM 5.5 5.5
2:40:04 PM 5.2 5.2

At 2:40:01 you can see two different prices in the data area.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______