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.
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.
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.
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.
Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc
CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.
Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to email@example.com and we will rectify it.
2015 © Edupristine. ALL Rights Reserved.