Using Google Finance & Google Spreadsheet for dividend investment

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:

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.

.

165 thoughts on “Using Google Finance & Google Spreadsheet for dividend investment”

  1. 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!

    Reply
    • 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.

      Reply
  2. 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

    Reply
      • 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.

        Reply
  3. 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?

    Reply
  4. 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)

    Reply
  5. 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

    Reply
      • 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.

        Reply
  6. 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!

    Reply
      • 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.

        Reply
        • 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.

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

    Reply
  8. 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.

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

        Reply
  9. 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.

    Reply
    • 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]

      Reply
      • 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.

        Reply
  10. Hello Tawcan,

    Excellent work!

    The ETF yield formula was working everywhere:

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

    But suddenly it is not working for some ETFs, such as for IDV. The yield value is in there when you do View source. How does one go about troubleshooting this?

    Thanks,

    Rick from Seattle

    Reply
  11. Hi Tawcan,
    I have some dividend data updates from think Stocks video : https://www.youtube.com/watch?v=uZY-AeZex2g&feature=emb_rel_end.
    Here are the formulas for anyone who is interested:
    Div Yield =if(iserror(substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&a2,table,8),8,2),”*”,””))*1))
    Annual Payout =substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&a2,table,8),7,2),”*”,””))*1
    Payout Ratio =substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&a2,table,8),11,8),”*”,””))*1
    Ex Div =index(importhtml(concatenate(“https://finance.yahoo.com/quote/”&A2),”table”,2),7,2) Note: It will display as a number, just change the format to “Date”, then date will display correctly.

    Reply
  12. Hi Tawcan,
    Sorry there are a couple of errors in my above email, here are the correct formulas:
    (1) Div Yield: =substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&A2,”table”,8),8,2),”*”,””)*1
    (2) Annual Payout: =substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&A2,”table”,8),7,2),”*”,””)*1
    (3) Payout Ratio: =substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&A2,”table”,8),11,8),”*”,””)*1
    Ex Div formula is correct.

    Reply
  13. Any tips to avoid the error loading message about “Loading data may take a while because of the large number of requests. Try to reduce the amount of IMPORTHTML, IMPORTDATA, IMPORTFEED or IMPORTXML functions across spreadsheets you’ve created.”? While your formula seems to work ok if I’m tracking many stocks I get loading errors.

    Reply
    • Hi Dean,

      This has more to do with how the data is being pulled and the amount of queries you’re doing. not much I can do… you can try reducing number of stocks you’re checking.

      Reply
  14. It can help to add the slash onto the end of the URL.
    Instead of:
    =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,C10),”table”,2),4,2),” “)
    This can work when the above does not:
    =SPLIT(INDEX(IMPORTHTML(concatenate(“https://finance.yahoo.com/quote/”,C10,”/”),”table”,2),4,2),” “)

    Reply
  15. Hi Richard,
    I refer to your email of 18/02/2021 and advise that you latest split function does not work for me, receive invalid parse error, as it looks like yahoo is continually switching both url’s and table numbers. I could get the data using
    =index(importhtml(concatenate(“https://finance.yahoo.com/quote/PFE/key-statistics?p=PFE”),”table”,3),10.1), but I cannot work out how to split the two cells ie Last Split Date3 and16/11/2020. As index will not allow you to use 2,10 (col 2, pos 10) Unless you can show me how to do that :>) Note you need to change the format to date to dis display 16/11/2020.

    Regards,
    Ron

    Reply
  16. Recently many of my stocks are not returning results from yahoo finance. It’s a bit sporadic (BNS works, but TD and RY do not work). It says “Resource at URL not found”. I’m wondering if you’ve encountered the same?

    Reply
  17. Hi,

    I’m living in Quebec and I have started my sheet from your template today.

    I have a little issue with the XPATH that is not working for $FBND. I’m getting #N/A. Can someone help me?

    =IMPORTXML(“https://ca.finance.yahoo.com/quote/FBND?p=FBND”,”div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div[2]/div[2]/table/tbody/tr[4]/td[2]/span”)

    I tried also with : “//*[@id=”quote-summary”]/div[2]/table/tbody/tr[4]/td[2]/span”

    I tried also with SPLIT() function.

    Help!

    Thank you a lot Tawcan for sharing the template sheet.

    Reply

Leave a Comment

 

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