Posts by [sHaQ]

    Re: Tertiary Y Axis


    Hi,


    That page does not have any data about making tertiary axis. Jon has added various other techniques to plot multiple series but the tertiary axis part has been removed from the page I guess.


    Regards,

    Hi,


    I am looking to add a tertiary Y-axis to my chart. I was searching through the internet and came across Jon Peltier's page:


    http://www.peltiertech.com/Excel/Charts/TertiaryAxis.html


    However, I think he nomore has the process of adding a tertiary axis on the page. It would be nice if someone could point to the resource if it is available anywhere else. Any other pointers would also be appreciated.


    Also would it be possible to hide the markers and the labels of the tertiary axis?


    Thanks,

    Hi,


    I am trying to manually format a few graphs in my sheet. I, however, leave the major units of the axes to be automatically calculated. The code that I use is given below:


    Code
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = Range("Min").Value
        .MaximumScale = Range("Max").Value
    End With


    Now what this does is that it sets the maximum of the scale exactly equal to maximum of the range being plotted and hence a few parts of my graph overlap with the border of the plot area.


    Is there any way in which I can round the max and min scale of y-axis to the next highest and lowest major unit respectively or may be add and subtract a value proportional to the major unit of the y-axis


    I cannot add a constant in the code given above since I that does not suite my requirements.


    Thanks a lot.

    Hey,


    I have a small query:


    I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.


    I would be grateful if someone 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,

    Re: Map Tick Mark Labels


    Sure, but I tried this after I had posted my initial query. Sorry about that.


    No, the spacing is not even. Hence I need a scatter plot. I am attaching a part of my data file that I am currently using to plot my data points and the mapping. I want to plot the mapped value vs. the Y-values but I need to label the x-axis tick marks with the actual time values to get the correct graph.


    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: Map Tick Mark Labels


    Aah that wont work since I want exaclty the points on the y axis to get the new labels. Let me explain again.


    For example, the point (1,5) should read (5,5) though it still (1,5) on the chart. This is done by only modifying the x-tick mark labels and not the point itself.


    I read through John Peltiers page http://peltiertech.com/Excel/Charts/ArbitraryAxis.html to achieve this. So what I did there was that I plot the graph


    X Y
    1 5
    2 6
    3 10
    4 15
    5 8



    I further use dummy Y and plot the line


    X Y
    1 0
    2 0
    3 0
    4 0
    5 0


    I then remove the x-labels and then use XY Chart labeller to label the new series as:


    5
    7
    9
    11
    13


    Hence, I have the point which was originally (1,5) being read as (5,5) now and so on.


    However, the problem that I am facing here is that since my ranges are dynamic, I cannot fix the value of dummy Y to zero, since I want the chart to be scaled later for best fit. The new labels hence would go out of the plot area.


    I could have tried to set the dummy Y to the minimum value of the Y-axis but then the tick marks would not be equally spaced and it would be too dirty to format the chart manually to make it look good.


    It would be a lot easier to label the 1 tick on the x-axis as 5 and so on and let excel take care of the formatting.


    Regads,

    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


    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,

    Hi,


    Is there any way in which I can map the tick mark labels on either the x-axis or the y-axis to different values other than the ones that are being plotted for.


    For example, lets say the data is:


    1 5
    2 6
    3 10
    4 15
    5 8


    I want to plot these in a chart but instead of displaying 1,2,3,4,5 on the x-axis, I want to display a different column, say,


    5
    7
    9
    11
    13


    The answer is not as simple as plotting the required x-values with the y-values. I have just used a simplistic example but the thing I am trying to do has dependencies involved so I cannot simply plot the chart with the required column.


    I would be thankful if you could help me out


    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,

    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