Automated Excel Stock Analyzer

This is a major update to the Excel Stock Analyzer. Company financial data is now automatically downloaded from Morning Star. and stock price history from Money control. Jignesh Acharya pointed out that there are issues with the Yahoo finance stock price downloader.

Here are the main features of the analyzer

Automated-stock-analyzer-1
The 'Read me first' page

This is a screenshot of the read-me page.

Valuation sheets 2 and 3 are based on Warren Buffett’s approach to stock valuation.

They help the investor ask and answer questions like

  1. What is the earnings yield? How does it compare with long-term govt. bond yield?
  2. How do returns projected with historical earnings growth fare?
  3. What is the sustainable growth rate of the company, based on ROE and dividend payout ratio?
  4. What is the return on investment based on book value growth rate and for different PE values?

 

Automated-stock-analyzer-2
Input page for getting financials from Morning Star
Automated-stock-analyzer-3
Input page for getting stock price from moneycontrol

Excel-stock-analysis-spreadsheet-1

The analysis sheet
The analysis sheet

All metrics from the analysis sheet can be graphically analyzed

All metrics from the analysis sheet can be graphically analyzed

Discounted-Cash-Flow-Valuation

New version: Download the automated Excel stock analyzer version 5.0Download the automated Excel stock analyzer version 3.0

Comments and suggestions are welcome.

Install Financial Freedom App! (Google Play Store)

Install Freefincal Retirement Planner App! (Google Play Store)

book-footer

Buy our New Book!

You Can Be Rich With Goal-based Investing A book by  P V Subramanyam (subramoney.com) & M Pattabiraman. Hard bound. Price: Rs. 399/- and Kindle Rs. 349/-. Read more about the book and pre-order now!
Practical advice + calculators for you to develop personalised investment solutions

Thank you for reading. You may also like

About Freefincal

Freefincal has open-source, comprehensive Excel spreadsheets, tools, analysis and unbiased, conflict of interest-free commentary on different aspects of personal finance and investing. If you find the content useful, please consider supporting us by (1) sharing our articles and (2) disabling ad-blockers for our site if you are using one. We do not accept sponsored posts, links or guest posts request from content writers and agencies.

Blog Comment Policy

Your thoughts are vital to the health of this blog and are the driving force behind the analysis and calculators that you see here. We welcome criticism and differing opinions. I will do my very best to respond to all comments asap. Please do not include hyperlinks or email ids in the comment body. Such comments will be moderated and I reserve the right to delete  the entire comment or remove the links before approving them.

