Given a chance, an informed investor would not buy-sell stocks blindly. They would do stock analysis before investing. But only a few people do it. Why? Because of the lack of suitable tools. So what is the solution? We can analyze stocks in Excel.
Generally, when we buy laptops/computers, our device is preloaded with MS Office which has Excel. So in a way, we can say that stock analysis in Excel can be done virtually for free.
But how to analyze stocks in Excel? What needs to be done? This is where Excel can carve a niche for itself as a stock analysis tool. In this regard, its capabilities are nearly endless. It is a great application.
But before people can take a plunge into Excel and start doing analysis on their own, it is better to take a step back and understand what actually is stock analysis.
This know-how will build a perspective on the subject, and would eventually chalk the path for more effective Excel usage. A good starting point for newbies will be to learn about the financial ratios used in stock analysis.
What is Stock Analysis?
As you can see in the above infographics, stock analysis basically deals with financial reports and price data of a stock/company.
They can be four types of financial reports: (1) balance sheet, (2) profit and loss account, (3) cash flow statement, and (4) quarterly reports. The fifth (5) report is the historical price data of the stock.
To analyze a stock in a more reliable way study of only one year report will not be enough. Hence, while analysing stocks in Excel we must use at least 10 year reports.
In the process of stock analysis, we study the reports and price data of stock. But what is the expected end results? The analyst expects to get an answer for the following questions as a result of stock analysis:
Expected answers from stock analysis:
- Business is Profitable? As an investor, it is important to invest only in those companies which are sufficiently profitable. To judge a company’s profitability levels, the use of financial ratios is essential.
- Financials are Healthy? There are two main aspects of a financially healthy company. First, less reliance on loans to fund working capital and Capex requirements. The second, generating fast cash in-flows to meet the working capital needs. Again, the use of financial ratios will come in handy.
- Business is Growing Fast Enough? Till a business is growing faster, it will attract investors’ attention. No matter how good is the business model, if it is not growing it will have less value for the investors. Analyzing the last 10-years data can give a feel about the probable future growth rates of a company.
- Valuations are Good? Investors will always buy stocks at undervalued price levels. One of the most important aspects of stock analysis is to identify if a Stock is currently undervalued or overvalued. The use of mathematical models can give an idea of the intrinsic value of the company.
[Note: It is important for people to first know about the fundamental analysis of stocks to make use of Excel more effective.]
How to Analyze Stocks in Excel?
A symbolic representation of how to analyze stocks in Excel is shown in the above flowchart. Basically, the total process of stock analysis will happen in four steps:
- #1. Data Sourcing: One should identify a source from where 10-year financial reports of a company can be located. Generally, the most reliable data is available on the individual companies’ website. Another easier option is to get these reports from websites like economictimes, Yahoo finance, moneycontrol, etc.
- #2. Entering Data in Excel: Once the 10-year data is in hand, the next step will be to enter them into an excel sheet. This is perhaps the most time-consuming activity of stock analysis in excel.
- #3. Financial Ratios: In this step, the 10-year data available in Excel will be used to calculate financial ratios. Most of the financial ratios can be calculated using simple excel formulas. Read More: About financial ratio analysis.
- #4. Mathematical Models: It is here that the process of stock analysis in Excel becomes more complicated. Unlike financial ratios which can be calculated using simple formulas, implementation of mathematical models can only be done by applying a combination of logic, rules, and formulas.
[Note: Know more about mathematical models like NCAVPS, DCF, Absolute PE, and Residual Income Model.]
Hurdles of Analyzing Stocks in Excel
As explained in the above four steps, step number two and step number four are the biggest hurdles in analyzing stocks in Excel.
- Manual Data Entry: As if accumulating a 10-year financial report of a company was not enough, data entry of these numbers in an excel sheet makes it even tougher. So for people who want to analyze stocks in Excel, this manual data entry process can be a big demotivator.
- Implementation of Mathematical Models: Mathematical models are not formulas. They are more like a concept/theory. So, whenever we are estimating intrinsic value we are actually implementing the concept. Hence, the answer derived from the same mathematical models will differ from person to person.
It must be understood that accurate Intrinsic value estimation is an art. People who know this art has become Warren Buffett’s and Peter Lynch’s. So it is needless to say that the intrinsic value is estimated by Warren Buffett will vastly differ from the intrinsic value estimated by we common people.
So one must ask if our estimated intrinsic value is not so accurate, why to try estimating it in the first place? Yes, it is true that our estimated intrinsic value will not be so accurate, still, it will be multiple times better than buying stocks blindly.
How to build a Template to analyze stocks in excel?
The best solution is, no matter how difficult is the concept of analyzing stocks one must go ahead and start learning about fundamental analysis. Give yourself 2 to 3 months time.
- First, learn how to read balance sheets, profit loss accounts, and cash flow statements of a company.
- Second, Learn how to calculate various financial ratios which assist in stock analysis.
- Third, learn the concept as described in various mathematical models used for the company’s valuation.
- Fourth, learn about Macros. It is a programming code that can make your excel sheet operate like a small software.
A combination of the above-mentioned four points can help you built an excel-template that can analyze stocks like a pro.
Easier Solution – Our Stock Analysis Worksheet
You can use our excel-template, we call it Stock Analysis Worksheet (V3.0). Here are the main features of this excel based tool.
#A. Usage
Automatic Data Fetch: Version 3 of the stock analysis worksheet can fetch stock’s data upon the click of a button. As of Oct’20, there is a database of 450+ number stocks that enables Excel to fetch 10-years data automatically upon the click of a button.
So there is no manual data-entry required. Ten-year balance sheet data, profit & loss accounts, cash flow statements, price data, and recent quarterly reports are pulled into the worksheet via the internet.
As a result, now it requires its users to remain connected to the internet. The worksheet will not be able to fetch data offline.
The Excel must also have Macros enabled. By default, Macros is always enabled in all Excel programs. Also, if the firewall setting of one’s laptop/desktop is blocking the external data import, it will not be able to fetch data. How to solve this issue? Your networking-guy can do it for you, or you can use another (personal) laptop/desktop device.
[Read here to know more about the other limitations of this worksheet.]
#B. Reporting
Intrinsic Value & Overall Score: Our Excel has been programmed to estimate the fair price (intrinsic value) of stocks based on seven mathematical models. A weighted average of all these methods is used to estimate a stock’s intrinsic value. The worksheet can also give an overall score based on their fundamentals.
Financial Ratios:
The worksheet has inbuilt formulas that can calculate more than 30 number financial ratios. All financial ratios have been clubbed into six types: (1) Liquidity ratios, (2) Solvency ratios, (3) Operating efficiency ratios, (4) Profitability ratios, (5) Financial risk ratios, and (6) Price valuation ratios. The financial ratios are calculated for the last 10 years.
10-Years Snapshot:
Reading 10-year balance sheet data, or profit and loss account data, or cash flow report at one glance gives huge insights into the changing company’s fundamentals. At a glance, one can see how the sales, profit, EPS, net worth, cash flows, etc are changing with time.
Last 10 year price trend
This worksheet gives a clear picture of the last 10-year price movements for a stock. How does it help? Generally speaking, the price of good stocks gradually appreciates over time. But a sudden increase and decrease in price must be deeply looked into by analysts.
Conclusion
Analyzing stocks in Excel is possible. But it requires good know-how on the part of the user to build such a tool. One of the main hurdles in using such a tool is the manual-data entry and application of mathematical models to estimate intrinsic value.
Frankly speaking, such a task must be left to the software. Our Stock Analysis Worksheet is a fair compromise between the purchase of a costly software application and investing in stocks blindly.
Before I built this sheet, my way of stock analysis was to check-out only financial ratios. But I heard experts say, “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 sheet numbers etc.
It took me some years to learn this skill, and then I decided to give a shape to my learning. We used the knowledge of financial reports, MS Excel, mathematical models, financial ratios, and macros coding to build this worksheet.
The idea was to provide such a tool to my readers which even a novice (non-finance guy) can use to get a first impression about their stocks.
Have a happy investing.
