Stock Comparison Tool: Use it to compare Five Stock at a time [Excel]

Open your excel sheet. 'Click a button' to fetch data of 1,000 nos stocks. Select five stocks, and see their comparison report in a jiffy. One of my favourite stock tools

Stock comparison tool was one of my first tools which I developed in excel. In last days we have made it more online savvy. Now this tool can fetch stock data automatically upon click of a button.

The worksheet also has few limitations. It works only on MS EXCEL. To use it, one must have a decent internet connection. The worksheet has a list of total 1002 nos stocks available for comparison.

Earlier this tool had a static database. I used to send three updates of it (once every 30 days) to its new users. But now the dependency on 3 updates are gone. New users can click a button to fresh the stock data from our database.

At the moment, we are updating our database once every week (every Friday). But as we get more conversant with database management, we will re-fresh its contents more frequently.

Even when this tool had a static database, I used to love it. Even today, this is one of my first tools which I use in the process of researching my stocks.

In this article I’ll explain how I use the stock comparison tool for myself. This way you can also get a clue about its features, and usage for yourself.

Stock comparison tool is not sold as an independent product. One can buy it at as a package in product basket. My product basket also has other tools like Stock Analysis Worksheet, Mutual Fund Comparison Tool etc.

Topics

Utility of Stock Comparison Tool

Stock Comparison Tool - Utility

While we are planning to invest in equity, we face a basic question, “we would like to do an active or passive investing“? What is active investing? When investor has to self analyze shares and then invest in them directly it is active investing. While passive investing is done through a third party (outside expert) – like mutual funds, Exchange Traded Funds (ETFs) etc.

Once we’ve answered this basic question, we’ll have two types of investment options in front of us: mutual funds and stocks. If choice is mutual fund, as-much self-research and involvement is not necessary. But in direct stocks investing I follow a three stage approach:

The Three Stages
  • Stage #1 (Screening): Generally I start with my stock screener. It is a web application having database of 3,800+ number stocks. We can use this screener to identify our stocks by applying several filters. I generally focus on those profitable stocks (high ROE, RoCE) which are trading at low P/E, low P/b etc.
  • Stage #2 (Comparing): This is where my stock comparison tool comes into play. Suppose, the above screener gave me five high quality stocks (high revenue/profit, high profitability etc). I use the stock comparison to compare these 5 stocks based on 40+ parameters. This is how I’ll pick my best stock out of the lot of five.
  • Stage #3 (Detail Analysis): A detailed analysis is done by my stock analysis worksheet. Good stocks obtained from the ‘stock comparison tool’ is analysed using stock analysis worksheet. This is one of a unique excel based tool. It is the most valuable product of our product basket.

Features of ‘Stock Comparison Tool’

Stock comparison tool can compare five stocks at a time. This worksheet has a dynamic database which can be refreshed once every week (on Saturday’s). The tool also presents the selected five stock’s data-sheet in a simple and intuitive format. Read more about how to use this tool.

Stock Comparison Tool - 5 stocks

The worksheet can compare stocks based on 40+ parameters. Allow me to show you and explain the various parameters and their relevance.

#1. General Data

Comparison Parameters 1 - General Data
  • #1 Size: There are 1002 numbers stocks in the worksheet’s database. All those stocks has been categorized into five types based on their market cap. Five types are: (a) Very Large, (b) Large, (c) Mid Size, (d) Small, and (e) Very Small. Categorisation gives a better sense of size to the users.
  • #2 Sector: Name of a company often does not speak about its nature of business. Hence to get a general idea about it, looking into the sector in which it operates, helps.
  • #3 Date: The date indicated here show’s the day in which the database of the sheet has been last updated. This gives a sense to the users that the price data there are referring belongs to which time frame.
  • #4 Price: There are three kind of price data indicated here. First is the current price. The next two data is ’52 Week High’ price and ’52 Week Low’ price. These represents two extreme price movement in a time span of last one year. Comparing ‘current price’ with ’52W High and Low’ gives an understanding about current price being high or low.
  • Trading Volume: Stocks which are traded more often in stock market has a high volume. Ideally speaking, one should prefer investing in ‘high volume’ stocks. All stocks are categorized into three types based on their trading volumes (High, Medium and Low).

