This Excel spreadsheet downloads historical dividend data and calculates annual dividend growth rates for a set of user-specified stock tickers.
You can analyze a single company, or a hundred companies – you just need their ticker symbols.
Companies that have stable, long-term dividend growth rates form the core of many value-oriented investment portfolios. You can use this spreadsheet to filter for companies that fit this profile
Additionally, the dividend discount method of stock valuation requires estimates of long-term dividend growth rates.
Watch me use this spreadsheet in this video.
Just follow these steps to use this tool
- Enter a set of tickers (you could enter one or over fifty)
- Enter a start year and an end year (data will be downloaded from 1st January of the start year to 31st December of the end year)
- Optionally, check “Write to CSV”, “Collate Data” or “Dividend Growth Rate”
- Click “Get Bulk Dividends”
The spreadsheet then downloads historical dividend data from Yahoo Finance. Since most companies pay dividends quarterly, you’ll find that you usually get four payments per year
If “Dividend Growth Rate” is checked, then the VBA performs a few extra operations. The code
- adds up the quarterly data to give yearly data (this is what most investors are interested in)
- calculates the annual dividend growth rate using this formula (where Dn is dividend in year n, and Dn-1 is the dividend in year n-1)
- calculates the arithmetic average annual dividend
- and also calculates the compound annual growth rate of the final year’s dividend DN with respect to the first year’s dividend D1.
Here’s a typical report for Exxon Mobil (ticker: XOM). We analyze the dividends paid between 2000 and 2014
Between 2000-2014, the average growth rate was 0.084 (or 8.4 %). The CAGR between the first and last annual dividends was 0.076 (7.8%).
You can now use Excel’s functionality to analyze and visualize the information in these reports. For example, you could chart the growth rates.
You can run these reports for a single company, or for a hundred companies.
Download Excel Spreadsheet to Analyze Historical Dividend Payments
Samir,
This is great, it saved me so much work and research how to do it in the first place.
This is a great site.
Thank you so much
A question:
do you have a plan to consolidate all the data automatically on a single page?
I don’t specifically plan to return to this spreadsheet unless there is a good reason.
Really great job and useful.
I would like to add some features to the VB module.
Is it possible to have the psw to access the code please?
Thanks in advance
Great Workbook!!
There is some other functionality I would love to add to it.
How do I go about obtaining the VBA password to do this?
can you pull data on Canadian companies somehow?
Only the stocks that Yahoo Finance for which Yahoo Finance allows API access. That may or may not include Canadian companies – you’d have to try
I downloaded this spreadsheet and it worked great, I loved it.
But it now fails to find any of the Stocks in the search list.
What happened?
Same here