Visualising India’s budget history

As we await the Indian Government’s budget to­mor­row, here are some visu­al­isa­tions we cre­ated to ex­am­ine the his­tory of our past budgets.

Working with The Economic Times, we cre­ated a view of how the budget break­up has changed by min­istry over time.

Sectoral trends

The large pink re­gion is the Economic Affairs min­istry, which takes up the bulk of the spend. Though it has been grow­ing in ab­so­lute terms, in re­l­at­ive terms, it has been shrink­ing in im­port­ance. You can see the break-up by plan and non-plan break­ups at our ET Ministry-wise Budget Allocation page.

Sectoral trends - PlanSectoral trends - Non-plan

In ab­so­lute terms, through, the spend on every sec­tor has been grow­ing smoothly and stead­ily, bar­ring a few kinks. Among these, the ag­ri­cul­tur­al spend­ing is not­able. It spur­ted up in 2009 to Rs 77 cr, but no sub­sequent budget has spent as much on ag­ri­cul­ture.

Sectoral trends - Total

One oth­er change that has happened is the re­l­at­ive ra­tion­al­isa­tion of budgets between 2002 and 2014. While in 2002 (left), the budget was re­l­at­ively more con­cen­trated among a few de­part­ments such as Economic Affairs, Defence, etc, by 2014, this dis­par­ity has re­duced mar­gin­ally.

Sectoral breakup 2001-2002Sectoral breakup 2013-2014

But how do mar­kets re­act to the budget?

One way of cap­tur­ing this in­form­a­tion is to look at how the mar­ket cap­it­al­isa­tion of com­pan­ies has moved on the day of the budget. For ex­ample, on the day of the 2007 budget, every single sector’s mar­ket cap­it­al­isa­tion fell, with the sole ex­cep­tion of Tobacco. The ex­act same thing happened on the day of the 2009 budget as well.

Market movement 2007Market movement 2009

But on the day of the 2010 budget, there was a per­fect re­versal of the situ­ation. Every sec­tor ex­cept Tobacco im­proved, while Tobacco (in a rare turn of events), lost con­sid­er­able value.

Market movement 2010

In fact, the Tobacco sec­tor is primar­ily just a single com­pany – ITC, and its fate of­ten moves counter-cyclically on budget days. But budgets are gen­er­ally good for ITC. In the last 11 budgets, ITC (and Tobacco) has grown ex­cept in 2010. On the oth­er hand, the Media and Entertainment in­dustry typ­ic­ally suf­fers set­backs on budget day. Barring 2010 and 2011, the mar­ket cap of this seg­ment has shrunk on every single budget day in the last 11 years.

Market movement history

For more in­sights and to ex­plore the his­tory of the Indian Budgets, please vis­it our site on The Economic Times and on Gramener.com.

Timing patterns in FII

In this blog post, we will ex­am­ine SEBI’s FII data. Let’s start with mak­ing sense of the columns in the data and how they are re­lated (the data column names will be it­alicized along­side). FIIs are in­vest­ment in­sti­tutes who re­gister purchases/sells for for­eign in­vestors. These are ex­ecuted by brokers re­gistered with the SEBI. There are about a dozen trans­ac­tion types such as pur­chase in the primary mar­ket, sale in the sec­ond­ary mar­ket, etc. The scrips are the shares of com­pan­ies be­ing bought or sold.

We plot­ted the total trans­ac­tion­al value for each day in 2012 and this is what we have:

Transaction volume

Some things we ob­served are:

  1. Thursdays have the highest volume of trans­ac­tions, fol­lowed by Friday. As we all already knew, Mondays are about the blues.
  2. The white cells sig­nal days of no busi­ness. Sundays have no trans­ac­tions tak­ing 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 fre­quency of trans­ac­tions. 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 ex­plained know­ing that these are quarter end­ings, but what about November then?

