In this post, we will discuss how to download dividend information for stocks in Excel for free.
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
To resolve the following error when opening the excel file, follow the steps below -
- 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.
The following VBA code is used to fetch historical dividend data from Yahoo Finance into Excel.
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 < 1 Then
MsgBox ("Date difference must be atleast one. Since EndDate is not inclusive of the date, you can have one day difference between start and end Date to fetch latest price")
Exit Sub
End If
OutputData.Range("A2:H1000000").ClearContents
'Loop over multiple symbols
For i = 8 To last
symbol = InputControls.Range("A" & i).value
OffsetCounter = 1
Call ExtractData(symbol, startDate, endDate, OffsetCounter, OutputData)
Next i
Set rng = OutputData.Range("A:D") ' Change as needed to target specific rows
rng.RemoveDuplicates Columns:=Array(1, 3, 4), Header:=xlYes
Application.Calculation = xlCalculationAutomatic
OutputData.Select
End Sub
Sub ExtractData(Symbols As String, startDate As String, endDate As String, OffsetCounter As Double, OutputData As Worksheet)
Dim resultFromYahoo As String
Dim objRequest As Object
Dim tickerURL As String
Dim combinedData As Variant
' Construct the API URL for the ticker
tickerURL = "https://query1.finance.yahoo.com/v8/finance/chart/" & Symbols & _
"?events=capitalGain%7Cdiv%7Csplit&formatted=true&includeAdjustedClose=true&interval=1d&period1=" & startDate & _
"&period2=" & endDate
' Fetch data from the API
Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
With objRequest
.Open "GET", tickerURL, False
.send
.waitForResponse
resultFromYahoo = .responseText
End With
' Call the ExtractDividends function to get the data
combinedData = ExtractDividends(resultFromYahoo, Symbols)
' Check if no dividends were found
If Not IsEmpty(combinedData) And VarType(combinedData) = vbString Then
OutputData.Cells(OutputData.Cells(Rows.Count, 1).End(xlUp).Row + OffsetCounter, 1).value = "NA"
OutputData.Cells(OutputData.Cells(Rows.Count, 2).End(xlUp).Row + OffsetCounter, 2).value = "NA"
OutputData.Cells(OutputData.Cells(Rows.Count, 3).End(xlUp).Row + OffsetCounter, 3).value = "NA"
OutputData.Cells(OutputData.Cells(Rows.Count, 4).End(xlUp).Row + OffsetCounter, 4).value = Symbols
ElseIf IsArray(combinedData) Then
' Output the dividends to the sheet
OutputData.Range("A" & OutputData.Cells(Rows.Count, 1).End(xlUp).Row + OffsetCounter).Resize(UBound(combinedData, 1), UBound(combinedData, 2)).value = combinedData
End If
End Sub
Function ExtractValue(ByVal json As String, ByVal startTag As String, ByVal endTag As String, Optional ByVal startPos As Long = 1) As String
Dim startPosTag As Long
Dim endPosTag As Long
Dim extractedValue As String
' Find the start position of the value
startPosTag = InStr(startPos, json, startTag)
' If the start tag is not found, return an empty string
If startPosTag = 0 Then
ExtractValue = ""
Exit Function
End If
' Adjust startPosTag to point to the actual value
startPosTag = startPosTag + Len(startTag)
' Find the end position of the value
endPosTag = InStr(startPosTag, json, endTag)
' If the end tag is not found, return an empty string
If endPosTag = 0 Then
ExtractValue = ""
Exit Function
End If
' Extract the value between the start and end tags
extractedValue = Mid(json, startPosTag, endPosTag - startPosTag)
ExtractValue = Trim(extractedValue)
End Function
Function ExtractDividends(ByVal json As String, ByVal symbol As String) As Variant
Dim result() As Variant
Dim dividendsSectionStart As Long
Dim dividendsSectionEnd As Long
Dim position As Long
Dim amountValue As String
Dim dateValue As String
Dim nColumns As Long
Dim numEntries As Long
Dim entryIndex As Long
Dim currentDividendStart As Long
Dim currentDividendEnd As Long
' Find the start of the "dividends" section
dividendsSectionStart = InStr(json, """dividends"":{")
If dividendsSectionStart = 0 Then
ExtractDividends = "No dividends found."
Exit Function
End If
' Find the end of the "dividends" section
dividendsSectionEnd = InStr(dividendsSectionStart, json, "}}")
If dividendsSectionEnd = 0 Then
ExtractDividends = "No dividends found."
Exit Function
End If
position = dividendsSectionStart
entryIndex = 1
numEntries = 0
' Count the number of entries
Do
currentDividendStart = InStr(position, json, "{") ' Move to the next opening brace
If currentDividendStart = 0 Or currentDividendStart >= 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
Hi Deepanshu, thanks so much for putting in the effort for this. Will be super useful to so many people. I think there be a small bug in your macro - not sure exactly how to fix it, but I used the ticker VTI. And I had the Start date as Aug 23, 2022 and End date of Sept 23, 2024 and when I clicked Historical Info, I get a duplicate entry. for Sept 23, 2022 - one shows $0.80 and the second for the same date shows $0.796 (the second agrees with what Yahoo has). I can send you a screenshot, but not sure how to attach it to this post.
ReplyDeleteThanks for reporting the issue. However, I am getting 0.796 only irrespective of the position of this ticker.
DeleteA little more info on the bug that I reported - what's strange is that it appears to only happen to VTI when it is in the first position in the list of tickers. When I put a different ticker first, then I don't get that duplicate.
ReplyDeleteThanks - very strange indeed. Is there a way I can share my spreadsheet with you (which is just the same one I downloaded from you), and the only thing I changed is the date range and the ticker? Not a huge deal, I can always just move the position of the symbol and it seems to resolve itself. I re-downloaded the sheet again from your link above and still getting the same behaviour with the duplicate $0.80
ReplyDeleteI guess the problem lies in underlying data.. It's easy to fix. Replace Array(1, 2, 3, 4) with Array(1, 3, 4) and it should fix the duplicate issue.. Let me know if it works for you.
DeleteYep - that fix worked perfectly. This is an amazing spreadsheet - thank you. !!!!
ReplyDeleteCool. Cheers!
DeleteMega thanks. Brilliant. Thank you SO much.
ReplyDeleteI am glad you found it useful. Cheers!
DeleteAm looking at the Import Dividend Data into Excel Worksheet. am getting an error in the Historical module... in the Sub ExtractData... the line Set json = ParseJson(response.Content) gives an error during runtime. The error is Compile error: Ambiguous Name Detected: ParseJson
ReplyDeleteWhat is the fix? Thanks
Which OS you are using? Mac or Windows? I can't reproduce this issue on Windows.
DeleteTry replacing ParseJson with WebHelpers.ParseJson in Historical Module and see if it works.
DeleteThank you for quick response. Qualifying by changinging to WebHelpers.ParseJson seems to do the trick. It worked last night, but dd not this morning. But appears to be fixed now. By the way am using Windows 11 and Excel 2021.
ReplyDeleteThe WebHelpers fix still works after a day (Great!), but today the "Summary" button does not work. The macro ends with no data and a MsgBox with Error Code: 404... At the same time the "Get Historical Data" macro works just fine...Ideas???
ReplyDeleteYF made a few changes to the backend so I updated the code accordingly. Please download the latest file and try again. Let me know if it works for you.
DeleteYep it works now. However it is too bad that Yahoo does'nt provide some of the performance data for Mutual Funds and ETFs. But thanks for the quick fix...
Delete