Solved

Add Horizontal line to stock chart

Posted on 2011-02-25
2
1,183 Views
Last Modified: 2012-05-11
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
Comment
Question by:waffe
2 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 300 total points
ID: 34985065
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
 

Author Comment

by:waffe
ID: 34985196
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question