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.

Share on:
.

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

  1. The html functions to pull dividends or yield from yahoo finance, morningstar or finviz.com were working. But some months ago, they all stopped working for me, maybe because I have too many functions on my sheet. I tried troubleshooting on several occasions, but I could not get them to work.

    So I hit on a work around: manually copy the dividend figure (not the yield but the $/share/yr) from finviz.com (because this site has the figure for both ETFs and stocks) and manually paste the figure into column for dividends $/sh/yr. Yes I must do this manually, but once per quarter is often enough. This figure varies less than the yield, as yield depends on the price, which varies a lot.

    If a better way comes along to reliably get the figure into my spreadsheet, I will adopt it, but in the meantime, I am getting meaningful information from my dividend tracker spreadsheet.

    Rick from Seattle

    Reply
  2. Hello Bob,

    Alas, once again there is a problem with importhtml and ETFs.
    As you will notice (I downloaded a copy of your spreadsheet) the lines with ETFs in them have #N/A in the Dividend and Yield columns.
    I explored your equation to retrieve the yield and experimented with it a bit, no way to get YAHOO to show a yeild or dividend for almost any ETF.

    Reply
    • Solved my own problem FOR NOW
      So, apparently some websites use different URL for ETF or Mutual funds or STOCKS – the structure of the URL changes in each case

      Here is the function that works (as of this date) for ETFs for Dividend
      =index(split(index(importhtml(concatenate(“https://www.zacks.com/funds/etf/”,B2,”/profile”),”table”,5),2,2),” “,true),1,1)
      It is contained in table #5 from that URL. (for now)
      It is a string which contains both the Dividend and Yield values – and has to be Split
      Here is the full table that results from the above Importhtml query
      Expense Ratio 0.59%
      Dividend (Yield) $5.39 (35.56%)

      The yield is within parenthesis, and google sheets treats that as a negative. So you must invert it
      = – index(split(index(importhtml(concatenate(“https://www.zacks.com/funds/etf/”,B2,”/profile”),”table”,5),2,2),” “,true),1, 2 )
      I have spaced it out for clarity.
      Following the = sign is a negate or invert ‘-‘
      From the split, the value is the 2nd value

      You could also get expense ratio to figure out what TRUE returns would be (although some other websites claim their results reflect that)

      Reply
  3. Hi Tawcan, thanks for the post. I wonder if you also tracking dividend pay date? I used to pull dividend pay date from dividendinvestor.com. For AAPL as an example it used to work with IMPORTXML(“https://www.dividendinvestor.com/dividend-quote/AAPL”,”/html/body/div[2]/div[1]/div/div/div/div[2]/div/div/div[3]/div[2]/div[3]/span[14]”) but for some reason it does not seem to be working anymore.
    Thanks!

    Reply
    • Hi Kan,

      ImportXML should be able to pull any data from any website. It looks like the format is correct so it might have something to do with ImportXML not working properly.

      Reply
  4. I am also using Terry’s finviz reference for US Stocks. I am still unable to find a working Google Sheet Formula for canadian stocks for Div per share info. Tried all of the above. US stocks work fine, not canadian.

    Reply
  5. Been trying to use this one and get formula parse error for stock referenced in cell A6:

    =substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&A6,”table”,8),8,2),”*”,””)*1

    Reply
  6. Has there been found a consistent formula that works for dividend yield? I keep getting errors for any that I try that are referenced.

    Reply
    • All of the ones mentioned in the article have been working quite consistently. It seems that something has changed on the Yahoo backend that’s causing the errors.

      Reply
      • =if(A6=””,””,split(index(importhtml(CONCATENATE(“https://finviz.com/quote.ashx?t=”,A6,””),”table”,9),8,2),”*”))
        Am not using Yahoo link but finviz. Any ideas why not working?
        The error it notes is “Function index parameter 3 value is 2. Valid values are between 0 and 1 inclusive.”

        Reply
  7. Hi There,

    I used your newest formula to import dividend information from yahoo but keep receiving an error. Any suggestions?

    Thanks!

    Reply
  8. Hi Bob,

    Thank you for sharing this info. After trying out ImportHTML, I discovered that the import doesn’t always get the most updated data. I came across
    a Reddit post that talks about leveraging Yahoo Finance’s JSON feed: https://www.reddit.com/r/sheets/comments/ji52uk/yahoo_finance_api_url/

    Add a script in Google Sheets by going Tools -> Script Editor and add the script from https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs

    And then in the cell you can use =ImportJSON()

    So for example, my Div/Sh column for RY.TO is:
    =ImportJSON(“https://query2.finance.yahoo.com/v10/finance/quoteSummary/RY.TO?modules=summaryDetail”,”/quoteSummary/result/summaryDetail/dividendRate/fmt”,”noHeaders”)

    Hope this helps.

    Ken

    Reply
  9. When I pull the data my yield comes as a negative percent….but I don’t see anyone else having that issue?? I am using the same formula. It pulls the dividend and puts it in one cell and the yield in another cell beside it…if I try and change the format/value of the yield it breaks the formula.

    Reply
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. 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
  16. 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
  17. 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

Leave a Comment

 

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