Home > Tableau > Tableau Candlestick Chart

Tableau Candlestick Chart

Recently I’ve had reason to pay more attention to the stock of homeaway.com who are the company that introduced me to Tableau. They implemented Tableau Server a few years ago while I was in a very small team of analysts working there at the time, enabling me gain the level of expertise I now have. While analysing the performance of their stock I’ve begun to gain a new appreciation for the candlestick chart and thought about creating this chart in Tableau.

I downloaded the daily stock data for the past 2 years from the Nasdaq website and found a very useful tutorial on the Tableau site about how to create a candlestick chart in Tableau. This saved me many hours of working out how to do this and now I’m going to write it up step by step on this site.

The downloaded dataset is very simple, it contains the date, open price, close price, day high, day low and the volume. I added the ticker to this as well, although unnecessary if only looking at one stock. This can be used as a filter should you load a number of different stocks into the same chart. Tableau should automatically assign the correct data type to the dimensions and measures, they should appear as follows:

Tableau Dimensions Measures

A candlestick chart shows the Open, Close, High and Low price of a stock over a given period. The first step to builing this is to drag the date field to the Columns shelf using the continuous Day setting. Next drag the High pill to the Rows and set it to AVG giving a line chart of the daily high values. The Low pill also needs to be dragged to the chart and set to AVG giving a line of daily highs and daily lows.

Away Daily Highs and Lows

Hint: If you drag the Low pill to the Y axis, poorly circled in red, and drop it there, this will automatically show both lines on the same chart.

With a candlestick chart the high and low forms the wick of the candle. To create the wick first change the DAY(Date) pill to be Discrete – rght click on the little arrow on the pill to do this. To separate this data out for each day the Detail section of the Marks is to be used. Drag the Date field from the dimensions to the Detail section and set it to MDY.

Also the Y axis can be edited as it doesn’t need to show the 0 (right click on the axis, Edit Axis, uncheck the Include Zero box). There are now many dots on the chart showing the daily highs and lows, which need connecting to create the wick of the candle.

Away Daily Highs Lows Dots

A simple calculated field is required to complete the wick. Call it Up or Down and the formula is as follows: IF AVG([Close]) > AVG([Open]) THEN “Up” ELSE “Down” END. In other words if the close is greater then the open it closed up otherwise it closed down.

Drag this Up or Down field to the Colour shelf and you’ll have the candle wicks fully formed. The colours can be changed giving a positive colour for Up and a negative colour for Down should you wish. In this example I’ve coloured the Up blue and Down green to assist those people with red-green colour blindness.

Away Candle Wick

Next the candle body is to be built. This is based on the Open and Close price. Reference lines and formulae are used to do this. To begin we’ll create the candles for those that closed up after the days trading.

  1. Drag the Open and Close fields to the Detail shelf, and set both to AVG
  2. Create a new calculated field called Closed Up
  3. The formula for Closed Up is IF AVG([Close])>AVG([Open]) THEN AVG([Close]) END
  4. Drag Closed Up to the Detail shelf
  5. Right click in the Y axis and select Reference Lines
  6. The reference line for those that closed up will be a Band with a Scope of Cell, starting with the AVG(Open) and ending with the AGG(Closed Up). It can also be coloured the same as the Up wicks already on the chart.

Tableau Reference Line

Now the candlesticks are complete for those that closed up leaving those that closed down to complete, which is very similar to building the candle for Closed Up:

  1. Create a new calculated field called Closed Down
  2. The formula for Closed Down is IF AVG([Open])> AVG([Close]) THEN AVG([Open]) END
  3. Drag Closed Down to the Detail shelf
  4. Right click in the Y axis to add a new reference line and set it up as follows:

Away Reference Line Down

Now you should see a fully fledged candlestick chart. There are a still a few cosmetic things that could be done to tidy the sheet, for example the colours of the candle and wick could be made the same (which I didn’t do as it’s one of those things that should be very simple that in reality is time consuming), the chart could be made to fit the width of the sheet, etc.

As can be seen in the visualisation I also added a Volume chart, which is very straightforward to do. Download the visualisation from Tableau Public should you want to learn how to do that as well.

  1. No comments yet.
  1. No trackbacks yet.