In this post we will learn how to analyze stocks in excel. How to do it? By using my excel based tool (I call it “Stock Analysis Worksheet“).
You may be wondering how an excel sheet can analyze stocks? Because I’ve hard coded it to perform this task.
Today I will show you how I use this tool. You too can use this excel sheet to analyze your stocks.
Why we need a tool like excel to do it? Because to do a more complete analysis of stocks, one must learn to read and comprehend financial statements of companies. My excel can read those statements, and churns out final numbers in form of a report. This complete the stock analysis. We will see how…
- 1. What this worksheet can do?
- 2. How the worksheet estimates intrinsic value?
- 3. How the worksheet gives overall score to stocks?
- 4. What makes this worksheet unique?
- 5. What other information this worksheet can provide about stocks?
- 6. How to use the worksheet?
- 7. Who can use this worksheet?
- 8. Use this worksheet to find answer of what?
- 9. Which data must be entered into the worksheet?
- 10. How to enter stock data?
- Final words…
#1. What this worksheet can do?
My stock analysis worksheet is special for me. Why?
Because of its two main highlights…
- Intrinsic Value: It can estimate true value (or intrinsic value) of stocks.
- Overall Score: The worksheet can also rate stocks (give scores) based on their fundamentals.
Intrinsic value can be compared with the stock’s market price to know if it is overvalued or undervalued.
For stock investors, only undervalued stocks are interesting.
Overall score gives a general idea about the business fundamentals of the stock.
For stock investors, stocks with strong underlying business is interesting.
1.1 Goal and limitation of the worksheet
The main goal behind developing this worksheet was to make it capable of estimating “intrinsic value”.
Disclaimer: Estimating intrinsic value of stocks is such a special skill that people who can do it accurately have become Warren Buffett’s and Peter Lynch’s. I’m no Warren Buffett. :). Hence, this worksheet can’t give investment advice.
Though I was aware of the limitations of building such a worksheet, but I proceeded it anyways.
The logic was simple, “at least it will give some clarity about stocks, which will be better than blind belief “.
In last couple of years, there has been several updates of this worksheet.
Today it has reached a stage where its users are beginning to like it more. Check the testimonials.
#2. How the worksheet estimates intrinsic value?
First it calculates the intrinsic value using 9 methods.
Out of 9 methods, 7 are proven mathematical models developed by experts.
I have taken clues from these models and tried to implement them on my excel sheet (#Customized)
Though my excel sheet may not calculate the numbers exactly as asked in the mathematical models, but I’ll say that it works.
The final number (intrinsic value) indicated by the worksheet in most cases are on the defensive side. Why?
Because the idea is to be ‘safe with stocks than sorry’.
How the worksheet renders the final value?
It uses its own algorithm to estimate this number. Though I will not disclose this secret here :), but I will say that it is result of a weighted average.
#3. How the worksheet gives overall score to stocks?
The worksheet checks the numbers from the financial reports of the company, and gives scores on the following heads:
- How low is the stock price? (Undervalued or overvalued).
- Future growth prospects? (Business growth and price growth).
- How efficient is the company’s management?
- How profitable is the underlying business?
- What is the financial health of the company?
- Is there any threat of bankruptcy?
When the overall score is 85% plus, and the stocks is also undervalued – there is a good chance of making profits by investing in such stocks.
In a normal market, majority stocks will not be able to fetch a 85%+ score. So what?
This is an indication that either the stock is overvalued or its business fundamentals are not appropriate.
#4. What makes this worksheet unique?
The thing that makes this worksheet special is its usability. How?
Anybody can use it.
Even a person who has zero knowledge of stocks, can use it to analyze stocks. How?
By relying on its “intrinsic value” and “overall score” estimation.
Why I say that “anybody” can use it?
Because it just asks the users to ‘copy and paste‘ stock data…and that’s it.
It is almost a ‘no brainer‘ for its users.
Area of improvement
There are people who may not like this worksheet because the data entry is manual (copy and pasting).
If this worksheet could fetch data automatically from internet, it could have been better.
#5. What other information this worksheet can provide about stocks?
The core of this worksheet is intrinsic value and overall score estimation.
But apart from these numbers, my stock analysis worksheet can do more.
This worksheet has been designed to churn 10 year financial data of stocks.
So, it will not be wrong to say that it can do virtually everything to analyze stocks fundamentally.
Lets see few other features of this worksheet:
#5.1 It can display last 10 years financial ratios.
It can calculate a stock’s financial ratios like: its profitability, debt ratios, liquidity ratios, price valuation etc.
#5.2 It can display the past growth rates
Based on the past performance of the business, the worksheet tries to estimate the future growth in near term.
In the front end, the worksheet displays the growth rates as “zero” where ever the number goes in negative. Why?
The only purpose is to present a neat report to the user.
This also gives a better interpretation ability to the user. How?
Because I’ve personally experienced that too many numbers often makes it confusing to conclude.
#5.3 Price trend in terms of Simple Moving Averages (SMA)
Looking at stock’s price through the lens of “Simple Moving Averages” (SMA) gives more clarity about the trend.
What this worksheet does is provide the graphical representation of the SMA for a stock.
Such a representation gives a more clear perspective of whether a stock’s price is trending upward or downward.
When a good stock is seeing a downward trend, it may be an indicative of a possible opportunity. How?
Good stocks can become undervalued when its price falls.
#5.4 Last 10 year price trend
This worksheet gives a clear picturisation of the last 10 year price movements for a stock.
How does it help?
Generally speaking, price of good stocks gradually appreciates over time.
But sudden increase and decrease in price must be deeply looked into by analyst.
The price chart provided by my worksheet pans out the 10 year price movements clearly.
#5.5 Performance in Last Quarter
Quarterly performance of stock is reviewed in terms of its total income, net profit and earning per share (EPS).
Though for long term investors, quarterly performance is not so interesting, but still it is worth considering. Why?
Because a stock whose price is rising or falling, may have its reasons hidden in its quarterly trends.
Which factors decides the stock’s performance the most? Income, PAT and EPS.
6. How to use the worksheet?
There are three (3) main steps of using this worksheet:
- Pick Stocks: Before using the worksheet, the user must self-decide which stock to analyse.
- Data Entry: Financial data of the picked stocks must be entered (copy and paste the information from internet) into the worksheet.
- Observe: The final step is to observe the “cover sheet” of the worksheet. Intrinsic value and overall score is visible in the cover sheet.
#7. Who can use this worksheet?
This worksheet can be used by anyone.
Irrespective of ones level of stock know-how, anyone can use this worksheet.
Why is this flexibility? Because what this worksheet demands from its user is only data entry, and that too in form of copy and paste.
#8. Use this worksheet to find answer of what?
When I started using this worksheet, getting answer to these two questions was the priority:
- If the current price (of stock) is high or low?
- If the underlying business (of stock) is strong or weak?
How to know if price is low or high? By comparing it with its intrinsic value.
How to know if the underlying business is good? By checking the overall score.
This worksheet try’s to give both the answers.
#9. Which data must be entered into the worksheet?
Following are the type of stock data that must be entered into the worksheet:
- Financial Reports: moneycontrol provides an excellent source for any company’s balance sheet, profit and loss accounts, cash flow reports. It can also be used to download the last 5 quarter’s financial data. All these numbers are available in excel friendly format. One needs to just copy the data and paste it into the worksheet.
- Price History: Investing dot com provides 10 years price data of Indian stocks in excel-downloadable format. Just download the file, copy the data, and paste it into the worksheet.
- General Data: This is the smallest data entry sheet of all. But In this worksheet data must be sourced from multiple websites (as shown in the infographics above).
9.1. How much time it takes to enter stock data?
For a beginner, it might take 20-25 minutes to enter stock data.
But with some practice, the data entry time can be substantially reduced.
For an expert hand, the data entry will not take more than 12-15 minutes per stock.
#10. How to enter data?
The best way to learn data entry is by watching the “data entry video guide“.
Quick hints on data entry…
To best utilise this excel sheet for stock analysis, one must first learn to enter data into this worksheet.
If data entry is done properly, stock’s report will come out cleanly.
There will no confusion.
So my advice to the users of this worksheet is that, see this video guide multiple times. If possible, practice the data entry into the worksheet while watching the video.
Once you’ve practiced data entry for 4-5 number stocks, you yourself will realise how easy it is to use this worksheet to analyze stocks.
My worksheet is a reasonable tool to analyze stocks in Excel.
Before I built this sheet, my way of stocks analysis was to check-out their financial ratios.
But I heard experts saying that, “it is essential to look into financial reports of companies to analyze their stocks.”
But the problem was that, I did not know how to read and comprehend balance sheets etc.
It took me some time to learn this skill, and then I decided to give a shape to my learning.
I used my knowledge of financial reports, MS Excel, mathematical models, financial ratios to build this worksheet.
The idea was to provide a tool to my readers which even novice non-finance guys can use to get a first impression about their stocks.
Hope you like this blog post. If you want to know more about my worksheet, check this link:
You can also leave your comment here to ask more about how to analyze stocks in excel.
Have a happy investing.