Tag Archives: Finance

Visualising India’s budget history

As we await the Indian Government’s budget tomorrow, here are some visualisations we created to examine the history of our past budgets.

Working with The Economic Times, we created a view of how the budget breakup has changed by ministry over time.

Sectoral trends

The large pink region is the Economic Affairs ministry, which takes up the bulk of the spend. Though it has been growing in absolute terms, in relative terms, it has been shrinking in importance. You can see the break-up by plan and non-plan breakups at our ET Ministry-wise Budget Allocation page.

Sectoral trends - PlanSectoral trends - Non-plan

In absolute terms, through, the spend on every sector has been growing smoothly and steadily, barring a few kinks. Among these, the agricultural spending is notable. It spurted up in 2009 to Rs 77 cr, but no subsequent budget has spent as much on agriculture.

Sectoral trends - Total

One other change that has happened is the relative rationalisation of budgets between 2002 and 2014. While in 2002 (left), the budget was relatively more concentrated among a few departments such as Economic Affairs, Defence, etc, by 2014, this disparity has reduced marginally.

Sectoral breakup 2001-2002 Sectoral breakup 2013-2014

But how do markets react to the budget?

One way of capturing this information is to look at how the market capitalisation of companies has moved on the day of the budget. For example, on the day of the 2007 budget, every single sector’s market capitalisation fell, with the sole exception of Tobacco. The exact same thing happened on the day of the 2009 budget as well.

Market movement 2007 Market movement 2009

But on the day of the 2010 budget, there was a perfect reversal of the situation. Every sector except Tobacco improved, while Tobacco (in a rare turn of events), lost considerable value.

Market movement 2010

In fact, the Tobacco sector is primarily just a single company – ITC, and its fate often moves counter-cyclically on budget days. But budgets are generally good for ITC. In the last 11 budgets, ITC (and Tobacco) has grown except in 2010. On the other hand, the Media and Entertainment industry typically suffers setbacks on budget day. Barring 2010 and 2011, the market cap of this segment has shrunk on every single budget day in the last 11 years.

Market movement history

For more insights and to explore the history of the Indian Budgets, please visit our site on The Economic Times and on Gramener.com.

Timing patterns in FII

In this blog post, we will examine SEBI’s FII data. Let’s start with making sense of the columns in the data and how they are related (the data column names will be italicized alongside). FIIs are investment institutes who register purchases/sells for foreign investors. These are executed by brokers registered with the SEBI. There are about a dozen transaction types such as purchase in the primary market, sale in the secondary market, etc. The scrips are the shares of companies being bought or sold.

We plotted the total transactional value for each day in 2012 and this is what we have:

Transaction volume

Some things we observed are:

  1. Thursdays have the highest volume of transactions, followed by Friday. As we all already knew, Mondays are about the blues.
  2. The white cells signal days of no business. Sundays have no transactions taking place at all; Saturdays are also very poor at that.
  3. Certain months are riddled with green cells. Most of February-March seem to have the highest frequency of transactions. Followed by September and December.
  4. All the highest peaks, seem to have come from the last weeks of quarter ends. That of March and September could be explained knowing that these are quarter endings, but what about November then?

Let’s take a break with some food-for-thought. If these are the patterns we see in all transactions, what do you observe in the following purchase and sell transactions? The transaction names can be seen in the top-left corner of the visuals:

Purchase in primary market

Sale in secondary market

The data is in a fairly easy format for anyone to play around with. Feel free to explore!

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.