Stock High-Low-Close Chart

[Tim]

Is it possible to produce a Hi-Lo-Close chart with reversed axes - ie with
the Hi-Lo bars horizontal rather than vertical? The normal HLC chart has
stocks listed along the x axis and price up the y axis. How would I switch
this around?
I want to display a series of stocks listed down the y axis and the hilo
price range given across the x-axis.
Thanks for any advice on this.

 

[John Mansfield]

Tim,

Assuming your data is first set up like this and cover the range A18:

High Close Low
Conoco 41 39.5 38
Exxon 42 40.5 39
Shell 41 39.5 38
Baker 40 38.5 37
Halliburton 39 37.5 36
Marathon 40 38.5 37
Diamond 40 38.5 37

Rearrange it to look like this - covering the range A10:E17:

Dummy X Dummy Y Close X Error + X Error -
36 1 39.5 1.5 1.5
36 2 40.5 1.5 1.5
36 3 39.5 1.5 1.5
36 4 38.5 1.5 1.5
36 5 37.5 1.5 1.5
36 6 38.5 1.5 1.5
36 7 38.5 1.5 1.5

The dummy X value of 36 is an arbitrary value based on the Low. In this
case I just took the minimum value of the Low range. This dummy X range will
eventually be used to add data labels to the Y axis. The + X Error is the
difference between the High and the Close. The - X Error is the difference
between the Close and the Low.

Select the range B11:B17. Activate the chart wizard and choose the XY
Scatter Chart option. Run through the prompts until Excel creates a basic
embedded XY Scatter chart. Double click on the X axis values. In the Format
Axis dialog box, set the minimum value to 36, the maximum to 44, and the
major and minor units to 1. Hit the OK button.

Now add a second series to the chart. To do so, click once on the chart.
In the Standard toolbar go to Chart -> Source Data -> Series -> Add. The X
values are the Close values (range C11:C17). The Y values are the dummy y
range (range B11:B17). Hit the OK button.

Now add positive and negative X error bars to the new second series. To do
so, double click on the points associated with the second series. In the
Format Data Series dialog box, go to the X Error Bars tab. In the error
amount options, choose Custom. For the + option, choose the range D1117.
For the – option, choose the range E11:E17. Hit the OK button.

To more accurately view the results, activate the chart again by clicking on
it once. Go to Chart -> Chart Options -> Gridlines and uncheck all of the
gridlines options.

Finally, you will need to use an XY data labeling tool to add the stock
names to the first series. You can use John Walkenbach’s Chart Tools add-in
or Rob Bovey’s XY Data Labeling add-in to perform this task. If you are
using John Walkenbach’s Chart Tools add-in, click once on the chart. In the
Standard Toolbar go to Chart -> JWalk Chart Tools -> Data Labels Tab. In the
Data Label Range box, choose the range A2:A8 and apply it to Series 1. Hit
the OK key.

At this point all you need to do is format the chart to your specifications.
Adjustments will be needed to the X axis maximum and minimum values so that
the actual data appears centered in the chart.

 

[Tim]

Many thanks John for rapid response.
Followed your guide well.
By adjusting Series1 x-values to all 0 and setting X-axis range 0 to 44, the
data labels (stock names) line up at zero.
Then setting Series1 Markers as "none" hides these, and left aligning
Series1 data Label Positions flips them out of the chart area - useful if
stocks have v.low "Low" values (ie near zero).
Good one. I would not have got this out without your guide.
Tim

 

[Jon Peltier]

John -

Been there, done that, wrote the web page:

http://peltiertech.com/Excel/Charts/HorizontalHLC.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Tim]

In fact not Jon.
I tried your solution which works for one stock followed over time, ie two
numeric variables in an XY chart.
Your solution does not appear to allow charting of several stocks at one
point in time - listing the stock names down the Y-axis.
This was the beauty of John M's second incremental numeric series and
applying stock name labels to these. Then left aligning these at 0.
Unless you can tweek your horizontal chart somehow?
Thanks for interest.
Tim

 

[Jon Peltier]

Hi Tim -

Doh! I guess I only read the first paragraph of your post. In that case I would have
done about the same as John proposed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Tim]

Thanks for the response.
Glad to hear I'm on the right track.
Might be worth updating your web-page to cover both charting scenarios:
i) following one stock over time
ii) comparing multiple stocks at one point in time.

Thanks again to John Mansfield for the solution.
Cheers
Tim

 

[xppuser]

hi john/tim,

windows xp pro sp2, office 2003 sp2,

i wanted to do the same horizoltal HLC chart. i followed john's
instructions. all seems to be ok until the part,

"Finally, you will need to use an XY data labeling tool to add the stock
names to the first series. You can use John Walkenbach’s Chart Tools add-in
or Rob Bovey’s XY Data Labeling add-in to perform this task. If you are
using John Walkenbach’s Chart Tools add-in, click once on the chart. In the
Standard Toolbar go to Chart -> JWalk Chart Tools -> Data Labels Tab. In the
Data Label Range box, choose the range A2:A8 and apply it to Series 1. Hit
the OK key."

well, neither Add-In seems to work i.e. i am left with the numerical y-axis
from 0 to 8. i just simply could not change the label from numerical to
(using your example) the oil companies. i installed the add-ins correctly
because both menus came up but when i highlighted the range A2:A8 and applied
it to Series 1 -> OK, nothing happened!

any advice/help would be appreciated.

thank you,
jes