How to Build a Dividend Portfolio on Google Sheets

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

Portfolio Tab – This was the clearest I could get the picture.

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.

Stock Data Tab – Unfortunately the Image is not very clear.

Dividend Tracker

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.

Portfolio Link

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.

 

 

9 comments… add one
  • Mr. Robot Jul 4, 2017, 7:12 am

    Nice, thanks for sharing! I hadn’t thought of the conditional formatting yet for highlightinh potentil buys. I’ll be sure to incorporate that when I build my watchlist.

    • Stefan Sharpe Jul 5, 2017, 6:18 pm

      It really makes a difference because it helps me spot stocks I normally just skim over.

  • timeinthemarket Jul 4, 2017, 8:10 am

    This is good info to have. I recently got a new PC and unfortunately it didn’t come with excel(aside from a one month trial of 365) so now I have to figure out whether to pay for it or figure out something new to use(or just use excel at work).

    • Stefan Sharpe Jul 5, 2017, 6:20 pm

      I would go with google sheets it is clunkier but does the job!

  • Lance @ My Strategic Dollar Jul 4, 2017, 9:13 am

    Thanks for sharing! Tracking is the exciting part and I found this to be extremely useful!

    • Stefan Sharpe Jul 5, 2017, 6:19 pm

      Thanks Lance, it really is fun to see growth every quarter.

  • Dividend Portfolio Jul 6, 2017, 7:18 am

    Thank you, thank you, thank you for this post. I recently started blogging about a year ago. It took me a while, but I finally found a way to track my dividend portfolio. I used Google Sheets, and built my portfolio using formulas associated with Yahoo Finance. I recently had problems with my portfolio not displaying properly on my blog. The portfolio would say N/A or sometimes No Data.

    Based on my research, I’ve determined that the culprit, as you’ve mentioned, is that Yahoo Finance changed the way it coded its API. So now, my old formulas (at least with respect to automatically recording the dividend on a spreadsheet) doesn’t work. As a result, I manually indicate what the dividend is. I bookmarked this post, because I’m going to try your formula. Hopefully it works.

    In any case, I may revamp my portfolio (even if it’s just my private portfolio) with some of the other suggestions in this post.

    • Stefan Sharpe Jul 6, 2017, 7:36 pm

      Gla d you liked it, I have got great feedback from many on it. There are many neat formulas in the workbook that many may not know/use to feel free to take as much as you want!

  • wealth from thirty Jul 8, 2017, 7:59 am

    It’s cool to see how you track your portfolio Stefan. I’m working on a portfolio tracking spreadsheet in Numbers (for OSX). Not quite as flexible as GoogleSheets but I enjoy the platform – having spent years developing Excel skills, like you, I found excel suddenly didn’t do a reliably good job of retrieving stock quotes – you can kind of get around it in Mac with CURL commands, but Numbers has it’s own formulae for stock quotes etc. GoogleSheets seems very popular with dividend investors!

Leave a Comment