Stock Charts in Excel 2007

I wrote in Stock Chart with Added Series how to add extra series to an Excel candlestick stock chart. The procedure was written while using Excel 97, and it has worked well until Excel 2007 forced some small changes to the protocol. The tutorial in that article was long and tedious, and could probably be made shorter. In any case, I’ll review the technique here for Excel 2003 and then show how Excel 2007 differs.

The data I used is shown below. A stock chart needs a column of dates plus four columns of price data. The price data has to begin with Open and end with Close. High and Low can be in any order in between, but conventionally High is listed first. I have included two index columns, one within the same range as the stock prices, one in a much higher range.

I have an extra row above and below the data. I prefer to have my vertical axis cross on a date, not between dates. This is fine, but the first and last candlestick in the chart are truncated (even if there are more dates on either side) unless you explicitly include an extra date before and after the data. Also, I’ve hidden rows 12 through 35 so the data doesn’t take make you scroll down two screens.

Excel 2003 Stock Charts

Select the first five columns and create a stock chart. I’ve applied fill colors to and removed the borders from the up-down bars (I always forget whether the white and black bars mean up and down, or vice versa).

If you want you can change the width of the up-down bars. Format any of the four series: on the Options tab of the Format Series dialog, you can change the Gap Width. Note also that you can add or remove the high-low lines and up-down bars.

Add the index data to the chart. The easiest way is to select the chart area or plot area of the chart, find the colored highlights indicating the chart’s source data range (see below), and resizing these ranges with the mouse. You could also copy the added data and paste in into the chart (I prefer paste special), or use the Source Data dialog to define a new series.

When you widen the blue outline to the right, the green outline also widens to the right. I stretched it by one column, which added the Index1 data to the chart. We can’t see the series itself, but we do see a profound distortion of the up-down bars.

Below I have selected the Index1 series and changed its chart type to Line. As you will recall from reading Stock Charts and Other Line Chart Tricks, a candlestick chart is just a dressed up line chart, with the series lines hidden, and high-low lines and up-down bars added to the chart. The Format Series dialog above is a reminder of this fact.

The up-down bars are messed up because the Index1 series is just another line series in the chart. Since it’s the last one in the plot order, it becomes the close end of the up-down bars.

Fix the Up-Down Bars Using the Secondary Axis

There are two ways to fix this mess. The first way is to exile the Index1 line series to the secondary axis.

If we were plotting Index2, with its values so much greater than those of the stock prices, we would fine tune the secondary axis scale and call it a day.

However, Index1 falls within the same range as the stock prices, and it would be best to use the same axis scale. You can laboriously format the two axes to align their scales, then whenever the data changed, laboriously reformat the scales.

Or you can remove the secondary axis altogether, which forces the secondary axis series to use the primary axis. Remove the axis by selecting and deleting it, or by going to the Chart menu > Chart Options > Axes tab, and unchecking it.

The up-down bars and high-low lines apply to all series within a chart group. A chart group is the group of all series of the same type (i.e., line) and on the same axis (primary). Since the Index1 series in in the secondary chart group, it no longer affects the primary chart group’s features.

This technique is pretty clean, except it ties up the secondary axis, preventing its use for plotting of other data (e.g., Volume) with different value ranges.

Fix the Up-Down Bars Using an XY Type Series

The second way to fix the messy up-down bars created by adding the Index1 series is to select the series and change its chart type to XY. Excel automatically assigns the XY series to the secondary axis, and we are given two misaligned axes.

If you reassign the XY series to the primary axis, it uses the existing axes, even though the axes were defined by the stock chart (line chart) series.

Using an XY chart has the benefit of putting all of this data onto the primary axis. This is useful if there is more data, such as Volume, that you would want to plot on the secondary axis.

Another benefit to using an XY series is that you can plot data with different dates than used by the stock (line chart) data. All line type series in the chart group have to use the same X values.

Excel 2007 Stock Charts

The Excel 2007 stock chart is constructed much like the Excel 2003 version.

Adding the Index1 series is accomplished the same way, by stretching the highlighting around the chart’s source data range. You can also copy and paste the data, or use the Select Data dialog, but I find this dialog much more confusing than the Excel 2003 version. However, the result shows the same distorted up-down bars as we saw in 2003.

The Index1 series can be changed to a line chart type series just like in 2003.

Fix the Up-Down Bars Using the Secondary Axis

Index1 can be assigned to the secondary axis, just like in 2003.

The secondary axis can be removed, just like in 2003.

As before, the first technique is good if you don’t need to use the secondary axis for anything else.

Fix the Up-Down Bars Using an XY Type Series

When we try the second technique, changing the type of the Index1 series to XY, we have a rude surprise. Apparently stock charts in Excel 2007 don’t realize they are just gussied up line charts. Whereas we could combine XY and Stock types in a single chart in Excel 2003, Excel 2007 disallows this combination.

Once you get over your initial shock, remember that a line chart can be dressed up with up-down bars and high-low lines to become a stock chart, much like Cinderella can be dressed up to become a princess.

Convert the chart from stock chart to line chart.

The steps to add up-down bars and high-low lines have been changed drastically in Excel 2007. These features are no longer conveniently formatted in the Format Series dialog. Instead you need to navigate through the contextual Chart Tools tabs of the ribbon. You can find the controls off to the right end of the Layout tab.

The gap width control for the up-down bars has been moved as well. In fact, as far as I can tell it has been completely removed from the user interface. But in a moment I’ll show you how to change this with a little bit of VBA.

Here’s our line chart with the stock chart features:

Hide the series lines on all but the Index1 series, and it looks familiar.

Change the chart type of Index1 to XY.

Reassign Index1 to the primary axis.

There is our combination XY-Stock chart. Using the XY series allows plotting of data with different dates than the stock prices, and leaves free the secondary axis for other data.

Changing the Gap Width of Up-Down Bars in Excel 2007

To change the gap width of the up-down bars, you need to apply a little VBA. But I’m going to try to make it less scary. First, select the up or down bars, then click on the Chart Tools > Layout or Format tab, and look at the Current Selection box in the top left of the ribbon.

It says Up-Bars 1, and the 1 refers to Chart Group 1. Open the Visual Basic Editor (VBE) by pressing Alt+F11, open the Immediate Window by pressing Ctrl+G, type the following line, and press Enter. Note that ChartGroups(1) refers to the chart group index we found above. Experiment with the gap width value until the bars look good. For this particular chart I didn’t have to change the gap width, but I’ve had others which required it.

Related Posts:

• Candlestick Alternative: Individually Colored Up-Down Bars
• High-Low Line Alternatives in Excel Charts
• Simple Box Plots
• Excel Waterfall Charts (Bridge Charts)
• Line-XY Combination Charts
• OHLC Stock Chart with Tick Marks in Microsoft Excel