Stock trade - time, price and volume graph

[John John]

Hello

I'm using Excel 2002 and trying to create a chart to illustrate the price, volume and time (not date) of a number of trades. The chart should have 'time' on the x-axis, and 'price' (line chart) and 'volume' (bar chart) on 2 y-axes. The problem with the custom chart called 'Line-column on two axes' is that it does not recognise the x-axes as a time-series i.e. if you graphed 10 trades occurring over an hour period and 8 trades occurred in the first 5 minutes, then 80% of the x-axis would be taken up with the first 5 minutes.

I could create a second spreadsheet containing every second of the trading period (A1=08:00:00, A2=08:00:01, A3=08:00:02 … A32400=16:59:59) and use VLOOKUP to slot the volumes and times against the correct second, but this leads to some very cumbersome tables.

Any ideas on how to do this chart more efficiently would be much appreciated. I've included some sample data below – apologies; I can't download the software allowing me to insert a proper table, so have used '/' to divide each column.

Time / Price / Volume
17:31:16 / 214 / -600
17:31:29 / 213 / 600
17:31:59 / 210 / -800
17:32:15 / 220 / 800
17:32:56 / 250 / -1200
17:33:15 / 225 / 1200


Thanks

john john

 

[Richard Schollar]

Hello John

I'm afraid I can't offer much help here but would it be useful to you to have the time as "number of seconds elapsed since time 0"? By this I mean the following:

Excel Workbook
	A	B	C	D
1	Time	Price	Volume	Seconds elapsed
2	17:31:16	214	-600	-
3	17:31:29	213	600	13.00
4	17:31:59	210	-800	43.00
5	17:32:15	220	800	59.00
6	17:32:56	250	-1200	100.00
7	17:33:15	225	1200	119.00
Sheet2

If you plot Seconds Elapsed as a Time Series on the X axis you should get the separation (you will have to format the numbers as:

0

though, otherwise you will end up as non-sensical dates).

 

[John John]

Hello Richard

Thanks for the suggestion - I'll need to have the times on the x-axis, rather than seconds elapsed. I'm not sure if I could use this method to do this. If so, please let me know.

Thanks

John John

 

[Richard Schollar]

As far as I know it won't be possible to use such a fine measure (ie seconds) on the X axis (as a Time series) which is why I suggested the seconds elapsed approach. What you had before ie with every second plotted) is the closest I think you are going to get.

 

[John John]

Thanks Richard, I'll try both methods and compare the charts.

John John