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).

 

Advertisements

, , , ,

  1. #1 by Santosh on January 8, 2011 - 4:41 PM

    this is brilliant. never thought it can be as simple as this!

  2. #2 by Kiran on January 9, 2011 - 2:59 PM

    Hi Santosh,

    Welcome to my blog!

    Simple indeed and very effective. Cuts down the downloading headache by more than half. More time for analysis – yay! 🙂

  3. #3 by Anand on January 10, 2011 - 12:53 PM

    That is quite Informative One . Thanks for sharing.

  4. #4 by kdaaku on January 12, 2011 - 11:03 AM

    Hi Anand,

    Welcome to the blog. Thanks for your kind words.

  5. #5 by Ne01985 on January 19, 2011 - 11:33 PM

    It’s working fine with ET, but not with money control.

  6. #6 by Kiran on January 20, 2011 - 3:22 PM

    @Ne01985 – Can you explain a bit more? It’s working for me alright.

    On the other hand, ET and Moneycontrol publish the same 5 yr data. How does it matter anyway?

  7. #7 by pritam on April 18, 2011 - 5:53 PM

    Hi

    I need to copy data from webpages into xls for many stocks

    there are 2 or 3 diff pages for each stock how can it be done in EXCEL so that a final table appears in my desired format .

    • #8 by GANESH on April 14, 2012 - 4:38 AM

      CAN U SEND ME DETAIL HOW TABLE APPEARS IN UR DESIRED FORMAT HELP ME

  8. #9 by Kiran on April 18, 2011 - 8:59 PM

    Hi Pritam,

    Unfortunately I know of no automatic way to get your desired results.

    Unless you know VBA (even then, I don’t think it’s possible), you need to copy individual pages, and then record a macro of getting the data from the different excel sheets into another sheet which has your desired format (within the same excel file).

  9. #10 by Anbazhagan Manoharan on September 13, 2011 - 2:31 AM

    Thank you. Useful idea.

  10. #11 by Rakesh K Singh on January 14, 2012 - 5:44 AM

    Thanks for this article. I’ll try to implement this and see if it works.

  11. #12 by satvik on February 21, 2012 - 10:05 PM

    GOD BLESS YOU,U REALLY HELPED ME

  12. #13 by Ajhay on April 14, 2012 - 4:50 AM

    Thanx a lot Mr.Kiran . U made my work simpler 🙂

  13. #14 by Vijay on September 19, 2012 - 6:07 PM

    I want the current prize of a particular stock in particular excel sheet cell. What is way to import it and refresh it time to time?

  14. #15 by Modesto on June 5, 2013 - 8:04 PM

    I benefit from looking through your websites. Thanks a lot!

  15. #16 by fat burner review on July 22, 2013 - 3:54 PM

    I don’t write many remarks, however i did some searching and wound up here Importing Financial Data into Excel – Indian Stock Market | Quest for Value. And I do have 2 questions for you if you don’t mind.

    Is it only me or does it look like a few of these comments come across like coming from brain dead people?
    😛 And, if you are writing on additional places, I would like to follow anything new you have to post.
    Would you list of the complete urls of all your communal
    sites like your Facebook page, twitter feed,
    or linkedin profile?

    Recommended Reading
    Recommended Reading
    Recommended Reading

  16. #17 by krishnendu dutta on January 10, 2014 - 12:22 AM

    First of all thanks for your information.
    But when i import data from ET some unwanted data also come.
    and i can not import data from money control will you please give an example how one can import data from money control.

  17. #18 by Abhishek on January 29, 2014 - 6:13 PM

    Hey, I joined this EXCEL CHAMPIONSHIP http://www.dezyre.com/competition/excel-championship#.UuN2whC6bIU
    Apparently the registration is for free for this competition and the prize money is $500 !!
    I am very excited so just wanted to share with you guys.

  18. #19 by Neil on April 26, 2014 - 8:41 PM

    Thank you very much. It was simple and in front of me all the time. Just did not look at the right option till you pointed to it.

  19. #20 by Chaugule on April 7, 2015 - 7:42 PM

    Can someone help with 10 year PE high/low of the the sensex & Nifty stocks each year from 2005-2014

  20. #21 by Sowmay Jain on June 22, 2016 - 2:24 PM

    How can get all live data by just putting down ticker name?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: