A while ago I shared my tutorial how to use Google Spreadsheet for dividend portfolio. In the tutorial I resolved the key problem of the build-in GOOGLEFINANCE function - its inability to pull stock dividend information, to be more specific, dividend per share and dividend yield. Although I shared the formula to pull dividend information automatically using Google Spreadsheet in the tutorial, many readers asked about sharing a Google Spreadsheet dividend portfolio template to track their dividend portfolio. As you can see, this post is a bit long over due (sorry!). After many months of revising my own Google spreadsheet dividend portfolio template, I thought it might be a good idea to write a "step by step guide" on how I created such template and share it with my readers.
I'm a self-proclaimed Excel nerd. I love Google Spreadsheet because I can access the spreadsheet anywhere in the world, as long as there's internet access. I really hope this step by step guide will help you many of you.
Please note, since I am sharing the template I use to track our dividend portfolio, the design is based on owning both US and Canadian listed dividend stocks.
Step 1: Create a Google Account
Most people have a Google account but if you don't, create one so you can use Google Drive. Once you have an account, access your Google Drive and create a new Google Spreadsheet.
Step 2: Create headings
The first sheet I rename it to Dividend Portfolio. I also create headings that I want to use to track my portfolio. After many changes, I decided to use the following headings:
- Sector: The sector the stock belongs to
- Accounts: Which accounts we hold the stock in
- Currency: Since we hold US and CAN listed stocks, this allows us calculate the portfolio market value in Canadian currency
- Ticker: Stock ticker symbol
- Name: Name of the stock
- Shares: Number of shares we own
- Cost Basis: How much we paid to purchase the stock
- Cost/Share: Price per share that we paid for the stock
- Price: Current market price
- Market value: Current market value of the stock
- Profit: How much unrealized profit we are making
- Weight: Stock weight of the portfolio as a percentage
- Div/Sh: Div per share received per dividend payout
- Yield %: The current dividend yield of the stock
- Freq: How many times the stock pays dividend in a year
- Div Est: Estimated dividend payout for the year
- DRIP: Whether we are DRIPing the stock or not
- YOC: Yield on cost
Step 3: Populate drop down list
For headlines like Sector, Accounts, Currency, Ticker, and DRIP, I populated drop down lists. To do so, select the desired rows then go to Data -> Validation. Under Criteria, select List of items, then enter the desired items separated by columns. I like drop down list as it saves quite a bit of time when entering data.
Step 4: Enter data
Enter the respective data in each row. Data like sector, accounts, currency, ticker, name, shares, cost basis, and DRIP should be pretty easy to enter.
The rest of the fields are calculated automatically based on straight forward formula.
For Cost/Share, the formula that I used was Cost Basis / Shares. For example, for Royal Bank, I entered "G3/F3."
The two columns that required some data pulling are the Div/Sh and Yield columns. For those I used the ImportXML function which I described in my original Google Spreadsheet tutorial. Unfortunately, there are a few shortcoming with the ImportXML function. First there's a limit of 50 queries, second it seems that Google has implemented some sort of security protection to block connections from time to time. What does this mean? Occasionally the ImportXML function can return/stuck in N/A or Error state. One way to eliminate this problem is to manually enter the dividend information (I know, not ideal).
Step 5: Data conditional formatting
To make it easy to see whether you're making a profit on a particular stock, I used conditional formatting to colour the Profit column red or green based on whether there's any profit or not. To do this, simply select data under Profit column, then go Format -> Conditional Formatting. A conditional format rules window will pop up on the right hand side of the sheet. The rule to use is "Format cells if Greater than or equal to 0." Then I labelled the formatting style to use green. Next I created another conditional format rule calling out "Format cells if less than 0" and labelled the formatting style to use red.
For this Google Spreasheet dividend portfolio template, I created 3 charts to track Sector Diversification, Accounts Breakdown, and Dividend Income Breakdown. I like charts because they give good visual summaries.
To create these 3 charts, first I had to create three separate tables with the appropriate data. To get the data needed, I used a simple function called SUMIFS. Basically the function adds up a range depending on the criteria. For example, to add up the market value of all dividend stocks in financial sectors I used the formula "SUMIFS(data in market value column,data in sector column,Financials)." (Actual formula: SUMIFS($J$3:$J$25,$A$3:$A$25,T19)
Next I calculated the percentage of each headline.
Once I had the tables completed, I selected the data range I wanted to use, clicked on the Insert Chart button and inserted the desire chart type.
Because our dividend portfolio has a mix of Canadian and US listed stocks, I decided to show a breakdown and the market portfolio value in Canadian dollar.
To query the current exchange rate, I used the function GoogleFinance("CURRENCY:USDCAD").
The SUMIFS function was utilized again to add up the market value of all Canadian and US stocks. With the US to CAN exchange rate available, I converted the US stock value into Canadian currency to show the entire portfolio value in Canadian dollar. The breakdown percentages are also calculated in the table.
I'm a very visual person so I like to have a summary report to see all the important numbers. Hence, for this Google Spreadsheet dividend portfolio template, I created a table to list all the important values like Book Value, Market Value, Profit, Estimated Annual Dividend, and YOC.
A dividend portfolio template is no good if you don't have a sheet to track your annual dividend income. Hence I created a new sheet called dividend income to track annual dividend income.
A long time ago I used to only list the stock tickers in the dividend income sheet. But it was confusing for Mrs. T when I got her involved in tracking our household finance. So I added stock names to make it easier. 🙂
Whenever a stock pays dividend, you can populate the data under the respective month. Once the year is over, you can create another sheet to track dividend income.
If you have read this far, you are probably wondering where's the actual template is. Here is the Dividend Portfolio Template that you have been waiting for. I have added a few notes in the document to answer some questions you might have.
Please feel free to make a copy of your own, play around with the spreadsheet, and adjust things to your liking. This is a long overdue post and I hope some of your will find this dividend portfolio template useful. If you have any questions, please either comment below or send me an e-mail. Even if you don't have any questions, I would love to hear from you on what you think of the template or if there's something you'd like me to add in the template. One of the things I would love to add is the 5 year annualized dividend growth rate. But that requires some complicated pivot table that I haven't gotten around to figure out yet (one day).
Dear readers, do you have any other tips for a dividend portfolio Google Spreadsheet? How do you keep track of your dividend portfolio and income?