51 thoughts on “Automated Excel Stock Analyzer

  1. brahmalok

    getting Err:502 when entering the stock name. I am using OpenOffice if it matters. Macros are all enabled. Some issue in VLOOKUP/INDIRECT.

    Reply
  2. brahmalok

    getting Err:502 when entering the stock name. I am using OpenOffice if it matters. Macros are all enabled. Some issue in VLOOKUP/INDIRECT.

    Reply
  3. Bharani

    Only the stock price download is not working for me (I use Excel 2013). I tried VA Tech Wabag (drop down works). Can you please review?

    Reply
    1. freefincal

      Initially it did not work for me too (I tried after seeing your comment). I tried and it worked. I have reuploaded the file. Please use this one and see.

      Reply
  4. Bharani

    Only the stock price download is not working for me (I use Excel 2013). I tried VA Tech Wabag (drop down works). Can you please review?

    Reply
    1. freefincal

      Initially it did not work for me too (I tried after seeing your comment). I tried and it worked. I have reuploaded the file. Please use this one and see.

      Reply
    1. freefincal

      The problem is with moneycontrol. The stocks codes are not consistent. Let me know for what stocks it does not work. I will make necessary corrections and repost

      Reply
    1. freefincal

      The problem is with moneycontrol. The stocks codes are not consistent. Let me know for what stocks it does not work. I will make necessary corrections and repost

      Reply
  5. Vandhana

    amazing effort Pattu sir...some quick additional stuff I would love to see
    1. yoy growth in operating margins,net margins, net incomes
    2. working capital/sales
    3. Salary cost, avg salary per employee (excluding management salaries)
    4.Interest percentage paid (interest paid/debt outstanding)
    5. Cash from operations-depreciation
    6. % of promoter shareholding
    My apologies if all these are already there in the file and I am unable to see where they are.

    Reply
    1. freefincal

      Thank you. I have reuploaded the file with yoy op margin and working capital. Other features dont appear to found in the morning star page. Please have a look in the MorningStar Data sheet and let me know if any other information can be added.

      Reply
  6. Vandhana

    amazing effort Pattu sir...some quick additional stuff I would love to see
    1. yoy growth in operating margins,net margins, net incomes
    2. working capital/sales
    3. Salary cost, avg salary per employee (excluding management salaries)
    4.Interest percentage paid (interest paid/debt outstanding)
    5. Cash from operations-depreciation
    6. % of promoter shareholding
    My apologies if all these are already there in the file and I am unable to see where they are.

    Reply
    1. freefincal

      Thank you. I have reuploaded the file with yoy op margin and working capital. Other features dont appear to found in the morning star page. Please have a look in the MorningStar Data sheet and let me know if any other information can be added.

      Reply
  7. Ravi Kumar N

    I tried both coal india and hdfc bank using moneycontrol sheet and in both the cases the excel tool takes hell lot of time to populate and finally it hangs... any help sir?

    Reply
  8. Ravi Kumar N

    I tried both coal india and hdfc bank using moneycontrol sheet and in both the cases the excel tool takes hell lot of time to populate and finally it hangs... any help sir?

    Reply
  9. Sandeep Arora

    Amazing Work, Pattu, just to understand a little better, if a stock is showing "overvalued" for a 3 year horizon and shows "undervalued" for 5 years, does it mean that period of owning the share should be more than 3 years (I am referring to Reliance Industries Limited in this case)

    Reply
    1. freefincal

      I know next to nothing about this. If you are confident about the company one can buy even if the stock is overvalued. I think confidence about the business is more important than valuation results.

      Reply
    2. Rushik Shah

      Yes it would certainly mean that it is undervalue only if you have a horizon of 5 year or more. But if you are looking to exit in three years, it is over valued for you. Also, lot would depend on assumption you make in B7 and B11. It would move current value significantly.

      Reply
  10. Sandeep Arora

    Amazing Work, Pattu, just to understand a little better, if a stock is showing "overvalued" for a 3 year horizon and shows "undervalued" for 5 years, does it mean that period of owning the share should be more than 3 years (I am referring to Reliance Industries Limited in this case)

    Reply
    1. freefincal

      I know next to nothing about this. If you are confident about the company one can buy even if the stock is overvalued. I think confidence about the business is more important than valuation results.

      Reply
    2. Rushik Shah

      Yes it would certainly mean that it is undervalue only if you have a horizon of 5 year or more. But if you are looking to exit in three years, it is over valued for you. Also, lot would depend on assumption you make in B7 and B11. It would move current value significantly.

      Reply
  11. Rushik Shah

    Hi,

    This is really amazing. Good work and very helpful.

    Something valuable I have always felt while analyzing bulk data is presentation and color coding. For instance large losses or spikes in leverage could be color coded to get highlighted. This can be done using conditional formatting.

    I think it would be helpful because there is far too much data on sheet. and often something critical can get lost in clutter.

    Just a thought.

    But still its wonderful work

    -Rushik

    Reply
  12. Rushik Shah

    Hi,

    This is really amazing. Good work and very helpful.

    Something valuable I have always felt while analyzing bulk data is presentation and color coding. For instance large losses or spikes in leverage could be color coded to get highlighted. This can be done using conditional formatting.

    I think it would be helpful because there is far too much data on sheet. and often something critical can get lost in clutter.

    Just a thought.

    But still its wonderful work

    -Rushik

    Reply
  13. addadutta

    I tried with The Byke Hospitality and BHEL . I am getting the error message in Moneytcontrol tab. Run time error 13 - Type Mismatch. Need your help in sorting this.
    Morningstar data is coming correctly. I am trying to automated excel ver 3.

    Reply
  14. Pushkaraj

    Hi,
    Amazing effort sir. It seems that morningstar data is based on consolidated results of the company. Is there a way to get the data from screener which has both standalone and consolidated data. So the user will both options: standalone and consolidated.

    Reply
  15. sriram

    Wonderful sheet. Started looking into the sheet and found that usage of 'specific codes' for each stock used by morningstar.in. I stumbled across an address "http://www.morningstar.in/handlers/autocompletehandler.ashx?criteria=stockname" which mimics the search criteria in that website by replacing the "stockname" to the stock name of company. Maybe useful in future versions of the sheet. Just a suggestion... Much appreciated in sharing your work...

    Reply

Do let us know what you think about the article