Compute and Display Heikin Ashi Charts in SQL Server and Excel

By: | Updated: 2022-04-29 | Comments | Related: More > TSQL


Free MSSQLTips whitepaper - "Understanding Windows Server Cluster Quorum Options"

Problem

Show me how to display in Excel Heikin Ashi charts based on financial time series data stored in SQL Server. Also demonstrate how to compute in SQL Server Heikin Ashi candlestick values as well as a method for easily transferring the computed candlestick values to Excel. Conclude with data visualization comparisons of Heikin Ashi computed candlesticks versus observed candlesticks for high, low, open, and close prices.

Solution

Heikin Ashi charts are a special kind of candlestick chart. A traditional candlestick chart represents open, high, low, and close prices for a set of periods, such as hours, days, or weeks. The prices for each time period are represented by a single candlestick. The collection of candlesticks over multiple periods denotes a time series of prices that represents a security's price behavior. Instead of plotting observed open, high, low, close prices, as in traditional candlestick charts, Heikin Ashi charts compute average candlesticks based on the current period and its prior time period. The Heikin Ashi candlesticks are based on computed values instead of actual price observations.

The candlestick chart was originally created for the use case of tracking rice market prices in Japan during the 1700's. Since the early 1990's, open, high, low, close candlestick charts and Heikin Ashi charts are used for other types of markets, such as stocks, bonds, treasury bills, commodities, fiat currencies, and crypto currencies. This prior MSSQLTips.com article introduces database professionals to candlestick and Heikin Ashi charts. The charts in the prior tip were based on data stored and charted at either the Yahoo Finance site or the Stooq.com site.

This tip extends the coverage of candlestick charts by taking advantage of financial time series data stored in SQL Server. Two prior tips (here and here) provide examples of how to migrate financial time series data from Yahoo Finance and Stooq.com to SQL Server. The current tip extracts selected time series data for different ticker symbols from SQL Server, optionally computes Heikin Ashi chart values, and displays the observed open, high, low, and close candlestick values along with the Heikin Ashi candlesticks (when they are computed). The displayed data are copied from SQL Server to Excel. Then, built-in Excel chart features are adapted for creating traditional candlestick as well as Heikin Ashi charts in Excel. The Excel built-in features are all based on user interfaces (no programming is required for displaying charts).

An example of a candlestick chart for data from SQL Server

The following screen shot shows a short T-SQL script for displaying open, high, low, close prices for two ticker symbols (AMZN, JPM); AMZN is for Amazon.com, Inc., and JPM is for JPMorgan Chase & Co. The prices for the two tickers are collected for time series starting in 2018-11-30 and running through 2019-02-11. The Results tab in the screen shot shows the first ten rows from the results set.

The next table presents the full results set for the AMZN ticker with its date, open, high, low, and close column values selected (these values are shaded). After copying the selected set of rows into the Windows clipboard from SQL Server, you can paste the rows into a blank tab of an Excel workbook file.

The following screen shot shows an Excel tab with a candlestick chart to the right of the copied data.

  • Candlesticks with a green body denote dates for which the close price is greater than the open price.
  • Candlesticks with a red body denote dates for which the close price is not greater than the open price.
  • Candlesticks with a wick above the top of their body identify dates for which the high price is greater than the price at the top of the candlestick body.
    • When the close price is greater than the open price, then the price at the top of the candlestick is the close price.
    • When the close price is not greater than the open price, then the price at the top of the candlestick is the open price.
  • Candlesticks with no wick above the top of their body indicate that the high price is the same as the price at the top of the candlestick body.
  • Candlesticks with a wick below the bottom of their body identify dates for which the low price is less than the price at the bottom of the candlestick body.
    • When the open price is less than the close price, then the price at the bottom of the candlestick is the open price.
    • When the open price is not less than the close price, then the price at the bottom of the candlestick is the close price.
  • Candlesticks with no wick below the bottom of their body indicate that the low price is the same as the price at the bottom of the candlestick body.

Configuring a candlestick chart in Excel

The following screen shot shows an excerpt from a tab in an Excel workbook file after it is populated with data for the JPM ticker.

The next step is to unprotect the tab. Click the triangle above the value of 1 denoting the first row and choose Format Cells > Protection. Make sure the Locked and Hidden check boxes are both cleared (see below). Then, click OK to commit the change(s). The configuration is necessary in order to make the OK button ungrayed out when you try to add a candlestick chart to a tab.

Next, perform the selections to add the candlestick chart to the Excel tab.

  • Start by selecting all the data for the chart.
  • Next, choose Insert > Recommended Charts > All Charts > Stock.
  • Then, select the candlestick chart image (see the image below).
  • Close the Stock chart menu by clicking OK.

Select the vertical axis for the freshly added candlestick chart and set the bounds to remove excessive white space above and below the candlesticks in the chart. Here's the tab with the chart image after resetting the vertical axis bounds. The legend towards the bottom of the chart containing open, high, low and close is unnecessary. You can remove the unnecessary legend by selecting it, and then pressing Delete on your keyboard.

The next step is to reset the horizontal axis to not include weekend days and other days, such as January 1 and Christmas day, when trading markets are closed. You can do this by right clicking the horizontal axis labels and choosing Format Axis. Next, choose the Text axis radio button under Axis Type. While you are in the Format Axis menu, you can click the Size and Properties icon (it looks like a cross). Change the Text Direction drop-down box to Rotate All Text to 270