Build candlestick chart in Tableau II: adjustable interval

Source: QuoteInspector.com

In the previous blog, I demonstrated how to build a candlestick chart with a fixed interval in Tableau. In this blog, I will demonstrate how to build a candlestick chart with adjustable intervals.

My method requires a column that indexes rows in chronological order. Fortunately, after processing the data I downloaded from the Yahoo Finance API, I already have an index column (see this blog for downloading stock market data from the Yahoo Finance API). If you don’t have an index column, you need to create one before loading the data into Tableau.

Step 1: adjustable opening and closing prices

The first thing we want to do is to create a parameter that allows us to toggle to change the interval.

Then we need to select the opening and closing prices corresponding to the selected interval. This is where we need the index. We will first create two indexes named “Interval max index” and “Interval min index” as follows:

These two indexes are populated with the largest and smallest indexes in the selected interval, which allows us to locate the rows of opening and closing prices. The following is an example that shows what the “maximum interval index” and “minimum interval index” look like in April 2018, when the interval is selected as a month.

With the “Interval max index” and “Interval min index”, we can easily find the opening and closing prices as shown in the figures below.

Step 2: Adjustable highest and lowest prices

Compared with the opening and closing prices, the highest and lowest prices are straightforward. You can easily get them using the equations “Max([High])” and “Min([Low])”.

Now that we have the opening price, closing price, highest price and lowest price, we can calculate the two spreads as follows:

Step 3: adjustable date granularity

When we adjust the interval of the candlestick chart, we need to change the granularity of the date fields in the columns shelf accordingly. My solution is to create calculated fields that adjust the date fields lower than the selected interval granularity to the required granularity. More specifically, I created the following calculated fields named “Quarter or higher”, “Month or higher”, and “Day or higher” to replace “Quarter(Date)”, “Month(Date)”, and “Day(Date)” fields.

Now that we have all the calculated fields we need, follow the same steps we used to build the fixed interval candlestick chart. We will have an adjustable interval candlestick chart!

The problem with the method I used here is that there will be duplicated column headings when the interval is increased. If anyone has a better way to construct an adjustable candlestick chart, please let me know. 🙂