Dividends Excel Sheet Guide and Template - Wisesheets Blog (2024)

• August 6, 2022

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("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!