Problem

You need to create stock charts like the High-Low-Close or candlestick charts that Excel provides, but ExcelApplication does not have an enumeration for these charts.

Solution

Introduction to Stock Charts

Excel can create four kinds of charts intended for analyzing stock data. These are often referred to as high-low-close or candlestick charts because of their distinctive shape. These charts can be easily created with the Chart Wizard in Excel.

It is possible to use these charts with ExcelTemplate as you would any other chart. While ExcelApplication contains enumerations for many kinds of charts it does not have one for stock charts. Because of this, they cannot be created directly. In this article I’ll explain how to use a line chart and some special formatting to create charts that look exactly like each type of stock chart provided by Excel.

Setting up the data

Stock charts require very specific data in a very specific order. If you look at the names of the charts in Excel, you’ll note that each of them is actually a list of the data that chart needs, in the order that chart needs it. The four kinds of charts are High-Low-Close, High-Low-Open-Close, Volume-High-Low-Close and Volume-High-Low-Open-Close. As you can see, there is significant overlap and the data always appears in the same order; only the presence or absence of Volume and Open Data is different. For all of the following examples, we’ll use the same data pictured below:

For simplicity, we’ll create areas for each section of data and name them volumeArea, openArea, highArea, lowArea and closeArea. Now that we have that, we can start creating stock charts.

A Basic High-Low-Close Chart

The simplest chart to create is a High-Low-Close chart. This chart displays the high and low values for a stock as a vertical line, with a tick mark at the point where the stock closed for the day. To begin creating this chart, we’ll follow a formula that will remain the same for all of the other stock charts.

First, create a Line.StandardLine chart. Anchor it wherever you like, then add the series you want for this chart. It is very important that you add the series to the chart in the same order that they are listed in the chart name. In this case, that means that first we create the high series, then the low one, then the close one. From this point on the steps to create each chart will be similar, and contain significant overlap, but just different enough that we should take them one at a time.

To create the high/low lines, set ShowHighLowLines to true in one of the Series from this chart. Then, to create the tick marks showing the close values, set the ShapeType of the close series DataPointMarkers to DowJones, and set their ForegroundColor to black.

Finally, remove all the visual elements of a regular line chart that we don’t want. Running the code below will produce the chart just below that. To make the screenshots small enough to fit on the page I took the liberty of resizing the charts with the Chart.Width and Chart.Height properties. This code is not shown, and does not effect the fundamentals of the chart.

// This anchor will put the chart just to the right of the data shown above.
Anchor a = ws.CreateAnchor(0, 7, 0, 0);
Chart c = ws.Charts.CreateChart(ChartType.Line.StandardLine, a);

// Create the series *in the same order* as they're listed
// in the name of the chart
Series high = c.SeriesCollection.CreateSeries(highArea);
Series low = c.SeriesCollection.CreateSeries(lowArea);
Series close = c.SeriesCollection.CreateSeries(closeArea);

// Set the name of each series so the legend shows correctly.
high.Name = "High";
low.Name = "Low";
close.Name = "Close";

// This adds the vertical lines
high.SettingsLineAreaScatter.ShowHighLowLines = true;

// This adds the little horizontal markers
close.DataPointMarker.MarkerType = DataPointMarker.ShapeType.DowJones;
close.DataPointMarker.ForegroundColor = Color.SystemColor.Black;

// This makes all the extra stuff in a line chart
invisible high.Line.Visible = false;
high.DataPointMarker.Visible = false;
low.Line.Visible = false;
low.DataPointMarker.Visible = false;
close.Line.Visible = false;


Creating Up/Down Bars for Fun and Profit: Open-High-Low-Close Charts

The next most complicated type of chart is the Open-High-Low-Close chart. This chart replaces the single tick mark of the High-Low-Close chart with a bar showing the difference between opening and closing prices of a stock. If the stock closed higher than it opened, this bar will be white. If it closed lower, black.

Making this chart is so similar to making the previous one that it will actually be easier to just highlight the differences between them. The first is that we create an extra series at the beginning, the high series. Note that we create it first, since it appears first in the name of the chart.

The next difference is that in addition to setting ShowHighLowLines to true, we also set ShowUpDownBars to create the boxes. Since we don’t want the tick marks on this chart, we don’t change the MarkerType of the close series, and in fact set its DataPointMarkers to be invisible along with the rest of the series. Here is the code to produce a Open-High-Low-Close chart, with the changes from the last section:

// Add the new 'open' series as well as the three from before.
// Note that we create them in the same order they appear in the chart title
Series open = c.SeriesCollection.CreateSeries(openArea);
Series high = c.SeriesCollection.CreateSeries(highArea);
Series low = c.SeriesCollection.CreateSeries(lowArea);
Series close = c.SeriesCollection.CreateSeries(closeArea);

// Name the series so they show up in the legend.
open.Name = "Open";
high.Name = "High";
low.Name = "Low";
close.Name = "Close";

// This adds the vertical lines and boxes
high.SettingsLineAreaScatter.ShowHighLowLines = true;
high.SettingsLineAreaScatter.ShowUpDownBars = true;

// The data marker for the close series will be invisible, so we don't do
// anything to it
// Make all the extra bits of a line chart invisible, including the new
// open series, and the close data markers.
open.Line.Visible = false;
open.DataPointMarker.Visible = false;
high.Line.Visible = false;
high.DataPointMarker.Visible = false;
low.Line.Visible = false;
low.DataPointMarker.Visible = false;
close.Line.Visible = false;
close.DataPointMarker.Visible = false;


