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

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. 

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 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:

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

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.

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

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.

Final Words

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!

Subscribe to get more great content like this. Learn more about dividends & financial independence.

  Name:       Email:       

147 thoughts on “Step-By-Step Guide on How to Make a Dividend Portfolio Spreadsheet”

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

    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
    • 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
      • 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
        • 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
  2. 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
    • 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
  3. Tawcan, thank you for the tutorial. Having this kind of spreadsheet in detail makes it easy to track dividend portfolio.

    Reply
    • 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
  4. 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
    • 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
    • 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
  5. 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
    • 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
  6. Tawcan,

    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.

    -TDM

    Reply
  7. Hi Tawcan,

    Great instruction.

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

    =split(ImportXML(concatenate(“http://finance.google.com/finance?q=”,D2), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)

    It always show up as “Error Could not fetch url: http://finance.google.com/finance?q=TSE:TD“.

    Anyways, I’ll try other work arounds.

    Reply
    • 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
    • I also receive the “could not fetch URL” error, even when I switch between https and http. Is there another source to get the annual dividend per share?

      Reply
  8. 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
  9. 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
  10. 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
    • 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.

      Cheers,

      Jack

      Reply
  11. Serge,

    Hi Tawcan,

    I have discovered your very interesting blog yesterday as well as your dividend portfolio template which meet my own rquirements. I am just a novice in googlesheets and am sure to learn a lot thanks to your expertise.

    When I click on the titles in the panels on the right part of your article, such as “Monthly dividend stocks” for example, I am always redirected to an Amazon advertisement page and can’t read anything more. How could I overcome this please ?

    Congratulations for your fantastic job !

    Reply
  12. Thank you for sharing the Google sheet instructions. I hope to try this set-up very soon. In the meantime, I shared the link to your instructions with the Facebook group called Dividend Growth Investing. There are over 5,000 members in the group, with the vast majority in the U.S.

    Reply
  13. Thank you for your tips and shares. Is there any way to pull sector through any function either google or yahoo instead of manual entry?

    Reply
  14. Hi Tawcan,

    Thanks for sharing your google sheets. I have a very basic question. How do you purchase your own stocks without a financial advisor? What brokerage do you use and how do you avoid the fees that play into buying and selling or MERs? Thank You

    Reply
  15. Hi Tawcan
    When I load my spreadsheet (which was originally an Excel spreadsheet) and converted it to a Google sheet, I replaced my macro’s that were yahoo based (no longer free) with your formulas and queries for both dividend and dividend yield. The Import XML functions seem to work as when I open my sheet all of the 38 stocks that I own show a “… loading” for both dividend and dividend yield for upwards of 20-30 minutes before they are all displayed. When I downloaded you sample sheet, those functions load immediately. My question is, could you potentially tell me what I should be looking for that might be causing the dividend and dividend yield to load so slowly. Your comments would be appreciated. Your site is great. Thanks for allowing others to benefit from your knowledge.

    Reply
    • Hi Mike_T,

      That’s odd, Google seems to have limited the Import XML function a bit due to security reasons so you might get loading or N/A from time to time. I’ve been trying to figure out how to get past this but haven’t found a solution. ๐Ÿ™

      Reply
  16. Hi Tawcan,

    I had a bunch of people asking me about the “Loading…” and “N/A” issues as well. Definitely very annoying thing to happen. One of the ways that seems to sometimes help is to change the http to https and then change it back when that stops working. It has something to do with the cache not clearing on Google Sheets.

    However, I recently updated my dividend investing spreadsheet to include a different method that works even better. It uses the scripting language that Google has built into Sheets. There’s more information about how I did that on my site: http://www.twoinvesting.com/2017/10/more-details-about-the-new-dividend-stock-portfolio-spreadsheet/

    Scott

    Reply
  17. Hi Tawcan, thanks for the walkthrough of your chart, I was looking for a way to do the yield in a separate column, and lucked out when your site came up, very much appreciated!

    Reply
  18. This is a great walkthrough. A tip: for Canadian stocks, one can go “TSE:[TICKER]”.

    Question: is there any way to automate calculating the DRIP dividends? Especially a DRIP that gives a discount? I’ve setup an Excel sheet but I have to manually update it.

    Reply
  19. Thank you very much for this great spreadsheet. I follow your site closely. I have been inputting my portfolio into your spreadsheet and ran into a bit of a problem when it came to adding my USA stocks. For example, when I added BLK, I used USA currency (left side of table), added my book value (taken from brokerage) under the cost basis , which came up with the correct cost/share in USA dollars, however the profit and market value numbers are incorrect. Somewhere along the line it must not be reading the currency exchange properly, as when I add my Canadian stocks with the same method, my profit or loss is bang on to what my TD Waterhouse account shows. Is there a manual conversion that I need to complete? Any help would be great. Thank again for this great spreadsheet.

    Reply
  20. Thank you for the quick response. I have marked the stock US in this case and the exchange remains off. Maybe some more into might help solve my problem.

    I use TD Waterhouse. When I input my average cost per stock, TD Waterhouse uses CAD, which I have inputted into the spreadsheet under cost/share. I used the book value from TD Waterhouse, which also appears to be in CAD and inputted that into ‘cost basis’

    As an example, I inputted GOOG into the spreadsheet. TD Waterhouse has my average cost/share as $1,248 CAD and the cost per share as of Friday’s close was $1,111.90, hence the spreadsheet says I am down but given the exchange I am up more than 10%., which is not reflected on the spreadsheet. The cost basis or book value that I added to the table was in CAD, taken from TD Waterhouse. I think I am missing something that is straight forward but can’t figure it out. Any more help would be great.

    Reply
    • Hi Aaron,

      I think the problem is that you put average cost/share with CAD and the price is being pulled as USD. What you need to do is cost/share in USD for the spreadsheet to calculate the correct gain. Hope this helps.

      Reply
  21. Hey Bob,
    Great info on your website. My question is this… I am currently using your spreadsheet to rebalance my ETFs portfolio and a couple of my ETFs(IEFA, IEMG) are now not being updated by Google finance. I just get an “N/A” in the cells that track name and current price. I have tried adding the exchange prefix for the ETFs and it still does not work. Without that data I am not able to rebalance. Any workaround?

    Thanks
    Ian

    Reply
    • Looks like Googlefinance function is having some hiccups the last few days. TSE stocks don’t seem to return any price info. Hopefully Google will get there stuff together soon and fix this.

      Reply
  22. Hi Bob,
    Thanks for all the work you’ve put into this. Very glad I found your website through RFD.
    Could you help point me in the right direction? I’m having trouble pulling the div/share data from yahoo finance, it appears the xpath is pointing sometimes to other data such as 3y beta.
    not sure if i’ve entered something wrong, part of me suspects it’s because the quote pages have different fields depending on the ticker symbol,
    Wayne

    Reply
  23. didn’t change xpath – i think i figured out why I’m pulling weird numbers for some of my holdings. it looks like XPATH is pulling the correct div info for individual stocks, but for ETFs the 6th field is 3y beta. (XLF pulling 0.92)
    thanks again!

    Reply
  24. Hi
    My sheet worked well for a few days but now some dividend data is incorrect and others are OK with the same formula. AFL returns 43 104,00 for dividend (actualy it is 1.04$) and the yeald is OK at 2.33%.

    Yahoo changed some of the data format?

    Thank you.

    Reply
  25. Hi TAWCAN
    I just wanted to say thank you for posting a method for importing dividend amounts and yields . It is really helpful and very much appreciated .
    Best

    Reply
  26. Hi, This has been very helpful for me.

    Can you shed some light on how to alter the XPath so that I can collect a different data point?

    For example I would like to get the Payout Ratio value from the Statistics tab of a stock quote. I see your XPath is pointing to:

    //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]

    how could you modify this Xpath to collect Payout ratio from the statistics tab of a quote?
    For example…. Verizon (VZ) statistics are found here: https://finance.yahoo.com/quote/VZ/key-statistics?p=VZ

    I see the payout ratio under Dividiends & Splits. How would I identify the correct Xpath for that data point?

    Reply
    • The easiest way is to use FireFox and use the Web Developer tool and inspect the code of the page. Once you find the code, then right click Copy -> XPath, then you need to clean up the XPath a bit.

      For example, the payout ratio entire XPath would be:

      /html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/section/div[2]/div[2]/div/div[3]/table/tbody/tr[6]/td[2]

      But you need to clean it up. You also have to point the concatenate formula to the correct URL.

      It’s a bit of trial and error. ๐Ÿ™‚

      Reply
    • Hi Kimm,

      There are ways to do that but they all involve some sort of manual work. There are websites that have DGR numbers you can pull from but most of these sites require monthly payments.

      Reply
  27. Hi Bob,

    Great insight and information. I came across your page as I was searching for a dividend portfolio template to reference in excel. Would you happen to have a google spreadsheet to share?

    Reply
  28. Hi.

    Thanks for the spreadsheet. I primarily use the function to pick up dividend and yield info from XML parsing from Yahoo Finance. It works great for my stocks. Yet, it always returns N/A for stocks that pay out dividend “monthly”. I will give you some examples, tickers such as “DMO”, “ETJ”, “GOF”, and “IGD”. Can’t figure out why so. Will you take a look at that? Thanks.

    Reply
  29. I love your template and use it all the time!! Thank you so much.

    Did you notice that the price function is no longer working for Canadian stocks? For example GoogleFinance(RY.TO,”PRICE”) now returns #N/A. Does anyone know how to fix this issue?

    Reply
  30. Iโ€™m trying to figure out how to download price data on the following stock on the TSE: National Bank preferred share class S

    On Yahoo finance I use NA-PS.TO

    On google sheets it comes back with #N/A.

    Any ideas on what the format I should use ?

    Reply
  31. Love your spreadsheet, many thanks!!!

    I can’t get the dividend info for ETFs. I copied and pasted your link for ETFs from your notes under the cell B43 and referenced DIV/SH to this cell instead but it doesn’t work. If I leave your regular link, I get the DIV/SH for what looks like monthly dividends and I don’t get the Yield. (Eg: REM, SPYD.)
    For XEI.TO, I get the yearly dividend but not the yield.

    Can you help with this?

    Reply
  32. Your guide has been a great help in building my own spreadsheet for tracking our dividend portfolio. Unfortunately I am having the same problem as Mary in the above comment. The Xpath for equities seems to propagate the Beta for an ETF but if you use the Xpath you gave for ETFS you get: “Error Imported Xml content can not be parsed”. I tried messing about changing values in the equity Xpath to try and get to the yield data on Etfs but needless to say was mucking about with no idea. I’m not even sure where Dividend Rate might be listed for Etfs on Yahoo Finance as the summary page only lists Yield % and not Rate. I was thinking to try and get it to pull the Yield so I could then do a formula for etfs to back figure the Rate but then it seems like I would almost need to do Etfs separately and I couldn’t figure out how to get the Yield to pull anyway

    Reply
  33. As indicated by Michael, Iโ€™m getting the beta (3y monthly) of 0.92 in DIV/SH, nothing in Yield %

    Thank you again for your help. Will wait to see if you find something out for ETFs dividends

    Reply
  34. Hi Mary, Tawcan. I was playing around with the numbers at the end of the Xpath thinking if I could get the Yield% for Etfs then the Yield column could be a Yield%*Price formula to get the Yield (this would be the right math?). Of course while I vaguely understand the concept of what the import formula Tawcan gave us does randomly changing numbers to get it to do something else didnt work ๐Ÿ™‚ Even if it did I’d then want to separate my Etfs out so and add the totals back in

    Reply
    • Hi Michael & Mary,

      I haven’t forgot about you two about this. I’ll have to take a closer look at the Xpath for ETFs and see what happens. Unfortunately, I’m not accessible to a computer right now so a bit limited on my ability to check. Stay tuned please. ๐Ÿ™‚

      Reply
        • Hi Mary & Michael,

          I have confirmed that Xpath for ETFs is below:
          //*[@id=โ€quote-summaryโ€]/div[2]/table/tbody/tr[4]/td[2]

          What you may need to do in the spreadsheet are:
          1. Make sure the for the cell entry is % not $ as Yaoo Finance only provides % yield for ETFs
          2. Move the query in the yield % column

          I have updated the spreadsheet to provide an example of ETF.

          Reply
  35. Any tips on getting the fetching to be more consistent? It has gotten to the point that if i leave my spreadsheet open in a tab maybe once a week it will actually briefly load the Imported Dividend/Yield columns, most of the time it actually loads a few reads #N/A for the rest so the totals and chart wont compute.

    Reply
    • Unfortunately this is all due to Google and the ImportXML function. Not a whole lot I can control. I’m looking at more reliable ways. I suppose in the mean time you’d need to manually enter the dividend amounts. ๐Ÿ™

      Reply
      • Hi TAWCAN,

        FYI, my problem is solved (for now)! I just went back to your updated google sheet, copied your paths for both stocks and etfs over mines and everything works. I don’t know what broke in my spreadsheet but I will remember to do the same if the dividends stop populating again.

        Reply
        • I was lucky when I did it on my March 19 comment. Got it to work only in the middle of a sleepless night… โ˜น Fetching never works during the day…

          Reply
  36. Hi Bob:

    Like your spreadsheet as it is fairly non-complex (Don’t want to use the word simple as it works). Since I am retired I depend on my dividend payments and do track them. I have one portfolio where I use the dividends for current expenses and another portfolio I use for those stocks I wish to reinvest the dividends. Kind of like a safety valve.

    What would be nice would be a transaction page that would fill in the main portfolio page as I entered each transaction. I could figure out how to differentiate between dividends that I use and those I reinvest.

    Anything in the works like this?

    BTW, I have used another spreadsheet, but it became very complex with items I did not need and my idea is to always KISS.

    Jim

    Reply
  37. HI Tawcan,

    Thanks for the spreadhseet, it is wonderful, If I can get the dividends to download I will send money for sure. All I get is “loading” or “N/A” what can I do. i have tried everything, I have re copied the formula, re typed it, hard coded the stock code. I have US and Cdn stocks. Some stocks show up and others just say loading etc. I have 40 stocks that I track and I have added some new columns to your spreadhseet. The funny thing is it works for 2 days then stopped and has not worked for about 3 days now. Is this a google thing and I have to type them in for now or what could I be doing wrong? Happy to send it to you. Thank you in advance

    Reply
    • Hi Ian,

      As stated in the spreadsheet, the “loading” or “N/A” has to do with the ImportXML function and Google. Not much I can do. You can try changing http to https or vice versa to see if this addresses the issue.

      Reply
    • Hi Ian:

      Late reply, but after trying a number of different sites to capture my portfolios I have found they all have a problem with the dividend capture no matter where they get it from. This one from Tawcan seems a lot less troublesome then others that I have tried and it now has become my main portfolio tracker. It just is slow loading sometimes, other times the information is there when I open the spreadsheet. It just may be a factor of how much the requested site is being used. Right now, Nov 18, 2019, 12 noon in CA when I open the spreadsheet about 20% are filled in and the rest are either NA or loading. Evenually they all get filled in. But if you need the information ASAP just go to the NASDEQ website for the info on dividends. I am retired. so I have a different outlook on how I use the portfolio trackers including modifying and adding some other functions to the Tawcan spreadsheet to give me more of the information I like to view. Be happy to share with you.

      Reply
  38. 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
  39. 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
  40. 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
  41. 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
  42. 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
  43. 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.

  44. 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
  45. 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
  46. 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

Leave a Comment

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