Menu

Sign up for More Articles:

Ecomonic Times covered our Investing Journey:

Price Saver Packages:

Investing Resources:

[Updated Version 2] Stock Analysis Excel Template (Screener.in)

Modified on May 3, 2019

All of us are aware about the usefulness of excel templates in stock analysis. These templates help the investors in quick analyses of the data as it presents the data in easily comprehensible formats like a dashboard.

We use a customized excel template to analyse stocks as per our preferred parameters by using the data downloaded from screener.in website. This template acts as a dashboard of key analysis parameters, which help us make an opinion about any stock within a short amount of time (sometimes within a few minutes). We have used this excel template and the analysis output in many stock analysis articles published on this website. You may read about various stock analysis articles written by analyzing companies using the excel template in the “Author’s Response” segment of the “Company Analysis” articles on our website.

In the past, many readers/investors had asked us to provide the copy of this excel file. Due to repeated requests from investors about sharing the excel template, we provided our customized excel stock analysis template, which is compatible with screener.in and provides stock data as a dashboard, as a paid download feature.

Investors who wish to get the customized excel stock analysis template may download it from the following link:

Dr Vijay Malik premium paid services workshops, guest lectures, Stock Analysis Excel Template compatible with Screener.in

Since the initial launch, many investors have used this template and provided their feedback & views about the excel template.

 

Users/Investors’ Feedback about the Stock Analysis Excel Template:

“This is a great tool for getting down to the heart of a company’s financials. When I was doing my MBA at NYU I had a valuation professor who encouraged everyone in the class of 60 to make their own customized sheet similar to what you’ve made. I was a fan of Buffett so I remember keeping some of his metrics in view and creating a sheet! Of course yours is head and shoulders above anything else I’ve seen – kudos!”

– Uday (via email)

“The excel template is quite useful. It makes things easy for us in not doing the hard labor and calculating all vital data for each company separately. “

– Ashish

“Thank you Dr. Malik. The tool is indeed very useful and super-fast to use. God bless you for creating it!

– Harsh (via email)

“Dear Sir, I have downloaded the excel. It’s simply AMAZING, EFFORTLESS and AWESOME. Kudos to you and your team for wonderful creation..”

– Vikram (via email)

“I have purchased your excel template and honestly speaking it is just great. Thanks for such a brilliant tool and the lessons you have taught here. This excel is really a great source of analyzing the financial position of the company”

– Krishnendu (as comments at “Ask Your Queries”)

“I am using your excel sheet on screener to analyse stocks. It’s been very useful and the concept of SSGR and FCF helps separate quality stocks from the rest.”

– Hardik (via email)

 

Stock Analysis Excel Template Version 2: A comprehensively updated version

Now we have updated the Stock Analysis Excel Template to incorporate our additional learning from doing various stocks analysis over recent years and we present it as the latest version of the excel template.

The structure and sample screenshots of the updated stock analysis excel template file (version 2) are as below:

1) Analysis sheet: This updated (version 2) sheet presents values of more than 60 key parameters in the form of a dashboard. These parameters cover analysis of profitability, capital structure, valuation, margin of safety, cash flow, creation of wealth, sources of funds, growth rates, return ratios, working capital and operating efficiency etc.

Having a quick look at these parameters in the form of the dashboard helps in quick assessment about the company, its historical performance and its current state of affairs.

Screenshot of larger resolution output of the Analysis Sheet: Click Here

 

Updates in this comprehensively revised Excel Sheet (version 2) over the previous version (1.6):

  • Added the section of various expenses as a percentage of sales (Raw material, Power & Fuel, Employee Costs, Selling and Admin Costs, other manufacturing expenses and Other expenses as a percentage of sales).
  • Added working capital cycle (days) calculation based on inventory and trade receivables.
  • Added rows for trade receivables and inventory and the calculation of total funds consumed in/released from receivables and inventory over last 10 years. It is helpful in quick assessment of one of the key reasons for differences in PAT and CFO.
  • Brought together the data of CFO, Capex, FCF, Total Debt, Share Capital, Dividend Payment and Cash + investment on the upper section of dashboard. This is essential to analyse the cash movements in the company in any year. E.g. in case, the company had a negative FCF, then investor can immediately see the sources of funds to meet negative FCF. In such cases, the company may have raised more debt, which will increase total debt. OR the company may have raised additional equity, which will show as increase in share capital. OR the company may have used existing cash, which will show as decline in cash + investment. Similarly, if a company has positive FCF, then it may show as an increase in cash + investment OR as decline in total debt OR high dividend payments or buyback (decline in share capital). The presence of these data points at the top section of the dashboard helps in quick assessment of flow of cash in the company over the years.
  • Added a calcuation of Free Cash Flow to Equity (FCFE), which is calculated as (FCF-interest expense). FCFE calculated in this manner indicates the surplus cash out of CFO remaining after meeting capital expenditure (Capex) and all interest payments (both capitalzied as part of capex as well as the interest expensed in P&L)

It is advised that investors should read the following article provides details of all the parameters provides in the Analysis sheet, their description, their method of calculation and their interpretation: Complete Details: Stock Analysis Excel Template (Screener.in)

2) Description sheet: This sheet contains details about description and interpretation of about each of the more than 60 parameters. It is advised that investors should read this sheet in detail before starting with the analysis of companies by using this template.

Read the complete details of the Description Sheet: Click Here

3) Instructions sheet: This sheet contains details about the steps by step apporach to get started with this sheet on screener.in website, change in settings for Microsoft Excel to resolve common issues and other instructions for the buyers.

Screenshot of the Instructions Sheet: Click Here

See the step by step guidefor uploading the excel sheet on Screener.in with screenshots: How to Use Screener.in Export to Excel tool

4) Version history: This sheet contains details about the changes/updates made in each of the new versions of the sheet.

You may read about various stock analysis articles and see the screenshots of the excel template in the “Author’s Response” segments on the following link: Stock Analysis Articles

 

Dr Vijay Malik premium paid services workshops, guest lectures, Stock Analysis Excel Template compatible with Screener.in

 

How to Download this Excel Template:

After you have purchased the excel template at the following page (Stock Analysis Excel Template version 2), you may download the excel in any of the three methods:

  1. After the payment is successfully done on Instamojo (or PayPal), you will be taken to a page showing the successful completion of your order. On this page, you will be given a link to download the excel file.
  2. After the order is successful, you will receive an email from us containing your order receipt and payment details. This order receipt will have a link to download the excel file.
  3. After successful order, you will get an email from us containing your username and password to log in at premium.drvijaymalik.com. You may log in to your account using these details and then download the excel file from My Account > Downloads

If you still face any challenges in downloading the excel file, then you may email us at vijay.malik@drvijaymalik.com, detailing the issues that you are facing. We would be happy to help you out.

 

Using the Excel Template on Mobile Phones:

  • Investors may use the app “Google Sheets” to open the excel template on mobile devices.

 

Key Instructions to the Buyer:

1) This excel sheet is for the sole use of the buyer from www.drvijaymalik.com. Any copying and sharing of this excel sheet is strictly prohibited.

2) This purchase is limited to the current version of the excel sheet only. Any future updates/versions of the excel sheet need to be bought separately.

If in future, because of any reasons, Screener changes the format of data it provides in the “Data Sheet” or makes this template invalid, then I would not be able to provide resolution of the issues in this excel template.

In such a scenario, I might come up with a new version of the excel template. However, the new version needs to be bought separately by the users.

3) In the original versions of the excel template sheet, all the formula links were locked for editing. However, after request from investors, now all the formula links are unlocked. Nevertheless, I suggest that investors should not make any change to any formula/sheet in this excel workbook. Any change might lead to corruption of the formula links and might lead to erroneous results

4) Links to detailed articles on drvijaymalik.com have been provided under each formula segment in the “Description” sheet, which you may read to learn more about each of the analysis parameters.

In case you have any further query after going through the article links shared in the “Description” sheet, then you may ask your query on www.drvijaymalik.com as a comment to the “Ask Your Queries” page.

5) Detailed interpretations and descriptions of each of the parameters used for analysis on “Dr Vijay Malik Analysis” sheet are provided in the “Description” sheet

6) Please read the “Description” sheet thoroughly before analysing the data of any company.

7) Steps to use the excel template sheet are detailed in the “Instructions” sheet.

8) Target Company:

The aim of the analysis is to find a debt free company, which is growing at a reasonable pace with sustained/improving profitability margins, which has been showing improvement in operating efficiency and has been generating positive Free Cash Flow over the years.

If an investor is able to find such a company that is available at a cheap price (P/E ratio < 10), then investment in such a company has a high probability of creating wealth for the shareholders.

All the best for your investing journey!

Regards,

Dr Vijay Malik

 

Frequently Asked Questions

 

Q. I had purchased a previous version of this excel template in the past. Will I get this newer version for free or any discount on purchase of this newer version?

Ans: Unfortunately, as mentioned earlier, the purchase of any version of excel template is limited to that particular version only. An investor needs to purchase new/updated version of the excel template separately. There is no discount based on previous purchases.

 

Q. If I purchase the excel template today and Screener team makes any changes in their website afterwards, which make this excel template invalid, then will you provide me any resolution for this error? Or in such a case, will you provide me a new compatible excel template for free?

Ans: As mentioned earlier, the purchase of any version of excel template is limited to that particular version only. In case, Screener team makes any changes to their website, which make this excel template invalid, then we would not be able to help you as we do not intend to provide any continued support to keep the excel template active. In such a case, we may or may not come up with a newer version of excel template. However, the newer version of the excel template needs to be purchased by investors separately.

 

Q. What are the updates/new parameters in this updated version of Excel Template (version 2) over previous version (1.6)?

  • Added the section of various expenses as a percentage of sales (Raw material, Power & Fuel, Employee Costs, Selling and Admin Costs, other manufacturing expenses and Other expenses as a percentage of sales).
  • Added working capital cycle (days) calculation based on inventory and trade receivables.
  • Added rows for trade receivables and inventory and the calculation of total funds consumed in/released from receivables and inventory over last 10 years. It is helpful in quick assessment of one of the key reasons for differences in PAT and CFO.
  • Brought together the data of CFO, Capex, FCF, Total Debt, Share Capital, Dividend Payment and Cash + investment on the upper section of dashboard. This is essential to analyse the cash movements in the company in any year. E.g. in case, the company had a negative FCF, then investor can immediately see the sources of funds to meet negative FCF. In such cases, the company may have raised more debt, which will increase total debt. OR the company may have raised additional equity, which will show as increase in share capital. OR the company may have used existing cash, which will show as decline in cash + investment. Similarly, if a company has positive FCF, then it may show as an increase in cash + investment OR as decline in total debt OR high dividend payments or buyback (decline in share capital). The presence of these data points at the top section of the dashboard helps in quick assessment of flow of cash in the company over the years.
  • Added a calcuation of Free Cash Flow to Equity (FCFE), which is calculated as (FCF-interest expense). FCFE calculated in this manner indicates the surplus cash out of CFO remaining after meeting capital expenditure (Capex) and all interest payments (both capitalzied as part of capex as well as the interest expensed in P&L)

It is advised that investors should read the following article provides details of all the parameters provides in the Analysis sheet, their description, their method of calculation and their interpretation: Complete Details: Stock Analysis Excel Template (Screener.in)

 

P.S.

  • Please note that screener.in uses the data from capitaline, which is a reasonably good source of data. However, before taking the final investment decision about any company, it is advised that the investor should cross-check the data from the annual report of the company as the annual report is the most accurate source of the data available in the public domain.

Get a FREE e-book and join 43,000 investors

Free Investing Ebook Case Studies Peaceful Investing
  • Get the e-book: "Case Studies: Applying Peaceful Investing Approach"
  • Learn fundamental analysis by reading 20 case studies in this e-book
  • Get email notifications of our future articles

Follow My Portfolio with Latest Buy/Sell Transaction Updates

  • Historical annualized return (CAGR) of the portfolio 34.16% against CAGR of Sensex of 11.35%
  • We identified companies, which were later invested by Sanjay Bakshi, Mohnish Pabrai, PE funds, Mutual Funds
  • See details of stocks in our portfolio
  • Get updates of buy/sell transactions in our portfolio by email

"Peaceful Investing": My Stock Investing Approach

“Peaceful Investing” approach is the result of my more than a decade of experience in equity markets. This approach helped me invest even when I had a full-time corporate job and could not spare a lot of time for stock analysis. During my investing journey, I have faced all the common challenges of the investors, the biggest one being “scarcity of time”. “Peaceful Investing” approach keeps in mind that an investor will have limited amount of time to spare for stock investing. 

The objective of “Peaceful Investing” approach is the selection of such stocks, where once an investor has put in her money, then she may sleep peacefully. Therefore, if later on, the stock prices rise, then the investor is happy as she is now wealthier. On the contrary, if the stock prices fall, even then the investor is happy as she can now buy more quantity of the selected fundamentally good stocks.

Watch Balance Sheet Analysis through a Free Sample Video:

Play Video

Please share your comments here:

Get a FREE e-book and join 43,000 investors

Free Investing Ebook Case Studies Peaceful Investing
  • Get the e-book: "Case Studies: Applying Peaceful Investing Approach"
  • Learn fundamental analysis by reading 20 case studies in this e-book
  • Get email notifications of our future articles
Bitnami