Graphing A Time Series OF Stocks

C

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.
 
T

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
 
C

carl

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

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
 
C

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.
 
J

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/
_______
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top