Plotting share performance, business hours only

[PatrickW]

Hi all

I've written a spreadsheet that tracks my investment portfolio performance, with data links that update every five minutes, and record this data for plotting on a x-y scatter graph.

This is working well, however, the share value only changes between 09:00 and 17:00, when the stock exchange is open. I end up with large stretches in my graphs where the information is static (from 17:00 till 09:00 the following day, or over weekends), since I want a true time-based reflection of the shares.

Is it possible to get my data to plot only on business hours?

I.e. I would like to see plotted only weekday information, between 9 and 5.

Is this possible?

Thanks

PatrickW

 

[Gerald Higgins]

There's probably lots of ways of doing this.

Is it possible to change your code so that the links only update between 9 and 5 ? Or maybe 9 and 5.15 or 6 or something, in case there is any kind of delay.

 

[PatrickW]

I have no problem with the links updating - it only updates when the spreadsheet is open, which is when I'm at work. The problem is the plotting.

I have share value on the y-axis, and date/time on the x. I end up with loads of changing values (alas, mostly negative at the moment!) between 9 and 5, and then a really long portion of either flatline or blank (depending what I do with the data).

The issue is that the sections with no changing data are so much longer than the periods where the data does change, that the graphs are generally unusable.

Ideally what I would like is on the x axis, "16/05/2011 09:00" as the start point of data, and "16/05/2011 17:00" as the end point, continuing immediately, on the same graph with "17/05/2011 09:00" etc. - simply cutting out all of the "dead time".

Thanks

PatrickW

 

[Gerald Higgins]

I see. Sorry, I think I misunderstood the original question.

This might be tricky to do.
If you're using an XY chart, Excel interprets the X axis data as dates or times, if it thinks the data looks like dates or times, which your's presumably does.
You could try formating the data into Number format, or some other format.
This will probably eliminate the empty overnight periods.
But it may also make the appearance of the x axis labels look odd.

 

[PatrickW]

Yes, I've toyed with the idea of removing the 'real time' element of it: if I simply took the time data and changed it to something else, like an index value, I'd be able to plot only what I wanted, but I still want to keep the 'real time' element.

If the datapoints I collected were exactly at 5 minute intervals this would be workable: remove the times and stick in an index value instead, but sometimes I have to close the sheet, or I force-update the values, and then the x-axis is no longer consistent, and I don't get a true representation of the data.

It boils down to the fact that I don't always have a consistent x-axis increment.

Any other ways around this that you can think of?

 

[Gerald Higgins]

Maybe you could do something like set up a timeline, and then use formulas to lookup the data for the nearest time, from your database. This might help you get a more consistent timeline, instead of just relying on the times returned by your link events.

 

[PatrickW]

Great suggestion - that's what I've now done, and (apart from issues with line charts - horrible things with very limited data points) it's now working very well.

For your information/interest, this is how I did it:

First cell of my 'Time index' column was the start date and time, and then underneath, dragged down was the following:

=+IF(TRUNC(MOD(CG2,1),8)=TRUNC(MOD(17.5/24,1),8),WORKDAY(CG2,1,PHols)+9/24,CG2+(5/(24*60)))

Which checks whether the above time is 17:30 (to 8 decimals): if true, it finds the next working day with PHols an array of public holidays and sets the start time to be 09:00; if false, it simply adds 5 minutes to the above time.

Then I used a set of vlookups to find the nearest match from my raw data, and presto! I had to use an IF to determine whether the index time was greater than the last captured time otherwise the vlookup result still output a number at the end of the timescale, 6 months in the future(!) but all good!

Something I'm investigating now is the use of the XY Chart Labeler addin to avoid using line charts, and this seems to work, but very slowly.

Thanks again for your help!

PatrickW

 

[Gerald Higgins]

You're welcome, thanks for coming back to say thanks and explain how you fixed it