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 Continue reading How to create stock charts using ExcelApplication →