Using Google Spreadsheet for dividend investment

how to

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.

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

Using Google Spreadsheet to track stock dividend information

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.

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.

After a bit of investigation I found a way to automate the dividend amount and dividend yield on Google Spreadsheet. 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. The formula is as follows:

=ImportXML(concatenate("","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("","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.

Note: Previously the formula isn't correct because WordPress turned " and ' in the formula to fancy curvy symbols (smart quote option on WordPress). I think I've fixed this issue but if the formula still doesn't work please check and manually change the double quotes and single quotes. Thanks!

For the AAPL query, 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 corresponsd to the current dividend yield percentage. Since dividend amount and dividend yield percentage are combined together, I can use the function Split to further split the ImportXML output.

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.

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

For Div$ and Yield % columns I'm using the following syntax to split the XML imported data into two fields.

=split(ImportXML(concatenate("",B2), "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/")

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.

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


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

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

            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


    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.

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



    • Reply
      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
        August 25, 2014 at 4:13 pm


        Thanks for the post. I've been doing something similar with my own Google spreadsheets. Details are here: 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

    • Reply
      November 14, 2014 at 9:04 pm

      Hi Alan,

      What formula are you using? Does " =ImportXML(concatenate("","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.


    • Reply
      December 10, 2014 at 11:15 pm


      This is the formula that I use: =index((split(ImportXML("" & 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(""&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:

      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
    March 13, 2015 at 2:58 am

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

    • Reply
      March 13, 2015 at 9:13 am


      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
    March 15, 2015 at 10:44 pm

    any ideas why it can't find and why or bbd/ 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.

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

    • Reply
      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
      December 17, 2015 at 8:45 am

      How did you automate the credit rating?

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


    • Reply
      August 31, 2015 at 2:16 pm

      Hi Heather,

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

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


    • Reply
      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
        January 9, 2016 at 9:07 am


        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("" & 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.


  • Reply
    November 6, 2015 at 5:47 am


    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.


    • Reply
      November 6, 2015 at 10:35 am

      Hi Jim,

      You're welcome!

  • Reply
    November 13, 2015 at 3:26 pm

    This is amazing. Thanks!

    • Reply
      November 18, 2015 at 8:10 am

      Glad to have helped. 🙂

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


    • Reply
      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("";B2); "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*");"/");1;1)

    to get Yield.
    =index(split(ImportXML(concatenate("";B2); "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*");"/");1;2)


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


    =value(regexreplace(index(split(ImportXML(concatenate("";B2); "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*");"/");1;1); "\."; ","))

    =value(regexreplace(index(split(ImportXML(concatenate("";B2); "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*");"/");1;2); "\."; ","))


  • 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
      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
          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
    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
      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
        June 1, 2016 at 11:23 am


        Anglo American Group LON Exchange

        (Yet I'm in the US on Comcast internet)

        • Reply
          June 1, 2016 at 11:28 am

          Weird when I entered

          =GoogleFinance("AAL","PRICE") and =split(ImportXML(concatenate("","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:".

    I even tried to enter the symbol as "TSE:FTS"

    • Reply
      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
        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
          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("",$A2), "//td[@data-snapfield='latest_dividend-dividend_yield']/following-sibling::*"),"/"),0,2),ImportData(""&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
      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 […]

  • Leave a Reply