Microsoft Excel has some built-in chart types for displaying how stock prices vary during a trading period. This is usually a day, but it could also show weeks, months, or years, or even intervals of minutes or hours during a trading day. These are the prices at open and close of the market as well as the high and low while the market is open. For example, the Open-High-Low-Close (OHLC) candlestick chart below was designed to show the high and low for a trading period as the top and bottom of the vertical line for each period, and the change from open to close as a rectangle, filled white for an increasing value or black for a decreasing value.
To make this chart type, you need five columns of data, in order Date (or other label of the trading period), Open, High, Low, and Close. If you omit the dates or labels, Excel will build the chart and label the periods 1, 2, 3, etc. Select the data, then go to the Insert tab > Other Charts, and pick the second stock chart type.
I guess I’m too dumb to remember such a straightforward convention, but I always have to check whether the white or black bars refer to increasing or decreasing values. So I like to color the bars red and green, like so:
The problem is that the colored bars do not photocopy well in black and white. Also, while the red and green colors in Excel’s default theme are distinguishable by most people with color vision deficiencies, I can’t imagine the colors they see are as easy to distinguish as red and green, or as black and white, for that matter.
Stock Charts with Open and Close Tickmarks
The best approach then is to use the OHLC chart style found in newspapers. These have a vertical line to indicate the spread from low to high during a trading period, with a small tickmark to the left indicating the opening price and a small tickmark to the right at the closing price. Excel has a High-Low-Close stock chart with the vertical line and a single tickmark, to the right, to signify closing price. But no leftward tickmark.
So we must resort to some smoke and mirrors. Actually, some short horizontal error bars will serve as our tickmarks.
Start with the data arranged in the same way, but create a line chart instead of a stock chart.
Select any series in the chart, go to the Chart Tools > Layout tab, click on Lines, and choose High-Low Lines.
Format the High and Low series to remove the line color.
Change the Open and Close series to XY: right click on each series in turn, select Change Chart Type, and choose an XY type.
These XY points don’t line up and there are misaligned secondary axes, but this is simple to fix. Format both XY series to assign them to the primary axis.
Format the Open and Close series to hide the markers.
Select the Open and Close series in turn, and add error bars. There are no markers to click on, but if you select the chart you can find the Chart Element selector on the Chart Tools > Layout and Format tabs. The Chart Element selector and the pop-up tool tip are highlighted with a red border in this screen shot. This is a handy tool to add to your arsenal.
Use the Chart Element selector to select the Open and Close series, one at a time. (You could also select a visible series or another visible chart element, and use the up or down arrows to cycle through the chart’s elements until the desired series is selected.)
With the series selected, go to the Chart Tools > Layout tab, select Error Bars, and choose one of the options. I simply used the Standard Error option, since it was easier to add everything and adjust or remove the parts as needed. Repeat for both Open and Close.
Reminds me of a swarm of starfighters from Star Wars.
Select the vertical (Y) error bars for each series. You may want to use the Chart Element selector for this.
Select the vertical (Y) error bars, and remove them by pressing the Delete key.
Select the “Open” X Error Bars, and press Ctrl+1 to open the Format dialog. Select the Minus direction and the No Cap end style, and enter a fixed value of 0.4.
Select the “Close” X Error Bars, and press Ctrl+1 to open the Format dialog. Select the Plus direction and the No Cap end style, and enter a fixed value of 0.4.
The vertical lines are one unit (one trading period) apart, so 0.4 reaches almost halfway to the next vertical line on either side.
This style of OHLC chart works well in black and white, and after the first time you go through the protocol, it doesn’t take too long to set up.