Plot XY Vertical Line on Secondary X Axis

wsnyder

New Member
Joined
Sep 23, 2018
Messages
29
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

stock.png
stock.png
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,933
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Sep 23, 2018
Messages
29
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

stock2.png
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,933
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Sep 23, 2018
Messages
29

ADVERTISEMENT

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

stock3.png
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,933
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,776
Messages
5,574,168
Members
412,574
Latest member
shadowfighter666
Top