Share with your network!

This blog is inspired from the NY Times’ work in this article, where the visualization shows annualized returns for S & P 500 for every starting year and every ending year since 1920. The purpose of the visualization created by NY Times is to show that in investing what matters is when you start and when you finish. Staying invested for long term does not necessarily guarantee good returns. As quoted by NY Times 1948-1968 were the best 20 years that gave annualized returns of +8.4% from S & P 500 whereas 1961-1981 were the worst ever 20 years that yielded -2% annualized returns.

The screenshot below depicts the work done by NY Times’ graphic team.

visualisations  by NY Times

Source: nytimes

Let us recreate the similar visualization but with Indian stocks’ price data using Tableau 9.0.

Data

One can perform the similar analysis for price data of Indian stocks or Indices. For this blog I have chosen 5 years’ historical closing price of Tata Consultancy Services (TCS/code: 532540) on BSE. I have monthly closing price data from 1st January 2010 to 1st April 2015 for TCS from BSE.

Data Exploration & Visualization

As usual, we will list down the steps to create the visualization for simplicity.

Step 1: Get the data into Tableau

One can choose to import only the Month and Closing Price data in Tableau. I decided to import all the columns as downloaded from BSE website. You might want to change the formatting of Month column before importing the data depending on the date format set on your computer.

importing stock  price data in tableau

Step 2: Have Month on rows and columns shelf

To recreate the visualization Month needs to repeat on both row and column shelf. Month on row indicates money invested in and Month on column indicates money withdrawn in. To achieve this we are going to create a duplicate of the Month dimension.

duplicating the  month dimension in tableau

duplicating the  month dimension in tableau

Step 3: Put “Close Price” on Text card

Putting Close Price on Text card just populates diagonal elements of the matrix that we have created as shown in below screenshot. E.g. we have data for Jan 2010-Jan 2010, Feb 2010-Feb 2010, Mar 2010-Mar 2010 etc.

putting close  price in text card in tableau

Step 4: Create calculated fields to fill “Close Price” across and down

The formula for Close Price across and down table calculation remains the same the only difference being one is to be computed using Table across whereas other needs to be computed using Table down.

creating  calculated fields in tableau

The result looks as below.

creating  calculated fields in tableau

Step 5: Create calculated field to create Index across and down

The formula for Index across and down table calculation remains the same the only difference being one is to be computed using Table across whereas other needs to be computed using Table down.

creating  calculated field to create index across and down

The result looks as below.

creating  calculated field to create index across and down

Step 6: Create a calculated field to create values of upper right angle triangle

The aim of this step is to create an upper right angle triangle to show only relevant data points. E.g. if we have N data points then we can have N * N comparisons but we are interested in only [N*(N-1)]/2 useful comparison data points. E.g., Let us say we have 4 months data Jan, Feb, Mar and Apr then we are interested only in plotting following combinations highlighted in green.

creating  calculated field to create values of upper right angle triangle

 

 

creating  calculated field to create values of upper right angle triangle

The result looks as below after using calculated field to filter only True values.

creating  calculated field to create values of upper right angle triangle

 

creating  calculated field to create values of upper right angle triangle

Step 7: Create a CAGR (compounded annual growth rate) calculated field

Create a CAGR calculated field as shown below and format it to show as percentage with 1 decimal precision.

creating a CAGR  in Tableau

The result looks as below.

creating a CAGR  in Tableau

Step 8: Create Heat Map and apply format

Convert tabular data into heat map by removing measures from Text Card, rearranging Month and Month copy on row and column shelf and putting CAGR on colour card. One can apply custom colouring to create heat map. Make sure to keep the filter on to have upper right angle triangle data points only.

creating heat  map in tableau

 

We have successfully created the visualization similar to NY Times graphic.

creating heat  map in tableau

Looking at the visualization above one can see that somebody who invested in TCS in December-12 to December-2013 would have earned 72% returns. Whereas somebody who invested in December -10 and withdrew in December-2011 made slightly less than 0% returns. Lot of analysis and inferences are possible once this visualization is created.

About Tableau

Tableau (NYSEDATA) headquartered in Seattle, Washington has a mission to help people see and understand data. It offers a product portfolio for data visualization focused on business intelligence.
One can visit the official Tableau website to find more details about Tableau and its product offering and features.