Step-By-Step Guide on How to Make a Dividend Portfolio Spreadsheet (Dividend Tracker)

This post was originally published in 2016. The key formula used in the dividend spreadsheet template for dividend info from Google Finance was formulated back in 2014.

Due to the changes in Google Finance in March 2018, it was necessary to update the formula for pulling the dividend data. As you may know, pulling dividend data directly Google Finance no longer works.

After looking on the web, I found that Yahoo Finance is a good reliable alternate source to pull the dividend data from.

Jul 2020 Update: The ImportXML is having regular issues pulling data from Yahoo Finance due to Javascript. So this made the dividend spreadsheet template a bit unreliable.

Many users have reached out to me regarding this issue. After some research, rather than relying on the ImportXML function, I decided to update the dividend spreadsheet template with a different and more reliable function that’d do the exact same thing. 

Dec 2020 Update: The ImportFromWeb add-on has a monthly pull limit. So if you check your portfolio regularly, by middle of the month, you won’t be able to see any dividend yield data. After experimenting a little bit, I decided to ditch ImportFromWeb and use another function called ImportHTML. ImportHTML is very similar to ImportXML but seems to be a bit more reliable.

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 dividend portfolio spreadsheet template that utilizes Google Spreadsheet to track their dividend portfolio and dividend income.

As you can see, this post is a bit long overdue (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 a Google finance dividend spreadsheet portfolio for tracking my own dividend portfolio and dividend income.

At the end of this post, I also shared a link for downloading the dividend Google spreadsheet template for free. 

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 (you can also enable offline mode so you can access the dividend spreadsheet without an internet connection). I really hope this step by step guide will help many of you.

Please note, since I am sharing the dividend investing template aka dividend tracker I use to track our dividend portfolio, the design is based on owning both US and Canadian listed dividend stocks. If you invest in a different currency, you should be able to easily modify the currency information.

Learning about dividend investing

If you’re reading this step-by-step guide. You are probably interested in dividend growth investing. Some here are great articles I have written in the past that you may find useful.

The first step when it comes to dividend growth investing and building your dividend portfolio is to determine how to start investing in dividend-paying stocks.

Eliminating stocks by looking at the different financial numbers is important but it is just as important to learn how to read the annual and quarterly reports. For Canadian dividend investors, you must understand how to build your portfolio so it can be as tax efficient as possible

For further reading, please take a look at the following two FAQ’s I have put together:

Google finance dividend portfolio template and dividend tracker

Ok, let’s get back onto topic. Here is the step-by-step guide on how I created the Google dividend tracking spreadsheet.

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 for the Dividend Portfolio Template

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 to 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 (Update: This field is no longer needed in the new version of the template)
  • 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 a List of items, then enter the desired items separated by columns.

I like the drop-down list as it saves quite a bit of time when entering data.

Google dividend spreadsheet data validation

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 a straight forward formula.

For Cost/Share, the formula that I used was for 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.

For formula requires specifying XPath from Yahoo Finance. For some reason, I wasn’t able to incorporate it into the actual formula. So in the spreadsheet template, I am referencing a cell.

Also a key thing to note is that stocks with a “.” in their symbol are labelled differently for Google Finance and Yahoo Finance.

For example, Rogers would be RCI.B on Google Finance, but it would be RCI-B.TO on Yahoo Finance. Since we utilize GOOGLEFINANCE in the Price Column, and use Yahoo Finance to pull the dividend data, you will need to manually tweak the ImportXML formula slightly. Again, please refer to my original Google Spreadsheet tutorial for more information.

July 2020 Update: ImportXML has been consistently outputting N/A. This is caused by Yahoo Finance using a lot of Javascript so ImportXML cannot pull the dividend info reliably.

As a dividend investor, not having the dividend info in the dividend spreadsheet is not ideal. So I looked around to find an alternative.

Fortunately, I found an alternative called ImportFromWeb. I have updated the spreadsheet and used an add-on function that’d do the same job. The add-on function is called “ImportFromWeb.” In order to use ImportFromWeb you need to do the following:

  1. Click on Add-ons and search for “ImportFromWeb”
  2. Install “ImportFromWeb” Add On
  3. Once it’s installed, you can replace “ImportXML” to “ImportFromWeb” in the formula
  4. Div & yields info should show up without any issues
Google dividend spreadsheet finding addon
Google dividend spreadsheet addon 1
Google dividend spreadsheet addon ImportFromWeb

For example:

ImportXML Formula: =split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,D5),$B$47),”()”)

