How to Import Dividend Data into Excel

Deepanshu Bhalla 16 Comments

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

Update (31st October, 2024) : Fixed 404 error while extracting dividend summary information.
Features

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

  1. Historical Dividend Data
  2. Forward Annual Dividend Rate
  3. Forward Annual Dividend Yield
  4. 5 Year Average Dividend Yield
  5. Trailing Annual Dividend Rate
  6. Trailing Annual Dividend Yield
  7. Payout Ratio
  8. Ex-Dividend Date
How to Use Macro

To resolve the following error when opening the excel file, follow the steps below -

Microsoft has blocked macros from running because the source of this file is untrusted.
  1. Go to the folder where the downloaded macro file is located.
  2. Right-click the file and choose Properties from the menu.
  3. At the bottom of the General tab, select the Unblock checkbox under security option and then click OK.
  4. Open the file and specify the start and end dates in cells B4 and B5.
  5. Enter the tickers (symbols) of the stocks for which you want dividend information, starting from cell A8.
  6. Press the "Get Historical Data" button to import past dividend details for each stock.
  7. Press the "Summary" button to import key dividend metrics for each stock.
Import Dividend Data into Excel

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.
Historical Dividend Data in MS Excel

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.

Key statistics related to dividends 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.
VBA Code

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

Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 16 Responses to "How to Import Dividend Data into Excel"
  1. 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.

    ReplyDelete
    Replies
    1. Thanks for reporting the issue. However, I am getting 0.796 only irrespective of the position of this ticker.

      Delete
  2. A 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.

    ReplyDelete
  3. Thanks - 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

    ReplyDelete
    Replies
    1. I 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.

      Delete
  4. Yep - that fix worked perfectly. This is an amazing spreadsheet - thank you. !!!!

    ReplyDelete
  5. Mega thanks. Brilliant. Thank you SO much.

    ReplyDelete
  6. Am 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
    What is the fix? Thanks

    ReplyDelete
    Replies
    1. Which OS you are using? Mac or Windows? I can't reproduce this issue on Windows.

      Delete
    2. Try replacing ParseJson with WebHelpers.ParseJson in Historical Module and see if it works.

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

    ReplyDelete
  8. The 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???

    ReplyDelete
    Replies
    1. YF 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.

      Delete
    2. Yep 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
Next → ← Prev