Dividends Excel Sheet Guide and Template

Dividends Excel Sheet

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").

Apple dividend yield excel

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.

Dividend yield for stocks in excel

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").

quarterly dividend yield excel

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").

Excel stock dividend payments

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.

Excel stock dividend template

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

Guillermo Valles

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 deals and learning the ropes.

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!

32 Responses

  1. 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.

  2. 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)

  3. Can the WISE dividend functions be used in the same spreadsheet alongside the built-in stock functions?

  4. 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

    1. 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 ๐Ÿ™‚

  5. 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!

  6. 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.

      1. 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!

          1. Thank you! Yea, I have just been deleting the ")" and the end and hitting enter to refresh manually. Appreciate the quick response.

  7. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts