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
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 2014
FerdiS,
I’m excited to see what layout/design you come up with. I’ll be checking your portfolio page to watch the progress. 🙂
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 Commentary
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!
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!
Wow. That would be awesome! Let me know when you are done. I’d love to put a link to it next to my dividend spreadsheet template.
Gurufocus is working on my computer here (Wisconsin) so it must be an issue with accessing it on the server from wherever you’re located. A good site I use to check that is isitdownrightnow.com
Funny thing is – I cannot get ANY data from the KEY+STATISTICS page in Yahoo – ?
This is pulling zero – =INDEX(ImportHTML(“http://finance.yahoo.com/q/ks?s=”&A1&,”table”,14),7,2)
T
Jason recently posted…Half of Van Den Berg’s Third-Quarter Transactions Were Reductions in Holdings
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?
Whats your email? Id like to share my initial screen with all the Tables / Etc I Figured out from Yahoo Key Statistics page?
Thanks for your time last week – I’d like to share this screener with you…
Still in flux – gonna be a few pages to get Z Score and Altman in there.
Also – there’s a LOT of items to get the Graham to work – SImply, its –
Sq Rt ( 22.5* Book Value Per Share * EPS)
But I’m not close to the numbers I’ve found online…..
Thanks!
[email protected]
Jason recently posted…Looking Ahead of Wall Street: JPMorgan Chase, Bank of America, Citigroup
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
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!
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?
I’ve noticed that with some as well. Which ticker symbols in particular? I’ll try to take a look.
BPY.UN
Ok. At work now. I’ll take a look tonight.
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.
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.
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.
Does it work depending on what exchange the stock is traded on?
Inconsistent. Some Canadian securities work (HR.UN works) others -most- do not. Most US securities show up but some don’t.
hello, would you please check again? The dividend annual yield formula not working for 2 days since March 30, 2016.
Thanks
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
It is updated and working now. Yahoo changed the website to access the data. See the comment here for more details.
Scott
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.
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 Shopify
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.
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
Dear Jim:
Thank you very much for your sharing~
Is there a way to screen scrape the quarterly dividend rate and payment date without using IEX? None of the old methods work anymore.
Looks like the Yahoo API has been shut down?
Formulas aren’t working.
Tried PEG recently and received a “#N/A” error.
the formula dont works 🙁
Formulas are not working
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 mind
So Brilliant! Thanks for it. You may plan ahead by structuring and strategizing your content strategy. I appreciate your work.