Investing Formulas for Google Spreadsheets

The page describing my portfolio spreadsheets was getting a little confusing with all the formulas. To simplify things I’ve decided to pull all the importxml and importhtml formulas that I’ve used over the years to retrieve such data as dividend yield, latest stock price, 5-year dividend CAGR, etc into this one blog post. These formulas are specifically for Google Spreadsheets.

If you have any formulas that you use, please feel to share them in the comments below.

ImportXML is too confusing to describe in this post. I did find this site very useful when constructing a few of these formulas.

ImportHTML is easier to understand. For example, the 5-yr div CAGR formula below references table 4 and then Index directs the output to refer to row 2 and column 3.

A link to this Google Spreadsheet can be found here: [Link to spreadsheet].
1. To access this spreadsheet you will need to first sign into your Google account.
2. Once signed in, click on the link above and make a copy by clicking on File and then Make a copy…


Sector Information:


Latest stock price:


Quarterly Dividend Amount:


Annual Dividend Yield:



Payout Ratio:

These payout ratio formulas are based on the annual dividend divided by either the trailing-twelve-month earnings per share (EPS) or the annual dividend divided by next year’s estimated annual EPS, as pulled from the Yahoo Finance API.

The payout ratio can be calculated easily this way, but there are some disadvantages as well. A payout ratio based on free cash flow may ultimately be the better method. See the articles below for more information:

In Search Of Dividend Growth: Dividend To Free Cash Flow
Dividend Payout vs. Free Cash Flow Payout
In Dividend Investing, Cash Is King


5-Year Dividend CAGR:


PEG Ratio:


Graham number:


XIRR:


Chowder Rule:
Dividend yield + 5-yr Div CAGR


Compound Annual Growth Rate:
=((E2/D2)^(1/(ceiling(((today()-C2)/365),1))))-1
E2 = current annual dividend
D2 = initial annual dividend
C2 = date initially purchased

You may also like...

35 Responses

  1. FerdiS says:

    Great post! Looks like I’ve got a few more formulas to try out… Thanks for the link to the XML guide. I agree that the XML formula is a little harder to describe/use.

    Take care!
    FerdiS recently posted…Monthly Review, November 2014My Profile

  2. Jason says:

    I cannot seem to navigate to Guru Focus? Do you know if the site is down??

    ALso – I found this a much cleaner and more dependable Annual Dividend Pull – For Google Sheets –
    =Left(ImportXML(Concatenate(“http://finance.yahoo.com/q?s=”,[Symbol],”&ql=1″),”//table[2]/tr[8]/td[1]”),4)

    Seems to refresh and get pulled into MS Excel much faster.

    Have a great day!!
    Jason recently posted…David Herro’s Oakmark International Fourth Quarter CommentaryMy Profile

    • scott says:

      Hi Jason,

      Gurufocus appears to be working for me. Maybe Google’s servers are having some loading issues again.

      Thanks for the additional method to lookup annual dividends. I’ll add it to the spreadsheet. I’ll give people the option on which one to use. I have tried to stick with Google Finance as much as possible to keep the differences in ticker symbols to a minimum…Yahoo and Google use different symbols for Canadian stocks, for example.

      Have a great day too!

      • Jason says:

        Thanks! – Yes, i Am pulling the 5 Yr CAGR for Dividends from Guru Focus for the Chowder Rule – but I cannot navigate and actually look at the time. I’ve tried chrome, Firefox, and my cell – safari…….

        I’m trying to get all the factors in to make a Piotroski z score / ALtman Z Score and Graham Number Hybrid sheet running 100% in Google Sheets give to the masses…..

        Have a great day!

  3. scott says:

    When I put in: =importhtml(“http://finance.yahoo.com/q/ks?s=”&A4&””, “table”, 14)
    I get the *Fiscal Year* table which looks like this:
    *Fiscal Year*
    Fiscal Year Ends: 9/27/2015
    Most Recent Quarter (mrq): 9/27/2014

    Maybe it’s not working because there isn’t a row 7 and column 2 in table 14?

  4. Jake says:

    Hey thank you for your post! I am having trouble pulling the Income Statement, Balance Sheet and Cash Flow Statement. I have done it once by typing

    =IMPORTHTML(“https://www.google.com/finance?q=NYSE%3AGM&fstype=ii&ei=XVeMVcG0LZG6e-LWsYAJ:”,”table”,4)

    But I can not get it to update itself
    I have tried

    =IMPORTHTML(“http://finance.yahoo.com/q/ks?s=”&Sheet1!A4″”,”table”,1)

    with A4 from Sheet 1 being various tickers that I plan on adjusting over time. It only seems to work and to input the table when I have the table on the webpage in front of me.
    Would anybody be able to help? Thank you

    • scott says:

      Hi Jake, I’m away from my computer for a little bit here (moving to Chicago). I’ll be able to try and help you shortly. Any luck still with those formulas? I have run into issues in the past in which it used to take a few minutes (or longer) for some formulas to get updated. That was very frustrating, as you can imagine!

  5. tim says:

    Sheets are great, however I am A Canadian investor and find that the Dividend Yield does not pull consistently or is very slow. Any suggestions?

    • scott says:

      I’ve noticed that with some as well. Which ticker symbols in particular? I’ll try to take a look.

        • scott says:

          Ok. At work now. I’ll take a look tonight.

          • tim says:

            Looks like the sheets are just having trouble updating. Various stocks are not showing up but it’s inconsistent.

            On another note, do you have a forecasting model for Dividend stocks? Something that determine the value of a stock with divide re-investment over a period of time. That would be very useful.

          • scott says:

            Yeah, Sheets as issues like that every so often. Take a look at the Spreadsheets tab along the top. I think you’ll find what you’re looking for under the DRIP/DCA Spreadsheets link.

      • tim says:

        I am getting this error on the Watchlist Sheet, hoping you can help:

        “Function INDEX parameter 3 value is 2. Values are between 0 and 1 inclusive”

        Here is the formula:

        =index(split(ImportXML(“http://finance.google.com/finance?q=” & B12, “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”), “/”),1,2) / 100

        The formula works sometimes but not others.

  6. linda says:

    hello, would you please check again? The dividend annual yield formula not working for 2 days since March 30, 2016.
    Thanks

    • scott says:

      Hi linda,

      I just checked and many of the formulas are not working for me either. It also is taking forever to load.

      I’ll search the Google support forums and write an update soon. Unfortunately, we’re at the mercy of Google for continuing to support Google Sheets. Hopefully they address this issue soon.

      Scott

    • scott says:

      It is updated and working now. Yahoo changed the website to access the data. See the comment here for more details.

      Scott

  7. Mufti says:

    Quarterly earnings comparison is the most important for any investor. I would like to retrieve for certain stock eps for the last four quarters. Say my first column is the stock ticker, my second column is EPS for the quarter (7/1/2015 to 9/30/2015), the third column is the EPS for the quarter (10/1/2015 to 12/31/2015), the fourth column is the EPS for the quarter (1/1/2016 to 3/31/2016) and the fifth column for the EPS for the quarter (4/1/2016 to 6/30/2016). How do I do this given EPS parameter is not linked to any date and will return the last EPS available.

  8. Hey,

    Nice Post! I think I got some new formulas that I haven’t try them yet.. I am excited to use them.. I must say link up posts are awesome I like them too. Now I have some useful knowledge. Thanks for sharing with us.
    Singapore Excel Course recently posted…5 Must Use Steps To Split Test in ShopifyMy Profile

  9. Chang says:

    Does anyone know how to get the PEG ratio or EPS next 5-year data from Finviz? the posted PEG formula (=(index (importhtml(“http://finance.yahoo.com/q/ks?s=”&A4&””, “table”, 11), 5, 2))) seems out of function? thank you very much for your help.

    • Jim says:

      This works at Yahoo Finance:

      =index(importhtml(“https://finance.yahoo.com/quote/”&A4&”/key-statistics?p=”&A4&””, “table”, 1),5,2)

      Try this for FinViz:

      =ArrayFormula(SUBSTITUTE(index(importhtml(“https://finviz.com/quote.ashx?t=”&A4&””,”table”,11),3,4) , “*” , “” )*1)

      -Jim

  10. Rudi Pittman says:

    Is there a way to screen scrape the quarterly dividend rate and payment date without using IEX? None of the old methods work anymore.

  11. Wil says:

    Looks like the Yahoo API has been shut down?
    Formulas aren’t working.
    Tried PEG recently and received a “#N/A” error.

  12. vincent says:

    the formula dont works 🙁

  13. Sam says:

    Formulas are not working

  14. thedataxp says:

    This is a fantastic article! I believe I have some new formulations that I have yet to try. Simply type =FV( into any cell of the spreadsheet to use the future value function. When you type =FV(, Microsoft Excel recognizes that you’re trying to calculate a future value function and walks you through the process: The variables are in the same order as in Google Sheets.
    thedataxp recently posted…Self-discipline and taking action is key to a happy, balanced mindMy Profile

  15. Chris Brown says:

    So Brilliant! Thanks for it. You may plan ahead by structuring and strategizing your content strategy. I appreciate your work.

Leave a Reply to Jake Cancel reply

Your email address will not be published. Required fields are marked *

CommentLuv badge

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