Update:1 As per requests, I have created a Google Finance dividend portfolio template available to download. You can use the template for Google Finance Dividend.
Update 2: As of March 2018, Google Finance has changed its formatting. Therefore, the old formula to pull dividend & yield info from Google Finance no longer works.
I have updated the formula to pull dividend & yield info from Yahoo Finance instead.
Update 3: While ImportXML still works. It seems to get errors from time to time due to how the webpages are set up.
I have updated the Google Finance dividend portfolio template with a slightly revised function that’d to the exact same thing. This new function should be able to pull dividend info reliably. This should be very useful for any dividend investor.
Update 4: The ImportFromWeb add-on has a monthly pull limit so it’s not very reliable. I have decided to utilize ImportHTML function instead.
Getting started on dividend investing is easy. Once you have a few dividend paying stocks in your dividend portfolio it can get tricky to keep track of your cost basis and various information. This is where using spreadsheets come in very handy.
I love using spreadsheets when it comes to tracking various items, so it shouldn’t surprise you that I use a spreadsheet to track our dividend portfolio.
Thanks to Google Drive or more specifically Google Spreadsheet, tracking our dividend portfolio and dividend income using a dividend spreadsheet has made life a lot easier.
I’m no longer tied to one computer, I can update the spreadsheet anywhere, on a laptop or on my smartphone, as long as there’s an internet connection.
Hence, I have created a Google Finance dividend spreadsheet template.
Google Spreadsheet and Google Finance for tracking dividend portfolio & dividend income
Google Spreadsheet has a very useful function called “GOOGLEFINANCE” which allows you to fetch current and historical security information from Google Finance. The function supports a lot of syntaxes such as price, volume, PE ratio, and market cap. For more information on all the syntax supported you can take a look here.
My biggest complaint with GOOGLEFINANCE function is that it does not have any syntax for querying the most important information when it comes to dividend investing – dividend amount and dividend yield.
I have no idea why parameters like Google Finance dividend yield and Google Finance dividend amount aren’t part of the GOOGLEFIANCE function.
For the longest time, I was updating the dividend information manually. This was easy to keep track of when we had a small portfolio with only a few stocks. As the dividend portfolio size and number of positions increased, it became more tedious to keep track of all the dividend information.
For me, I like to automate the dividend spreadsheet as much as possible, so I’ve been looking for a way to track the dividend portfolio on Google Spreadsheet automatically.
Dividend Yield and Dividend Amount using Google Finance
After a bit of investigation, I found a way to automate the dividend amount and dividend yield on Google Spreadsheet. Unfortunately, it’s not quite the same as a simple parameter as Google Finance Dividend, Google Finance Dividend Yield, or Google Finance Google History.
The trick is to use ImportXML function. The function allows me to pull data from any various structured data types including XML, HTML, CSV, TVS, and RSS feeds. By using this function I can pull feeds from Google Finance directly and show dividend amount and dividend yield.
Old Formula:
=ImportXML(concatenate(“http://finance.google.com/finance?q=”,”NAME”), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”)
If I want to query dividend info for Apple (AAPL) the formula would look like this:
=ImportXML(concatenate(“http://finance.google.com/finance?q=”,”AAPL”), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”)
If I want to query a Canadian stock like Royal Bank I’d simply replace “AAPL” with “RY.TO”. If I want to query a REIT like RioCan, I’d replace “AAPL” with “REI.UN”. For REITs or income trust, there’s no need to populate “.TO” at the end. The formula would look like this:
Dividend Yield and Dividend Amount
Since Google has changed how Google Finance is structured, the old formula (below) no longer works (Boo!). We have to find another way to pull the dividend and yield information elsewhere. I searched the web and found that Yahoo Finance to be a reliable source to pull the dividend and yield data from.
We are still using the ImportXML function. For some reason, I couldn’t insert the XPath directory as part of the formula, so we need to add the Xpath in a separate cell then refer to it in the formula.
New Formula
In a separate cell copy //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]
Then use this
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,NAME), ‘XPath cell’),“()”)
Where NAME is the stock symbol and the ‘XPath cell” would be the cell your Xpath is in. For example, if your NAME cell is in B1 and Xpath cell is in C10, then the formula would look like
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,B1),$C$10),“()”)
Similar to Google Finance, Yahoo Finance puts Div/Yield info together.
If you query AAPL, the output will be 0.47/(1.96%).
The first number 0.47 corresponds to the dividend amount received each payment period, while the second number 1.96 corresponds to the current dividend yield percentage.
Since the dividend amount and dividend yield percentage are combined together, I used Split function to further split the ImportXML output.
A quick note is GOOGLEFINANCE and Yahoo Finance use different symbols for stocks with . in their symbols.
For example Rogers is RCI.B for Google Finanance but RIC-B.TO for Yahoo Finance. The easiest way is to manually enter the symbol in the ImportXML formula and remove the concatenate function. The formula would look like:
=split(IMPORTXML(“https://finance.yahoo.com/quote/RCI-B.TO”,$C$10),”()”)
Even Newer Formula
Since ImportXML does not pull the data consistently, I decided to utilize ImportHTML instead. So the “new” formula would look like the following:
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/RCI-B.TO”),”table”,2),6,2),” “)
Using Google Spreadsheet with dividend info to track our dividend portfolio
How do I use this information to track our dividend portfolio and dividend income?
I’m glad that you asked! Below is a sample tracker that I put together. I like to keep life easy and only track the important stuff. The dividend portfolio you see on this blog is a simplified version. Our personal spreadsheet is very close to the sample tracker that you see here.
Update: As per requests, I have created a Google spreadsheet dividend portfolio template available to download. Best of all, it’s free!
Here is the syntax I used for querying cell items:
To get the market value we use the following syntax
=GoogleFinance(B2,”PRICE”)*C2
For Div$ and Yield % columns I’m using the following syntax to split the XML imported data into two fields.
Old Formula
=split(ImportXML(concatenate(“http://finance.google.com/finance?q=”,B2), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)
New Formula
Xpath: //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”,B2), ‘XPath cell’),“()”)
With this dividend portfolio tracker, I’m able to track our dividend portfolio and dividend income very easily and able to get a quick summary of what’s going on. This is the beauty of Google spreadsheet!
Note: If you are trying to track ETFs, you need to use a different Xpath.
For ETFs, use Xpath: //*[@id=”quote-summary”]/div[2]/table/tbody/tr[4]/td[2]
Even Newer Formula
Rather than relying on Xpath and using ImportXML, I decided to use ImportHTML function for an easier data pulling. So the new formula is
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,B2),”table”,2),6,2),” “)
As you can see, rather than calling /div[2]/table/tbody/tr[6]/td[2], ImportHTML calls table 2, 6, 2 which is more or less the same as the Xpath just in a slightly different formate.
Similar, for ETF, you’d use the following formula and pointing to a different section of the table:
=SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,B2),”table”,2),4,2),” “)
Tracking monthly dividend income
For tracking monthly dividend I use a very simple spreadsheet like the one below. Usually, the dividend tracker is a separate sheet within the same spreadsheet document.
By having everything in the same document, I can add another column called Div Rx in the portfolio tracker and link the dividend total for each position.
There are many ways to track a dividend portfolio. I like to keep it simple and the above methods work for us. Automation makes tracking the dividend portfolio a breeze.
How do you track your portfolio? In case you want to download the template for free, please head over here.
Learning about dividend investing
In case you want to learn more about dividend investing, here are some articles you might 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.
Also check out the best Canadian dividend stocks where I hand picked some of the top Canadian dividend stocks that I think every Canadian dividend investor should hold in their portfolio.
Hi Tawcan,
Sorry that function in my previous email should have been =index(importhtml(“https://au.finance.yahoo.com/quote/GLN.AX/key-statistics?p=GLN.AX”,”table”,7),1,2), It did not need substitute for this data, I was using that for other data I was extracting.
Hi Ron,
I haven’t used ImportHTML function previously, only used ImportXML as I find that a little bit more reliable.
When it comes to ImportXML you need to look at the Xpath.
For example, the Path for PE ratio on Yahoo is…
/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[3]/td[2]
Which would shorten to something like…
//*[@id=”quote-summary”]/div[2]/table/tbody/tr[3]/td[2]
Hi Tawcan,
I find importxml more cumbersome to use, it is easier to use importhtml, when you use var i = 1; [].forEach.call(document.getElementsByTagName(“table”), function(x) { console.log(i++, x); }); in the console, to give you the table numbers. I guess it is a personal preference.
But either way do you know how to reference the relative cell with (D33) instead of GLN.AX, it should work the same regardless of whether you use either importxml or importhtml, no ? Do you know how to add the cell reference with importxml ?
Regards,
Ron.
Hi Tawcan,
How do you refer to a ticker in a cell when trying to get the PE value from Yahoo Finance via this command: https://au.finance.yahoo.com/quote/GLN/key-statistics?p=GLN. I want to replace the ticker code with the cell reference ie D33 which has the ticker code of GLN.AX. I tried using: https://au.finance.yahoo.com/quote/“&D33″/key-statistics?p=&D33. But it did not recognise this. Could you please advise me how I reference to cell D33, so I do not have to manually input the ticker for each stock.
Hi Ron,
For PE ratio you can just pull it from Google Finance using this formal:
GOOGLEFINANCE(ticker, “PE”)
Hi Tawcan,
Thanks for the reply. I guess I used the wrong example, I already know what I can get from Google Finance, but I want to use Yahoo finance, as Google finance does not provide all the data I want ie ROCE, ROA, Future ROE, PEG, Debt/Equity etc. For ROA as an example I can get the ROA data by using the command:
=substitute(index(importhtml(“https://au.finance.yahoo.com/quote/GLN.AX/key-statistics?p=GLN.AX”,”table”,7),1,2),”$”,””), which would display ROA -8.02% in the cell.
However, I would like to be able to replace the two GLN ticker codes (ie GLN.AX), in the above-mentioned function, with the cell reference of D33, where GLN.AX resides, so I do not have manually enter the ticker for each stock.
Hopefully this is much clearer, do you know how to replace the two ticker codes, with the relative cell reference of D33, do you know how to do this ?
Regards,
Ron
Hello Bob
Can you let me know how I can add or remove account types? Such as adding an RESP and removing the 401K, Roth etc…
Thanks!
SC
You can add or remove account types by disable the filters, then add the accounts you want, then turn on filters again.
Can we add a new sector in the dropdown list and automatically get the sector return in both table and chart?
Thanks
Greg
Yes, you can add a new sector in the dropdown. You just need to disable the selection then add the sector in the dropdown list.
Intrinio can help but their fees are not acceptable, as to me.
So far, I use Google spreadsheets + manual updates. Periodically, I send the feedback to Google about missing tickets. I don’t know why but they lose some from time to time.
I’m running into the same issue as Rio and interested in what you might be able to come up with for a fix.
Thanks so much for all the information you’ve provided!
Looks like it has something to do with ImportXML and Yahoo Finance. I’m trying to come up with a more reliable way to pull the data.
Hi, wondering if anyone has found a solution to the yahoofinance import issue. I’m getting a lot of n/a and need to constantly have to change the ticker suffix from .to to .To to TO, etc. And even then the info doesn’t load. I wonder if it because of too many “hits” per second. Maybe there’s an API to subscribe to or buy. Something I’m looking into.
Hi Pat,
I haven’t found anything that’s super reliable. There are other ways to pull data but they aren’t free.
IMPORTXML doesn’t work anymore for some symbols like BCE.TO (works for BNS.TO though) because the page is using javascript to load the data and IMPORTXML cannot scrape data that is in the DOM structure which javascript is loading directly into.
Thanks Richard.
Hi Bob,
For some reason this doesn’t seem to work anymore as of 5th March 2020!
Is it possible that Yahoo changed their path/code?
Can you please provide a fix? I very much enjoy using your spreadsheet.
Thanks
Yea I noticed that. The Xpath hasn’t changed. I’ll have to spend some time investigating the issue.
Thanks for your prompt reply Bob.
I’ve tried everything, but I can’t find a solution to working around this. I suppose Yahoo are trying to prevent people from scraping their website/data. Same with Google.
FWIW, the following seems to be working for me to pull yield …
=index(split(IMPORTXML(“https://finance.yahoo.com/quote/F”, “//*[@data-test=’DIVIDEND_AND_YIELD-value’]”),”()”),0,2)
I have the funny error after applying the formula: Array result was not expanded because it would overwrite data in the P20 (actually next cell on the right). Did you guys experience something like this?
Hi,
just discovered you website as I was looking to have dividend yield in a google spreadsheet.
But it doesn’t work
in A1 : //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]
A2 AAPL
B1 : =SPLIT(IMPORTXML(CONCATENATE(“https://finance.yahoo.com/quote/”, A2), A1),”()”)
But I have an #ERROR! message (formula parse error)
Thanks for you help
Hmm weird, it’s working for me in the original spreadsheet. Is the same formula working for other symbols?
Seems like a lot of these importXML type usage are getting errors. This way of pulling dividend/yield info from Yahoo Finance used to work last year but now, 2019, it pops an error most of the time. There were lotsa complains about this on google forums.
Is that formula working nowadays?
Because after reading the post I’m stuck and I’m not able to show the Dividend Yield from Yahoo Finance.
I have in A1: //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]
In A4: ETR:DAI
In A7: =SPLIT(IMPORTXML(CONCATENATE(“https://finance.yahoo.com/quote/”, A4), A1),”()”)
But no works 🙁
Does anybody know why?
Many thanks!
It’s because ETR:DAI is a ticker for Google Finance. It will work for returning the stock price.
Since the formula is pulling data from Yahoo Finance, you need to use the ticker for Yahoo Finance. In this case the ticker is DAI.DE.
I love this! Was wondering if you know the formula into Google sheets to import the Dividend Payout Ratio from Yahoo Finance? Think it sits under the “Statistics” tab. Thank you!
You can easily calculate the payout ratio by dividing EPS by dividend amount. No need to query from Yahoo Finance, the data is already available to you in the template. You just need to create an extra column and add your own formula.
Hi Kanh,
If you copy and paste directly from this website it uses the wrong quotation marks which causes errors. Try pasting the formula into Google Sheets then deleting every quotation mark and re-entering them. That should work as the formula is correct.
Brilliant!
It works now. This is so important and I cannot believe I missed it.
Thanks!
Glad to see that everything works now. 🙂
Brilliant, Ken! Thank you. I’ve spent an hour figuring out what I’m doing wrong and your comment on deleting the quotation marks and reapplying them solved it.
Note: Tawcan has already mentioned this, for ETFs use this XPath to get the yield in percentage: //*[@id=”quote-summary”]/div[2]/table/tbody/tr[4]/td[2]
If you have the Div/Share price column on the left of the yield % column you can use the “Split” command. You can also add the Xpath to the entire formula so you don’t need to have a separate cell. Here is the formula I used in the Div/Share column for Fortis:
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”, “FTS.TO”),”//*[@id=’quote-summary’]/div[2]/table/tbody/tr[6]/td[2]”),”()”)
Hope this helps. If not I know Tawcan will be able to help you out.
Thanks for replying. I copied your formula to a Google Sheets but got a error message saying “formula parse error”. I got the same message with the formula using two separate cells. I am not sure which part I did wrong.
Thank you for sharing. Unfortunately it doesn’t seem to be working for me. Would you mind take a look at my inputs and see if anything I did wrong?
I tried:
C32: //*[@id=ʺquote-summaryʺ]/div[2]/table/tbody/tr[6]/td[2]
C33: =split(IMPORTXML(“http://finance.yahoo.com/quote/RCI-B.TO”,$C$32),“()”)
Alright sounds good. Thanks for your help with this. I appreciate it.
That is why I was wondering where it was pulling these numbers from. Must have been from the wrong field since Div/Share isn’t even on the page.
Any ideas where I can pull the information from regarding VCN, XAW and ZAG Div/Share Prices?
If not I could just manually input the dividend amount and keep the frequency column. It’s so much nicer when it’s automatic though.
Hmm if this info isn’t available on Yahoo Finance page then I’m not sure where to pull them from. I suppose you can try pulling them from the ETF company’s websites but the XPath would be very different (i.e. Vanguard, iShare, etc).
I should have been more clear. I was referring to the Div/Share $ column data being incorrect. Not the Yield %.
The Yield % matches the Yahoo Finance website and is close to what the Fund website reports.
The Div/Share $ value is way off. Any ideas on how to correct this?
Sorry what did you mean? If you look at this page:
https://finance.yahoo.com/quote/VCN.TO
There’s no Div/Share $ info available.
Ok thanks. I was expecting it to work like the single stocks where you can enter it in one column and it fills in the next. It appears that with ETFs you have to enter both. Not a big deal.
I do have a question though. Where is it pulling this so called div/share value from because it is not accurate?
XAW is showing double then what it actually is. None of them are even close.
I’m not sure where Yahoo pulls this info. They could have used the last dividend amount to calculate the dividend yield percentage.
I was able to avoid using the separate XPath cell by changing your double quotes around quote-summary to single quotes.
The split does not work. However if I use the following: =split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”, “ZAG.TO”),”//*[@id=’quote-summary’]/div[2]/table/tbody/tr[4]/td[2]”),”()”) in it’s own cell then I get the correct Yield results.
You don’t need to split because for ETF’s there’s only yield and no div $ info in the same cell of the table.
Good morning,
I entered the following:
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”, “ZAG.TO”),”//*[@id=’quote-summary’]/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/div/div[2]/div[2]/table/tbody/tr[4]/td[1]”),”()”)
I received an imported content is empty error. Did I make a mistake in the formula?
Try this XPath
//*[@id=”quote-summary”]/div[2]/table/tbody/tr[4]/td[2]
I got it working when I point to the cell in the formula rather than having it incorporated in the formula.
Hello,
The formula worked perfectly for regular stocks. The Xpath was not required.
If I use the exact same formula for ETFs the Div/Share will show up correctly but the Yield % is blank. Any suggestions?
Formula Used:
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”, “ZAG.TO”),”//*[@id=’quote-summary’]/div[2]/table/tbody/tr[6]/td[2]”),”()”)
This is also the case for VCN.TO and XAW.TO.
Appreciate your work so far.
Thanks
Looks like for ETFs the Xpath location might be different.
Try
div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/div/div[2]/div[2]/table/tbody/tr[4]/td[1]
I got an error when I did a copy/paste from the site directly into the sheet. The double quotes from the HTML are a different ASCII code and Sheets doesn’t like it. I only noticed that when I cross referenced the copied text with your downloaded spreadsheet. Awesome work – thank you!
Yes the double quotes from the HTML are different than the quotes that Google Spreadsheet use. You need to correct this manually. 🙂
Thx for looking
Yeah again thats because I deleted my formula in the row “I” which is supposed to be the annual income =I4 X A4
The minute I put in the formula is when the error pops up and the dividend stops fetching.
Must be my sheet, very bizarre Error “Array result was not expanded because it would overwrite data in I4”
The column beside my dividend column is annual income which has the formula dividend cell x number of shares cell
If I delete the formula (dividend cell x number of shares ) than the dividend fetches properly but that column with the annual income displays 0.04 which I can not delete.
something is weird with my sheet I guess. Here’s a mock up one if you wanna have a look
https://docs.google.com/spreadsheets/d/1FRvuUdovboZUCCVI-5AJOnmuCEqa-DhrmizLNnlSw-E/edit?usp=sharing
Hmm I don’t see any error in the mock up.
I keep getting an error 🙁
What kind of error are you getting?
Anyone know answers to these:
1) How to pull dividend from Google?
2) Where the sector trend information is located on Google Finance?
3) Can this information be pilled from Yahoo, BarChart, or another site and used on Google Sheets?
4) Why Google mutilated their Finance section?
Google has changed Google Finance format. I’ll have to provide an update on how to pull the data.
I have updated the formula. You can take a look at the step-by-step template here
https://www.tawcan.com/step-step-guide-make-google-spreadsheet-dividend-portfolio-template/
To avoid the XPATH cell, you can change the double-quote around quote-summary to a single quote:
=split(IMPORTXML(concatenate(“https://finance.yahoo.com/quote/”, B7),”//*[@id=’quote-summary’]/div[2]/table/tbody/tr[6]/td[2]”),”()”)
Thanks for all your hard work! Love the blog!
Cool, will give that a try. For some reason, it didn’t work when I was testing the formula.
Added an s on the end of http
It fetched the info on some of the stocks but not all…Weird
What is the correct link? The one posted does not work.
The formula to pull in the dividend data does not seem to be working anymore. Is there a fix?
Hmm looks like Google Finance has changed. I need to spend some time in the upcoming days to figure out how to fix this. 🙂
Any fix for Google Finance’s change to their API? I notice they do yield only now on their table, but I’ll take what I can get. I only seem to be able to pull share price now.
Great GoogleFinance formula. Your post saved my day as Yahoo has discontinued their finance api.
How do I query Dividend payout frequency? Thank you!
Hi Chets,
You’re welcome. There’s no way of query Dividend payout frequency, you need to manually enter that unfortunately.
Hi
Great Info, ty for sharing!!
Just wondering if I can somehow get the dividend info imported from google finance for ETF’s and Preferred shares.
For example TSE:XIU or TSE:GWO-l
These show the yearly dividend but not the quarterly.
You can use the same formula… instead of multiplying by 4 when you calculate the yearly dividend, just multiple by 1.
HAD TO MAKE FOLLOWING ADJUSTMENTS:
Copied your formula over (using Mac if it matters) and changed all of YOUR commas and quote marks with the commas and quote marks from my keyboard and then it all worked. Thanks a bunch!
Yes, the commas and quote marks get formatted differently with wordpress, so if you just copy and paste the formula wouldn’t work properly.
I love Google Sheets, but I’ve gotten terribly frustrated with the inconsistent data feeds. I stumbled across this website: https://intrinio.com/ which has proven (so far) to be incredibly helpful. They have a Sheets add-on that allows access to 500 data feeds for free (still requires a signup on their website). I’ve replaced all of my Yahoo! data imports with their imports, and my sheet (so far) has stayed current. There are some limitations with certain securities, but overall I am much happier. I thought I’d share, since I found this dialogue (and a few closely associated) to be helpful with my trouble-shooting (I have no affiliation with Intrinio). Cheers!
Having trouble pulling Hydro one dividend yields. I am using H.To as my ticker to avoid pulling from NYSE. I keep getting ERROR!. All of my other stocks work without issue in template. Would you know why Hydro One is giving me these problems?
It seems to be a Google glitch. I have found some Canadian listed stocks cannot be pulled properly. Does H.TO instead of H.To help at all?
Hi I just came across this spreadsheet! it is amazing! Blows my amateur attempt away! Thank you for sharing it! I was just wondering is it possible to add preferred shares?
Hi Brian,
Should be possible to add preferred shares since they’re listed in Google Finance too.
Hi
Where can i see the sheet? It wil be a great help,
Thanks
Here it is:
https://www.tawcan.com/step-step-guide-make-google-spreadsheet-dividend-portfolio-template/
Thank you for your kind help, the sheet is at https://docs.google.com/spreadsheets/d/1HrQYjdyBBCJi2KwwxHivBM9k5SUsbtQApzf1xsnvjLI/edit?usp=sharing if you have any issues accessing please let me know. I found I had better luck with using importhtml but results with even that were not good. I am hoping we can delete the link after we are done with it.
To get the Yield % I use this formula:
=iferror(INDEX(Split(ImportXML(concatenate(“https://finance.google.com/finance?q=”,$A2), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”),0,2),ImportData(“https://finance.yahoo.com/d/quotes.csv?s=”&YahooSymbol($A2)&”&f=y”))
It fetches first from Google. If it as an error, it will fetch the data from Yahoo instead. (The order can be reversed)
The following javascript function, you add to your sheet (via the Tools -> Script Editor) converts a Google stock symbol to Yahoo (U.S and Canada):
/* Transform symbol for Toronto Stock Exchange if need be */
/* Example: TSE:CBR becomes CBR.TO */
/* CVE:XX becomes XX.V */
function YahooSymbol(input)
{
Utilities.sleep(Math.random() * 5000);
var ys = “”;
var Symbol = input.split(‘:’)
if (Symbol[0] == “TSE”)
ys = Symbol[1].replace(“.”, “-“).concat(“.TO”);
else if (Symbol[0] = “CVE”)
ys = Symbol[1].replace(“.”, “-“).concat(“.V”);
else ys = Symbol[1];
return ys;
}
Cool will have to give this formula a try.
I am still couldn’t get it to work most of the time, getting the message:
“Error Could not fetch url: https://finance.google.com/finance?q=FTS“.
I even tried to enter the symbol as “TSE:FTS”
Yeah Google seems to be having issues fetching the info for some reason. Occasionally if you click on the formula and hit enter a few times, it’d work.
This exact problem is such a disappointment. Seems like google sees its own spreadsheets as a DDOS attack and blocks connections. Makes the entire ImportXML function useless. Everything always devolves to gathering data via manual screen scraping. The world never permits anything useful out of these better technologies like xml. Back to manual data entry I guess.
Has anyone figured out how to make this work reliably
Perhaps way limit update intervals?
Hmm that’s interesting about Google sees its own spreadsheet as a DDOS attach and blocks connections. Will have to review and determine another method to do this.
Glad to see someone working on Dividends and Yield for Google Sheets. (Just wish they would add “d” and “y” to their criteria.
Quick question, is there a way to have google point to a stock exchange?
When I use the above formula for “AAL” (American Airlines Group) it points to another AAL on a different exchange.
Thank you!!
Hi Rob,
Odd American Airlines Group should be the one that shows up for AAL. Which one is coming up for you?
https://www.google.com/finance?q=aal&ei=0SdPV_mzNcjCe9PbufAK
AAL
Anglo American Group LON Exchange
(Yet I’m in the US on Comcast internet)
Weird when I entered
=GoogleFinance(“AAL”,”PRICE”) and =split(ImportXML(concatenate(“https://finance.google.com/finance?q=”,”AAL”), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)
I get $31.99 and 0.1/1.25.
Typically for American listed stocks you don’t need to specify the stock exchange.
Great instruction!
Managed to create my own fairly quickly.
However, I do have a few questions:
1. How do you calculate the “Exp Div”(as expected total annual dividend?)
2. Is there any way to incorporate DRIP?
Hi Jack,
You need to multiply 4 if the company pays out quarterly dividends, or 12 if it’s monthly payout. If it’s ARD or irregular payment schedule, then I use the percentage to get an expected dividend amount. Incorporating DRIP would simply need manual work.
Thanks for the quick response,
I just added another “Frequency” column to distinguish companies that pay annually, quarterly and monthly.
Another issue I am experiencing is “Error, couldn’t fetch url:………..” This error occurs spontaneously and it is quiet frustrating. So far, I haven’t come across any solutions yet.
Yes, I guess I’ll just need to make some manual adjustment periodically.
Has anyone figured out work around for this problem?
Best can find is that a place called cloudterra sees these importxml calls as a ddos attack and resets the connection.
Does it mean that googles own servers are blocking connection to its own spreadsheets?
And.. another one.. if you need to convert the decimal dot to comma… use the formula below:
Dividend
=value(regexreplace(index(split(ImportXML(concatenate(“https://finance.google.com/finance?q=”;B2); “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”);”/”);1;1); “\.”; “,”))
Yield
=value(regexreplace(index(split(ImportXML(concatenate(“https://finance.google.com/finance?q=”;B2); “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”);”/”);1;2); “\.”; “,”))
Bye
Hello…. fixed formula working for me…
Use B2 cell to put your stock ticker
To get Dividend
=index(split(ImportXML(concatenate(“https://finance.google.com/finance?q=”;B2); “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”);”/”);1;1)
to get Yield.
=index(split(ImportXML(concatenate(“https://finance.google.com/finance?q=”;B2); “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”);”/”);1;2)
Bye
Great work ! I was able to get up and running in no time .
Two things I found :
.TO suffix to get tsx as exchange did not always work for all fields , I found using tse: as prefix produced the most consistent result .
I copied the formula that you showed for dividends , however the result did not split the amount and yield. I found a function in google that splits it easily : =split(G2,”/”) where g2 is the result of amount and yield.
Thanks
Thanks for the tips on tse prefix and split, will check them out.
This is amazing. Thanks!
Glad to have helped. 🙂
Tawcan,
I just wanted to thank you for all of this information. It was useful in helping me create a spreadsheet for my blog! Good luck with reaching your goals and I look forward to following your progress.
Jim
Hi Jim,
You’re welcome!
Hi Tawcan,
I thought this may be the magic bullet I’ve been looking for regarding auto importing dividend information. However, I was halfway through updating my spreadsheet I when came to PEP and realized that google rounds it from 0.7025 to .70. A small quibble, but probably a deal breaker for me. Regardless, I appreciate the article, and it has given me something to ponder.
Ryan
Hi Ryan,
Good point, I’m investing ways to get more accurate dividend amount and dividend history somehow.
Ryan/Tawcan,
I was bothered by the 2 digit rounding that Google was doing on the dividend as well. I came up with another formula that might work for you:
=importxml(“https://www.dividendinvestor.com/dividendhistory.php?symbol=” & A3, “//tr[td/text() = ‘Dividend Amount Current:’]/td[2]”)
Where A3 is the field of your Stock Symbol. You may have to increase the displayed decimal places in Google Sheets, but the formula returns the dividend to 4 decimal places.
Hope this works for you.
KG
Hi KG,
Just curious. Is your formula still working?
I’m afraid I’m just not technically savvy enough to put all this together – it would take me months to figure it out! Any chance that you would consider producing an app or online software to accomplish this? I’d be happy to pay – you could make a fortune!
H.
Hi Heather,
I may publish a Google spreadsheet so people can download. Would that help?
I love Google Sheets. I automated it so calculate the GICS listing, the credit rating (if you care), annual dividend, quarterly dividend (for the breakdown matrix like what you did), the name, etc etc etc. If you are automatically calculating the GICS, you can then sort it out so your portfolio is always automatically (live) sorting your sector breakdowns. Obviously information overload, I do it just for enjoyment. Sitting at 29% in Consumer Staples. Haha
How did you automate the credit rating?
Hi Tawcan,
Thanks for your sharing.
Have you had any further luck with getting historical dividend data in?
This is where I am stuck.
Carl
Hi Carl,
Sorry I haven’t spent any time doing further investigation. Not sure if we can pull that data from google finance at this time.
any ideas why it can’t find bmo.to.. and why zut.to or bbd/b.to come out like
-/4.08. Seems to do that for a lot of etf, but i can’t figure out why bombardier and bmo are not working.
TIA
Hi Matthew,
For some odd reasons some Canadian stocks do not get populated properly when you use the GoogleFinance function. I think this is probably a glitch in the system. 🙁
Thanks for this.
By the way Canadian stocks work fine BUT sometimes when it is on two exchanges it gets confused.
For example: BMO alone gets the USA stock BUT if you use TSE:BMO (like on Google finance) it seems to work fine.
Thanks again
Hi Tom,
Yes I noticed that on some of the Canadian stocks. It’s really weird, I blame it on Google finance database.
no more updates since end of january??????????????
Hi,
Sorry what did you mean? If you mean no update on the table, it’s because the table is supposed to be a sample.
Hi Tawcan
I have been using the GoogleFinance spreadsheet since 2010 and have found it to be an extremely useful tool, the one drawback for me was its inability to handle dividends.
Having just read your article, which I found to be very informative, I thought brilliant my problem with the spreadsheet has been resolved
Unfortunately, however this has not been the case as I keep getting error messages in the cell (parse error)
I have tried substituting LON (given that I am using the London Exchange) prior to the EPIC but to no avail
Any advice would be really appreciated
Kind regards
Alan
Hi Alan,
What formula are you using? Does ” =ImportXML(concatenate(“https://finance.google.com/finance?q=”,”AAPL”), “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”) ” formula work for you at all? Would be good to have a baseline of your formula so we can determine what’s causing the error.
Cheers
Alan,
This is the formula that I use: =index((split(ImportXML(“https://finance.google.com/finance?q=” & A2, “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)),1,1)
Since it is using a GoogleFinance function, make sure to use the ticker symbol as the Google Finance website sees it.
Also, make sure that the stock has a dividend that is listed on Google Finance. LON:EPIC doesn’t appear that it does.
Another way to grab dividend data but using the Yahoo Finance site is:
=ImportData(“https://finance.yahoo.com/d/quotes.csv?s=”&B7&”&f=d”), with B7 referencing the ticker symbol in Yahoo’s format.
For reference, I’ve listed a number of the formulas that I use here: https://www.twoinvesting.com/2014/12/investing-formulas-for-google-spreadsheets/
Lastly, sometimes the formulas will copy with the incorrect type of double quotations marks, which will also cause an error on Google Spreadsheets. I’d check that if you are unable to get your formulas to work with any stocks, included US based ones.
I use desktop Excel to track my portfolio as well as my dividends. I have macros/plugins to pull the latest stock price and dividend information from yahoo and update the sheet automatically I recently started moving the data onto google spreadsheets so that I can share the information in my blog.
But lately I have been having problems with the GoogleFinance function in google spreadsheet. The current price shows up when I open the google sheet, but doesn’t work when I open the published sheet through the blog. Not sure what the problem is. I googled, but couldn’t find anything. So right now I have the spreadsheet with static prices that I update once a month or so.
Maybe I will try the importxml function and see if that works.
Thanks.
DGJ
I’m noticing that Google Spreadsheet keeps stuck in “loading” when you share it on blogs. I couldn’t figure it out either. The functions works fine when you view it in the actual document. Maybe Google needs to do something on their end.
Hey!
Thanks for the post. I’ve been doing something similar with my own Google spreadsheets. Details are here: https://www.twoinvesting.com/2013/08/investing-spreadsheets/. I reference Gurufocus in order to get the 5-yr dividend growth rate in order to calculate the Chowder number.
I’ve run into that loading issue as well. It seems to work best if you’re logged into your Google account. Then, if it is hanging I find a refresh of the page tends to cause it to work properly.
Take care!
I use spreadsheets all the time, but never thought about Google Drive. I have one at work, and one at home. Once in a while I send them back and forth to sync up. Great idea.
That’s the key reason why I use Google Drive. I don’t need to ‘sync’ all the different versions of docs on different computers.
Tawcan,
I love using Google Spreadsheets and use some of the same formulas. I actually wrote an article not long ago that you may find useful. I imported the entire CCC sheet and am pulling in any of that information automatically now. So I’m able to grab the 5-year DGR for instance and calculate a metric I like to use, the 10-year YOC based on the DGR rates. I’m sure you can do the same for grabbing the historical dividends from this sheet.
https://www.allaboutinterest.com/2014/05/using-google-docs-for-your-portfolio.html
Very useful post. Thanks for sharing!
Great article, Tawcan!
I’m looking into building my own Google spreadsheet, but the lack of dividend information from the GOOGLEFINANCE() function is a big problem.
Currently I have an entire MS Excel sheet that tracks some companies, but I have to enter the dividends and the yield manually. I’m also tracking the dividend growth over the past ten years, but that seems to be impossible to automate.
Do you have any idea on how to get historical dividend payments into Google spreadsheets? I wish to just enter a ticker and have the spreadsheet look up the information all by itself!
Hi NMW,
I’m not sure how to get historical dividend payments into Google Spreadsheets. Google Finance does not have such information but maybe Yahoo Finance does. If so you could potentially import the historical dividend information from Yahoo Finance.
I’ll let you know if I find something useful!
Thanks for the response,
NMW
Hi, did you find anything to get the historical dividends?
I`m struggling to get these info!
huge thanks
Hi Elena,
No I haven’t found a way to pull historical dividends yet. Still relying on using other websites. Will have to spend more time to see whether it’s possible to pull these data from Google Finance.
Did you have any luck finding ways to pull historical dividends through?
Or would you have suggestions for other/best ways to obtain them?
I have 165 listings that I want to obtain the dividend per share for the last 1,2,5 years.
Any advice would be greatly appreciated.
Hi Carl,
Have you look at some of the tools I listed here? https://www.tawcan.com/tips-on-dividend-investing-with-canadian-perspective/
I just use these tools for historical dividend data. I find if I try to do too much in the spreadsheet, it gets too slow for my liking.
This is an awesome way to keep your information updated without much work. Great idea! I will have to check it out for myself.
Google spreadsheet is a good tool, I like how you can incorporate live stock prices into the data. I primary use excel for all my tracking. I like it, it works.
Good post, cheers!
Thanks Henry. I used to use Excel for tracking but the lack of live stock prices and the fact I’m tied to one computer were deal breaker for me. I supposed with Office 365 you could have the Excel document on the cloud.
Hi tawcan, interesting and useful post about google sheets. i use them too and my dividend payout tracker looks a lot like yours, except yours has an awesome ‘x’ pattern! COOL
Hi M,
Thanks for visiting. I love the X pattern lol.
Hey man great lil article. I really want to upload my info into a spreadsheet but my skills are non existent and I really need to set aside a block of time to learn spreadsheets in general as I still do them by hand with a pen, sheet of paper and calculator. Great tips man!
Hey AG,
No shame doing the old school way and use a notebook to track. I love spreadsheets because they give you a good overview of what’s going on. No need to flip back and forth in the notebook to find all the stock transactions. 🙂