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
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("COKE", "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!
22 Responses
Absolutely pent content, regards for entropy.
how about a feature that provides the dividend payments for a period (i.e. year to date)?
You can do =SUM(WISE("ticker","dividend",{"lq","lq-1","lq-2","lq-3"})) for that 🙂
How can I retrieve the next payment and ex dates for a dividend stock?
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.
Is there a formula we can use to retrieve the ex-date only?
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.
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.
What is the function/parameter to return the next dividend ex-date given a ticker symbol, say XXX?
Hi Eric we don't have a parameter like this yet. The closest we have is =WISEPRICE("Ticker", "Earnings Announcement").
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)
You can do =WISE("ticker", "dividend", "lq") * the payment frequency for this or =WISE("ticker", "dividend", 2022).
Can the WISE dividend functions be used in the same spreadsheet alongside the built-in stock functions?
Absolutely here is a good example =AVERAGE(WISEPRICE("aapl", "volume", 30)) to get the average for the past 30 days.
Hi,
How to measure the discounted cash flow for any stock?
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/
Hi, what is the function or how to get adjusted dividend for yearly basis?
You can do this =SUM(INDEX(WISEPRICE("aapl","dividend",,"01/01/2020","12/30/2020"),,3))
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
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 🙂
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!
You have to calculate it manually or use the past year as a reference for example =WISE("aapl", "dividend", 2022).