Books at Amazon.com
Stock Charts and Other Line Chart Tricks
This page explains how to use Excel's candlestick-style OHLC stock charts, how to make your own using built-in line chart features (high-low lines and up-down bars), and how to combine line chart and XY chart series to produce stock charts with open and close tickmarks, so you can dispense with the candlestick chart's up-down bars altogether.
A Proper Stock Data Table
As with all Excel charts and many other Excel features, it is important to get the data right. Five minutes spent with the data will save five hours down the road. A stitch in time saves nine.
The following table exemplifies good stock chart data. The table does not have to start in cell A1, but wherever it starts, it should contain no blank rows or columns. The first row should contain clear labels. Dates are shown here in the first column, but this could be any type of category data (such as company names), or even absent (not what I would consider a best practice). If the first column does not include data which are obviously dates or text, the label in cell A1 should be deleted. A blank cell in the top left of a chart's source data range helps Excel to parse the range appropriately, by signifying that the incomplete top row and left column are different from the rest.
The Y values should be in Open-High-Low-Close (OHLC) order. Or in OLHC order. Most important: the Open data should be first and the Close data last, because up-down bars compare the first and last line series at a given category or date. High and Low lines connect the high and low values among all of the line series at a given category or date, so the order of the High and Low columns is less critical as long as they don't interfere with the Open and Close series. When you add a series such as a market index or a moving average to a stock chart, you should add it as an XY series or on the secondary axis so it doesn't interfere with the high-low lines or up-down bars; see Stock Charts with Added Series for the protocol.
The Tick data in column F is used in OHLC Chart with Open and Close Ticks below, where it is used as data for the Open and Close tick marks.
Standard OHLC or Candlestick Stock Chart
Select the five columns of data and start the chart wizard. Select the Stock chart type in the left list in step 1 of the wizard, and Excel will automatically select the OHLC subtype in the right panel. In case you forget my instructions above, the wizard gives you a hint about the order of the four required series.
The resulting OHLC chart is shown below. You can make slight adjustments to get a vertical gridline between each week's data. Double click on the date axis, and on the Scale tab, set the Base Unit to Days and Major Unit to 7 Days, and set the Minimum to Sunday's date. Then on the Chart menu, select Chart Options, click on the Gridlines tab, and check Major Category (X) Axis Gridlines.
That's pretty good. The default candlestick colors are White for Up (Close > Open) and Black for Down (Close < Open). I always forget this complex color code, so I prefer Red and Green; which I change by double clicking in turn on the up and down bars. I also like to change the gap width to 50 or 100: double click on a line series (not on the up or down bars as you'd expect), click on the Options tab, and change the value.
Some prefer not to leave a gap for weekends. To satisfy this preference, the time scale X axis needs to be converted to a regular category axis. On the Chart menu, select Chart Options, click on the Axes tab, and under Category (X) Axis, change from Automatic to Category. To maintain the weekly gridlines, make sure the first data point is on a Monday (or pad the data with rows that contain data only in the Date column). Double click the X axis, and on the Scale tab, set the Number of Categories between Tick Marks to 5.
Handmade Stock Chart (Line Chart)
Select the five columns of data and start the chart wizard. Select the Line chart type in the left list in step 1 of the wizard, and the plain line without markers subtype, top left in the right panel. Excel should notice the dates in the first column, and use that column for the category values. If not, delete the chart, clear the top left cell of the data range, and go back to the chart wizard.
Adjust the chart to get a vertical gridline between each week's data. Double click on the date axis, and on the Scale tab, set the Base Unit to Days and Major Unit to 7 Days, and set the Minimum to Sunday's date. Then on the Chart menu, selecte Chart Options, click on the Gridlines tab, and check Major Category (X) Axis Gridlines.
To get High-Low Lines, double click any of the line series in the chart, click on the Options tab, and select High-Low Lines. High-Low Lines connect the highest and lowest line series in an axis group (High and Low in Open-High-Low-Close chart).
To get Up-Down Bars, double click any of the line series in the chart, click on the Options tab, and select Up-Down Bars. Up-Down Bars compare first and last line series in axis group (Open and Close in Open-High-Low-Close chart). Default colors are White for Up (Close > Open) and Black for Down (Close < Open).
This line chart has both high-low lines and up-down bars. Excel now considers this a stock chart: select the chart, go to Chart Type on the Chart menu, and the Stock Chart type is selected in the left hand list.
Now hide the series lines to clean up the chart. Double click on one line series, and on the Patterns tab of the dialog, select None for Line (None should already be selected for Marker). Select each of the other series and press the F4 function key, shortcut for Repeat Last Action.
This chart looks just like the default OHLC chart, so if this is what you're after, just select the built-in OHLC type in the Chart Wizard. But we didn't waste your time, because you've just learned about High-Low Lines and Up-Down Bars, and that a stock chart is just a fancy Excel line chart.
OHLC Chart with Open and Close Ticks
Start with the OHLC line chart from the previous section.
Line chart series support only vertical error bars, but XY series can have vertical and horizontal error bars. To allow us left and right tick marks for the Open and Close series, we will convert these series from Line to XY type, and apply negative and positive X error bars.
Select the Open or Close series, and choose Chart Type from the Chart menu. Select XY from the left hand list; I find the line without markers option to be easier to use in this case. Excel puts the new XY series onto the secondary axes, but you'll fix that in a minute. Select the other series you need to change, and press the F4 function key, shortcut for Repeat Last Action.
Move the Open and Close XY series to the primary axis. Double click on one of the series, and on the Axis tab of the dialog, select Primary. Select the other series, and press the F4 function key, shortcut for Repeat Last Action. I know I'm repeating myself, but how else will you learn this useful shortcut.
The chart now looks nearly identical to the OHLC line chart that we started with. The difference is that the order of entries in the legend have changed. All four series are in the primary axis group, but they are no longer in the same chart group. A chart group is the group of all series on the same axis of the same type. The original chart had only one chart group, consisting of four line series on the primary axis. The new chart has two chart groups, one with two line series on the primary axis, the other with two XY series on the primary axis. Excel's legend always lists series first by axis, then by axis group, and finally by plot order. The High and Low line series are thus listed first, even though Open comes first in the plot order. This is how Excel manages its legend, and the user cannot change this.
Add High-Low Lines: double click the High or Low line series, click on the Options tab, and select High-Low Lines.
Add error bars to the Open and Close series (see for more about Excel's error bars). Double click the Open series and click on the X Error Bars tab. Click on the Minus icon, then click in the Custom (-) box and select cell F2. You could have simply entered a fixed value in the Fixed Value box, but linking to a cell makes it easy to fine-tune the error bar appearance. Repeat the process to add Plus error bars based on cell F2 to the Close series.
Now hide the series lines. Double click on the lines of one series, and on the Patterns tab of the dialog, select None for Line (None should already be selected for Marker). Select each of the other series and press the F4 function key, shortcut for ... Anyone?? The F4 shortcut works here even though some series are Line series and some are XY series.
Finally, fix up the error bars. Double click on one set of error bars, and on the Patterns tab, select the option without end caps. You might also use a thicker line for the ticks. In any case, I have found that the end cap setting is not completely stable unless it is the last thing you select before clicking the OK button. Select the other set of error bars, and press F4 to format it the same way.
Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.