Add line to Stock Chart (XL 97 )

Topic

New Reply

Just discovered the Stock Chart option for displaying my data. It’s 99.5% of what I need to display the min, max, and avg +/- Standard Deviation of my data. The crowning touch would be to add a line connecting all the average values for each of the data points. So far I have been unsuccessful.

Is there a way to overlay a line (new data series) onto a stock chart?

Thx.

Reply | Quote

Replies

Can you attach an example?

Reply | Quote

Done.

You will see the range that defines the chart. What I would like to do is to add the data from row 6 (avg), and have it appear as a line overlaying the stock chart.

I kinda get the feeling that it’s not possible, but was hoping that somebody has found a way to do it.

Thx.

Reply | Quote

First off, I’m on Office2000, but I think it is still applicable to 97. The problem is that your chart only allows 4 sets of values (Open-High-Low-Close) What you need to do is use *Avg* to set your trendline, here again, there isn’t a neat linear solution from marker to marker.

2 ways to do it as follows; (a or
Step 1a: Set focus on the chart area and click from the menubar on CHART | ADD DATA…, Or;
Step 1b: Right click within the chart area and select Source Data…

Step 2a: add the cells in the *Avg* row, in this case [=Summary!$A$6:$O$6] and click OK, Or,
Step 2b: From the *DATA RANGE* tab, change [=Summary!$A$5:$O$5,Summary!$A$8:$O$11] to include the *Avg* row by changing *$O$5* to *$O$6* so it looks like this [=Summary!$A$5:$O$6,Summary!$A$8:$O$11]

Step 3: You will see the chart change (it drops one of the original series and adds the *Avg* series). To reset this, hover over any one of the *Up-Bars* then check each of the 4 series point, you will then find the *Avg* series (Probably the bottom of the *barrel*).

Step 4: Right-Click on that *Avg* series and select FORMAT DATA SERIES…

Step 5: Select the *AXIS* tab and check the radio-button for the *SECONDARY AXIS* then click on OK

Step 6: That series has now moved off the primary axis and is keyed to the secondary. In 2000, it gave me a range of 0 to 40 in increments of 5. Now we need to change those values to fit the primary of 0 to 40 in increments of 10

Step 7: Right-Click the Secondary Axis and select FORMAT AXIS…

Step 8: Select the *SCALE* tab and change the values to match that of the primary. You can also do other formatting changes to dress up this axis. Now you’re ready to drop in the trendline.

Step 9: Click from the menubar on CHART | ADD TRENDLINE…, As you will see from my example, I put added two trendlines, the red line *Line 1* is a *Moving Average* (it only starts at the second or higher value) and the blue one, *Line 2* is a *Polynomial*.

Step 10: Now right-click and select CHART TYPE…, select the *CUSTOM TYPES* tab; Select from the *USER-DEFINED* radio-button and add this chart as a new custom chart type. In this case, I named it *Open-High-Low-Close+Average*.

NOTE; I tested it by deleting the present chart and inserting a chart. The wizard walked me thru the usual stuff, but didn’t allow me to define which values would key to the primary vs the secondary axis. That meant more time expended to rekey series/values to repeat the desired output. Due to this problem of not keeping the orginal series and configuartion, I would imagine there would be more tinkering involved before I would attempt to automate this chart. I think I would try to tie this chart to a pivot table and let the pivot table do all the automation, i.e. regularly scheduled reports.

Also, now that the second axis has been introduced to the chart, one could experiment some more and lay in a line without using the trendline function. If I have time, I may fool around in this direction and see what happens.

regards
AJF

Reply | Quote

WOW!!!!!

AJF – you have gone above and beyond the call of duty! Thanks very much. The key to your trick was how to get that average onto the secondary axis. I had been able to get it onto the graph, but as you know, adding the data series dropped one of the other four. I didn’t know how to recover all five series. Your trick of hover and right-click was just the ticket!!

Thanks again.

Reply | Quote