Tracking your portfolio is one of the most exciting parts of investing. Everybody wants to know how much money they have, how their portfolio is doing against the market, and more. I have found it best to build your own spreadsheet to track all of this data. However, I found it incredibly hard to find a good guide to building a dividend portfolio.
Before Yahoo Finance made their API changes, it was very easy to make a portfolio in excel. Unfortunately, they switched their coding which broke many people’s personal spreadsheets coding. That is why I will teach you how to make your very own dividend portfolio on Google Sheets. You can find a free, simple to use template at the end of this post. You will learn how to build a dividend portfolio, a dividend tracker, and have access to a few criteria for spotting undervalued stocks.
Create Google Account
First, you need to create a Google account. Once you have a Google account created, access you’re Google Drive and open Google Sheets. If you cannot find this, simply type in Google Sheets on any web browser and it will take you to it.
Create your portfolio
Everybody will likely customize their portfolio to their needs but after many changes I have settled on the below headings. I refer to the Portfolio tab as the lead page because it consolidates all the information into one easy to read page. Any of the below headings that are highlighted green populate automatically in the portfolio template.
- Sector: What sector does the stock belong to
- Ticker: Stock ticker symbol
- Quantity: Number of shares you own
- Price paid: Average cost you paid per share
- Cost basis: Total value of shares you bought
- Current Price: Current price of stock via Google Finance
- Current Value: Current value of your holing
- $ Change: Difference between Current Value and Cost Basis
- % Change: Percentage points your stock is up or down
- Overall weight: Percentage of your portfolio in one stock
- Yield: Current dividend yield
- Annual Div/Sh: Annual dividend per share
- Annual Income: How many dividends you receive per year
- Yield on cost (YOC): What is your dividend yield on the price you paid for the stock
Some other information I add on my personal portfolio, which is included in the template, is a pie chart of sector allocation as well as a chart showing the allocation per sector. This will let you know if you are overexposed or underexposed to certain sectors.
Unfortunately Google Finance does not automatically pull the dividend yield and annual dividend per share. Thankfully, there is a formula that can. Here is the secret formula for a dividend portfolio; you must type into your spreadsheet if you do not use the template:
=split(ImportXML(concatenate(ʺhttp://finance.google.com/finance?q=ʺ,(insert ticker symbol here)), ʺ//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*ʺ),ʺ/ʺ)
This formula will pull the per period dividend (that could be monthly, quarterly, or annually) for the stock. You will manually need to update the number of times a company pays out a dividend in the “Stock Data” tab.
One quick note: Sometimes this formula will return “#NA.” There are two ways to fix it. The first one is to give the sheet some time to populate. The second option is to change the ticker symbol and change it back to what you want. Sadly not everything will work properly instantly.
Stock Data Tab
The stock data tab is where most of the information for the Dividend Portfolio is pulled from. This helps make the Portfolio tab very clean. There are multiple uses for this tab:
Spot Undervalued Stock
A dividend portfolio on Google Sheets has options to many criteria thanks to Google Finance. Some of my favorites for getting a quick glimpse of value are the % off 52 week high vs % off 52 week low. Through conditional formatting, you can set your own personal criteria. I personally have a condition for the cell to turn green if a stock is 10% off its high and 7% off its low. You can adjust as you want.
Determine an approximate payout ratio
Payout ratio is simply calculated as dividend per share dividend by earnings per share. Simply put, how much of earnings are management paying out as a dividend. The stock data tab will automatically calculate the payout ratio for you. If you have a criteria on an appropriate payout ratio, you can create conditional formatting in this column to identify stocks that fall in your range.
For example, one of my four parameters is a payout ratio less than 70%. If you right click and go down to conditional formatting, you can create a criteria to highlight the cell green if it is less than 70% and red for greater than 70%. This makes sorting through information very easy.
This feature does not work properly for REITs as they use AFFO rather than EPS.
Track day-to-day performance
One final feature is the ability to track the one day change of your portfolio vs. the market. While most dividend investors will not use this feature, I have found it very useful for seeing if apps like personal capital accurately track my portfolio. I have found using this feature is more accurate than most apps that I use.
There is no way to accurately pull the dividends you will receive from the stock in the appropriate month. You will need to manually make all entries on this tab.
I currently have this tab setup to be split between taxable, and retirement accounts but you can adjust as needed. This sheet keeps all of your dividend payments neat and easy to follow as well as allows you to see when stocks have increased their dividends.
In order to make your own edits to the spreadsheet, please click File, Make a Copy. This will allow you to make all edits you desire.
Here is the link to the template: Dividend Portfolio Template
That is all I have for a guide on how to build a dividend portfolio on Google Sheets. There are many other features such as charts, data validation, and other techniques you can use to make your personal portfolio as crazy or simplistic as you want.