Plot XY Vertical Line on Secondary X Axis

[wsnyder]

Hi all,

Using M365 (frmr O365)

I have a line chart with multi line x axis J,F,M,A, M, J, J, A, S, O, N, D | Year
Now I would like to plot vertical lines at 12/31/2019 (Data Element #108 and Max Date (7/15/2020 as of today - Data Element #115)

I tried using Date and Data Element # on Secondary Axis but neither are plotting the XY lines in the correct position

Any ideas?

Thanks
lou

 

[Jon Peltier]

The stock data must be monthly, right? The X values for your lines are slightly off. The first stock value is plotted in the middle of the first month, so it's really at position 0.5. So the first vertical line is at 107.5 (not 108) and the second is at 114.5 (not 115).

Then you need to set your secondary horizontal axis scale. It should have a minimum of 0 and a maximum of 120 (not the automatic values of 107 and 116). Then of course, you should hide the secondary horizontal axis (format it to use no markers and no line). You can hide the secondary vertical axis, or if you keep it visible, use the same number format as the primary vertical axis.

 

[wsnyder]

Thanks Jon,

It's looking much better.
I added some markers so I could add data labels for the values for 12/31/2019 as well as current (7/16/2020 here)
The markers do not quite align with the intersection of the line chart and the vertical lines.
I played around with the values for the vertical lines a bit - but nothing quite lines everything up nicely.

Any thoughts?

Thanks,
lou

 

[Jon Peltier]

You should add another XY series to the chart, using the same X values as the secondary axis series that provide the vertical lines, and Y values corresponding to the line chart series on the primary axis. Leave the secondary axis in the chart but hidden. Delete the secondary Y axis, and the secondary axis points will be plotted along the primary Y axis. Alternatively, keep the secondary Y and scale it to match the primary Y axis.

 

[wsnyder]

Hi,

I took a new approach to my Stock Chart.
I pulled data using the new Stockhistory function

I then created a table from 1/1/2011 - 12/31/2020 and added a formula to return the value from Stockhistory, if no value, then NA().
I then added several series to a chart

One of the Series (Scatter with Straight Lines) appears to have a ghost mark of some type that I cannot remove
I have tried formatting the Data Series several times but I can't get the image to go away.

I tried removing and re-adding the series. I still see the ghost mark.
Circled red below

Thanks,
lou

 

[Jon Peltier]

Could you show what the data looks like, at least in the 2019-2020 range?
Could you indicate what series there are, what data they use, what types they are, and which axes they are on?

 

[wsnyder]

Thanks Jon,

I uploaded the workbook to Google Drive
Stock Tracker

Thanks,
lou