Stock High-Low-Close Chart

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.

Tim,

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

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 D11:D17.
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.

----
Regards,
John Mansfield
http://www.pdbook.com



"Tim" wrote:

> 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.
>

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

"John Mansfield" wrote:

> Tim,
>
> Assuming your data is first set up like this and cover the range A1:D8:
>
> 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 D11:D17.
> 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.
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
>
> "Tim" wrote:
>
> > 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 -

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/
_______

John Mansfield wrote:

> Tim,
>
> Assuming your data is first set up like this and cover the range A1:D8:
>
> 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 D11:D17.
> 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.
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
>
> "Tim" wrote:
>
>
>>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.
>>

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" wrote:

> 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/
> _______
>
> John Mansfield wrote:
>
> > Tim,
> >
> > Assuming your data is first set up like this and cover the range A1:D8:
> >
> > 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 D11:D17.
> > 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.
> >
> > ----
> > Regards,
> > John Mansfield
> > http://www.pdbook.com
> >
> >
> >
> > "Tim" wrote:
> >
> >
> >>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.
> >>
>
>

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 wrote:

> 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" wrote:
>
>
>>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/
>>_______
>>
>>John Mansfield wrote:
>>
>>
>>>Tim,
>>>
>>>Assuming your data is first set up like this and cover the range A1:D8:
>>>
>>> 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 D11:D17.
>>>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.
>>>
>>>----
>>>Regards,
>>>John Mansfield
>>>http://www.pdbook.com
>>>
>>>
>>>
>>>"Tim" wrote:
>>>
>>>
>>>
>>>>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.
>>>>
>>
>>

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

"Jon Peltier" wrote:

> 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 wrote:
>
> > 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" wrote:
> >
> >
> >>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/
> >>_______
> >>
> >>John Mansfield wrote:
> >>
> >>
> >>>Tim,
> >>>
> >>>Assuming your data is first set up like this and cover the range A1:D8:
> >>>
> >>> 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 D11:D17.
> >>>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.
> >>>
> >>>----
> >>>Regards,
> >>>John Mansfield
> >>>http://www.pdbook.com
> >>>
> >>>
> >>>
> >>>"Tim" wrote:
> >>>
> >>>
> >>>
> >>>>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.
> >>>>
> >>
> >>
>
>