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.
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.
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.
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.
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.
The result looks as below.
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.
The result looks as below.
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.
The result looks as below after using calculated field to filter only True values.
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.
The result looks as below.
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.
We have successfully created the visualization similar to NY Times graphic.
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 (NYSE: DATA) 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.
Comments