Bitcoin pricing has been extremely volatile over the past year. There are a number of drivers of this volatility: geopolitical risks, country regulatory changes to crypto currencies, crypto currency forks and speculators. With all this volatility, there is a need to efficiently understand how prices change within a period. One of the ways to visualize these price changes is by using candlestick charts. In this post, I’ll walk you through what a candlestick chart is and how to make your own in Tableau.
Our goal in this exercise, is to produce a candlestick chart as per the below.
A. How do You Read a Candlestick Chart?
Before we start, let’s go through with some definitions of what a candlestick chart is and why it is useful. A candlestick chart is a visualization of open, high, low and close prices for each time period. It is believed that the Japanese first used a form of this visualization and technical analysis when trading rice in the 17th century.
OK, so how do you read this candlestick? The coloured thick part of the candlestick is called the ‘body’ and represent the open and closing values. When closing prices are above open prices, the chart is often shaded green and when closing prices are below open prices, the chart is often shaded red. The long thin lines are the ‘tails’ or ‘wicks’ and represent the high and low values.
Chart 1: Candlestick Definitions
Generally, the longer the body, the more intense the buying or selling pressure. Conversely, short candlesticks indicate little price movement.
Chart 2: Long Body Buying/Selling Pressure
Candlesticks with a long upper tail, long lower tail, and small body are called spinning tops and represent indecision. The small body (whether green or red) shows little movement from open to close, and the long tails indicate that both bulls and bears were active during the session.
Chart 3: Spinning Tops
B. Make Your Own Tableau Candlestick Charts
Now we know about what a candlestick chart is, let’s go and make our own.
Firstly, the type of data we will need for the exercise is in the form below. This data comes from an API to GDAX.com (https://docs.gdax.com/) – where we pull hourly pricing information for a date range. If anyone wants this python code, I can share it in another post.
Chart 4: Raw Bitcoin Pricing Data
Step 1 – Connect Your Data
In Tableau, go to Data Sources -> New Data and select text file (my data was in CSV format.)
Chart 5: Raw Data
Step 2 – Add Low and Open Prices
Bring in Low and Open columns into the rows. Next click on the right corners of SUM(Low) and SUM(Open) and change the aggregation measure to AVG.
Chart 6: Low and Open Price
Next right click on AVG(Open) and select ‘Dual Axis’. After this is done, it is worthwhile synchronizing the axes. Do this by right clicking one of the axes and select ‘synchronize axes.’
Chart 7: Dual Axis and Synchronize Axis
Step 2 – Remove the Zeros From the Axis
To help the scale of the axes, right click one of the axes and select ‘edit axis.’ Once in the edit mode, un-select ‘Include zero’.
Chart 8: Scale the Axis by Removing Zeros
Step 3 – Change Chart to Gantt Bar
Under ‘Marks’, select ‘All’ and change the chart to a Gantt Bar.
Chart 9: Gantt Bar
Step 4 – Calculated Fields for Difference Between High/Low and Close/Open
Create a calculated field Called ‘Delta High – Low’ by subtracting Low from High.
Chart 10: Range Between High and Low
Similarly, create another calculated field called ‘Delta Close – Open’ by subtracting the Open column from the Close.
Chart 10: Range Between Open and Close
Step 5 – Drag the Delta Calculations to Size
First click on the AVG(Low) Gantt chart under Marks and make sure it is highlighted in bold. Next drag the calculated field ‘Delta High – Low’ onto Size (below where it says ‘Gantt Bar’.)
Chart 11: Add Tails
Next click on the size icon and scale the size down to the lowest level as we will use this part of the chart as the thin tail of the candlestick.
Chart 12: Scale Tail
Repeat a similar process with the AVG(Open) value. Select ‘AVG Open’. Then drag in the calculated column ‘Delta Close – Open’ onto the Size icon. You may leave the size as the default setting, as we need some thickness to create the body of the candlestick.
Chart 13: Add Body
Step 6 – Add in the Date
Next add in the date field into the columns. Note to remember to make sure you click on the axis and deselect the ‘Include Zero’ check mark. This helps scale the graph.
Chart 14: Insert Dates
Step 7 – Add Colour
The final step is to add in colour into the chart. We want green if the close price > open price and red if the the close price < open price. We can do this via a calculated field where we create an IF statement. We type in IF([Close]-[Open]>0) THEN ‘Increase’ ELSE ‘Decrease’ END
Chart 15: Calculation for Colour
Once we have the calculated field ‘Colour’, we can drag the field onto the Colour Icon and edit the colour pallet to green for increase and red for decrease.
Chart 16: Add Colour
That’s it, you now have an awesome candlestick graph to help visualize pricing and make more informed decisions.