- Last Updated: August 24, 2024
Do you want to get started tracking dividends on Excel? Whether you're a beginner or an experienced investor, this guide will show you how to get stock dividend data onto your spreadsheet. We'll also provide a free template that you can use to get started right away!
- How to get dividend yield and dividend payment data in Excel
- Dividend yield and dividend payment amount on Excel Stocks?
- Real-time dividend yield in Excel
- Historical dividend yield in Excel
- Historical stock dividend payments in Excel
- Free dividend payment analysis Excel spreadsheet
- How to find the best dividend stocks using Excel
- Conclusion
Table of Contents
- How to get dividend yield and dividend payment data in Excel
- Dividend yield and dividend payment amount on Excel Stocks?
- Real-time dividend yield in Excel
- Historical dividend yield in Excel
- Historical stock dividend payments in Excel
- Free dividend payment analysis Excel spreadsheet
- How to find the best dividend stocks using Excel
- Conclusion
How to get dividend yield and dividend payment data in Excel
Unfortunately, there is no easy free way to get dividend yield and payment data on your Excel spreadsheet. The most common method is to copy-paste data from a site like Yahoo Finance, but this tends to be inefficient because the dividend yield of stocks changes all the time with the price, and this approach limits your ability to analyze hundreds of stocks at once.
A better way is to use an API and use code programmatically to get and update the data on your spreadsheet, but APIs are generally expensive, and you need a good level of coding knowledge to be able to retrieve the data in the way that you want.
The best alternative, which will be discussed in this article, is to use Wisesheets, an Excel and Google Sheets add-on that allows you to get the dividend yield and dividend payments for thousands of stocks in a simple function call.
Dividend yield and dividend payment amount on Excel Stocks?
Excel Stocks and the STOCKHISTORY function do not include dividend yield and historical dividend payments, but luckily, you can get this data very easily using the method described below.
Real-time dividend yield in Excel
In order to access the real-time dividend yield of a stock or ETF across 50+ exchanges, all you have to do is enter the following formula on any Excel cell:
=WISE("ticker", "dividend yield", "ttm").
For example, if you would like to get the dividend yield of Apple, you need to use the following function call =WISE("AAPL", "dividend yield", "ttm").
As you can see, this will return Apple's latest dividend yield based on the last dividend payment made and the current price of the stock.
You can apply the same function call to many stocks at once, referencing cells from a list of stocks, as you can see below.
This allows you to analyze all these stocks at once in a single spreadsheet view.
Refreshing the data is very simple all you have to do is click enter on the cell or drag the formula down again for it to refresh across multiple tickers.
Historical dividend yield in Excel
Accessing the historical dividend yield of particular stocks is not much different. You can access the historical dividend yield annually when the financial statements were released by using =WISE("AAPL", "dividend yield", year) for annual data or =WISE("AAPL", "dividend yield", year, "quarter") for quarterly data.
For example, to get Apple's dividend yield in the second quarter of 2021, you must enter =WISE("AAPL", "dividend yield", 2021, "q2").
As you can see, this will return the dividend yield based on the dividend payment made at the time and the closing price when the financial statements were released.
Historical stock dividend payments in Excel
Getting historical dividend payment data is super easy. All you have to do is enter =WISEPRICE("ticker", "dividend").
For example, to see the dividend payments Coca-Cola has made to shareholders, you need to enter =WISEPRICE("co*kE", "dividend").
This will allow you to see all the dividends paid by Coca Cola including the date, ex-date, dividend payment, and declaration date going back all the way to 1985. From here, you can make any calculations you'd like, such as calculating the dividend payment growth at different time periods, and you can even graph this data to visualize the dividend payment trajectory.
Free dividend payment analysis Excel spreadsheet
To make the process of getting familiar with these formulas and help you access stock dividend data a lot faster on Excel, you can get started with this template for free.
This template provides you with important information such as the current dividend yield of a stock, the expected dividend payment from the company as well as the dividend payment growth in the last 5 years.
The best part is that you can change the ticker and get all this data for any company you'd like at once.
Altogether this represents a powerful template to give you an edge when looking at many dividend stocks and save you countless hours of time getting the data and making calculations.
Using Wisesheets, you can access many other prebuilt stock analysis templates that go beyond dividend stocks, such as DCFs, watchlists, live stock trackers, and more.
You can sign up for a free trial here.
How to find the best dividend stocks using Excel
Now that you know how to access dividend data in Excel, it's time to learn how to find the best dividend stocks.
Here are a few key things you should look for:
– A high and sustainable dividend yield
– A long history of consistent dividends payments
– A strong balance sheet with little debt
– A history of dividend growth
With Wisesheets, you can screen find all this information using the add-ons formulas, but more importantly, you can build your own screener to filter through stocks based on different criteria such as dividend yield, market cap, free cash flow, dividend growth, and more.
This is a great way to save time and find the best dividend stocks for your portfolio.
Conclusion
In this article, you learned how to get real-time and historical dividend data on Excel using Wisesheets and how to find the best dividend stocks by building your own screener.
You can also access a free dividends analysis Excel template that you can use to get started with your own analysis.
What are your thoughts on dividend investing? Let us know in the comments below.
Happy dividends Excel-ing!
Guillermo Valles
|Website
Hello! I'm a finance enthusiast who fell in love with the world of finance at 15, devouring Warren Buffet's books and streaming Berkshire Hathaway meetings like a true fan.
I started my career in the industry at one of Canada's largest REITs, where I honed my skills analyzing and facilitating over a billion dollars in commercial real estate deals.
My passion led me to the stock market, but I quickly found myself spending more time gathering data than analyzing companies.
That's when my team and I created Wisesheets, a tool designed to automate the stock data gathering process, with the ultimate goal of helping anyone quickly find good investment opportunities.
Today, I juggle improving Wisesheets and tending to my stock portfolio, which I like to think of as a garden of assets and dividends. My journey from a finance-loving teenager to a tech entrepreneur has been a thrilling ride, full of surprises and lessons.
I'm excited for what's next and look forward to sharing my passion for finance and investing with others!
35 Responses
-
Absolutely pent content, regards for entropy.
Reply
-
how about a feature that provides the dividend payments for a period (i.e. year to date)?
Reply
-
You can do =SUM(WISE("ticker","dividend",{"lq","lq-1","lq-2","lq-3"})) for that 🙂
Reply
-
-
How can I retrieve the next payment and ex dates for a dividend stock?
Reply
-
Hi Kevin, we will add the next payment to our data. For the ex-dates, you can do =WISEPRICE("ticker", "dividend"), and the "Date" column is the ex-date.
Reply
-
Is there a formula we can use to retrieve the ex-date only?
Reply
-
-
-
can I use this function on any excel sheet I create for any ticker symbol? I tried using the trial data for TSLA and it reports back unavailable.
Reply
-
Hi Erik, you can access all the data and functionalities on the trial for Apple, Tesla, and Amazon. Could you send us an email at info@wiseshets.io with some screenshots? We are happy to help.
Reply
-
-
What is the function/parameter to return the next dividend ex-date given a ticker symbol, say XXX?
Reply
-
Hi Eric we don't have a parameter like this yet. The closest we have is =WISEPRICE("Ticker", "Earnings Announcement").
Reply
-
-
Do you have a way to get the Indicated Annual Dividend (IAD)? Its the derived total dividend amount (usually its calculated by multiplying Latest Dividend Amount and Dividend Frequency)
Reply
-
You can do =WISE("ticker", "dividend", "lq") * the payment frequency for this or =WISE("ticker", "dividend", 2022).
Reply
-
-
Can the WISE dividend functions be used in the same spreadsheet alongside the built-in stock functions?
Reply
-
Absolutely here is a good example =AVERAGE(WISEPRICE("aapl", "volume", 30)) to get the average for the past 30 days.
Reply
-
-
Hi,
How to measure the discounted cash flow for any stock?Reply
-
We recommend using the stock analysis template or DCF template available on Wisesheets and then make any assumptions or modifications to calculate the DCF.
Here is a useful article on this: https://blog.wisesheets.io/free-dcf-template-excel-and-how-to-use-it/
Reply
-
-
Hi, what is the function or how to get adjusted dividend for yearly basis?
Reply
-
You can do this =SUM(INDEX(WISEPRICE("aapl","dividend",,"01/01/2020","12/30/2020"),,3))
Reply
-
-
Hi Guillermo, I have a time series with daily stock price data, I want to add the dividend declared or paid amount in the same daily time series (add a column which returns a zero in case no dividend was declared or paid and a dividend amount in case a dividend was declared or paid). I there a simple way to do that?
So the following columns: date, stock price, dividend.
I would like to calculate the dividend reinvested return or total shareholders return on a daily or cumulative daily basis.
Thank.
Menno
Reply
-
Hi Menno, the best way to do this is to use the WISEPRICE function like this =WISEPRICE("ticker", "dividend") and something like =WISEPRICE("ticker", "close", 30) and then create a pivot table with this data where you can perform these calculations 🙂
Reply
-
-
Can you pull the annual dividend (based on the latest payment) directly or do you have to manually calculate it (multiply latest payment by payment frequency)? Thanks!
Reply
-
You have to calculate it manually or use the past year as a reference for example =WISE("aapl", "dividend", 2022).
Reply
-
-
Hello — this API no longer seems to be working in my Google Sheets. I've pasted it just as shown in the blog text above:
=WISE("ticker", "dividend yield", "ttm")Any advice? Many thanks.
Reply
-
Just make sure to be logged into the add-on and enter a ticker for example =WISE("aapl", "dividend yield", "ttm") 🙂
Reply
-
-
How do I get just one line of dividend x-date, pay date, etc. I do not want the history, just the most recent data.
Reply
-
To get the latest dividend info, you can use this formula: =INDEX(WISEPRICE("aapl","dividend"),2) the first column is the ex-date.
Reply
-
Hi Guillermo…
On the =INDEX(WISEPRICE("aapl","dividend"),2) latest…Does this auto refresh each month/quarter or do I have to manually refresh (reload by retyping) each individual line? I notice that I dont see this area changing in my spreadsheet.
Just curious on the refresh updates, when you have like 40+ line items…
Thanks!Reply
-
This does not auto-refresh. You can delete the cells and then undo them using control z to get them to refresh fast.
Reply
-
Thank you! Yea, I have just been deleting the ")" and the end and hitting enter to refresh manually. Appreciate the quick response.
-
No problem 🙂
-
-
-
-
-
I get this error, #NAME?, when trying to do this…what am I doing wrong? Latest Excel, new PC…and get this same eror for all the above suggested formulas. Appreciate any help, email is whackedmytoe2@ the g mail
Reply
-
Could you email us at info@wisesheets.io? We are happy to help
Reply
-
-
Hi Guillermo
Is there any issue with the =INDEX(WISEPRICE("aapl","dividend"),2) formula? I'm not sure but if I refresh them its #REF! on refresh, but the formula shows the same…. same result on all my tabs in the spreadsheet. Just curious if something might be off with the application itself.Thanks
JasonReply
-
Nevermind on last post, somehow my spreadsheet removed the add-in and signed me out… so all good now. 😉
Jason
Reply
-
Thanks for letting us know 🙂
Reply
-
Related Posts
Yahoo might not be the choice for the ideal search engine anymore, and let’s
Read More »
January 24, 2022 4 Comments
Yahoo Finance APIs are libraries, or APIs, or methods to obtain historical and real-time
Read More »
January 12, 2022 No Comments
What Is Net Income Growth, and How to Calculate It?
If you're an investor, you'll want to know about net income growth and how
Read More »
April 10, 2023 No Comments