#2. Price Valuation

Price Valuation Format - Comparison and datasheet
  • #1. EV/Share: This is Enterprise Value (EV) of a company expressed in per share form. EV is nothing but Market Capitalisation adjusted for debt and cash (EV=Mcap+Debt-Cash). This is a more realistic representation of the true value of a company as compared to market cap. Read more about enterprise value.
  • #2. EPS x 15: Benjamin Graham states that a P/E ratio of 15 or below indicates that current price is trading at fair levels. Using this analogy, multiplying current EPS of a company with 15 will give us its fair price. Ream more about it in stock for defensive investors.
  • #3. BVPS x 1.5: Like PE15 rule, Benjamin Graham also believed in BVPS1.5 rule. If current price is at 1.5 times or below its ‘book value per share (BVPS)’ the stock may be fairly priced. Read more about it in book value of shares.
  • #4 @PEG=1: This is an important metric of stock comparison tool. A stock whose PEG is less than or equal to one (1) may be treated as fairly priced. So this metric in the comparison table shows what? It tells us, what should be price of the stock so that its PEG will come as at least one (a). Read more about it in this article on PEG ratio.

#3. Size of Company

Size of company - comparison
  • #1 Market Valuation: It is interesting to compare stocks based on their market valuation. It gives an idea that which stock is more liked by the market. The parameters based on which market valuation is compared are (a) market capitalisation, and (b) enterprise value. Read: Book value, vs Market value.
  • #2. Sales, Profit & NW: The best measure of the size of company is based on its fundamentals. When I say fundamentals it is mainly revenue, net profit (PAT), and Book Value (Net Worth). Comparing stocks based on these 3 parameters gives us the true estimate of its bulk. Why size is important? Because it is also an indicator of its market share over its competitors. Read more about MOAT companies.
  • #3. Liquid Cash: Though liquid cash in isolation is not much of a meaning for analysts, but we can still use it as a measure of size. A company which looks cash-rich are liked by investors. Though a more apt measure of company’s health is liquidity ratios.
  • #4. Cash Flow: The first things that investors like to see in a company is their ability to generate “positive cash flows”. Cash flow is also used by analysts to measure intrinsic value of a company (like in DCF model). One of the best measures of cash flow of a company are (a) cash flow from operations, and (b) free cash flow.

#4. Past Returns

Past Return in last 10 years

Five companies are compared based on their historical returns (CAGR). For sake of convenience and ease-of-comprehension, the return numbers are presented in five parts: (a) last 3 months, (b) last 12 months, (c) last 5 years and (d) last 10 years. Comparing five stocks based on their past returns is one of my favourite past-times 🙂 . Read more about how to measure investment returns in Excel.

#5. Price Valuation Ratios

Price Valuation Ratios

In #2 above, what we saw was price valuation of stocks based on their intrinsic value. Here, we can compare stocks based on the ratios which also hints about its approximate price valuations. Few ratios that we use here to draw a comparison is P/E, P/B, PEG, Dividend Yield etc. Though a more detailed article on price valuation ratios is here. Please go ahead and read it as well please.

#6. Financial Health

