Stock Chart with Left Tick Marker for Open Series.

Excel Dashboards by ExcelUser.com - Instructional eBook and Ready-to-Use Workbooks.

When an Open-High-Low-Close chart is created in Excel, the result is a candlestick chart, shown below. The bar spans the range from open to close and the drop lines show the low and high extremes, with a white fill indicating an increase and a black fill indicating a decrease. Personally I find this confusing, because I have to read the data to recall whether white fill means up or down. Stock charts in the newspaper and on line show a tick mark to the left of the low-high drop lines for open and one to the right for close. Microsoft has seen fit to only provide right-handed markers for stock closing prices. A common question people have is "How can I make the tick mark go to the left for the opening price?"

This describes my procedure for left-handed opening price tick marks. First, create a standard HLC Stock chart with the High-Low-Close data.

Now add the Open series as an extra Line or XY series, as described on Stock Charts with Added Series, elsewhere on this web site. This series is denoted by the red crosses below.

Now we'll construct a custom marker for this added series. You can make any shape, copy it, select the series, then paste, and the shape you had selected becomes the series marker style. Make a left leaning marker by drawing two rectangles in the worksheet, side by side. Format the left rectangle with a black fill and no border, the right one with no fill and no border. (The invisible box on the right balances the marker.) Group the rectangles, then shrink them to a very small size.

Black and white rectangles side-by-side:

Black and transparent rectangles side-by-side:

Black and transparent rectangles grouped and shrunk to size:

Copy this custom shape, select the Open series, and paste (Ctrl-V). The Open series takes the custom shape as its marker.

You can move the Open series to the top of the Legend by changing the '4' in the series definition formula:

=SERIES('Stock Chart'!$B$1,'Stock Chart'!$A$2:$A$23,'Stock Chart'!$B$2:$B$23,4)

to a '1':

=SERIES('Stock Chart'!$B$1,'Stock Chart'!$A$2:$A$23,'Stock Chart'!$B$2:$B$23,1)

This moves the series from fourth to first in the plot order.

Peltier Technical Services, Inc.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile