Screener.in is one of the best resources available to equity investors in Indian markets. It is a website, which provides the investors with the key information about companies listed on Indian stock exchanges (BSE and NSE).
We have been using screener.in as an integral part of our stock analysis and investments since last many years and have been continuously impressed by the tools offered by it that cut down the hard work of an investor. Some of these features, which are very useful for equity investors are:
- Filtering of stocks based on multiple objective financial parameters. Investors can share these parameters in the form of “Saved Screens”.
- Company information page, which collates the critical information about a company on one single page including balance sheet, profit & loss, cash flow, quarterly results, corporate announcements, links to annual reports, credit rating reports, past stock price movement etc. A scroll down on the company page provides an investor most of the critical information, which is needed to make a provisional opinion about any company.
- Email alerts to investors for new stocks meeting their “Saved Screens”
- Email alerts to investors on updates about companies in their watchlist.
All these features are good and have proved very beneficial to investors. However, there is one additional feature of screener.in, which we have found unique to screener.in. This feature is “Export to Excel”.
“Export to Excel” feature of screener.in lets an investor download an Excel file containing the financial data of a company on the investor’s computer. The investor can use this excel file with the data to do a further in-depth analysis of the company.
The most important part of the “Export to Excel” feature is that it allows the investor to customize the Excel file as per her preferences. The investor can create her own ratios in the excel file. She can arrange the data as per her preferred layout in the excel file and when she uploads her customized excel file in her account at screener.in, then whenever she downloads the “Export to Excel” sheet for any company, she gets the data of the company in her customized format with all her own ratios auto calculated and presented to her in her preferred layout.
The ability to get the financial data of any company in our customized format with our key ratios and parameters auto calculated has proved very useful to us in our stock analysis. “Export to Excel” feature of screener.in allows us to analyse our preferred financial ratios of any company at the click of a mouse, which makes it very easy for us to make a preliminary view about any company within a short amount of time. Sometimes within a few minutes.
We have been using the “Export to Excel” feature since last many years and it has become an essential part of our stock analysis. It has helped us immensely while doing an analysis of different stocks and while providing our inputs to the stock analysis shared by the readers of our website. Investors may read the “Analysis” articles at our website on the following link:
Further Reading: Stocks’ Analysis articles
Over time, more and more investors have started using the “Export to Excel” feature of screener.in and as a result, we have been getting a lot of queries about it at the “Ask Your Queries” section of our website. These queries have been ranging from:
- Why is there a difference between the data provided by the screener and the company’s annual report?
- How does screener calculate/group the annual report data in the “Export to Excel” tool?
- What is the source of the data that screener.in provides to its users?
- How to customize the “Export to Excel” file?
- How to upload the customized file in one’s account at screener.in
We have been replying to such queries based on our understanding of screener.in, which we have gained by using the website for multiple years and based on our learning by listening to the founders of screener.in (Ayush Mittal and Pratyush Mittal) in June 2016 at the Moneylife event in Mumbai.
In June 2016, Moneylife arranged a session, “How to Effectively Use screener.in” by Ayush and Pratyush at BSE, Mumbai in which Ayush and Pratyush explained the features of screener.in in great detail. This session was recorded by Moneylife and has been made available as a premium feature on their private YouTube channel.
The recorded session can be accessed at the following link, which would require the viewers to pay to view it:
(Disclaimer: we do not receive any referral fee from Moneylife or Screener.in to recommend the above video link to the session by Ayush and Pratyush. For any further information about the video, investors may contact Moneylife directly)
As mentioned earlier that we have been replying to investors’ queries related to “Export to Excel” feature on “Ask Your Queries” section of our website. However, in light of repeated queries from different investors, we have decided to write this article, which addresses key aspects of “Export to Excel” feature of screener.in.
The current article contains explanations about:
- The financial data provided by screener.in in its “Export to Excel” file and its reconciliation with the annual report of companies
- Steps to customize the “Export to Excel” template by investors
- Steps to upload the customized Excel file on screener.in so that in future whenever any investor downloads the “Export to Excel” file of any company, then it would have the data in the customized preferred format of the investor.
Financial Data provided by Screener
The “Export to Excel” file of screener.in contains a “Data Sheet”, which contains the financial data of the company, which in turn is used to calculate all the ratios and do in-depth analysis. As informed by Ayush and Pratyush in the Moneylife session, screener.in sources its data from Capitaline, which is a renowned source of financial data in India.
The data sheet contains the data of the balance sheet, profit & loss, quarterly results, cash flow statement etc. about the company.
We have taken the example of a company Omkar Speciality Chemicals Limited (FY2016: standalone financials) to illustrate the reconciliation of the data provided by screener.in in its “Export to Excel” file and data presented in the annual report.
Let’s now understand the data about any company, which is provided by screener.in.
This is the section, where investors get most of the queries as screener.in groups the annual report items differently while presenting the data to investors. Let’s understand the data in the balance sheet section of the “Data Sheet” of “Export to Excel” file taking the example of FY2016 data of Omkar Speciality Chemicals Limited:
Balance Sheet Screener.in “Data Sheet”
Balance Sheet (Annual Report FY2016)
- Equity Share Capital: It represents the paid up share capital taken directly from the balance sheet (₹20.58 cr.).
- Reserves: It represents the Reserves & Surplus taken directly from the balance sheet (₹160.87 cr.).
- Borrowings: It represents the entire debt outstanding for the company at March 31, 2016 (₹185.76 cr.). It comprises of the following components:
- Long Term Borrowings: ₹79.23 cr taken directly from the balance sheet.
- Short-Term Borrowings: ₹95.49 cr. taken directly from the balance sheet.
- Current Liabilities of long-term borrowings: ₹11.04 cr. taken from the notes to the financial statements. This data is included as part of “Other Current Liabilities” of ₹15.89 cr. under “Current Liabilities” in the summary balance sheet. In the annual report of Omkar Speciality Chemicals Limited, “Current Liabilities of long-term borrowings” can be found in Note No. 7 at page 89 of the FY2016 annual report.
- Sum of these three items: 79.23 + 95.49 + 11.04 = ₹185.76 cr. Investors might find a the small difference for various companies, which might be due to rounding off.
- Other Liabilities: It represents the sum of rest of the liabilities (₹79.52 cr.) like:
- Deferred Tax Liabilities: ₹8.04 cr. taken directly from the balance sheet
- Long – Term provisions: ₹2.42 cr. taken directly from the balance sheet
- Trade Payables: ₹50.52 cr. taken directly from the balance sheet
- Other Current Liabilities net of “Current Maturity of Long Term Debt”: ₹15.89 – ₹11.04 = ₹4.85 cr. is considered in this section.
- Short-Term Provisions: ₹13.69 cr. taken directly from the balance sheet
- Sum of these items: 8.04 + 2.42 + 50.52 + 4.85 + 13.69 = ₹79.52 cr. Investors might find a small difference for various companies, which might be due to rounding off.
- Net Block: It represents the sum of Tangible Assets (₹ 77.75 cr) and Intangible Assets (0.15 cr.) taken directly from the balance sheet. The total netblock in the “Data Sheet” is ₹77.90 cr, which is the sum of the tangible and intangible assets.
- Capital Work in Progress: It represents the paid up Capital Work in Progress taken directly from the balance sheet (₹112.67 cr.).
- Investments: It is the sum of both the Current Investments and the Non-Current Investments presented in the balance sheet. The Current Investments are shown under “Current Assets” in the balance sheet whereas the Non-Current Investments are shown under “Non-Current Assets” in the balance sheet.
- In the case of Omkar Speciality Chemicals Limited, there are no current investments, therefore, the “Investments” (₹13.91 cr.) in the “Data Sheet” of “Export to Excel” file is equal to the Non-Current Investments in the balance sheet (₹13.91 cr.)
- Other Assets: It represents (₹242.25 cr.) the sum of rest of the assets:
- Long-term Loans and Advances: ₹26.53 cr. taken directly from the balance sheet
- Inventories: ₹61.78 cr. taken directly from the balance sheet
- Trade Receivables: ₹102.26 cr. taken directly from the balance sheet
- Cash and Cash Equivalents: ₹6.63 cr. taken directly from the balance sheet
- Short-term Loans and Advances: ₹44.14 cr. taken directly from the balance sheet
- Other Current Assets: ₹0.89 cr. taken directly from the balance sheet
- Sum of these items: 26.53 + 61.78 + 102.26 + 6.63 + 44.14 + 0.89 = ₹242.23 cr. The difference of ₹0.02 cr. in this sum and the figure in the “Data Sheet” of ₹242.25 cr. is due rounding off.
It is important to note that certain additional items, if present in the balance sheet, are usually shown by screener.in as part of “Other Liabilities” or “Other Assets” depending upon their nature (Liability/Assets). E.g. “Money Received Against Share Warrants” is shown as a part of “Other Liabilities” in the “Data Sheet” in the “Export to Excel” file.
Profit and Loss:
Let us now study the reconciliation of the profit and loss data of the company provided by screener.in in the “Data Sheet” of “Export to Excel” and the annual report:
Profit & Loss Statement Screener.in “Data Sheet”
Profit & Loss Statement Annual Report FY2016
- Sales: It represents only the “Revenue from Operation” of ₹300.02 cr. taken directly from the P&L statement.
- Raw Material Cost: It represents the sum of Cost of Material Consumed (₹167.09 cr) and Purchase of stock in trade (₹73.42 cr.) taken directly from the P&L statement.
- Sum of these two items: 167.09 + 73.42 = ₹240.51 cr. Investors might find a small difference for various companies, which might be due to rounding off. In the case of Omkar Speciality Chemicals Limited, the difference is ₹0.01 cr.
- Change in Inventory: ₹12.93 cr. taken directly from the P&L statement: “Changes in Inventories of Finished Goods, Work in progress and Stock in Trade”.
- It is to be noted that if the inventories have increased during the period, then this figure would be negative and if the inventories have decreased during the period, then this figure would be positive.
- Negative figure (increase in inventory) indicates that some material was purchased whose cost is included in the Raw Material Cost, but this material is yet to be sold as finished goods because this material is still lying in inventory. That’s why this cost is not the cost for this period and thus deducted from the expenses of this period.
- Positive figure (reduction in inventory) indicates that some amount of finished goods sold in this period were created from the raw material purchased in previous periods. Therefore, the raw material cost of the current period does not include the cost of these goods whereas the sales of this period include the revenue from these sales. That’s why the cost is added in the expense of this period.
- Power and Fuel, Other Mfr. Exp, Selling and admin, Other Expenses: together constitute the “Other Expenses” item of the P&L statement. The breakup of “Other Expenses” is present in the notes to financial statements in the annual report.
- Sum of these four items in the “Data Sheet”: 1.45 + 4.74 + 4.08 + 5.87 = ₹16.14 cr. is equal to the “Other Expenses” figure in the P&L statement. Any small difference might be due to rounding off.
- Many times, there are 10-30 items, which come under “Other Expenses” in the annual report and it becomes difficult for investors to segregate, which of these items are grouped by screener under “Other Mfr. Exp” or under “Other Expenses” or under “Selling and admin” etc. E.g. in the case of Omkar Speciality Chemicals Limited, the Power and Fuel costs of ₹1.45 cr. seem to include both the “Factory Electricity charge” of ₹1.28 cr. and “Water Charges” of ₹0.17 cr.
- Therefore, an investor would need to put some extra effort in the analysis in case the “Other Expenses” item is a large number.
- Employee Cost: ₹12.93 cr. taken directly from the P&L statement
- Other Income: ₹8.89 cr. taken directly from the P&L statement. For some companies, it might be shown as non-operating income in the P&L statement.
- Depreciation: ₹4.28 cr. taken directly from the P&L statement.
- Interest: ₹16.52 cr. taken directly from the P&L statement.
- Profit before tax: ₹33.37cr. taken directly from the P&L statement.
- Tax: It represents the sum total of all the tax-related entries in the P&L statement including all credits, debits and previous year adjustments. E.g. for Omkar Speciality Chemical Limited, the tax for FY2016 (₹11.16 cr.) represents the sum of:
- Previous year adjustments of ₹0.50 cr.
- Current Tax of ₹6.99 cr.
- Deferred Tax of ₹5.81 cr.
- MAT Credit Entitlement of negative ₹2.14 cr. This effectively adds to the profit of the company for the period.
- Total of all these entries: 0.50 + 6.99 + 5.81 – 2.14 = ₹11.16 cr. is equal to the “Tax” in “Data Sheet” in screener.in. Investors might find a small difference for various companies, which might be due to rounding off.
- Net profit: ₹22.21 cr. taken directly from the P&L statement.
- Dividend Amount: It represents the entire dividend paid/declared/proposed for the financial year without considering the dividend distribution tax. We may get to know about this figure from the Reserves & Surplus section of the annual report. E.g. for Omkar Speciality Chemical Limited, the dividend amount (₹3.09 cr.) in the “Data Sheet” of screener.in has been taken from the reserves & surplus section of the annual report on page 88:
Further Reading: Understanding The Annual Report Of A Company
- The data for three key constituents of the cash flow statement i.e. Cash from Operating Activity (CFO), Cash from Investing Activity (CFI) and Cash from Financing Activity (CFF) are taken directly from the cash flow statement in the annual report
- Net Cash Flow is the sum of CFO, CFI and CFF for the financial year.
- Sometimes, investors may find a small differences in the data, which might be due to rounding off.
Cash Flow Statement Screener.in “Data Sheet”
Cash Flow Statement Annual Report FY2016
Further Reading: Understanding Cash Flow from Operations (CFO)
Quarterly Results Screener.in “Data Sheet”
Quarterly Results March 2017, Company Filings to Stock Exchange
- Sales: it represents the revenue from operations from the quarterly results filing of the company. E.g. for Omkar Speciality Chemical Limited, the sales of ₹91.56 cr. in March 2017 quarter represents the revenue from operations from the March 2017 results of the company.
- Expenses: it represents all the expenses from the quarterly results filing except finance cost and depreciation. “Expenses” in the “Data Sheet” of screener.in includes the exceptional items if any disclosed by the companies in their results. E.g. for Omkar Speciality Chemical Limited, the “Expenses” in the data sheet of the amount of ₹135.84 cr. is the sum of:
- Cost of material consumed: ₹50.09 cr.
- Purchase of stock in trade: Nil
- Changes in Inventories of Finished Goods, Stock in Trade, Work in progress and Stock in Trade: ₹12.75 cr.
- Employee benefits expense: ₹2.11 cr.
- Other expenses: ₹7.68 cr.
- Exceptional Items: ₹63.21 cr.
- Total of all these entries: 50.09 + 12.75 + 2.11 + 7.68 + 63.21 = ₹135.84 cr. is equal to the “Expenses” in “Data Sheet” in screener.in. Investors might find a small difference for various companies, which might be due to rounding off
- Other Income: (₹5.47 cr.) taken directly from the quarterly Statement. For some companies, it might be shown as non-operating income in the quarterly statement.
- Depreciation and Interest: are directly taken from the “Depreciation and Amortization Expense“ of ₹0.99 cr. and “Finance Costs” of ₹5.14 cr. in the quarterly statement.
- Profit before tax: Loss of ₹55.89cr. taken directly from the quarterly statement.
- Tax: It represents the sum total of all the tax-related entries in the quarterly statement including all credits, debits and previous year adjustments. E.g. for Omkar Speciality Chemical Limited, the tax for March 2017 quarter (positive change of ₹11.59 cr.) represents the sum of:
- Current Tax of negative ₹5.37 cr. This effectively adds to the profit of the company for the period.
- Previous year adjustments of negative ₹6.75 cr. This also effectively adds to the profit of the company for the period.
- MAT Credit Entitlement of ₹1.14 cr. This also effectively adds to the profit of the company for the period.
- Deferred Tax of ₹1.67 cr.
- Total of all these entries: -5.37 – 6.75 – 1.14 + 1.67 = – ₹11.59 cr. is equal to the “Tax” in “Data Sheet” in screener.in. The negative tax effectively adds to the profit of the company for the period.
Investors might find a small difference for various companies, which might be due to rounding off.
- Net profit: Loss of ₹44.29cr. taken directly from the quarterly statement.
- Operating Profit: represents sales – expenses (as calculated in the description above). E.g. for Omkar Speciality Chemical Limited, the operating profit for March 2017 quarter (loss of ₹44.28 cr.) represents the impact of:
- Sales of ₹91.56 cr. less Expenses of ₹135.84 cr. = Loss of ₹44.28 cr.
Further Reading: Understanding the Quarterly Results Filings of Companies
With this, we have come to the end of the current section of this article, which elaborated the reconciliation of the data presented by screener.in with the annual report and quarterly filings of the companies. Now we would elaborate on the steps to customize the default “Export to Excel” template sheet provided by screener.in.
Customizing the Default “Export to Excel” Sheet
Customizing the “Export to Excel” template and uploading it on screener.in in the account of an investor is the feature, which differentiates screener.in from all the other data sources that we have come across.
We have used premium data sources like CMIE Prowess, Capitaline during educational and professional assignments in the past as part of the subscription of MBA college and the employer. These premium sources as well as other free sources like Moneycontrol etc. provide the functionality of data export to excel. However, the exporting features of these websites are primitive, which provide the data present on the screen to the investor in an Excel or CSV file on which the investor then needs to separately apply the formulas etc. to do the analysis, which is very time-consuming.
Screener.in is better than the above-mentioned sources in the terms that it allows investors to customize the Excel template and upload it on the website. The next time any investor downloads the data of any company from the screener.in website, the downloaded file has the data of the company along with all the formulas put in by the investor auto calculated, which saves a lot of time of the investor in doing in-depth data analysis.
Steps to customize:
Once the investor downloads the data of any company by clicking the “Export to Excel” button from the screener.in website, then she gets the data of the company in the default Excel template of screener.in
The default Excel template contains the following six sheets:
- Profit & Loss
- Balance Sheet
- Cash flows
- Customization and
- Data Sheet
The “Data Sheet” contains the base financial data of the company, which has been described in detail in the above section of this article. It is not advised to make any change to this sheet otherwise all the data calculations might become erroneous.”Customization” sheet contains the steps to upload the customized sheet on the screener website in an investor’s account. We will discuss these steps in details later in this article. Rest of the sheets: Profit & Loss, Quarters, Balance Sheet and Cash Flows contain the default ratios along with formulas etc. provided by the screener.in team for the investors.
An investor may change all the sheets except the Data Sheet in any manner she wishes. She may delete all these sheets, change formulas of all the ratios, put in her own ratios, create entirely new sheets and create her own preferred ratios and formulas in the new sheets by creating direct linkages for these new formulas from the base data in the “Data Sheet”. The investor may do any amount of changes to the excel sheet until she does not tinker with the Data Sheet.
Given below is the screenshot of the “Profit & Loss” sheet of the default “Export to Excel” template provided by screener.in
Given below are the changes that we have done to the “Export to Excel” template to customize it as per our preferences by creating a new sheet: “Dr Vijay Malik Analysis”
(A large resolution image of the latest Stock Analysis Excel Template sheet is at this page: Click here)
Further Reading: Stock Analysis Excel Template (Screener.in): Premium Service
The above-customized template helps us to do a very quick assessment of any company on the checklist of parameters that we use for stock analysis. This is because this customized template provides us with our preferred ratios etc. in one snapshot like a dashboard, which makes decision making very quick and easy.
Readers would be aware that we use a checklist of parameters, which contains factors from Financial Analysis, Business Analysis, Valuation Analysis, Management Analysis and Margin of Safety calculations.
Further Reading: Final Checklist for Buying Stocks
The customized template screenshot shared above allows us to analyse the following parameters out of the checklist in a single view:
- Sales growth
- Tax payout
- Interest coverage
- Debt to Equity ratio
- Cash flow
- Cumulative PAT vs. CFO
Further Reading: How to do Financial Analysis of Companies
- P/E ratio
- P/B ratio
- Dividend Yield (DY)
Further Reading: How to do Valuation Analysis of Stocks
- Conversion of sales growth into profits
- Conversion of profits into cash
- Creation of value for shareholders from the profits retained: Increase in Mcap in last 10 yrs. > Retained profits in last 10 yrs.
Further Reading: How to do Business Analysis of Companies
- Consistent increase in dividend payments
Further Reading: How to do Management Analysis of Companies
Margin of Safety:
- Self-Sustainable Growth Rate (SSGR): SSGR > Achieved Sales Growth Rate
- Free Cash Flow (FCF): FCF/CFO >> 0
Further Reading: 3 Simple Ways to Find Out Margin of Safety in a Stock
Operating Efficiency Parameters:
Further Reading: How To Analyse Operating Performance of Companies
The ability to see the above multiple parameters in one snapshot for any company for which we download the “Export to Excel” file, allows us to have a quick opinion about any company that we wish to analyse. It saves a lot of time for the investors as she can easily determine, which companies have the requisite strength that is worth spending more time on them.
We believe that to fully benefit from the great resources available to the investors today, it is essential that investors should use screener.in to the fullest and therefore must customize their own “Export to Excel” templates as per their preference and upload it to their accounts at the screener.in website.
Uploading the Customized “Export to Excel” Sheet on Screener.in Website
The “Customization” sheet of the default “Export to Excel” template file provided by screener.in contains the steps to upload the customized Excel file on the screener.in website. We have described these steps along with the relevant screenshots below for the ease of understanding:
- Once the investors have customized the excel file as per their preference, then they should rename it for further reference. The excel file that we have used for illustration below is our customized excel template, which is named: “Dr Vijay Malik Screener Excel Template Version 1.6 (Unlocked)”
- Once the investor has saved her customized excel file with the desired name, then she should visit the following link in the web-browser: https://www.screener.in/excel/. She would reach the following screen:
- It is required that the investor is logged in the screener.in website before she visits the above link. Otherwise, the browser will direct her to the login/registration page like below:
- If the investor is directed to the above page to register and she does not have an account on screener.in website, then she should create her new account by providing her details on the above page and clicking “Register”
- However, if she already has an account on screener.in, then she should click on the button “Login here”.
- In the next page, the investor would be asked to provide her email and password to log in and after successfully logging in, the website will take her to the Dashboard/home page of screener.in
- Now the investor would have to again visit the page: https://www.screener.in/excel/ to upload the customized Excel. To avoid this duplication, it is advised that the investors should visit the page: https://www.screener.in/excel/ after they have already logged in the screener.in the website.
- Once the investor is at the Excel upload page, then she should click the button: “Choose File”
- Upon clicking on the button “Choose File”, a new pop-up window will open. In the newly opened window, the investor should browse to the folder where she had saved her customized excel sheet and select it:
- Upon selecting the customized Excel file of the investor, in our case the file “Dr Vijay Malik Screener Excel Template Version 2.0”, the investor should click on the button “Open” in this pop-up window.
- Upon clicking the button “Open”, the pop-up window will close and the investor would see that on the web page, there is a summary of the name of her customized excel file near the “Choose File” button.
- The presence of the file name summary indicates that the correct file has been selected by the investor for the upload.
- Now, click on the button “Upload” on the webpage.
- Clicking on the “Upload” button will upload the excel file customized by the investor in her account on the screener.in website and take her to the homepage/dashboard of the screener.in website.
From now on whenever the investor downloads the data of any company from screener.in by clicking the button “Export to Excel”, then she would get the data in the format prepared by her in her customized Excel file containing all her custom ratios and formulas, formatting and the layout as selected by her.
This concludes all the steps, which are to be taken by an investor while uploading her customized excel file on the screener.in website.
Updating/Changing the already uploaded customized sheet:
- In future, if the investor wishes to make more changes to the excel file, then she can simply do all the changes in the Excel file without making any changes to the “Data Sheet’ and save it.
- She should then repeat the above steps to upload the new excel file in her account on the screener.in.
- Uploading the new file will overwrite the existing template and henceforth, screener.in will provide her with the data in her new Excel file format upon clicking the “Export to Excel” button for any company.
Removing the customizations:
- However, in future, if the investor wants to delete her customized excel file and go back to original default excel template of screener, then she again would need to visit the following link: https://www.screener.in/excel/ and click on the button “Reset Customization”
- Upon clicking the button “Reset Customization”, the web page will ask “Are you sure you want to reset your Excel customizations?”
- If the customer is sure about deleting her customized excel file, then she should click on the button “Confirm Excel Reset” on the web page.
- Clicking the “Confirm Excel Reset” button will delete the customized Excel file from the investor’s account and reset the excel file to the default Excel template file of screener described above.
- From now onwards, whenever the investor downloads the data of any company from screener.in by clicking the button “Export to Excel”, then she would get the data in the default Excel format of screener.in.
There is no limit on the number of times an investor can upload her customized excel file or change it or delete it by resetting the customization. Therefore, an investor may do as many changes and iterations as she wants until she gets her preferred excel sheet prepared, which would help her a lot in her stock analysis.
With this, we have come to an end of this article, which focussed on the key feature of the screener.in “Export to Excel”, the reconciliation of the financial data in the “Data Sheet” with the annual report, quarterly results file etc. and the steps to customize the Excel file and upload the customized Excel file in the investor’s account on screener.in.
Now, Let us address some of the queries asked by investors regarding the Screener export to excel feature:
When financial ratios have different values on different sources
Differences in the financial data provided by Screener and the annual report
Dear Dr. Vijay,
I find gross discrepancy between the financial numbers like CFO and net debt of Ashok Leyland in FY 2017-18 in Company’s annual report and the values in screener. Kindly clarify on this
Thanks for writing to us!
The above article will helps you to understand the manner in which Screener calculates its data sheet from the annual report.
As the basis for all the data is annual report, therefore, it might be grouping/regrouping i.e. different ways of classifying the data, which might have resulted in the differences.
The above article will help you a lot in understanding the manner in which Screener treats different data segments of annual report before presenting it in its Export to Excel file.
All the best for your investing journey!
Dr. Vijay Malik
I was also checking Nitin Spinner. On Economic times – Debt/equity = 0.9. and on Edelweiss it is 1.65. Screener = 1.1.
Every site would have different assumptions about what to include in debt or equity. It is important that the assumptions remain same over the entire past data that an investor analyses.
It is advised that before making final investment decision, an investor should calculate all the ratios herself by taking financial data from the annual report.
If raw material prices go down, then normally margins should increase until the time company has to pass on the benefits to customers due to competitive pressures.
Hope it helps!
Investor’s queries about Export to Excel
Screener or Annual Report: Which data to use in analysis?
While analyzing company data, I get confused which website to use Screener, monyworks4me or balance sheet of company from its portal (tedious job of data entry). Many a times, company portal data is different from screener. Can you please clarify the reasons for the difference?
Thanks for writing to me!
Data in the balance sheet of the company is the most authentic data about any company. You should use it to make final investment decision about any company. However, as you also acknowledge, it entails tedious job of data entry. Therefore, annual report financial data should analyzed once you are satisfied from analysis of the company from the information available on the public sources like screener, moneycontrol etc. that the company financials represent a potential good story.
Open portals like Screener etc. use the data from company report and rearrange it to keep consistency between the data formats reported by them. This may involve regrouping of certain items which may give the impression that the data on screener is different from annual report.
Therefore, it is recommended that an investor should open portals like screener to filter stocks based on her favorite parameters, do preliminary analysis based on their data and if satisfied, then see the annual report to make final decision. By following this process, an investor can make good use of the limited time she has got.
Hope it helps to resolve your query.
Calculating Other Assets & Other Liabilities from Screener Data
I am unable to calculate other asset and other liabilities from screener website balance sheet. If you don’t mind can u please explain how screener calculate the other asset and other liabilities?
Thanks for writing to me!
As per my understanding, the screener balance sheet data has the following classification:
- Equity Share Capital = directly from balance sheet
- Reserves = directly from balance sheet
- Borrowings = Long term borrowings + Short term borrowings + current maturity of long term debt presented in Other Current Liabilities in the annual report
- Other Liabilities = All other liabilities + Payables + Provisions etc.
Total = equal to the balance sheet total liabilities
- Net Block = tangible + Intangible net fixed assets directly from balance sheet
- Capital Work in Progress = directly from balance sheet
- Investments = current investments + non-current investments
- Other Assets = rest all the assets/working capital/inventory/receivables/long & short term loans & advances etc.
Total = equal to total assets in balance sheet
There might be minor adjustments in these items based on the details provided by the companies in their schedules/notes to accounts.
In case you need any further clarifications, then I would request you to write to screener directly and update us as well.
Hope it clarifies your queries!
All the best for your investing journey!
Calculating different formulas in our Stock Analysis Excel Template
I need some detailed meanings of terms used in screenshot of excel template shared by you: “Stock Analysis Excel Template (compatible with Screener.in)”
Please elaborate with terms used in Screener & some of the following terms in detail-
- Total Debt = (Borrowings + Liabilities) or Only Borrowings
- Receivable Days = from where and how you calculated this?
- MCap = how you calculated or taken for last 10 years each?
- Cash + Equivalent = (Cash & Bank) or (Cash & Bank + Reserves)
- FCF = does it mean Net Cash Flow
- Book Value = How did you calculated or taken from where for each of last 10 years?
- In Capex NFA means “Net Block” or something else? Also how to calculate or what figure you have taken for WIP Change?
Thanks for writing to me!
All the data has been used from the default data provided by screener.in
1) Total Debt = (Borrowings + Liabilities) or Only Borrowings
Total debt = long term debt + short term debt + current maturity of long term debt
2) Receivable Days = from where and how you calculated this?
Receivable Days = (Average trade receivables at start and end of the year * 365) / Sales
You may see more details in the following article: How To Analyse Operating Performance of Companies
3) MCap = how you calculated or taken for last 10 years each?
Market cap increase in 10 years = current market cap – market cap in the year back as per Screener Data Sheet
4) Cash + Equivalent = (Cash & Bank) or (Cash & Bank + Reserves)
The excel template refers to Cash + Investments which equals: Cash & equivalents (bank balance + FD etc.) + current investments + noncurrent investments
5) FCF = does it mean Net Cash Flow
It is free cash flow.
You may find formula and the further clarification in the following article: Free Cash Flow: A Complete Guide to Understanding FCF
6) Book Value = How did you calculated or taken from where for each of last 10 years?
It is calculated from the latest annual report data provided by screener.in
7) In Capex NFA means “Net Block” or something else? Also how to calculate or what figure you have taken for WIP Change?
NFA is net block/net fixed assets. The data has been provided by screener.in in default data sheet.
Hope it clarifies your queries!
All the best for your investing journey!
Can we calculate Current Ratio in Screener Export to Excel Sheet?
Thank you Dr. Vijay Malik. This analysis helped me a lot to include some more components on my stock checklist in terms of management behavioral analysis.
I’ve one question to you, do you know how to get the current ratio from screener excel sheet? The main sheet (Data sheet) doesn’t have separate component for Current Asset and Liability. Would it be possible to help me on this?
Thanks for writing to me!
You are right that the current assets and current liabilities figures, which are conventionally used to calculate current ratio are not available in the Screener data sheet. Therefore, it’s advisable to calculate the current ratio from either the annual report or the data from any other source like moneycontrol.
All the best for your investing journey!
It’s your turn now to share your feedback about this article, about screener.in website and its “Export to Excel” feature. We are looking forward to reading your inputs in the comments below. Feel free to share your experiences with screener.in or any other such resource, which provides financial data on stocks. Your inputs would be very beneficial to all the investors, readers and author of this website.
We keep on writing articles related to stock investing. Therefore, you may like the blog’s Facebook page, subscribe by email, follow us on Twitter or RSS feed so that you would immediately get to know when new articles are published on the website.