Do you want to be able to screen stocks for their Graham number value? In this blog post, we will show you how to create a Graham number stock screener in Excel or Google Sheets. This is an excellent tool for investors who want to ensure they are investing in good companies. We will walk you through the steps of creating the screener and give you some tips on how to use it. Let's get started!
What is a Graham Number?
The Graham number is a valuation metric commonly used in value investing. It is based on the work of Benjamin Graham, who is considered the father of value investing.
The Graham number is calculated by taking the square root of 22.5 times a company's earnings per share (EPS) multiplied by its book value per share (BVPS). The formula for the Graham number is:
Graham Number = sqrt(22.5 x EPS x BVPS)
In this formula, EPS is the company's earnings per share over the past 12 months, and BVPS is the company's book value per share. The number 22.5 is a multiplier that Graham used to account for a company's growth rate.
The Graham number is used to determine whether a stock is undervalued or overvalued. If a stock's current price is significantly lower than its Graham number, it may be considered undervalued and a potential buying opportunity. On the other hand, if a stock's current price is significantly higher than its Graham number, it may be considered overvalued and a potential selling opportunity.
It is worth noting that the Graham number is just one of many valuation metrics that investors can use to evaluate stocks. It should be used in conjunction with other factors, such as a company's financial statements, competitive position, and management quality, to make informed investment decisions. More on that later.
How to use the Graham Number to find undervalued stocks
Once you have calculated the Graham number for a stock, you can use it to find value stocks. You should look for stocks with a Graham number lower than their current price – these are considered undervalued and may present an opportunity for investors to make money. Additionally, you should also look for stocks with Graham numbers that are significantly lower than their peers – these stocks may be mispriced and could offer a good investment opportunity.
For example, in 2022, Apple Inc. has an EPS of 6.15 and BVPS of 3.12, giving it a Graham number of 20.77 (sqrt(22.5 x 6.15 x 3.12)). If Apple's current price is above 155.55, it may be overvalued, and you should consider not investing in it. On the other hand, if Apple's current price is significantly lower than 20.77, it may be undervalued, and you should take a closer look at the company before investing.
Creating a Graham Number Stock Screener in Excel or Google Sheets
Now that you know how to use the Graham number to find value stocks, let's look at how to create a Graham number stock screener in Excel or Google Sheets. To do this, you will need the following:
- A spreadsheet program (such as Excel or Google Sheets)
- Current financial data for the stocks you want to screen (more on that below)
- The Graham number formula (see above)
Once you have all the necessary items, follow these steps to create your Graham number stock screener.
Find a stock list you are interested in analyzing
The first step is to find a list of stocks you are interested in analyzing. You can do this by searching for stocks on Google, using a stock screener, or researching stocks yourself. A simple way to do this is to determine what kinds of stocks you are interested in and Google a stock list. For example, "semiconductor stock list".
Gather financial data for each of the stocks on your list
Once you have a list of stocks you are interested in analyzing, the next step is to gather financial data for each stock. We recommend you use the Google Sheets and Excel plugin called Wisesheets. This tool allows you to get all the data you need for the calculation and the Graham number itself using simple spreadsheet formulas. For example, you can enter any cell =WISE("ticker", "graham number", "ttm") to get the value right away for as many companies as you'd like.
Better yet, you can add other important key metrics and financials to your screener like this:
You can try it free for yourself here.
Refresh the stock data
Since the data is always changing based on the latest earnings per share and book value, you can always press the refresh data button at the top right of the add-on to make sure your Graham number stock screener is up to date.
Add conditional formatting (optional)
Once you have all the financial data for your Graham number stock screener, you can optionally add conditional formatting to make it easier to spot potential opportunities. For example, you could set up Graham numbers lower than the current price of a given stock to be highlighted in green.
Filter through the stock data
Now that you have all of the Graham number data and conditional formatting in place, you can filter through the stock data to find potential opportunities. Look for stocks with Graham numbers lower than their current price – these are considered to be undervalued, as well as those with Graham numbers significantly lower than their peers – these may be mispriced and could offer a good investment opportunity. You can use data filters to help you find these opportunities quicker.
Once you've identified potential stocks, take the time to do further research on each one and make an informed decision before investing. You can also use other metrics, such as the price-to-earnings ratio (P/E) or the dividend yield, to help refine your stock picks.
Tips for improving your stock screening results
- Ensure your Graham number stock screener is always up to date by regularly refreshing the data.
- Be aware of potential outliers or stocks that may be mispriced due to market anomalies.
- Use additional metrics such as P/E or dividend yield to help refine your screening results.
- Don't rely solely on Graham numbers – make sure to do extra research before investing.
- Take your time and don't rush decisions – invest in stocks you understand and are confident in.
By following these steps, you can easily create a Graham number stock screener for Excel or Google Sheets and start screening stocks for potential opportunities. Be sure to take the time to do further research on each stock before investing, and remember that Graham numbers are just one of many metrics you should consider when evaluating a stock.
Conclusion
Creating a Graham number stock screener in Excel or Google Sheets can be a great way to quickly and easily evaluate stocks for potential investment opportunities. To do this, you'll need to gather financial data for each stock on your list, use the Wisesheets add-on to calculate Graham numbers, refresh the data regularly, and add conditional formatting (optional).
Once you have the data in place, use filters to quickly identify potential stocks and then take the time to do further research on each one before investing. By following these steps, you can easily set up your Graham number stock screener and start screening for possible investment opportunities.
Happy Investing!
