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.
Jun 2023 Update: Recently .UN tickers stopped working with Google Finance. I’ve found ways to workaround this. Scroll below to find out how.
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:
- Dividend Investing FAQ (Mostly for Canadians but works for other countries too).
- Dividend & Index Investing FAQ.
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
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.
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:
- Click on Add-ons and search for “ImportFromWeb”
- Install “ImportFromWeb” Add On
- Once it’s installed, you can replace “ImportXML” to “ImportFromWeb” in the formula
- Div & yields info should show up without any issues
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 pointing to the location of the table directly.
For those of you that are coders, you’ll notice that I’m still referencing the exact place in the code but using the HTML format rather than the Xpath format.
Jun 2023 Update: Recently, .UN tickers (FTS.TO too) stopped working with Google Finance. You can try using the following formula to pull price instead:
=INDEX(importhtml(https://www.morningstar.com/stocks/xtse/rei.un/quote”,”table”,1),2,2)
Change “rei.un” to the desired ticker Alternatively, you can also use this formula
Or this formula below:
=INDEX(importhtml(https://ycharts.com/companies/DIR.UN.TO/price,”table”,1),2,5)
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.
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.
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. 🙂
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, top US dividend ETFs, and other ETFs to own. You can check out these pages.
- Best Canadian dividend stocks
- Best Canadian monthly dividend stocks
- Tops Canadian dividend ETFs
- Top US dividend ETFs
- Canadian Dividend Calendar
- Best Canadian utility stocks
- 6 Best Canadian Bank ETFs
- All-in-one ETFs comparision
- All Equity ETFs comparision
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!
Hi Bob,
LOVE this Google sheet and I’ve been using it for years!! However I still can’t get the following tickers to display a price lately: GRT.UN, BEP.UN, BIP.UN.
I’ve tried both of these suggestions mentioned in your post but they still give me ERROR. What am I doing wrong?
=INDEX(importhtml(https://ycharts.com/companies/grt.UN.TO/price,”table”,1),2,5)
=INDEX(importhtml(https://www.morningstar.com/stocks/xtse/grt.un/quote”,”table”,1),2,2)
Hi Marilyne,
Odd, these work for me on my spreadsheet. It could be that importhtml is having some access issue from time to time.
I guess im stupid but I cant even populate the ticker for Air Canada there should be like a tutorial on You Tube or something
Change the formula ticker to AC.TO.
Hi Bob,
I have been using this formula to fetch Ex-dividend date =index(importhtml(Concatenate(“https://ca.finance.yahoo.com/quote/”,B4),”table”,2),7,2) without issue for sometime, then out of the blue it stopped working.
Now trying to do a different way =IMPORTXML(“https://ca.finance.yahoo.com/quote/”&B3,”/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/div/div[2]/div[2]/table/tbody/tr[7]/td[1]/span”) and it still doesn’t work.
Not sure why there has been various problems with scraping data from Google sheets these days, is there another way that does work?
Thanks
Yea me neither, it has something to do with Google sheet’s importxml/importhtml function not very reliable. 🙁
I’m pretty sure yahoo is blocking it. I’m able to use the same formula on other sites with success
Yea I noticed that too but when pulling from other sites, it doesn’t consistently fresh, unfortunately.
Hello,
It seems that I’m not able to pull data from yahoo finance using import html or import xml. Are there any other free alternatives? I used to use IEX cloud which was great but now they charge money. I can use finvz, but I’m looking for the dividend pay date.
Hi Cameron,
It has something to do with import html/xml function… for some reason it’s not very consistent. It has nothing to do with Yahoo Finance.
Thanks for getting back to me. What is your current solution for google sheets then? Are you still using import html or import xml to get the dividend data into google sheets from yahoo finance?
Thank you!
Yes I still am but if you look at the template again I have done some minor update in terms of formula for pricing.
Love this! Noticed the Yahoo data isn’t pulling yields anymore, and Google Finance is no longer supporting Canadian income trust tickers. Am I the only one with errors?
Thanks Mick. There’s been a bunch errors with Google Finance the last few weeks. Hopefully Google will sort this out quickly.
We for me. This happened for several weeks few months ago then resolved. Any other recommended reliable portfolio tracking software? I’d be interested in hearing experiences.
I figured out a fix via ChatGPT- seems to work 95% of the time, sometimes throws and ERROR, but seems to resolve within a few hours.
See my shared sheet: https://docs.google.com/spreadsheets/d/1hsr-fJr9_2H0oL4cwCib6mAG7m94k-pENDfdKuacKE4/edit?usp=sharing
You also have to add this script under Extensions > App Script
You can cut and paste this:
function SAMPLE(url) {
const res = UrlFetchApp.fetch(url).getContentText().match(/DIVIDEND_AND_YIELD-value.+?>(.+?) 1 ? res[1] : “No value”;
}
Awesome! Thanks for sharing!
I get a syntax error now.
Syntax error: SyntaxError: Invalid regular expression: missing / line: 2 file: Sample.gs
Nevermind – pasting this worked:
function SAMPLE(url) {
const res = UrlFetchApp.fetch(url).getContentText().match(/DIVIDEND_AND_YIELD-value.+?>(.+?) 1 ? res[1] : “No value”;
}
Hello,
I’m trying the code but getting the same syntax error. It seems identical to the code posted above so wasn’t sure what the change was.
Thank you!
For ETFs, need to modify the function slightly (added a second function called ETF):
function ETF(url) {
const res = UrlFetchApp.fetch(url).getContentText().match(/YIELD-value.+?>(.+?) 1 ? res[1] : “No value”;
}
Then to return the yield of an ETF (in this case, XEQT), put this in the sheets cell:
=ETF(“https://ca.finance.yahoo.com/quote/xeqt.to”)
Slightly different format but we can play around with it.
Can also play around with this code to get the live prices from Yahoo Finance for our Canadian Income Trusts that Google no longer supports 🙂
Yes, the Income Trusts stopped being recognized. what a pain
Also TSE:FTS has stopped working recently
I reported to Google. Let’s see how long it takes. All TSX listed income trusts.
btw if you change the formula in column N of the Dividend Portfolio sheet to:
=ArrayFormula(SPLIT(INDEX(IMPORTHTML(concatenate(“https://ca.finance.yahoo.com/quote/”,D5),”table”,2),6,2),” “)*{1,-1})
you can make column O have a positive yield and not have to worry about hiding and having the extra column P with the corrected version of it
Thanks for pointing that out.
sure thing – I think the reason it comes in negative is because the web site has the yield in parenthesis which often denotes negative numbers in accounting/spreadsheets… pretty annoying though
Wow, what a tremendous amount of work you’ve done here Bob. There are not enough thanks, you really are educating people. You should be very proud of your work and your contribution to all the people who are feeling like they’re floating around the investing space with no direction. It can be very overwhelming and, quite honestly, downright scary, but it’s people like you that steer the ship.
Thank you for your work on these spreadsheets and I will be donating what I can.
With this, the result is OK
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,”AAPL”),”table”,2),6,2),” “)
But when I changed to
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,”TLKM.JK”),”table”,2),6,2),” “)
=> Loading..
Why ? I only changed AAPL with TLKM.JK
Try changing it back to AAPL and see what happens. Sometimes when you force the formula to retrieve the data again it works. There’s some sort of intermittent issue with the Google functions.
hi Tawcan, great write-up. Do you actually have a page showing your portfolio and the income it generates? I started in 2020 and am now at 475,000 USD. Yielding about 3%. My best month was May with approx. 3,000 USD. Plan to invest another 200,000 USD before the end of the year. Keep up the good work, your blog is surely an inspiration to me! Cheers, Noah
Hi Noah, please see here – https://www.tawcan.com/dividends/
Hi Bob,
Thanks for all that you do and post – it’s been a great help!
Up until recently, I found that 2 weeks ago, the Div and Div Yield formulas worked very nicely. Today, I see all the cells showing #N/A with an error, for example, saying “Could not fetch URL: https://ca.finance.yahoo.com/quote/RY.TO”
Could you let us know if there’s an updated formula? I tried using parts of the formula and can import table 1 data from the URL but not table 2 data, which used to have the Dividend info.
Thanks for your help!
Ran
Hi Ran,
This #N/A issue occurs from time to time due to Google not being able to fetch data from Yahoo. There’s nothing wrong with the formula.
Is there a suggested work around? I was hoping to pickup some dividend paying stocks that I’ve been tracking in my spreadsheet for awhile and wanted to use a filter based on Dividend Yield – was hoping to avoid having to pull up the Div Yields manually – Given all the #N/As, I may be forced to, unless you know another way to fetch the data.
You can try changing the URL from https to http or vice versa.
Today I changed the URL from https to http and it works!
I tried changing and it worked for a day, then stopped working. I would change it back; some worked, and some didn’t.
Yes it worked for one day. Had to change it back to https.
Tried both ways, http and https, and continue to get sporadic N/A on Div/Sh column.
I love this spreadsheet, however this issue is frequent and making the sheet not reliable. Is anyone else having this problem?
This has to do with Google functions, not much I can do, unfortunately. What you can do is create multiple columns, one you manually enter the dividend info, and the other columns you pull the data using the formula. Then use the if function to determine which column to pull the info from.
Where would I enter various dates of purchase and sales?
It’s not part of the spreadsheet template but you can easily add these yourself by creating new columns.
Just wondering if anyone has come up with a solution for the ERROR, Resource at URL not Found…? My only recourse has been manual entry for a dozen or so equities. Thanks in advance.
I haven’t had much luck with that other than trying to switch between https and HTTP.
Thanks for your reply.
I have noted one consistency between the equities that are returning the error. They seem to be associated with NASDAQ or NASDAQGS listings. I have tried different suffixes, but no success yet. Could the table reference change with these equities?
BTW, thank you for sharing your work. I will try to help out in the near future.
Hi Bob,
Do you have a formula to pull ex-dividend dates in Google sheets?
Thanks
The Xpath is //*[@id=”quote-summary”]/div[2]/table/tbody/tr[7]/td[2]/span for ex-dividend dates.
Hi Bob,
Thanks for continued support.
Can you recommend any video tutorials on learning how to use xpath in googlesheets as the importhtml whether using http and https no longer works in my sheet 🙁 ?
Hi Ran,
There are a lot of video tutorials available already. Here’s one you might want to take a look – https://www.youtube.com/watch?v=pwZ44kAeiOo&ab_channel=LearnGoogleSpreadsheets
https://ca.finance.yahoo.com/ does with the symbol for msft.ne
https://ca.finance.yahoo.com/quote/MSFT.NE?p=MSFT.NE
You’ll notice that all the dividend info is N/A.
How do I fix GoogleFinance for Candian CDR stocks like aapl, nflx, amzn, goog and so on? Thanks and where can I find the spreedsheet?
You just change the symbols.
For example The symbol is aapl.ne …
If you’re looking for Apple, Netflix and other US listed stocks it would just be appl, nflx, amzn, goog, etc. There are examples of US listed stocks in the spreadsheet.
no, these are Canadian CDR stocks provided by CIBC and traded on NEO trade exchange …….. https://cdr.cibc.com/#/cdrDirectory. Please provide me the exact command. Thanks
Sorry, won’t be able to pull those since Yahoo Finance doesn’t list any of those.
=IMPORTXML(“https://ca.finance.yahoo.com/quote/MSFT.NE?p=MSFT.NE”, “//*[@id=’quote-header-info’]/div[3]/div[1]/div/fin-streamer[1]”)
Hi,
Thank you so much for all the work you put into this! Super useful info!
I’ve had my portfolio tracking spreadsheet set up for a while now and have had an issue with pulling dividend info for certain securities for months and just can’t seem to figure out what is causing it. So I thought maybe you’d be able to help.
I use the same formula you do. Directly copy/pasted from one of my ‘working’ cells it is: =SPLIT(INDEX(IMPORTHTML(concatenate(“http://finance.yahoo.com/quote/”,$A6),”table”,2),6,2),” “) with A6 being OKE.
However, sometimes, and the instances seem very arbitrary, it simply doesn’t work. Copy/pasted from one of my non-functioning cells is: =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,$A2),”table”,2),6,2),” “) with A2 referencing BANX. On this cell, I get the error “Resource at URL not found”.
I have checked and rechecked the page source to make sure it isn’t a different table on the yahoo site for the tickers that the formula doesn’t work for. And besides the ticker symbol and the specific cell being referenced, the formulas are literally just ‘pulled-down’ from one row to the next, so they are identical.
For a few months now, whenever I have had this issue with a certain ticker I just enter the dividend yield by hand. However, I am trying to share my spreadsheet with friends/family for their own trading journies and want it to be able to work for them without the extra steps.
If you happen to have any ideas/suggestions on why this would be happening and steps to try and correct it, they would be greatly appreciated. I feel like I have tried everything at this point :/
Again, thanks for the work you have put into this site! Wishing you well!
Replying because I feel like I didn’t explain this well; it is very clealrly the specific ticker symbols and/or my referencing of them that is the issue when it causes the function not to work. If I type A3 into the exact function that isn’t working with A2, it works immedietly. And putting https://finance.yahoo.com/quote/banx into the search bar takes me to the webpage, so it does exist at that address.
So the issue is happening somewhere between the symbol I am trying to pull data for and Google Sheets’ ability to see that data/table on the Yahoo page. But again, it is only happening for certain, random tickers.
I hope this makes sense
Hi I have had the same problem. Again only with some of the tickers. After playing around with the formula I discovered that by referencing directly to the ticker, it will pull the dividend.
example:
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://ca.finance.yahoo.com/quote/”,D13),”table”,2),6,2),” “)
Changes to:
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://ca.finance.yahoo.com/quote/”,”NA.TO”),”table”,2),6,2),” “)
Thank you. For some reason errors happens from time to time with ImportHTML and ImportXHML functions. I think it’s a conflict between Google function and Yahoo finance.
Hi Tawcan,
Have you tried pulling the same Dividend & Yield info for Asian stocks (more specifically stocks listed in HK). I tried pulling it from stock 0001.HK and 0002.HK using =index(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,”0001.HK”),”TABLE”,2),6,2) , but receive a #N/A (resource at URL not found). Do you know of a possible fix?
J
Hi James,
Haven’t tried pulling dividend and yield info for Asian stocks but some readers have pointed out that it doesn’t work precisely for some reason. Not exactly sure why not.
Thanks for this. Have noticed I needed to change some formulas for Google that have =GoogleFinance(“TSE:SU”,”PRICE”) instead of
=GoogleFinance(C27,”PRICE”) where C27 is the example field location of a stock.
Also, wondering why “cost basis” is a figure that one must enter, as my brokerage already provides cost/share so by multiplying that by the number of shares bought equals the cost basis.
The cost basis is there for you to enter as you wish. If you want to pull that from your brokerage, you’re free to do that.
Thank you so much for this, working perfectly for my US stocks.
Could you perhaps assist me with getting dividend data from South African stocks? I have been unable to get it working.
Been having difficulties pulling data other than US and Canadian stocks. You may want to try changing the .com to the name associated with South Africa to give that a try.
Yes, much better now!
Goes to show that the formula works. 🙂
Seems to be working again as of today morning!
Yea!
Same here
Yah, I think the problem is the yahoo feed – whether I use importfromweb, importhtml or importxml I get the same error: “Could not fetch url: https://ca.finance.yahoo.com/quote/BMO.TO”
Not sure why that is but this works:
=IMPORTXML(“https://www.google.com/finance/quote/BMO:TSE”, “//*[@id=’yDmH0d’]/c-wiz/div/div[3]/main/div[3]/div[2]/div[1]/div[7]/div[2]”)
Gives you the yield, and you can separately get the price with:
=GoogleFinance(“TSE:BMO”,”PRICE”)
Multiplying one by the other gives you the dividend amount…
That string doesn’t seem to be working here… Maybe the table changed on yahoo finance ??
I’m trying out the string using Google Finance but not having any luck. 🙁
Not sure if your spreadsheet is still working but even with ca.finance.yahoo.com it now returns #N/A (Could not fetch url: https://ca.finance.yahoo.com/quote/BMO.TO), having worked fine for months.
I tried downloading your template to see if it works but it does the same thing.
Just wondering if yours is working (in which case it could be some setting I have on Google Sheets) or if yours is also suffering…
Thanks!
You can give ImportFromWeb function a try. There’s a monthly data pull limit though…
It doesn’t seem to work here :/ I see N/A for PE and Div/Sh. On mouse over I see the error and it says it can’t get the URL which is working when I copy/paste in my browser…
Any idea?
This has been an on-going problem with these ImportXML/ImportHTML functions. Google and Yahoo from time to time don’t like each other. 🙂
You can always try using ImportFromWeb add-on.
Just downloaded the spreadsheet a few days ago. The spreadsheet works until a couple of days ago where I am getting an error message under PE and Div/ columns “Could not fetch url: https://ca.finance.yahoo.com/quote/BNS.TO“, in this case for BNS. I tried refreshing the Google Sheet but still got the same errors. Anyone else having the same issues?
It may has something to do with Yahoo Finance. The function works but the data pulling may not be 100% reliable, unfortunately.
the way around it for me was to have a column with the exchange name and the ticker without .TO; the formula for name was edited as follows: =GOOGLEFINANCE(F2&”:”&D2, “name”); and for PE used =iferror(GOOGLEFINANCE(F2&”:”&D2, “pe”), “”).
For Div per share, I used: =IFERROR(IF(A2=”ETFs”, J2*INDEX(IMPORTHTML(concatenate(IF(OR(F2=”TSE”,F2=”CVE”),”https://ca.finance.yahoo.com/quote/”, “https://finance.yahoo.com/quote/”),IF(OR(F2=”TSE”,F2=”CVE”),SUBSTITUTE(D2,”.”,”-“,1)&”.TO”,SUBSTITUTE(D2,”.”,”-“,1))),”table”,2),4,2),INDEX(SPLIT(INDEX(IMPORTHTML(concatenate(IF(OR(F2=”TSE”,F2=”CVE”),”https://ca.finance.yahoo.com/quote/”, “https://finance.yahoo.com/quote/”),IF(OR(F2=”TSE”,F2=”CVE”),SUBSTITUTE(D2,”.”,”-“,1)&”.TO”,SUBSTITUTE(D2,”.”,”-“,1))),”table”,2),6,2),” “),1)), 0)
where F2=Exchange and D2 = Ticker.
This worked much better, however, the formula for that one doesn’t always get pulled fully, so I can end up with zeros there on some days. I generally just close my spreadsheet and try again later, OR I copy and paste it on another blank document.
It doesn’t work consistently but it’s been better than everything else I have tried.
I hope this helps
Wow, that Div per share is pretty complex..I am no Google Sheet expert so I am just going to enter the Div per share manually for now since it doesn’t change that much 🙂
Hi,
I have the same error message for both USD and CAD.
This is a new problem, it used to work well, sadly!
Curious to see if there’s a fix or it’s a “Yahoo finance” problem…
Thanks.
I just discovered this today and am excited to use it! I tried entering an American mutual fund ticker symbol and get error messages for P/E, DIV, Yield and Div Est. Can this work for mutual funds?
The formula should work for American mutual funds, as long as they appear on Google Finance and Yahoo Finance.
fun times, lol! ok thanks for the tip. I have tried so many different spreadsheets and formulas, manual entry is probably the way to go.
Thanks for the great spreadsheet! I am having the same issue as most people, however I haven’t been able to fix it at all.
I used =SPLIT(INDEX(IMPORTHTML(concatenate(“https://ca.finance.yahoo.com/quote/”,D4),”table”,2),3,2),” “) on ticker TSE:BCE.
I tried using ImportXML, ImportFromWeb, removed the ca. for the yahoo url, change the ticker to BCE:TO or BCE itself. And I did those manipulations for every single stock, trying all possible combinations for each stock I have. None of them works, neither for the PE nor for the Div per share. I ended up using the GoogleFinance pe function, which works on a third of them and not on the others, and it’s not always for American stocks that it works.
I honestly can’t figure it out…any suggestion would be appreciated!
Yea I am not sure what’s going on lately, there are certain tickets just won’t work for some reason. Seems like some sort of backend issue. For those, I typically just manually type in the dividend & PE info.
Awesome! Thanks for sharing. And, thanks for the time and effort put in.
Hi Bob! Thanks so much for this awesomely helpful spreadsheet!!
I have a question: How did you pull the data for the hidden column O? I thought there would some sort of formula there to fetch the data from somewhere, but upon clicking the cell I found just a plain number. Where does column O fetch data from?
Column O is the populated from formula in column M. Yahoo Finance lists dividend info in dividend amount and yield %. The SPLIT function splits the two numbers and dumps them in two separate column. For some reason the numbers are showing up in negative, so I had to do an absolute conversion in column P.
Hope this helps!
Hi Bob
I see your spreadsheet template is suffering the same issue as mine is on Canadian stocks – most have #N/A and say “Resource at url not found” using IMPORTHTML. Is this a Google thing? I read about Google ‘throttling’ live stream demands more and more. Have you found any solutions?
Also IMPORTFROMWEB will not work anymore as their entire server burnt down 2 days ago 🙁
Ahh that’s too bad about ImportFromWeb.
I added a note in the template… for Canadian stocks, you may need to use ca.finance.yahoo.com rather than finance.yahoo.com.
Awesome, thanks, I literally just worked that out, and it does work!
Glad to hear!
Oh wow, that fixed all my Canadian stocks. Thanks for that information!
You’re very welcome.
Hi Tawcan,
Thanks for the reply.
It was working for some time, then one day started getting “n/A”, instead of numbers pulled from yahoo. i have been trying to pull data for many Canadian and US stocks. This time it was for “IPL.TO”.
Cheers
Abraham
Which formula are you using? ImportXML or ImportHTML?
If you’re getting N/A this has nothing to do with the spreadsheet, more has to do with Google spreadsheet and the function’s inability to pull the data from the source.
Hi Tawcan,
The spreadsheet is very useful, if we don’t have to manually enter the dividend. So I like it very much, but
it is now not working.
I had this working for sometime, now looks like it is not updating
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,B79),”table”,2),6,2),” “)
Any idea or solution to it?
Thank you and keep up the good work!
Abraham
Which ticker are you trying to pull the data from? ImportHTML has some limits so if it’s having issue pulling that’s caused by Google, not the spreadsheet.
Hi, this is a fantastic sheet however I do have an error coming up for the dividends but only related to the Canadian ones (I just downloaded your file and used ‘as is’. Do I need to change the formula at all to get the dividend information to pull for Canadian stocks? I have tried what you had, as well as changng the ticker TSX, .TO, TSE, etc.
Any suggestions and are they working for you?
Thanks
Not sure what error you’re having but for Canadian you may need to use TSE:ticker or ticker.TO. The post explains which format to use.
Thank you for developing and providing this spreadsheet. My wife, Hong, and I both are building dividends for our retirement income. I look forward to further exploring this spreadsheet.
Am I able to use and manually enter each transaction? I do not wish to have info updated/imported online. I do not worry about the hacking of information, but do not like Google or Yahoo. Also, having to enter transactions manually does force a certain level of “intimacy” regarding the information. All transactions are available and easily viewed through our brokerage accounts. Eventually I will change my email address also to get away from Google and Yahoo. Just a personal preference regarding information and tracking.
Again, thank you.
Yes you can enter things manually.
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.
You’re welcome.
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?
NVM I think I see what you did there for the yield %
🙂
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
Column O for some reason has the yield percentage as negative numbers. So I just did an absolute number conversion and dumped the numbers in Column P.
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!
If you look at the spreadsheet template you’ll see that for ETF you’d reference table 2,4,2 for dividend info.
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.
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.
Hi Linda,
The link is pretty clearly posted at the end of the article. It’s even highlighted in a teal box. 🙂
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”
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.
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); ” “)
Weird, the syntax should be commas, not semi columns. But glad to hear that it’s working for you.
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
You may need to format that particular column to “numbers” rather than text.
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.
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.
Hello
Thanks for sharing the detailed instructions. it helped a lot.
Do you have a different version of sheet that shows the growth over the years due to DRIP. Like how you showed here
https://www.tawcan.com/wp-content/uploads/2020/11/Tawcan-dividend-income-Oct-2020.png
https://www.tawcan.com/wp-content/uploads/2020/08/Tawcans-projected-dividend-income-Updated-Aug-200.png
if yes – could you please share or advise how to create these..
Hi,
The table is simply creating a summary table and the chart is through Google Spreadsheet chart option.
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.
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.
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.
I still can’t find a formulae to extract dividend to spreadsheet. Can somebody give me a working formula please?
It’s in the article under step 4. 🙂
This might also help:
https://www.tawcan.com/using-google-spreadsheet-dividend-investment/
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
How are you pulling the data? I’ve found that some stocks don’t seem to work no matter what symbol arrangement you do.
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?
Correct, you can do that.
Since last week i have been getting” “#MONTHLY_QUOTA_REACHED” under the Div/Share column. Any idea how to fix this?
Thanks
There’s a limit on ImportFromWeb function. You’re reaching the monthly quota, unfortunately.
thank you for your prompt response. do u know what the limit is, I suppose I shouldn’t look at the spreadsheet daily
I think the limit is 60? Can’t recall, sorry.
working now.
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
Did you install the ImportFromWeb add-on?
yup. its installed
Was also broken, had to hit re-activate plugin under Manage Addons.
Working after.
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?
This probably has to do with the fact that ETF distributions are not the same eahc time so the yield and amount can vary slightly.