Visualising Sensex returns in Excel

You don’t need specialised data visualisation software. Excel is a powerful tool in itself. For example, you could re-create and extend our When to Invest visualisations in Excel. Here’s an example:

bse-sensex-returns-visualisation

Each row indicates a month in which you’ve made an investment. The columns indicate how long you’ve held the investment for. For example, the 10th row, 10th column shows the returns you’d make if you invested in the 10th month (Oct 1991) and held it for 10 months (until Aug 1992). Red indicates negative returns, green indicates positive returns.

Here’s a tutorial on how you could do this.

First, let’s get the data

Let’s start by downloading the Sensex data. Visit the BSE Historical Indices page, choose the SENSEX and select a monthly feed for Jan 1991.

bse-indices

Click submit to see the results for the year 1991.

bse-sensex-monthly

Copy and paste the table into Excel.

Now, repeat this for each year from 1991 to 2012, and you’ll have the full database of monthly Sensex prices. (It’s possible to scrape, copying and pasting 21 pages is faster.)

bse-excel-data

Next, let’s calculate the returns

To find the returns after 1 month, 2 months, etc, we can use the OFFSET formula:

offset-formula

OFFSET($B2, $C1, 0) says:

  • Start with cell B2 (and when I copy and paste, always get me data from column B)
  • Move down C1 rows (and when I copy and paste, always get the offset from row 1)
  • Move right 0 columns

As we copy and paste the formula, we’ll get the opening price 1 month, 2 months, 3 months after the starting month. (The 1, 2, 3… months come from the first row).

Now, we need to calculate the annualised return from this price. That’s done by the following formula:

=POWER(OFFSET($B2,C$1,0)/$B2, 12/C$1)-1

return-formula

Put another way, this is the same as:

=POWER( Future_Amount / Initial_Amount, 12/Number_of_months) – 1

Copying and pasting this formula to all the cells gives you a sheet that shows the returns for every period.

returns

Lastly, let’s colour it.

If you want to see what the returns look like as a gradient, select the cells, go to Conditional Formatting – Color Scales and choose one of the scales there.

conditional-formatting

You may then want to tweak the colours for each value. Go to any of the cells, then select Conditional Formatting – Manage Rules, and double-click the “Graded Color Scale” rule. Then you can change what Red, Yellow and Green stand for. (In the example below, they’ve been set to –50%, 0% and +50% returns respectively.)

formats

This will give you the visualisation below when you zoom out.

bse-sensex-returns-visualisation

Another way of looking at the same data is to only show those periods where the return was more than, say, the average inflation rate (let’s say 11%).

formats2

… this is what the result will look like.

bse-sensex-returns-inflation

We’re not trying to endorse Excel as a product: We admire all spreadsheets. Excel is merely our abbreviation for “Excel and other spreadsheets”. The same principles would apply equally well to Open Office, Google Docs, or any other spreadsheet of your choice.

There’s a good chance Excel is already on your machine. You’re likely to be familiar with it. Why bother learning a new tool, or a programming language, when you’re already familiar with something?

The point is, most tools can visualise well. But you need to have the imagination.

Finding the right investment

Creating and managing a mutual fund portfolio can be quite a task without the right set of tools to compare funds. On the one hand, you have volumes of information from research reports. On the other hand, you have data on Excel that let you compare specific parameters. But few tools offer a complete view of the portfolio with all relevant details across the funds in a single snapshot.

At Gramener we applied visualisation to a set of equity funds, comparing them on various parameters.

The layout has been kept simple, but the information density is rather high. Both are on purpose, This, clearly, is quite a complex visualisation and merits closer observation.

The first block shows the name of the fund, the current net asset value (NAV) and the overall trend over the last 30 days. The black line shows the daily trend while the light blue line behind it shows the movement of the benchmark – the Sensex in this case.

The next block shows daily returns as a horizon graph. The returns on each day over the last 30 days (from left to right) are shown in red (for negative returns) or green (for positive returns). The intensity of the colour indicates the magnitude of the return. This makes it easy to compare returns for a given period across funds. For instance, it is clear that the beginning of the month was a bad time (since the left-end is red) for almost every equity fund.

The returns are also plotted against the returns of the Sensex as a jitter plot. The red dots on the left are days when the returns fell below the Sensex. Far left indicates very low returns, far right indicates very high returns. A glance shows that the second and third funds have returns that are spread out, but the others are fairly closely clustered around the Sensex’s returns.

The next block shows the average return over longer periods – a week, month and quarter. The colour indicates how high or low the returns are with respect to other funds. Red indicates the lowest return, green indicates the highest return. Here, the second and third fund have had a fairly high return compared to others during the last week.

Some of the more complex parameters that investors look at are Jensen’s alpha, beta, sigma and the information ratio. These too are included in this visualisation, again showing their relative performance with respect to the other funds. The only fund that does not perform poorly on these parameters here is the third fund.

But one other parameters that would be of interest is, how good is the fund’s return compared to other funds. Irrespective of the return with respect to the Sensex, a key parameter is the return with respect to other equity funds if these funds are the investments of concern to us. We plotted the weekly return as a percentile rank. If the fund is the best performing fund on a given day, we plot a dot at the right end. If it is the worst performing fund, we plot a dot on the left end. The mix of dots tells us how the fund has performed relatively over the last month.

The last column shows the average of the weekly percentile rank. This is a good indication of the fund’s relative performance averaged over time. The first fund, for instance, has on average outperformed 63.7% of the other funds. The next has outperformed 61.6% of the other funds. And so on. The table is sorted based on this column.

The aim of this visualisation is to present, in a very condensed and information-dense fashion, all the information an investor requires to make a decision. As you can see, an explanation of the visualisation takes more space than the visualisation itself! Yet with a bit of understanding, getting insights out of this plot can take just a few seconds. A far cry from having to read 50 equity research reports!

When to invest

Sometimes, timing is everything in investments.

Last year, The New York Times published a piece titled In Investing, It’s When You Start And When You Finish. This showed the significant impact of timing in investment decisions.

At Gramener, we applied the same visualisation to a few Indian stocks over the last 5 years.

Here’s what it looks like for ICICI’s stock.

 

If you invested in ICICI stock in Jan 2007, the first row of boxes show the kind of returns you would have seen.

The colours indicate the degree of profit or loss. Red for losses, green for profits, and yellow for neutral values. Selling in March 2007 would have made significant losses. Selling in Jan 2008, one year later, would have given you a good profit. And so on.

The same is extended to investments made in other months.

The black boxes show a holding pattern of 1 year, 2 years, etc. You can get a sense of what kind of returns you would make with a strategy of holding for 1 year, 2 years, and so on.

Here are similar pictures for Infosys stock and SBI stock.

At Gramener, we took a look at a number of such stocks and their performance over the last five years. A interactive app showcasing sample of those is available at http://gramener.com/whentoinvest/.