ImportFromWeb Formula: =split(ImportFromWeb(concatenate(“https://finance.yahoo.com/quote/”,D5),$B$47),”()”)

Basically the formula is identical, we are just interchanging ImportXML and ImportFromWeb. You would continue to get the same dividend info in the Google dividend spreadsheet.

Note: ImportFromWeb will ask you for some access permission for your Google Account. If you’re not comfortable with this, do not use this add-on.

Dec 2020 Update: I received a lot of complains about using ImportFromWeb. First of all, some readers forgot to enable the add-on, rendering the spreadsheet somewhat useless. Then some readers found that they’d constantly use up the ImportFromWeb’s monthly limit, again, rendering the spreadsheet somewhat useless.

I started looking at an alternative way to pull dividend data into the spreadsheet and found that ImportHTML works pretty well.

So the spreadsheet has been updated to use ImportHTML.

ImportHTML Formula: =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,D5),”table”,2),6,2),” “)

As you can see, it’s very similar to the old formula, expect pointing to an Xpath, I know point to the location of the table directly.

For those of you that are coders, you’ll notice that I’m still referencing to the exact place in the code but using the HTML format rather than the Xpath format.

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.

Step 6: Creating charts for the Dividend Portfolio Template

For this Google Spreadsheet 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

Step 7: 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 the 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 the Canadian dollar. The breakdown percentages are also calculated in the table.

Dividend Google Spreadsheet template 2 - exchange rate

Geographical diversification is even more important than ever.

If you’re a Canadian, the last thing you want to do is investing 100% in Canadian stocks. This is another reason why utilizing an ex-Canada international index ETF like XAW or VXC is a good idea. It is also a good idea to purchase US dividend-paying stocks in your RRSP to avoid withholding taxes.

Step 8: 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.

Step 9: 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 dividends, you can populate the data under the respective month. Once the year is over, you can create another sheet to track the new year’s dividend income.

Possible Improvements for the dividend spreadsheet template

I personally use this dividend spreadsheet myself for tracking our dividend portfolio and found it extremely useful. There are some possible improvements to be made to the spreadsheet to make it even better though.

For example, being able to incorporate some or all of the parameters in the dividend scorecard so one can quickly analyze stocks in the dividend portfolio. In addition, some numbers that may be useful in the spreadsheet are:

  • 5 year annualized dividend growth rate
  • PE ratio for each stock
  • 10 year average dividend yield percentage. This can be a good indicator on whether the stock is over-valued or not.
  • Last dividend increase
  • Dividend payout schedule

Some of these should be straight forward to implement (just takes time), some may take a bit of time. Once I have a bit more time, I may fine tune and improve the dividend spreadsheet template a bit.

Dec 2020 Update: I updated the spreadsheet and added a column for PE ratio.

Final Words – Dividend Portfolio Template (Dividend Tracker)

If you have read this far, you are probably wondering where’s the actual Google dividend spreadsheet template is.

Here is the free Google Finance Dividend Portfolio Template you can download. I have added a few notes in the document to answer some questions you might have.

Please feel free to play around with this spreadsheet and adjust things to your liking.

This is a long overdue post and I hope some of you will find this Google Finance 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 to the template.

Now if you are looking for tops 10 Canadian dividend stocks, top 10 Canadian dividend ETFs, and top US dividend ETFs to own. You can check out these pages.