Let’s take a break with some food-for-thought. If these are the pat­terns we see in all trans­ac­tions, what do you ob­serve in the fol­low­ing pur­chase and sell trans­ac­tions? The trans­ac­tion names can be seen in the top-left corner of the visu­als:

Purchase in primary market

Sale in secondary market

The data is in a fairly easy form­at for any­one to play around with. Feel free to ex­plore!

Visualising Sensex returns in Excel

You don’t need spe­cial­ised data visu­al­isa­tion soft­ware. Excel is a power­ful tool in it­self. For ex­ample, you could re-create and ex­tend our When to Invest visu­al­isa­tions in Excel. Here’s an ex­ample:

bse-sensex-returns-visualisation

Each row in­dic­ates a month in which you’ve made an in­vest­ment. The columns in­dic­ate how long you’ve held the in­vest­ment for. For ex­ample, the 10th row, 10th column shows the re­turns you’d make if you in­ves­ted in the 10th month (Oct 1991) and held it for 10 months (un­til Aug 1992). Red in­dic­ates neg­at­ive re­turns, green in­dic­ates pos­it­ive re­turns.

Here’s a tu­tori­al on how you could do this.

First, let’s get the data

Let’s start by down­load­ing the Sensex data. Visit the BSE Historical Indices page, choose the SENSEX and se­lect a monthly feed for Jan 1991.

bse-indices

Click sub­mit to see the res­ults for the year 1991.

bse-sensex-monthly

Copy and pas­te the table in­to Excel.

Now, re­peat this for each year from 1991 to 2012, and you’ll have the full data­base of monthly Sensex prices. (It’s pos­sible to scrape, copy­ing and past­ing 21 pages is faster.)

bse-excel-data

Next, let’s cal­cu­late the re­turns

To find the re­turns af­ter 1 month, 2 months, etc, we can use the OFFSET for­mu­la:

offset-formula

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

  • Start with cell B2 (and when I copy and pas­te, al­ways get me data from column B)
  • Move down C1 rows (and when I copy and pas­te, al­ways get the off­set from row 1)
  • Move right 0 columns

As we copy and pas­te the for­mu­la, we’ll get the open­ing price 1 month, 2 months, 3 months af­ter the start­ing month. (The 1, 2, 3… months come from the first row).

Now, we need to cal­cu­late the an­nu­al­ised re­turn from this price. That’s done by the fol­low­ing for­mu­la:

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

return-formula

Put an­other way, this is the same as:

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

Copying and past­ing this for­mu­la to all the cells gives you a sheet that shows the re­turns for every peri­od.

returns

Lastly, let’s col­our it.

If you want to see what the re­turns look like as a gradi­ent, se­lect the cells, go to Conditional Formatting – Color Scales and choose one of the scales there.

conditional-formatting

You may then want to tweak the col­ours for each value. Go to any of the cells, then se­lect 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 ex­ample be­low, they’ve been set to –50%, 0% and +50% re­turns re­spect­ively.)

formats

This will give you the visu­al­isa­tion be­low when you zoom out.

bse-sensex-returns-visualisation

Another way of look­ing at the same data is to only show those peri­ods where the re­turn was more than, say, the av­er­age in­fla­tion rate (let’s say 11%).

formats2

… this is what the res­ult will look like.

bse-sensex-returns-inflation

We’re not try­ing to en­dorse Excel as a pro­duct: We ad­mire all spread­sheets. Excel is merely our ab­bre­vi­ation for “Excel and oth­er spread­sheets”. The same prin­ciples would ap­ply equally well to Open Office, Google Docs, or any oth­er spread­sheet of your choice.

There’s a good chance Excel is already on your ma­chine. You’re likely to be fa­mil­i­ar with it. Why bother learn­ing a new tool, or a pro­gram­ming lan­guage, when you’re already fa­mil­i­ar with some­thing?

The point is, most tools can visu­al­ise well. But you need to have the ima­gin­a­tion.