Previously, I introduced in my blog how to download stock market data from the Yahoo Finance API. This time I want to use the download data to build a candlestick chart in Tableau. I will use the SPY ETF data I downloaded in the previous blog to build the chart.
In this blog, we will build a fixed interval candlestick chart. The fixed interval is the interval between the opening and closing prices provided by the data set. The fixed interval of the SPY data we downloaded is one day. I plan to write another blog post to build an adjustable interval candlestick chart later on.
Step 1:
We first need to create two calculated fields, called “Spread Open to Close” and “Spread Low to High”, which determine the length of the candles.
Then we drag the Date field to the Columns shelf and expand it to the day level. Drag the Open price and Low price to the Rows shelf. We will get two graphs as follows:
Step 2:
Now we will switch both charts to the Gantt bar chart. Drag “Spread Open to Close” to the size card of the SUM(Open) Marks card, and drag “Spread Low to High” to the size card of the SUM(Low) Marks card. At the moment, the bars of the charts are almost invisible. We can right-click on the vertical axis, click “Edit Axis…” and uncheck “Include zero” for both charts to dynamically adjust the range of the axes.
Step 3:
Before making a dual-axis, we first color the bar chart according to the candlestick chart convention, where the red bar represents the price drop in the interval, and the green bar represents the price rise in the interval. To do this, we drag “Spread Open to Close” onto the color cards of the two charts. Change the color to Red-Green Diverging, tick the Stepped Color option, and change the steps to 2.
After coloring the bars, we stack the two charts by clicking on a measure on the Rows shelf and selecting “Dual Axis”. Right-click on the secondary axis on the right and select Synchronize Axis to align the axes. Then we can hide the secondary axis. Finally, adjust the bar size of the SUM(low) chart to be much smaller than the SUM(Open) chart, and we will have a beautiful candlestick chart!
Author: Binbin Chen
Binbin came to Australia and studied mechanical engineering at Monash University. After earning a bachelor's degree, he pursued a PhD degree and worked as a part-time teaching associate at Monash University during the period. When analysing data in research projects involving human eye movements, he discovered his enthusiasm for data. He was delighted to learn about Data School Down Under as it provides a unique opportunity to start a career as a data analyst through a thoughtful training program. Outside of work, Binbin enjoys watching historical and political videos. He is also an anime fan!