Find cheap income shares with a free Internet-connected spreadsheet.
Click a single button to analyze the entire NYSE and get the best-value buys.
The tool uses a metric based on the current share price, PEG, EPS and dividend yield. You can also use it to analyze listings on other exchanges.
My retirement portfolio is a mixed bag of stocks, mutual funds and bonds across several international markets. The backbone, however, is a collection of stocks that pay out a healthy yearly income (which, of course, is reinvested).
Finding stocks that pay a dividend of 3% or more is simple. But finding cheap stocks with a reasonable dividend is a little more involved.
I use several ways of valuing companies, including the Graham Number or discounted dividend models. In this spreadsheet, I employ a simple metric that employs data available from the Yahoo Finance API.
This VBA-powered spreadsheet contains the entire list of tickers traded on the New York Stock Exchange – over 2900 stocks. When you click the single, solitary button, Excel downloads and displays the
- last trade date and price
- dividend yield
- book value
- and EPS
for every ticker.
The spreadsheet then picks those stocks for which these criteria are satisfied:
- share price < 1.5 x book value
- dividend yield > 3%
- 0 < PEG < 1.1
- EPS > 2 x dividend per share
These criteria aren’t set in stone (and they’re not necessarily the criteria I use); they’re just a convenient, but relatively reasonable method of identifying cheap income share.
You also see how long the last run of the spreadsheet has taken.
- Be patient! After you click the button, you might be waiting five or six minutes for the spreadsheet to update.
- The VBA hits the Yahoo Finance API many times for a single update. In fact, as saved, the spreadsheet sends 60 API requests, each for fifty tickers and 7 items of data. If you try to update more than one or twice an hour, Yahoo Finance will block your IP address for a while.
- Yahoo Finance have a 15-minute delay – so while the data isn’t real-time, it’s recent enough.
You can use this tool for find cheap dividend stocks on the LSE, TSX, ASX or any other international exchange. Just delete the existing tickers and paste your tickers into the Ticker list in Column A (This spreadsheet has a complete list of tickers across many international exchanges.)
If you like this spreadsheet, please link to http://investexcel.net. I appreciate your support!