Get historical dividends for many companies, and generate a payout stream cross-referenced against time. Free spreadsheet!
Update on May 2nd 2022: new version that works past changes by Yahoo
Update on April 14th 2018: modified to sidestep a few changes made by Yahoo
Update on June 18th 2017: now works with the new Yahoo Finance system
This Excel spreadsheet downloads historical dividends for many companies. It’s a companion sheet to the ever-popular Bulk Stock Quote Downloader, and is as simple-to-use as its counterpart. Get this free spreadsheet and learn how to download dividend data.
Simply enter a start and end date, a list of ticker symbols, and click the “Get Bulk Dividends” button.
Some clever VBA whirs away, and interrogates the Yahoo Finance web service. You get the historical dividend data for each ticker in its own sheet.
If you check Collate Data, all the dividends are collected onto one sheet. This gives you a stream of dividends over time, cross-referenced against the payout date.
So what can you do with this tool. You can identify groups of companies with a history of regular (and rising) dividend payouts.This provides points to mature financial management, and the long-term financial security of the business. Stocks like this are the bedrock of any long-term investment portfolio
Moreover, stock with higher dividends are generally less volatile than those with little or no history of paying divdends. That is the change in stock price plus the dividend payouts – can often be less volatile than the change in stock price itself . This protects your downside when markets are volatile.
Moreover, in the US, Canada and other countries, dividends are taxed at a lower rate than interest or salary income. So by targeting stocks with higher dividends, you minimize your tax . For example, in Canada, GIC bond income is taxed at your marginal tax rate. However, dividend income benefits from the “gross up” tax credit system that can cut the tax paid by about half.
You can view and edited the spreadsheet VBA. Feel free to learn from the code. I’d appreciate a link to https://investexcel.net if you find this spreadsheet of value.
Get Excel Spreadsheet to Download Dividends for Multiple Companies
Hey Samir,
Thank you very much for all of the work you have done here. Currently I am having problems because your code is writing it to excel, not just the csv file. So when I have 7k tickers to download, it is trying to add the output to excel pages as well which is freezing up excel for me. How do I comment out that code and which one is it. Simply I just want to write directly to the csv without seeing any output in excel?
Thanks!
The code needs to see the data on the “excel pages” (actually called “sheets”) before the data is written to CSV. You can’t completely disable the creation of the sheets. It’s not a simple case of commenting out code.
However, you could
*download the data for a single ticker to a sheet
*write out the data from the sheet to a CSV file
*delete the sheet
*continue for the other tickers listed.
This would require some rewriting the code (BTW you wouldn’t be able to use the “Collate” function”). I’ll do it for you if you donate $25 via the “Donate” button on the top-right.
Hey Samir,
That definitely sounds good to me; you have done a lot of work anyway and deserve it. The only problem is I do not see a “Donate” button. If you just email me your PayPal email or the donate page URL I will send it now.
Thanks again!
The Donate button is on the top-right of the website.
Thanks for fixing that 😉
Also I forgot to mention, the payment will be from my business account “Plutoa.”
I’ve modified the spreadsheet so that it deletes sheets as soon as it’s written out a CSV file. Get it here: https://investexcel.net/wp-content/uploads/2014/08/Bulk-Dividend-Downloader-NO-Collate-ONLY-CSV.zip
Hey Samir,
Great job! Thank you for doing that for me. Also, I am sure this will help many others along the way. I was wondering if you would actually do me one last favor (my fault to not have asked before); to delete the dates and only have the ticker in the file name (AAPL.csv), along with deleting the titles inside the file (Historical Dividends for “…” (A1), Date (A2), and Dividends (B2))? This way it would simply be:
A1 = 2014/07/08 (date)
A9 = 2012/08/09 (date)
B1 = 0.47 (dividends)
B9 = 0.37857 (dividends)
You’re awesome, thanks again!
Hey Samir,
Are you able to do that for me?
So I guess that is a “No!”
Thank you for the excel sheet with the stock prices. It was so good, and it saved me alot of time on a school assignment. But i have a question
How to i download the index for OBXBID.OL?
Is it possible? Because the assignemt is to compare different stocks to the index.
Sincerly Daniel from Norway
Look at http://finance.yahoo.com/q/hp?s=OBXBID.OL+Historical+Prices. I don’t think Yahoo Finance has historical data for that ticker
Hi, Samir Khan
Thanks a lot for your great job
Is there any chance, that you have the same spreadsheet but for downloading split’s history?
I’ve tested this sheet a few times, and it does download the Yahoo dividend data, onto 26 sheets! That is really great, and I just looked at the collated dividends sheet, but nothing is there. Perhaps the number of tickers was too much?
Anyways, a nice effort that can be modified.
I found that I had tried to be clever, and inserted =NOW() into cell B6 on the Parameters sheet. That caused a problem when the vlookup ran for collated data. So, your worksheet and code work fine.
Great work but I need to import into MS Access database with the 3 fields – Date, Symbol, Dividend – So all dividend payments would have to be listed in one worksheet with these 3 columns only like this :
Date | Symbol | Dividend
01/01/12 | AGD | .14
01/02/12 | GNT | .11
01/02/12 | BX | .09
Can you do that and how much would it cost
I need this very much
Thank You
Hi Samir,
This is a great file, any chance you are going to update it to link to the changes that Yahoo made? I know that you already updated other files and now those are linking back to Yahoo. Any plans to update this one?
Thanks,
Marc