Evaluating stocks with TCI Rapid X-Ray

So many stocks…So little time

Investing is largely a process of elimination, and the more efficiently you can eliminate investments that do not check the boxes on your investing checklist, the more time you can spend diving deep into the ones that do.

For equity investors in the Indian Stock Market, there are about 1,500 listed stocks on the NSE and nearly 5,000 on the BSE, to choose from when making investment decisions. Sure, there are ways to whittle that list down using filters of market capitalisation and sectors, but you’re still left with a few hundred potential investments to sift through.

Some investment-related sites / blogs offer Microsoft excel templates in which one can download and manually input relevant information from sources like annual reports or moneycontrol.com. These sheets have formulae that, based on the information manually copy-pasted, show indicators that aid in judging the investment-worthiness of the stock. One such Indian investment site offers such a stock evaluation template for a fee, the accompanying instruction video shows it takes two hours to fill in all the information. That’s for one stock!

Even if you do manage to stay focused to do the copy-pasting without errors, best of luck trying to resist the temptation to go ahead and buy a stock simply because of the time that’s gone into getting the numbers in one place.

So, I was excited when I came across screener.in and it’s ‘export to excel’ functionality with ten years of financial statement information. I used it to make the first version of the TCI Rapid X-Ray. I received a lot of emails asking for the template plus I noticed some tweaks that were needed in the template, which needed a revision. This post provides easy download access to the new TCI Rapid X-Ray to save your time when evaluating stocks.

Using the new TCI Rapid X-Ray Template

Step 1: Download the TCI Rapid X-Ray

You will receive the download link by email after confirming your sign up.

Step 2: Using the template and what’s new

The point of the TCI Rapid Stock XRay is to automate the task of pulling 10 years of financial information and to be able to quickly scan what the numbers say. Using the template on screener.in enables that. The process is covered in detail here. Go to the section titled ‘Using screener.in to automate TCI Rapid XRay”.

What’s new from the previous version? Two things

Two blue tabs have been added. The ‘TCI Rapid XRay’ tab summarises financial information from existing tabs and offers some colour-coding to help quickly spot trends over a ten year timeline

The Calm Investor | TCI Rapid XRay
TCI Rapid X-Ray | Quick view of ten-year financial performance

At the bottom of the sheet is what I refer to as ‘Idiot’s Valuation’, so named to avoid any impression of being a sophisticated valuation model to arrive at precise measures of a stock’s worth. At the risk of being too rudimentary, here’s how it works:

  1. Since cash is all that really matters, TCI Idiot’s Valuation considers the ‘Cash flow from Operations’ from the Balance Sheet. (I would have preferred using Free Cash Flow but the screener.in template does not break out purchase of fixed assets on the balance sheet.) Let’s say CFO is ₹100 in a given year
  2. It assumes that some of that cash will be needed for operational expansion, buying new equipment, replacing old machines etc, but that a certain percentage can be earmarked to pay interest on hypothetical debt (stay with me here). The default assumption is 50%, i.e. ₹50 as the maximum interest payment the company can make because the rest of the cash is needed to run and grow the business. This assumption can be changed by changing the input in cell B46.
  3. Conservatively assume this interest payment can be made sustainably into perpetuity, ₹50 this year, ₹50 next year and so on. Note how we’re not building a growth rate into this number
  4. By applying a discount rate = rate of interest of the loan, let’s say 10%, we value the company as the present value of ₹50, in perpetuity. In this case, value of the firm = (₹50 / 10%) = ₹500. Changing this discount rate in cell B47 will also have significant impact on the ‘Safe Value’, you should try different inputs to see how the values move
  5. Divided by the number of shares gives us a “safe” value of what the company is worth. That’s all, no intricate modeling here.
The Calm Investor | TCI Rapid XRay
TCI Rapid X-Ray | Comparing stock price with idiot’s valuation

Step 3: Upload to screener.in

Log into screener.in, go to https://screener.in/excel

The Calm Investor | TCI Rapid XRay
screener.in | excel upload screen

Click ‘Choose File’ and upload the file downloaded in step 1. You’re set! You can make your own enhancements, edit the blue tabs, or even add new tabs of your own to do further analysis.

Now, navigate to the screener.in home screen, enter any stock into the search bar and then click ‘Export to Excel’. The file will contain the new tabs described above with information specific to the stock you wanted. Do this for any stock you’d like to evaluate for investment.

This is the just the jump-off point in evaluating a stock. If the basic financial metrics look healthy and the stock doesn’t look like it’s already priced assuming it’s the next Apple, then the real work of investment evaluation follows. Whether you think the company will prosper and grow profitably over the next 5-10 years.

Do let me know if you find it useful, either in the comments or by writing in at ‘thecalminvestor [at] gmail [dot] com’. Best of luck!

You can also follow ‘The Calm Investor’ on twitter and sign up for the email subscription through the form in the right sidebar.

9 thoughts on “Evaluating stocks with TCI Rapid X-Ray

  • August 28, 2017 at 11:36 pm

    Great thing going here. I had to calculate this about any company I was serious in – and not in this detail with graphs and all.

  • August 31, 2017 at 5:59 pm

    Thanks for the template…It is really narrowing down the options to choose the stock

  • September 2, 2017 at 11:41 am

    Thanks! Glad you’re finding it useful.

  • September 12, 2017 at 3:03 pm

    This is really a great tool. It speeds up the stock analysis process. Thanks for sharing.

  • September 13, 2017 at 7:23 am

    You’re welcome Kamlesh

  • November 5, 2017 at 3:09 pm

    Thanks a lot for the template.. and I receive 500 error when I try the export to excel feature.. Here are the steps followed.

    1. Upload excel file
    2. open a company and click export to excel.

    now I’m receiving 500 server error.

    Error 500: Whoops! Something has broken down.

    I tried with custom excel and it worked fine. Kindly advice.

  • November 5, 2017 at 5:40 pm

    Will check this out and get back to you.

  • November 10, 2017 at 5:12 pm

    Thanks for the template. Though couple of formula errors grabbed my attention. For eg: the inventory and receivable days formula has linking error. Should have been linked to the Annual sales instead of the Quarterly sales figure. Profitability ratios wherein the Change in inventory has been considered as a part of expenses hence reducing the Profitability ratios than what it should be. But overall quite impressive in helping us find such a solution to save hell lot of time. Thanks once again

  • November 10, 2017 at 7:37 pm

    Thanks Manan for pointing out the error. Easy to miss these. Will fix quickly.

What do you think?