How to Import Dividend Data into Excel

In this post, we will discuss how to download dividend information for stocks in Excel for free.

  Download Excel Macro File

This excel macro returns the following key metrics related to dividend.

Historical Dividend Data
Forward Annual Dividend Rate
Forward Annual Dividend Yield
5 Year Average Dividend Yield
Trailing Annual Dividend Rate
Trailing Annual Dividend Yield
Payout Ratio
Ex-Dividend Date

Go to the folder where the downloaded macro file is located.
Right-click the file and choose Properties from the menu.
At the bottom of the General tab, select the Unblock checkbox under security option and then click OK.
Open the file and specify the start and end dates in cells B4 and B5.
Enter the tickers (symbols) of the stocks for which you want dividend information, starting from cell A8.
Press the “Get Historical Data” button to import past dividend details for each stock.
Press the “Summary” button to import key dividend metrics for each stock.

Historical Dividend Data

Historical data including dividends and dividend dates will be provided for the period between these dates. Please note that the end date is not included in the range.

After clicking on the Get Historical Data button, dividend data will be downloaded from yahoo finance and will appear in the “HistoricalData” sheet tab.

This macro returns dividend information for hundreds of thousands of stocks from 48 countries. It is important to follow the ticker format used by Yahoo Finance. For US stocks, you just need to specify tickers. Whereas, for non-US stocks, you need to add suffix as shown in the table below.

Example : The ticker for Volkswagen is VOW. So we need to input VOW.DE as ‘DE’ refers to the exchange in Germany.

Market
Exchange
Suffix

Argentina
Buenos Aires Stock Exchange (BYMA)
.BA

Austria
Vienna Stock Exchange
.VI

Australia
Australian Stock Exchange (ASX)
.AX

Belgium
Euronext Brussels
.BR

Brazil
Sao Paolo Stock Exchange (BOVESPA)
.SA

Canada
Canadian Securities Exchange
.CN

Canada
NEO Exchange
.NE

Canada
Toronto Stock Exchange (TSX)
.TO

Canada
TSX Venture Exchange (TSXV)
.V

Chile
Santiago Stock Exchange
.SN

China
Shanghai Stock Exchange
.SS

China
Shenzhen Stock Exchange
.SZ

Czech Republic
Prague Stock Exchange Index
.PR

Denmark
Nasdaq OMX Copenhagen
.CO

Egypt
Egyptian Exchange Index (EGID)
.CA

Estonia
Nasdaq OMX Tallinn
.TL

Europe
Euronext
.NX

Finland
Nasdaq OMX Helsinki
.HE

France
Euronext Paris
.PA

Germany
Berlin Stock Exchange
.BE

Germany
Bremen Stock Exchange
.BM

Germany
Dusseldorf Stock Exchange
.DU

Germany
Frankfurt Stock Exchange
.F

Germany
Hamburg Stock Exchange
.HM

Germany
Hanover Stock Exchange
.HA

Germany
Munich Stock Exchange
.MU

Germany
Stuttgart Stock Exchange
.SG

Germany
Deutsche Boerse XETRA
.DE

Greece
Athens Stock Exchange (ATHEX)
.AT

Hong Kong
Hong Kong Stock Exchange (HKEX)***
.HK

Hungary
Budapest Stock Exchange
.BD

Iceland
Nasdaq OMX Iceland
.IC

India
Bombay Stock Exchange
.BO

India
National Stock Exchange of India
.NS

Indonesia
Indonesia Stock Exchange (IDX)
.JK

Ireland
Euronext Dublin
.IR

Israel
Tel Aviv Stock Exchange
.TA

Italy
EuroTLX
.TI

Italy
Italian Stock Exchange
.MI

Japan
Tokyo Stock Exchange
.T

Latvia
Nasdaq OMX Riga
.RG

Lithuania
Nasdaq OMX Vilnius
.VS

Malaysia
Malaysian Stock Exchange
.KL

Mexico
Mexico Stock Exchange (BMV)
.MX

Netherlands
Euronext Amsterdam
.AS

New Zealand
New Zealand Stock Exchange (NZX)
.NZ

Norway
Oslo Stock Exchange
.OL

Portugal
Euronext Lisbon
.LS

Qatar
Qatar Stock Exchange
.QA

Russia
Moscow Exchange (MOEX)
.ME

Singapore
Singapore Stock Exchange (SGX)
.SI