Financial health of a company - list of parameters
  • #1 Altman Z-Score: It gives companies a score. The lowest score is zero (0) and goes higher. The higher is the score the better. A company with a high score has no bankruptcy threat. It means a high scoring company will never become a Kingfisher, Yes Bank, DHFL etc. Rule of thumb: Safe company is one which score 5 or above. Negative Z score is a clear sign of bankruptcy happening anytime soon.
  • #2. Piotroski F-Score: The company’s F-score is based on 9 parameters. Every parameter will fetch one point for the company upon its compliance. According to me, F-score is one of the most powerful tool to measure the financial health of a company. Read more about the 9 parameters of F-score.
  • #3. D/E and ICR Ratio: Debt on its own is not bad. But when debt level crosses a threshold it starts to become a cause of worry. One of the measure of this threshold is Debt-Equity (D/E) and Interest-Coverage (ICR) ratio. Comparing company of the same sector using D/E and ICR ratio gives good insight about their debt levels. Read more about debt free company.
  • #4. Current Ratio: This is a measure of company’s ability to pay its current liabilities. Non payment of liability leads to bankruptcy. Current ratio (CR) = Current Assets / Current Liability. If CR is one or more means the company’s current assets can cover its current liabilities. Read: Net Current Asset Value of a company.
  • #5. Dividend Payout Ratio (DRP): This is a measure of how much of its net profit (PAT) the company is sharing with its shareholders. Large blue chip companies share more profits as dividends, hence their DPR is high. Growing companies prefers to retain their profits, hence their DPR is low.

#7. Profitability

Profitability ratios

The stock comparison tool uses several metrics to check a company’s profitability. The measurement of profitability is done with respect to total assets, total equity, employed capital, operating profit and net profit (PAT) of the company. Out of all these, analysts give more weightage to ROA, ROE and RoCE. Read more about how to do profitability analysis of a company.

Comparing companies based on these profitability metrics gives a clear idea about their inherent strength (profitability of business).

Example, a manufacturing based company will be less profitable than a services based company. Similarly a company operating in the sector of “utility supply” – like power, water etc will have lower profitability. Likewise, franchisee based companies will show comparatively a higher profitability numbers.

Suggested Reading: Highly profitable companies operating in India.

#8. Past Growth Rates

Stock Comparison Tool - growth rate

What is the point behind seeing the past growth rate numbers? It gives an idea about the future growth prospects of the company. Which parameters we should select to check the growth rates? The most important number is earnings (profit). But we must also checked the linked parameters which eventually leads to profit-making for the company.

Moreover, seeing growth numbers for only one quarter or one year is not enough. To get a clear long term view, last 3, 5 and 10 years growth numbers shall be seen.

This worksheet uses growth numbers for the metrics like total revenue, operating profit, earning per share (EPS), and book value.

How to Use The Stock Comparison Tool?

You can use the following steps as your guide. But even before you can use the file, you must Enable Macros for the worksheet when prompted.

EnableMacros Button

If “Enable Macros” prompt does not appear after opening the file, please change the macros setting in the trust centre.

  • Step #1: First go to the sheet called “Names”. Here in this sheet you will find a list of 1002 nos stocks. These are stocks which can be compared using this tool. The list of stocks has been carefully selected. You will find almost all quality stocks listed here.
HowToUse - Sheet
  • Step #1.1: You can refresh database on every Saturdays. You shall click the “Refresh Stocks Data” button to fetch the new data.
Refresh Stocks Data - button
  • Step #2: Clear the contents of columns marked as COL5 and COL6. Please note that there are 1002 number rows that needs to be cleared. To clear the contents, select all 1002 rows of COL5 and COL6, then right click, then click ‘Clear Contents’.
Clear contents of COL5 and COL6
  • Step #3: You will have to put “Yes’s” and “number the Yes’s from 1 to 5”. Check this video guide to learn how to put YES and then number them from 1 to 5. Please note that this video guide is of the older version. So please do not get confused when your sheet looks slightly different. Just use this video for “Yes’s and its numbering”.

Step #4: The final report can be checked in the below shown two sheets.

Report sheet 2

Conclusion

This tool gives a complete picture about a company. Starting from price, price valuation, size, returns, past returns, financial health, profitability, growth rates – the worksheet touches all relevant metrics.

As the ‘stock comparison tool’ can compare five stocks based on these metrics, this further enhances its value for its user. As I said before, this worksheet is one of my first developments, but even today its utility seems like ever increasing.

Join Our Email Subscribers

Be the first to comment

Leave a Reply

Your email address will not be published.


*