Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1340
  • Last Modified:

Add Horizontal line to stock chart

Hi,

I am using the the stock chart and I would like there to be a horizontal line across the chart with a y-axis of the opening price. In the end I want this to be in VBA but I think if someone can show how to do this manually I should be able to put it in loop of data that will produce many charts with a horizontal line starting at the day opening.

I have tried to add a new series to the chart but I'm using the Candle stick stock chart and the new series comes out as a vertical not horizontal line. I read about using a secondary horizontal axis but all the tutorials I find are in Excel 2003 and they donlt line up well with Excel 2007.

Any thoughts on how this is done?

I found this link on charts with horizontal lines but again they are in 2003 and I'm using a candle stick stock chart not a scatter chart.
http://www.tushar-mehta.com/excel/charts/straight_lines/

Thanks,
waffe
0
waffe
Asked:
waffe
1 Solution
 
dlmilleCommented:
Ok - I've never had to do this with a stock chart, but have had this type of problem, before.  for stock charts, you might look at the below utility, otherwise, I propose a "quick fix"

Utility:
For a price, there are utilities that may help : http://peltiertech.com/Excel/Charts/StockChartPlus.html

Quick Fix:
Apparently one can't combine a secondary axis line with a stock chart, so a utility like the above may prove useful ($$).  However, in a pinch, when I've had to do this, I create two charts, both with NO fill in the plot area, and I overlay one over the other, with the exact same scale settings.

See attached demonstration.  You'll want to have your VBA set the chart min/max to be the same on both charts.  I've done this quite a bit with other combinations I had to do but not stock charts...

Good luck!

Dave
sample-horiz-line-with-stock-cha.xlsx
0
 
waffeAuthor Commented:
Thanks, that will work perfectly!

I found that I could move the y-axis to the start of each opening but your concept will allow for more control over how the data is viewed.


0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now