South Africa
Johannesburg Stock Exchange
.JO

South Korea
Korea Stock Exchange
.KS

South Korea
KOSDAQ
.KQ

Spain
Madrid SE C.A.T.S.
.MC

Saudi Arabia
Saudi Stock Exchange (Tadawul)
.SAU

Sweden
Nasdaq OMX Stockholm
.ST

Switzerland
Swiss Exchange (SIX)
.SW

Taiwan
Taiwan OTC Exchange
.TWO

Taiwan
Taiwan Stock Exchange (TWSE)
.TW

Thailand
Stock Exchange of Thailand (SET)
.BK

Turkey
Borsa İstanbul
.IS

United Kingdom
London Stock Exchange
.L

Venezuela
Caracas Stock Exchange
.CR

Note : The macro returns NA if no data is found on Yahoo Finance.

Key Dividend Metrics

Key statistics related to dividends are downloaded from Yahoo Finance when you click on the “Summary” button in Excel.

Forward Annual Dividend Rate : The total dividends a company is expected to pay in a year based on its current payout.
Forward Annual Dividend Yield : The expected annual dividend divided by the current stock price in terms of percentage.
5 Year Average Dividend Yield : The average yield of a company’s dividends over the past five years.
Trailing Annual Dividend Rate : The total dividends a company actually paid in the past one year.
Trailing Annual Dividend Yield : The actual annual dividends paid divided by the stock price in the past one year.

Sub GetData()
Dim InputControls As Worksheet
Dim OutputData As Worksheet
Dim symbol As String
Dim startDate As String
Dim endDate As String
Dim last As Double
Dim OffsetCounter As Double
Dim result As Integer
Dim rng As Range

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

‘Sheet Names
Set InputControls = Sheets(“Inputs”)
Set OutputData = Sheets(“HistoricalData”)

With InputControls
last = .Cells(.Rows.Count, “A”).End(xlUp).Row
End With

‘ Arguments
startDate = (InputControls.Range(“B4”) – dateValue(“January 1, 1970”)) * 86400
endDate = (InputControls.Range(“B5”) – dateValue(“January 1, 1970”)) * 86400
period = “1d”

Dateminus = -InputControls.Range(“B4”) + InputControls.Range(“B5”)

If InputControls.Range(“B5”) > Date Then
result = MsgBox(“EndDate seems greater than today’s date. Okay to you?”, vbYesNo, “Validate End Date”)
If result = vbNo Then
Exit Sub
End If

End If

If Dateminus = dividendsSectionEnd Then Exit Do
numEntries = numEntries + 1
position = InStr(currentDividendStart + 1, json, “{“) ‘ Move to the next opening brace for the next entry
Loop

‘ Redimension the array to store the results (3 columns: date, amount, symbol)
nColumns = 4
ReDim result(1 To numEntries, 1 To nColumns)

‘ Reset position to extract data
position = dividendsSectionStart

‘ Loop through each dividend entry
For entryIndex = 1 To numEntries
‘ Find the next opening brace for the current entry
currentDividendStart = InStr(position, json, “{“)
If currentDividendStart = 0 Or currentDividendStart >= dividendsSectionEnd Then Exit For

‘ Extract the date value
position = InStr(currentDividendStart, json, “””date””:”) ‘ Find the date key
If position = 0 Or position >= dividendsSectionEnd Then Exit For
dateValue = ExtractValue(json, “””date””:”, “}”, position)

‘ Extract the amount value
position = InStr(currentDividendStart, json, “””amount””:”) ‘ Find the amount key
If position = 0 Or position >= dividendsSectionEnd Then Exit For
amountValue = ExtractValue(json, “””amount””:”, “,”, position)

‘ Store the date, amount, and symbol in the result array
result(entryIndex, 1) = Format(CDate(CDbl(dateValue) / 86400 + DateSerial(1970, 1, 1)), “yyyy-mm-dd”)
result(entryIndex, 2) = amountValue
result(entryIndex, 3) = ExtractValue(json, “””currency””:”””, “”””)
result(entryIndex, 4) = symbol ‘ Add the symbol to the third column

‘ Move position to the next entry for the next loop
position = InStr(currentDividendStart + 1, json, “{“) ‘ Move to the next opening brace for the next loop
Next entryIndex

ExtractDividends = result
End Function

Read MoreListenData