And if you’re doing index ETF investing, you might find the ETF Portfolio spreadsheet useful.

Thank you for taking the time to go through this step-by-step dividend spreadsheet template tutorial. If you have any questions, tips, or recommendations, feel free to contact me. I’d love to hear from you.

And please leave a comment on how you’re finding this dividend spreadsheet template!

Share on:
.

223 thoughts on “Step-By-Step Guide on How to Make a Dividend Portfolio Spreadsheet (Dividend Tracker)”

  1. Excellent spreadsheet. Quick question, when playing around I noticed it doesn’t pull dividend yield for some ETFs (ex: ITOT), how can that be changed? Additionally, for stocks that don’t have a dividend it returns a value of “N/A” is there a way to make this return a “0”? Thanks a bunch! Brian

    Reply
    • scratch that Bob- I saw your note in your Portfolio sheet that you’re investigating. I’ll post here if I figure it out.

      Reply
  2. Hey! Very useful information, and thanks for the share!

    I saw in your previous post that you had issues getting the XPATH accepted in the actual formula. If you did wish to integrate it, it’s just a matter of replacing the double quotes with single quotes in the XPATH, and then quoting that back into the formula.

    ie:
    //*[@id=’quote-summary’]/div[2]/table/tbody/tr[6]/td[2]
    into
    =split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,D5),”//*[@id=’quote-summary’]/div[2]/table/tbody/tr[6]/td[2]”),”()”)

    Cheers, and thanks again

    Reply
  3. Hello!

    First of all, thank you for this information, it is really useful! I am from Canada as well!

    I have been trying to get the dividend information from Yahoo Finance with the formulas that you have provided but I keep getting error. Could you confirm that the formulas have not changed?

    Cheers,

    Reply
  4. Tawcan, Great spreadsheet!
    Do you think if we download/convert this from Google sheets to Excel that would resolve or get around the Google import limit?

    Reply
  5. Hi,
    People from 2020 who are facing problem with IMPORTXML, can use IMPORTFROMWEB instead.
    You need to download this addon from the addon tab.

    =split(IMPORTFROMWEB(concatenate(“https://finance.yahoo.com/quote/”,D6),”//*[@id=’quote-summary’]/div[2]/table/tbody/tr[6]/td[2]”),”()”)

    It’s working fine for me.

    Reply
    • Hi there, I am getting an “#ALL_SELECTORS_NOT_VALID” error message returned when using
      =split(IMPORTFROMWEB(concatenate(“https://finance.yahoo.com/quote/”,A4),”//*[@id=’quote-summary’]/div[2]/table/tbody/tr[6]/td[2]”),”()”)
      I tried re-generating the X-path without success…

      Reply
    • Not all of us are as computer literate as you are. I can find no such item in the addons tab on the spreadsheet. I use a Mac if that makes a difference. Lets try the step1-2-3 method in plain english so I can figure out what you are saying and make the corrections. BTW, I have no idea of what the X path is and don’t plan on becoming a programmer to find out. So again – plain and simple explainations would be very helpful to the non-computer literate. Thank you

      Reply
  6. Even by just copying your example spreadsheet I get the #ALL_SELECTORS_NOT_VALID result from all tickers. Can you test it yourself at the current state of your sheet and Yahoo to see if it still works on your end and suggest a solution please?

    Reply
      • Yes Sir I copied your template into my own sheet, made sure I installed the add-on and so far all it gives me while loading the web data from Yahoo is that error message. Not just in your copied document but also when I implement the same feature in my own sheet.
        Do I need to be a Premium member of Yahoo Finance to be allowed to run that specific query?

        Reply
        • Hi,

          You don’t need to be a premium member of Yahoo Finance. Does the copied template work for you before you copy things into your own sheet? It’s possible you don’t have the Xpath set up properly.

          Reply
          • Thanks for the upgrade move to Search The Web as it resolves almost all of the issues. My only outstanding issue is that neither the old or new method using Search the Web work for General Dynamics (GD). Ever since I purchase some shares and added them to the spreadsheet over 1 year ago, it fails to return a dividend yield. Everything else works such as high price, low price, Beta, PE ratio, etc.. just not the yield. Any idea what this might be and why specific to GD ?

          • Hello

            I have the same problem with Ford and GM. All other stocks, etc show up except those 2 where I get NA for dividends, yield and the other parameters associated with dividends. It used to work find with the old formula, but now even that does not work for these two.

            BTW, I finally figured out how to change the formula – The add on had to be searched for as it was not on the first drop down screen – and just replacing IMPORTXML with the newer formula got everything working again (with the exception above). It would have been nice for someone to explain in plain English those steps instead of talking about “X path” and other functions of which, I suspect, a lot of us have no understanding.

          • Hi Jim,

            XPath is simply the location of the information on a certain website. You can think of it as the “address” for the info you’re looking for.

  7. Hi Bob,

    I have been using your formula using the add-on ImportFromWeb for my own Google Sheets and while the formula works flawlessly, I just want to point out that Yahoo! Finance actually shows different Dividend Yield for ETFs than its actual one. For example, as of 26 August 2020, Seeking Alpha & StockNews.com shows that VTI has 1.68% yield ($2.90) but Yahoo! actually shows 1.85% ($1.04).

    I am not an experienced investor, so that may be a reason to this that I did not think of, but I am looking into this. What do you think?

    Reply
  8. I changed the formula to fromtheweb but in the coloum it says “#Activate” any idea why this could be. I also tired to redownload ur sheet and in that spreadsheet it says #activate as well in the Div/share coloum

    Reply
  9. Since last week i have been getting” “#MONTHLY_QUOTA_REACHED” under the Div/Share column. Any idea how to fix this?
    Thanks

    Reply
  10. I think I noticed a calculation issue: Check out the Div/Sh value for an ETF, like cell M28 for VXC. It appears that value is being read from the Beta value on Yahoo Finance. Last previous year of dividends per share for VXC was more like 0.682. As an alternative for these ETF cases, I guess you could just multiple the yield% against the current price?

    Reply
  11. I’m having a problem with the BNS.TO Stock. Across you spread sheet it still appears as either NA or REF. I have changed in some cells where I found applicable to the TSE:BNS or BNS.TO. I’m confused as to why this particular stock ticker is doing this. Thanks

    Reply
      • Thanks for the quick response. Let me give a try. One more question, how do we write the logic to consider DRIP. In the template we have option to select Drip YES/No. How is this applied to the dividend table or to consider changes due to reinvestment of dividend.

        Reply
        • Sorry I don’t quite understand your question. The dividend table tallies up the amount of dividends that you received. If you get $50 in dividends, that’s $50 deposited in your account. If you DRIP and reinvest $45 out of the $50, you still “received” $50 worth of dividends. Hope this makes sense.

          Reply
          • My apologies that i didn’t frame the question clearly. Question was primarily on the DRIP column you have in the excel sheet where we can select Yes or No. i was wondering whether any rules or code written in the sheet to do something based on what i select their(YES/No) or it is just dummy. Is sheet doing Something like if i select DRIP Yes for a stock then the sheet will automatically add the dividend amount to the total invested on that stock..
            Thanks for the patience. I am not a pro excel person.

          • It’s just to indicate whether you’re DRIPing or not. There’s no rules set in the spreadsheet to add the dividend amount to the total invested on that stock.

  12. Thanks for the advice on the spreadsheet. I used the ImportfromWeb function, but it maxes out at a certain # of calculations / month and then starts asking you to pay for it, otherwise it returns error messages.

    I found this pulls yield and dividend info for individual stocks on Google Sheets with no plugins needed:-

    =SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML(“https://finance.yahoo.com/quote/AQN.TO?p=b7&.tsrc=fin-srch”,”table”,2),6,2),”(“),1,2),”)”)

    Just change AQN.TO to whatever stock you are looking at.

    For funds (returns yield only, but you can then auto-calculate $ amounts easily enough):-

    =SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML(“https://finance.yahoo.com/quote/XEI.TO?p=b7&.tsrc=fin-srch”,”table”,2),4,2),”(“),1),”)”)

    Just change XEI.TO to whatever fund you are looking at.

    Reply
    • Yup, the ImportFromWeb function has a monthly limit so it can get a bit restrictive. I did find ImportHTML and ImportXML a bit limited previously but may have to switch back to these functions so the spreadsheet doesn’t run into the monthly limit.

      Reply
  13. Hello.
    I put this formula:
    =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,D2),”table”,2),6,2),” “)

    Where D2 is PSA, but it doesn’t seem to work. I keep getting “formula parse error”

    Reply
    • Odd I have no problem…. formula used:

      =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,D5),”table”,2),6,2),” “)

      D5 = PSA

      You may want to double check the quotations to make sure they’re actual ” not some weird quotations.

      Reply
      • Very weird.. But later I tried to REDO the formula and what got it working was: =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”; D2); “table”; 2); 6; 2); ” “)

        Reply
          • Hi Tawcan,

            first of all awesome Work!

            Apparently the semicolon has something to do with the location. I live in Germany and for me only this command works:

            =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”;C5); “table”;2);6;2);” “) Result: 2.21 (3.82%)

            Unfortunately when I want to calculate my dividend with e.g.
            Number of shares X 2.21

            I get the error message: Parameter 2 must be a numerical value, but this is a text value and cannot be forced to a numerical value.

            Do you have an idea how to fix this problem?

            Greetings from Germany

  14. would you please so kind to post just one clear link just led to a Real google spreadsheet that has a cell to fetch dividend pay amount? I have been on your site clicked all the links(in blue) you posted without getting to the real one. very frustrating.

    Reply
  15. I used the function: =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,B79),”table”,2),6,2),” “)

    In my case, B79 refers to symbol BSV (a bond ETF). The formula returns a value of 0.40. This appears to be the ETF’s beta per Yahoo Quote page and not the most recent dividend amount, which should be $0.102

    Any ideas? Thanks!

    Reply
      • Thank you! That change retrieves the TTM distribution yield of 1.90% and I see you use that to calculate the annual div $/share in your template.

        Unfortunately for me, bond ETF distributions continue to decline due to low rates, so projecting a forward dividend $ is super challenging. I guess I could use the SEC 30-day yield as an approximation, knowing the true distribution yield will probably be higher. The difference between the distribution yield and SEC yield is dramatic! For BSV the difference is 80%, while a longer duration bond ETF like IUSB, is “only” 42%. I might average the current TTM yield with the SEC yield to approximate a go forward rate. Even though it won’t be accurate, at least it will be more accurate than the current rate.

        Reply
  16. Hey Bob,

    The new sheet looks very clean! nice improvements, one questions when reviewing it the column O that you hid, when opened only gas a value in it, seems like the formula is missing? or is it just me? If it is missing what would be the formula to pull the yield% I’ve tried to make it work but the only method that seem to work for me in directly inputting the % (ie 5.05). I’m no excel guru. can you help me out? Thanks

    Reply
  17. I think Yahoo Finance may have adjusted the way they report from their site. The current formula of =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,D28),”table”,2),4,2),” “) pulls the data for EPS so for BAC stock the yield % is listed as 187%. When change formula to =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,D28),”table”,2),6,2),” “) to reference the Forward Dividend & Yield it comes with a #REF error. Maybe because it is listed as 0.72 (2.28%)? Anyone else getting this error or have a solution?

    Reply
  18. I’ve been using your ImportXML method for quite a while now with no issues, but I changed to the new ImportHTML method today just in case.

    Thank you for keeping this up to date.

    Reply

Leave a Comment

 

This site uses Akismet to reduce spam. Learn how your comment data is processed.