Step-By-Step Guide on How to Make a Google Spreadsheet Dividend Portfolio Template

Dividend Google Spreadsheet template 3 - charts

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

Dividend Google Spreadsheet1

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.

Creating charts

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.

Dividend Google Spreadsheet template 3 - charts

Currency Exchange

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.

Dividend Google Spreadsheet template 2 - exchange rate

Summary Report

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.

Dividend Income

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. 🙂

Dividend Google Spreadsheet template 4 - dividend income

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.

Final Words

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?

You Might Also Like


  • Reply
    August 17, 2016 at 11:32 am

    Neat Spreadsheet.
    Quick question - why do you track Yield on Cost (YoC)?

    ie: you don't use that for investment decisions? ie: decide not to sell an investment that has a YoC of 10% because the replacement only has a yield of 4%?

    • Reply
      August 17, 2016 at 11:37 am

      Hi Matt,

      Good question! I track YOC to give me an idea how much the portfolio is yielding. It's not used for investment decisions, it's just for tracking purposes.

      • Reply
        August 17, 2016 at 11:41 am

        Ok, it's ok for curiosity sakes I suppose - I just see so many people use it incorrectly.
        Won't 'yield' give you what your portfolio is yielding?

        • Reply
          August 17, 2016 at 11:51 am

          Correct, Yield would give me what my market value portfolio is yielding.

          YOC would give me what I'm getting based on my cost basis. It's just a number I like to see. It shows how important & powerful dividend growth is.

  • Reply
    August 17, 2016 at 1:44 pm

    I like this for a "snapshot in time" look at the portfolio.
    I'd like to see something that shows how the portfolio value and income changes over time.
    That may not be easy though.

    • Reply
      August 17, 2016 at 2:06 pm

      Hi Fred,

      I wouldn't call it a snapshot in time as this provides real time portfolio information. 🙂

      If you want to see how the portfolio value and income changes over time, you can simply create another sheet and tabulate the values at specific time and generate a chart to show the results. Hope this helps.

  • Reply
    Jayson @ Monster Piggy Bank
    August 17, 2016 at 2:38 pm

    Tawcan, thank you for the tutorial. Having this kind of spreadsheet in detail makes it easy to track dividend portfolio.

    • Reply
      August 18, 2016 at 9:42 am

      Glad to have helped you Jayson.

  • Reply
    Dividends Down Under
    August 17, 2016 at 3:50 pm

    Really appreciate this Tawcan. At the moment we have a fairly basic Excel sheet for tracking our (Aussie) dividends. Perhaps we could use yours, or really go to down on adding more information to ours..


    • Reply
      August 18, 2016 at 9:42 am

      Feel free to use my template or add parts of mine into your existing one. This template I just shared has evolved over time. The original template was pretty simple too.

  • Reply
    August 17, 2016 at 5:07 pm

    will it add the dividend to the total amount of shares automatically if the drip is on ? or does it do it when you add it to the dividend income page ? also when you add another year to the dividend income page how is that done ?

    • Reply
      August 18, 2016 at 9:45 am

      Hi Alex,

      No it does not add the dividend to the total amount of shares automatically if DRIP is on. It's not possible to do because I don't know if you are enrolled in full DRIP or synthetic DRIP. Second, I don't know what price the DRIP shares were purchased since sometimes companies offer DRIP discount. You'd need to add this DRIP information manually to keep track of your cost basis.

      To add another year to the dividend income, I simply create a new sheet, enter all the stocks, and enter the dividends received accordingly. Hope this helps.

  • Reply
    Mr. Tako @ Mr. Tako Escapes
    August 18, 2016 at 1:42 am

    I've built nearly the exact same spreadsheet for my own portfolio Tawacan. I track a few different things, such as dividend growth rate, but it's pretty much the same spreadsheet!

    Great minds think alike I guess? Cheers!

    • Reply
      August 18, 2016 at 9:45 am

      Great minds do think alike.

      How do you get the dividend growth rate? Is that something you enter manually? I'm trying to figure out a way to have DGR calculated automatically.

  • Reply
    August 18, 2016 at 7:48 am

    Great post! I haven't used ImportXML myself. Learning XPath doesn't look like too much fun.

    I'm busy with research to revisit the use of ImportHTML for pulling in data from financial websites. See

    Thanks for sharing!

    • Reply
      August 18, 2016 at 9:47 am

      ImportHTML is similar idea as ImportXML, you're just importing from different sources. 🙂

  • Reply
    August 18, 2016 at 10:48 am

    Thank you so very much for this. I really, really needed this. I looked at other similar posts and tutorials, but they were not clear to me. I have yet to try your instructions, but I'm eager to do so. Question: Can I set up 2 (or more) spreadsheets? One for my registered account, and one for my non-registered account?

    Request: Since you're an Excel nerd, can you provide instructions on how to custom sort the Dividend All-Stars Excel spreadsheet, or the CCC lists (David Fish lists), I mean, creating a sort based on 4-5 criteria. Example: (1) dividend streak >/=8 years, (2) dividend yield >2.8%, (3) 1-Yr, 3-Yr, and 5-Yr DGR >/=5.0% and (4) by the latest dividend increase >/=5.0%. 

    Thank you.

    • Reply
      August 18, 2016 at 11:09 am

      Hi Helen,

      Glad to have helped. You certainly can set up 2 or more spreadsheets if you want. The template I shared have all the accounts in one sheet for easy tracking purposes.

      Rather than using Sort you can select all the data and Filter them. The trick is to create special rules as you indicated.

  • Reply
    Investment Hunting
    August 18, 2016 at 2:55 pm

    Thanks for sharing Tawcan. Scott over at has been building similar spreadsheets for a while. I'm gonna take the best of yours and his and make a master version for my use. Thanks

    • Reply
      August 19, 2016 at 9:18 am

      Hi Investment Hunting,

      Scott has built great spreadsheets. 🙂

  • Reply
    The Dividend Mogul
    August 19, 2016 at 8:49 am


    Thanks for sharing! As the post above mentioned - I am actually using Scott's at twoinvesting as well, but I also have my own in which I track some additional items I like to track. Your's did include some items for me to make mine better as well! My spreadsheet has like 10 tabs on it though. Love the simplicity.


  • Reply
    Jack Luo
    August 19, 2016 at 8:55 am

    Hi Tawcan,

    Great instruction.

    However, I still couldn't get the dividend/yield formula to work:

    =split(ImportXML(concatenate("",D2), "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/")

    It always show up as "Error Could not fetch url:".

    Anyways, I'll try other work arounds.

    • Reply
      August 19, 2016 at 9:20 am

      Hi Jack,

      Unfortunately Google seems to have implemented some sort of security protection to limit ImportXML function. I've found that if you switch back and forth between http and https the dividend/yield info would show up. Other method (not ideal), is to simply enter the dividend/yield info manually.

  • Reply
    August 20, 2016 at 7:42 am

    Request to readers: Would you please share your spreadsheets? It would be great to see the variations people come up with.

    • Reply
      August 22, 2016 at 9:49 am

      I'd love to see other people's spreadsheets too.

  • Reply
    Finance Solver
    August 20, 2016 at 1:28 pm

    The spreadsheet looks very clean, thanks for sharing it! I love excel and I love investing and I need to figure out how to combine the two together in the coming years. I just might use your template because I don't think I'm that skilled at tracking information.

    • Reply
      August 22, 2016 at 9:49 am

      Thanks Finance Solver. Feel free to use my template.

  • Reply
    Jack Luo
    August 25, 2016 at 3:08 pm

    Hi Tawcan,

    Not meant to comment twice here, but I noticed some discrepancies in the information provided by Google Finance, and I hope you can shed some light. Some of the information showed on Google Finance deviates quiet a bit from other sources and the official financial reports.

    For example, Shaw Communication (SJR.B). The EPS shown on Google Finance is "1.16", which is doesn't match anything of the EPS stated on the financial reports, either the Basic or Diluted. TMX Money, which is the alternative source I use for verifying information, is shown the EPS is "2.78".

    I wonder how Google Finance and other sites calculate the EPS, as well as other attributes.

    This is quiet concerning since the discrepancy is huge, and could potential impact many of our investment decisions.

  • Reply
    August 25, 2016 at 10:02 pm

    Hi Jack,

    I have noticed that a bit but I typically don't use Google Finance for evaluating things like EPS. I typically use Morningstar when it comes to stock evaluation. Yes it's quite concerning that the discrepancy is so large. Without looking into it further is it possible that the discrepancy is due to GAPP vs. non-GAPP?

    • Reply
      Jack Luo
      August 26, 2016 at 3:23 pm

      Hi Tawcan,

      When I evaluating a stock prior to buying, I almost always cross-reference some of the values against the actual financial reports.

      Speaking of the evaluation and analysis process, it will be great if you can write a post and share some of your experiences and approaches as well, I am curious to learn it from an expert like you.



      • Reply
        August 26, 2016 at 3:36 pm

        Hi Jack,

        Makes sense. Cross reference is always good. Good point on evaluation and analysis process. I'll see what I can put together.

    Leave a Reply