Importing Financial Data into Excel – Indian Stock Market

Siddharth Shukla of Thrify Investor fame (@shuklasiddharth – are you following him yet?) and I had a short discussion on importing financial data from various financial websites into Excel. Some websites do not have an option of downloading financial information from their portals directly into Excel (unless you do copy-paste and then reformat in excel to adjust for columns etc., which is very cumbersome. Come to think of it, even downloading into Excel is an issue, because for one company, you’ll have three excel files atleast (BS, CF and P&L) and you need to consolidate them into one excel for easier analysis – painful!). As it is, financial analysis is strenuous. We would not want the trouble of downloading and then formatting etc, do we?

This post will explain a very simple method to import financial data (in fact, any data from any website, but financial data for this discussion) into excel seamlessly. I learnt this trick very recently. If you already know this trick, do suggest any improvements on this one (or accede to my request at the end of this post).

I will explain this with an example of importing Balance Sheet data of Infosys from Economic Times website. (I will go through each small step. Some steps might look too basic and hence increase the number of steps (there are only 2 main steps though). It might also look like these 21 steps is too long a process, but believe me you, the entire process will not take more than a minute or two).

1) The first step is to open up Economic Times website (http://economictimes.indiatimes.com/) and type in ‘Infosys’ in the Stock Quote text box in the top right hand corner.

2) In the Search Results page that gets displayed, click on the hyperlink of ‘Infosys Technologies’

3) The next page details Infosys under different tabs like Summary, Prices, Financials, Reports etc etc.

4) Click on the Financials tab

5) The sub-tab of P&L, Cash Flow, Balance Sheet etc etc appear below the Financials tab.

6) Note the url. It is – http://economictimes.indiatimes.com/infosys-technologies-ltd/profitandlose/companyid-10960.cms. If you observe closely, just before companyid in the URL, you have ‘profitandlose’. This indicates that Economic Times treats this as a Profit and Loss page of Infosys Techologies.

7) Since we want to download Balance Sheet data for this example, we will go ahead and click on BalanceSheet subtab hyperlink beside the Cash Flow subtab.

8) Once BalanceSheet hyperlink is clicked, notice the URL. It is –http://economictimes.indiatimes.com/infosys-technologies-ltd/balancesheet/companyid-10960.cms. Observing closely again, just beside companyid in the URL, it says ‘balancesheet’. This indicates that Economic Times treats this as the BalanceSheet page of Infosys Techologies. We are in the right location.

9) We now minimize the browser (IE/Mozilla/Chrome/Safari – works for all browsers) and open Excel (for this example, I am going to explain it for Excel 2003. Works equally for Excel 2007 and Excel 2010)

10) In the menu bar, there is an option called ‘Data’.

11) Under that ‘Data’ menu, go to ‘Import External Data’. Another sub-side bar opens up which has options like ‘Import Data’, ‘New Web Query’, ‘New Database Query’ etc.

12) Click on ‘New Web Query’. A new pop up window opens up within Excel.

13) Now, we use the Economic Times Balance Sheet web page URL (step 8), copy that URL and paste it in the Address field of this ‘New Web Query’ pop up window. Hit ‘Go’

14) The Balance Sheet page of ET should open up in this pop up window. (If you get some error saying, ‘Scripts error’ etc., just press ‘Yes’ on the error window and go ahead – no impact)

15) Scroll down within this pop up window to the Balance Sheet table.

16) On the top left corner of the Balance Sheet table, there is an image of a little Arrow inside a Yellow box. Click on it. The Arrow will change to a tick mark.

17) Click on ‘Import’ (on the bottom right hand corner of the pop up window)

18) Excel will prompt you with a window ‘Import Data’ and ask you to specify where it has to import the data.

19) Let’s say it’s a new excel sheet and you want to import it in cell A1. Specify that cell as A1 and click Ok.

20) Excel will say, ‘Getting data’ etc (it must be pulling data now).

21) In a couple of seconds, Voila! You have the BalanceSheet data imported into Excel sheet without any effort J

You can do similar stuff for P&L, CashFlow and practically any page and enjoy your results.

Few Additional Points:

1) You can do this for Money Control too. They have 10 years of Cash Flow data which is obviously more useful in analysis than just the 5 years in ET.

2) I am not too sure about Consolidated vs Standalone results in ET vs Moneycontrol vs Valuenotes etc. Please check the same before downloading. The only condition is they should have different URLs (with Ajax and XHTML and what not these days, you can have a lot of data within/between tabs within the same webpage without affecting the URL – so please check). Between, it is always recommended to do analysis off consolidated results (unless you want to do a Sum of Parts valuation using Standalone results and then combining your analysis).

3) Siddharth’s tip – Smallcap/Microcaps usually don’t have subsidiaries. They only have Standalone results. MoneyControl’s results will work better since they have 10yrs of Standalone data if you are researching small cap/microcap stocks. (Warning: Please do check the Annual Report if these small cap/microcap have any subsidiaries before downloading data of standalone vs consolidated)

Request:

If anybody reading this is already an expert in Excel/Macro, I have a request (post a link if you have done this already). Can you create a web query file (*.iqy is the extension I think – I am not much of a techno person) that would prompt me to enter the Stock Quote/Ticker symbol from Excel and then this query pulls BS/CF/PL data from ET/Moneycontrol automatically and populate it in Excel (just like above)? (Steps 1-21 are really simple and repetitive for each page – macros should ideally work, as far as I know. However, entering the stock quote/ticker query is a challenge – can somebody crack this please – it would lift a huge burden for many of us).