Using Google Finance for dividend investment

Update:1  As per requests, I have created a 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.

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 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 internet connection.

Hence I have created a dividend template using Google Spreadsheet.

 

Google Spreadsheet and Google Finance

Google Spreadsheet has a very useful function called “GOOGLEFINANCE” which allows you to fetch current and historical securities information from Google Finance. The function supports a lot of syntax 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 complain 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 handful of stocks. As the 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 spreadsheet as much as possible, so I’ve been looking for a way to track dividend information 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 using Yahoo Finance

But 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 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 concatenate function. The formula would looks like:

=split(IMPORTXML(“https://finance.yahoo.com/quote/RCI-B.TO”,$C$10),“()”)

 

Using Google Spreadsheet with dividend info to track our dividend portfolio

How do I use this information to track our dividend portfolio?

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 dividend portfolio template available to download.


The column headings are quite self explanatory I hope. 🙂
Note: I made the numbers in the sample tracker static so the spreadsheet loads faster. For some reason the cells would often stuck in “loading” when shared on the internet.

Here are the syntax I used for querying cell items:

To get 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 sample tracker, I’m able to track our dividend portfolio very easily and able to get a quick summary of what’s going on.

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]

 

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 dividend portfolio a breeze.

How do you track your portfolio?

Update: As per requests, I have created a dividend portfolio template available to download.

You Might Also Like

116 Comments

  • Reply
    Asset Grinder
    August 14, 2014 at 11:34 pm

    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!

    • Reply
      Tawcan
      August 15, 2014 at 9:28 am

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

  • Reply
    M
    August 15, 2014 at 2:55 am

    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

    • Reply
      Tawcan
      August 15, 2014 at 9:27 am

      Hi M,

      Thanks for visiting. I love the X pattern lol.

  • Reply
    Henry @ Living At Home
    August 15, 2014 at 6:37 am

    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!

    • Reply
      Tawcan
      August 15, 2014 at 9:26 am

      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.

  • Reply
    Kipp
    August 15, 2014 at 1:03 pm

    This is an awesome way to keep your information updated without much work. Great idea! I will have to check it out for myself.

  • Reply
    No More Waffles
    August 15, 2014 at 11:56 pm

    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!

    • Reply
      Tawcan
      August 18, 2014 at 9:29 am

      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.

      • Reply
        No More Waffles
        August 18, 2014 at 12:10 pm

        I’ll let you know if I find something useful!

        Thanks for the response,
        NMW

        • Reply
          elena
          December 21, 2014 at 11:46 am

          Hi, did you find anything to get the historical dividends?
          I`m struggling to get these info!
          huge thanks

          • Tawcan
            December 23, 2014 at 3:02 pm

            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.

          • Carl
            May 6, 2015 at 12:27 am

            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.

          • Tawcan
            May 6, 2015 at 10:14 am

            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.

  • Reply
    Brent @ AAI
    August 16, 2014 at 12:05 pm

    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

    • Reply
      Tawcan
      August 18, 2014 at 9:29 am

      Very useful post. Thanks for sharing!

  • Reply
    No Nonsense Landlord
    August 19, 2014 at 5:28 am

    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.

    • Reply
      Tawcan
      August 19, 2014 at 10:35 am

      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.

  • Reply
    Dividend Growth Journey
    August 21, 2014 at 2:11 pm

    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

    • Reply
      Tawcan
      August 21, 2014 at 2:14 pm

      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.

      • Reply
        Scott
        August 25, 2014 at 4:13 pm

        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!

  • Reply
    Using Google Spreadsheets to Track Your Dividend Portfolio, Updated Template | No More Waffles
    November 10, 2014 at 9:21 am

    […] actually implement the formula above if you wished to. If you decide to do so, take a look at Tawcan’s guide for implementation […]

  • Reply
    Alan Bateman
    November 14, 2014 at 2:44 am

    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

    • Reply
      Tawcan
      November 14, 2014 at 9:04 pm

      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

    • Reply
      Scott
      December 10, 2014 at 11:15 pm

      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.

  • Reply
    clod
    March 13, 2015 at 2:58 am

    no more updates since end of january??????????????

    • Reply
      Tawcan
      March 13, 2015 at 9:13 am

      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.

  • Reply
    matthew
    March 15, 2015 at 10:44 pm

    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

    • Reply
      Tawcan
      March 15, 2015 at 10:50 pm

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

      • Reply
        tom
        May 6, 2015 at 1:12 pm

        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

        • Reply
          Tawcan
          May 7, 2015 at 1:34 pm

          Hi Tom,

          Yes I noticed that on some of the Canadian stocks. It’s really weird, I blame it on Google finance database.

  • Reply
    Carl
    April 26, 2015 at 3:52 am

    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

    • Reply
      Tawcan
      April 27, 2015 at 7:40 pm

      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.

  • Reply
    Stephen Hodgson
    August 1, 2015 at 5:39 am

    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

    • Reply
      Orion
      December 17, 2015 at 8:45 am

      How did you automate the credit rating?

  • Reply
    heather
    August 30, 2015 at 11:07 pm

    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.

    • Reply
      Tawcan
      August 31, 2015 at 2:16 pm

      Hi Heather,

      I may publish a Google spreadsheet so people can download. Would that help?

  • Reply
    Ryan
    September 19, 2015 at 2:22 pm

    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

    • Reply
      Tawcan
      September 20, 2015 at 7:00 am

      Hi Ryan,

      Good point, I’m investing ways to get more accurate dividend amount and dividend history somehow.

      • Reply
        KG
        January 9, 2016 at 9:07 am

        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

        • Reply
          KANH
          May 15, 2018 at 7:38 am

          Hi KG,
          Just curious. Is your formula still working?

  • Reply
    Jim
    November 6, 2015 at 5:47 am

    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

    • Reply
      Tawcan
      November 6, 2015 at 10:35 am

      Hi Jim,

      You’re welcome!

  • Reply
    michelle
    November 13, 2015 at 3:26 pm

    This is amazing. Thanks!

    • Reply
      Tawcan
      November 18, 2015 at 8:10 am

      Glad to have helped. 🙂

  • Reply
    Rick
    February 8, 2016 at 9:48 am

    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

    • Reply
      Tawcan
      February 9, 2016 at 11:41 am

      Thanks for the tips on tse prefix and split, will check them out.

  • Reply
    Fabio Silva
    April 13, 2016 at 11:40 am

    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

  • Reply
    Fabio Silva
    April 13, 2016 at 11:58 am

    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

  • Reply
    Jack Luo
    April 22, 2016 at 10:56 am

    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?

    • Reply
      Tawcan
      April 22, 2016 at 2:20 pm

      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.

      • Reply
        Jack Luo
        April 22, 2016 at 2:41 pm

        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.

        • Reply
          PigDo
          August 4, 2016 at 3:44 pm

          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?

  • Reply
    Rob
    June 1, 2016 at 6:48 am

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

    • Reply
      Tawcan
      June 1, 2016 at 11:10 am

      Hi Rob,

      Odd American Airlines Group should be the one that shows up for AAL. Which one is coming up for you?

      • Reply
        Rob
        June 1, 2016 at 11:23 am

        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)

        • Reply
          Tawcan
          June 1, 2016 at 11:28 am

          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.

  • Reply
    Jack Luo
    June 1, 2016 at 11:16 am

    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”

    • Reply
      Tawcan
      June 1, 2016 at 11:25 am

      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.

      • Reply
        PigDo
        August 4, 2016 at 3:33 pm

        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?

        • Reply
          Tawcan
          August 4, 2016 at 3:40 pm

          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.

  • Reply
    Jacques Gauthier
    July 22, 2016 at 5:40 am

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

    • Reply
      Tawcan
      July 22, 2016 at 2:28 pm

      Cool will have to give this formula a try.

  • Reply
    Poweria salkun seurantaan - Osa 1 data - P.Ohatta - Kolmevitosena eläkkeelle
    September 30, 2016 at 7:18 am

    […] IMPORTXML:stä löytyy netistä mukavasti artikkeleita ja tässä yksi, missä oli rakennettu GS-taulukko osinko-osakkeiden […]

  • Reply
    Mukul Kumar
    January 11, 2017 at 3:39 pm

    Hi
    Where can i see the sheet? It wil be a great help,
    Thanks

  • Reply
    Brian
    March 11, 2017 at 11:24 am

    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?

    • Reply
      Tawcan
      March 14, 2017 at 1:19 pm

      Hi Brian,

      Should be possible to add preferred shares since they’re listed in Google Finance too.

  • Reply
    Kaleb
    May 7, 2017 at 9:10 am

    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?

    • Reply
      Tawcan
      May 7, 2017 at 9:37 pm

      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?

  • Reply
    income
    May 8, 2017 at 9:45 am

    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!

  • Reply
    Stuart
    July 14, 2017 at 10:32 am

    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!

    • Reply
      Tawcan
      July 14, 2017 at 1:15 pm

      Yes, the commas and quote marks get formatted differently with wordpress, so if you just copy and paste the formula wouldn’t work properly.

  • Reply
    Mike
    November 3, 2017 at 8:51 pm

    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.

    • Reply
      Tawcan
      November 5, 2017 at 12:48 pm

      You can use the same formula… instead of multiplying by 4 when you calculate the yearly dividend, just multiple by 1.

  • Reply
    Chets
    November 5, 2017 at 10:05 am

    Great GoogleFinance formula. Your post saved my day as Yahoo has discontinued their finance api.

    How do I query Dividend payout frequency? Thank you!

    • Reply
      Tawcan
      November 5, 2017 at 12:50 pm

      Hi Chets,

      You’re welcome. There’s no way of query Dividend payout frequency, you need to manually enter that unfortunately.

  • Reply
    Thexdor
    December 3, 2017 at 7:46 pm

    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.

  • Reply
    Using Google Finance for dividend investment – Tawcan – Clean biofuel and food from waste residues
    January 10, 2018 at 5:05 am

    […] Fonte: Using Google Finance for dividend investment – Tawcan […]

  • Reply
    Jeroen Berendsen
    March 16, 2018 at 1:46 am

    The formula to pull in the dividend data does not seem to be working anymore. Is there a fix?

    • Reply
      Tawcan
      March 16, 2018 at 10:24 am

      Hmm looks like Google Finance has changed. I need to spend some time in the upcoming days to figure out how to fix this. 🙂

  • Reply
    Mike
    March 16, 2018 at 9:59 pm

    Added an s on the end of http

    Changed to:
    https://finance.google.com/finance.etc

    .It fetched the info on some of the stocks but not all…Weird

    • Reply
      Sean
      March 20, 2018 at 8:45 am

      What is the correct link? The one posted does not work.

  • Reply
    Sean
    March 20, 2018 at 8:44 am

    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?

    • Reply
      Tawcan
      March 20, 2018 at 12:34 pm

      Google has changed Google Finance format. I’ll have to provide an update on how to pull the data.

      • Reply
        Tawcan
        March 20, 2018 at 2:14 pm

        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/

        • Reply
          Erik
          March 23, 2018 at 10:02 am

          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!

          • Tawcan
            March 23, 2018 at 11:10 am

            Cool, will give that a try. For some reason, it didn’t work when I was testing the formula.

  • Reply
    Mike H
    March 23, 2018 at 1:12 pm

    I keep getting an error 🙁

    • Reply
      Tawcan
      March 23, 2018 at 1:25 pm

      What kind of error are you getting?

  • Reply
    Mike H
    March 23, 2018 at 2:12 pm

    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

    • Reply
      Tawcan
      March 23, 2018 at 2:33 pm

      Hmm I don’t see any error in the mock up.

  • Reply
    Mike H
    March 23, 2018 at 2:45 pm

    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.

  • Reply
    Brad
    March 25, 2018 at 8:23 pm

    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!

    • Reply
      Tawcan
      March 26, 2018 at 9:05 am

      Yes the double quotes from the HTML are different than the quotes that Google Spreadsheet use. You need to correct this manually. 🙂

  • Reply
    Ken
    April 17, 2018 at 3:33 pm

    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

    • Reply
      Tawcan
      April 18, 2018 at 8:56 am

      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]

  • Reply
    Ken
    April 18, 2018 at 9:14 am

    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?

    • Reply
      Tawcan
      April 18, 2018 at 9:47 am

      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.

  • Reply
    Ken
    April 18, 2018 at 10:14 am

    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.

    • Reply
      Tawcan
      April 18, 2018 at 10:30 am

      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.

  • Reply
    Ken
    April 18, 2018 at 11:16 am

    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.

    • Reply
      Tawcan
      April 18, 2018 at 1:50 pm

      I’m not sure where Yahoo pulls this info. They could have used the last dividend amount to calculate the dividend yield percentage.

  • Reply
    Ken
    April 18, 2018 at 1:58 pm

    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?

  • Reply
    Ken
    April 19, 2018 at 10:30 am

    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.

    • Reply
      Tawcan
      April 19, 2018 at 11:19 am

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

  • Reply
    Ken
    April 19, 2018 at 12:20 pm

    Alright sounds good. Thanks for your help with this. I appreciate it.

  • Reply
    KANH
    May 14, 2018 at 6:47 pm

    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),“()”)

  • Reply
    Ken
    May 14, 2018 at 8:36 pm

    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.

    • Reply
      KANH
      May 14, 2018 at 10:11 pm

      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.

  • Reply
    Ken
    May 15, 2018 at 1:19 am

    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.

    • Reply
      KANH
      May 15, 2018 at 6:15 am

      Brilliant!
      It works now. This is so important and I cannot believe I missed it.

      Thanks!

      • Reply
        Tawcan
        May 15, 2018 at 10:23 am

        Glad to see that everything works now. 🙂

    Leave a Reply