Discontinuous Time Series Charts

  • Hi All,


    I just joined ozgrid and this is my very first post. I have read the rules but spare me if I make some errors.


    My question is related to plotting time series charts using VBA. I have data in the following format.


    04/21/08 9:04:47 4.5055
    04/21/08 9:04:48 4.5055
    04/21/08 9:04:49 4.5055
    04/21/08 9:04:49 4.5055
    04/21/08 9:04:50 4.5055

    04/21/08 10:05:00 3.5055
    04/21/08 10:05:32 3.5055
    04/21/08 10:05:40 3.5055
    04/21/08 10:05:48 3.5055
    04/21/08 10:05:51 3.5055

    04/21/08 11:07:59 5.5055
    04/21/08 11:09:00 5.5055
    04/21/08 11:10:01 5.5055
    04/21/08 11:11:02 5.5055
    04/21/08 11:13:08 5.5055
    04/21/08 11:14:11 5.5055
    04/21/08 11:15:16 5.5055


    The column on the left is NOW() output and the one on the right is data corresponding to the given date and time. You may interpret the above data as snapshots taken at regular time intervals.


    Now what I want to do is make a chart with the above data. Where ever there is a break in the snapshots, represented by a blank line, I want the graph to jump to the next data point much as the way stock prices move from the closing price to the opening price on the next day. I am not able to figure how to do that. Since I want the x-axis to represent time, using scatter charts does not work since the intervals are not proportional to the time then.


    I would be grateful if someone would be able to help me. I need to use VBA.


    Regards

  • Re: Discontinuous Time Series Charts


    Hi PCI,


    Please have a look at the attached file. What I want is the connecting sloping lines between the 3 different snapshots, to be a vertical line with a break in the x-axis.


    I have plotted the data with what you suggested. What I am looking to implement is to put breaks in the x-axis. The graph should look something like the one with the grey background in the sheet, only that the times after the vertical lines should represent a jump.


    I hope I was able to explain my problem better now.


    Regards,

  • Re: Discontinuous Time Series Charts


    Thanks for the help.


    But I am looking for a jump in x-axis. What I want is pretty much what you have plotted but without the horizontal lines.


    So if the data points are:


    04/21/08 09:05:50 4.5055
    04/21/08 09:05:50 3.5055
    04/21/08 10:04:00 3.5055
    04/21/08 10:05:32 3.5055


    I want a jump from 04/21/08 09:05:50 to 04/21/08 10:04:00 without the horizontal line in the plot.


    The thing I am trying to get at is plotting something like inter-day stock prices of which I am taking a snapshot at regular intervals. Since there is no change in the value when the markets are closed, I do not want to waste graph space by plotting redundant over-night data. So I am looking to jump from the closing time to the opening time on the x-axis rather than have it continuous. Specifically the x-axis should be piecewise continuous from 9am to 3pm for day 1 followed by 9am data point for day 2.


    Regards,[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Aahh.. Junho thanks for replying. PCI suggested the same thing as well. But I do not want the horizontal lines in the graph. The x a-xis label should to the next value rather than the horizontal line in the graph!


    Regards,

  • Re: Discontinuous Time Series Charts


    Thanks Andy,


    But I do not think I have been able to make my self clear as yet. I want the region that I have marked in the graph that you sent me to be collapsed to a single point. The x-axis will hence not be continuous but broken.


    The data I have is something like this:


    04/21/08 09:04:30 4.5055
    04/21/08 09:04:48 4.5055
    04/21/08 09:04:49 4.5055
    04/21/08 09:04:49 4.5055
    04/21/08 09:05:50 4.5055


    04/21/08 10:04:00 3.5055
    04/21/08 10:05:32 3.5055
    04/21/08 10:05:40 3.5055
    04/21/08 10:06:48 3.5055
    04/21/08 10:07:51 3.5055


    04/21/08 11:07:59 5.5055
    04/21/08 11:09:00 5.5055
    04/21/08 11:10:01 5.5055
    04/21/08 11:11:02 5.5055
    04/21/08 11:13:08 5.5055
    04/21/08 11:14:11 5.5055
    04/21/08 11:15:16 5.5055


    I hence want a (almost) vertical line connecting the following points:


    (04/21/08 09:05:50, 4.5055) & (04/21/08 10:04:00, 3.5055),
    (04/21/08 10:07:51, 3.5055) & (04/21/08 11:07:59, 5.5055)


    This means that the next data point on the x-axis after 04/21/08 09:05:50 is 04/21/08 10:04:00 and not 04/21/08 09:05:51.


    I hope I have been able to explain myself.


    Regards,

  • Re: Discontinuous Time Series Charts


    Hi Andy,


    What I am doing is that I am taking snapshots of stock prices in a sheet at an specified interval. Since once the markets close, the price remains constant, I do not want that particular period to take up space in the graph. Hence I want to jump to the opening price of today from the closing price of yesterday by compressing the non-activity period (horizontal line) into a single point.


    Well even I thought that this might be tough to achieve. I kind of figured another way to work around what I want. I think you have already seen my post already regarding mapping the tick-mark labels to differnet values. That is precisely what I am trying to do is here. I am trying to map the discontinuous time values to continuous time vlaues and graph them on a chart, but this would give me wrong x-axis values for my data points. Hence I would need to re-lable the x-axis with the correct values for which I have the mapping. I don't know if such relabelling of tick-mark values is possible either.


    It would be wonderful if you could help me out.


    Regards,

  • Re: Discontinuous Time Series Charts


    I could use the line graph for sure, but my data points are not equally spaced. Considering that I am prettyy much going no where, I think I'll equally space all the data values and take off from there.


    Thanks a lot for your time and effort though.


    Regards,

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!