Adding a Second Axis: Volume-High-Low-Close Charts

This next brand of stock chart adds a significant wrinkle to the mix: volume data. Volume data measures the amount of stock sold, and as such needs to be plotted on a different axis than the price data. For the reason behind this, look at the sample data we’re using: the volume in a given row is often well over 100,000 while the prices are less than 100. Trying to plot data separated by four orders of magnitude on the same axis would force the price data down along the bottom making it unreadable.

The second trick with volume data is that it needs to be represented as a column, but the base type of our chart is Line.StandardLine. Fortunately, ExcelWriter supports creating and modifying combination charts.

This technique is very similar to the first chart we made. In fact, the only differences are that when we create the series we explicitly set their types and axes, and we hide the secondary category axis. By setting the AxisType and ChartType of the series we put the volume data on the primary axis and the price data on the secondary axis, just like the stock charts Excel makes.


// The type of the chart (and thus the default type of all its
// series is Line.StandardLine. To set the volume series to be a column chart,
// we need to pass that option to its constructor.
// Similarly, the default axis of all series is the primary one. To change
// the price data to be on the secondary axis, we need to pass
// that option to the constructor.
// As always, the series are added to chart in the order of the chart name.
Series volume = c.SeriesCollection.CreateSeries(volumeArea, ChartType.Column.Clustered, AxisType.Primary);
Series high = c.SeriesCollection.CreateSeries(highArea, ChartType.Line.StandardLine, AxisType.Secondary);
Series low = c.SeriesCollection.CreateSeries(lowArea, ChartType.Line.StandardLine, AxisType.Secondary);
Series close = c.SeriesCollection.CreateSeries(closeArea, ChartType.Line.StandardLine, AxisType.Secondary);

// Name the series so they show up in the legend properly.
volume.Name = "Volume";
high.Name = "High";
low.Name = "Low";
close.Name = "Close";

// The SecondaryCategoryAxis gets automatically made visible when we
// create a series with an AxisType of Secondary, but we don't want
// to see it, so we turn it off.
c.SecondaryCategoryAxis.Visible = false;

// Create the vertical lines
high.SettingsLineAreaScatter.ShowHighLowLines = true;

// Create the tick marks to show the closing value
close.DataPointMarker.MarkerType = DataPointMarker.ShapeType.DowJones;
close.DataPointMarker.ForegroundColor = Color.SystemColor.Black;

// Remove the cruft left over from the line chart.
high.Line.Visible = false;
high.DataPointMarker.Visible = false;
low.Line.Visible = false;
low.DataPointMarker.Visible = false;
close.Line.Visible = false;

This chart is the one most commonly referred to as a candlestick chart, because of the way the columns resemble the base of a candle, and the high/low lines coming out the top wicks (especially column eight). Note that the color of the volume columns may be different from the default color of volume columns in Excel Volume-High-Low-Close charts. If you’d rather have the default (or any other) color, simply change the Interior.ForegroundColor property of the volume series to whatever you’d like.

The Kitchen Sink: Volume-Open-High-Low-Close Charts

The final kind of stock chart that Excel has combines all the data we have and all the techniques we’ve used to far to create a truly impressive piece of data visualization: the Volume-Open-High-Low-Close chart. Having come this far, this is almost the easiest chart to create. There is only one line of code that we haven’t seen before. This chart contains up/down bars on top of columns, and if we leave the up/down bars at their default width this will look awkward. Excel’s solution is to increase the width of the up/down bars so that they are just slightly wider than the columns. The way to do this (in both Excel and ExcelWriter) is to decrease the space between the up/down bars, called the “gap width”.


// Create all five series in the correct order. Also make
// the volume data be columns on the left axis, while the price
// data goes on the right axis.
Series volume = c.SeriesCollection.CreateSeries(volumeArea, ChartType.Column.Clustered, AxisType.Primary);
Series open = c.SeriesCollection.CreateSeries(openArea, ChartType.Line.StandardLine, AxisType.Secondary);
Series high = c.SeriesCollection.CreateSeries(highArea, ChartType.Line.StandardLine, AxisType.Secondary);
Series low = c.SeriesCollection.CreateSeries(lowArea, ChartType.Line.StandardLine, AxisType.Secondary);
Series close = c.SeriesCollection.CreateSeries(closeArea, ChartType.Line.StandardLine, AxisType.Secondary);

// Name all the series so that the legend shows them properly.
volume.Name = "Volume";
open.Name = "Open";
high.Name = "High";
low.Name = "Low";
close.Name = "Close";

// Hide the top axis.
c.SecondaryCategoryAxis.Visible = false;

// Create the vertical lines and boxes, and make the boxes slightly wider
// than the volume columns.
high.SettingsLineAreaScatter.ShowHighLowLines = true;
high.SettingsLineAreaScatter.ShowUpDownBars = true;
high.SettingsLineAreaScatter.UpDownBar.GapBetweenBars = 100;

// Remove all the cruft left over from the line chart.
open.DataPointMarker.Visible = false;
open.Line.Visible = false;
high.DataPointMarker.Visible = false;
high.Line.Visible = false;
low.DataPointMarker.Visible = false;
low.Line.Visible = false;
close.DataPointMarker.Visible = false;
close.Line.Visible = false;

And there it is. That’s everything you need to know about modifying line charts to create stock charts in ExcelWriter. These charts can now be modified with ExcelApplication just like you would any other chart.

Follow

Related posts: