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…
Latest stock price:
Quarterly Dividend Amount:
Annual Dividend Yield:
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:
5-Year Dividend CAGR:
Dividend yield + 5-yr Div CAGR
Compound Annual Growth Rate:
E2 = current annual dividend
D2 = initial annual dividend
C2 = date initially purchased