Html - Using VBA to extract financial data from webpage in to excel

BhavikJain
June 21, 2023
313 views
0 votes
2 Answers

I’m working on a project to automate the extraction of data of how the various stock indices are performing into Excel from Yahoo Finance. But the problem is that all the code and help videos I have found online use Internet Explorer as their search engine, but as it is no longer available, I cannot use their code. Even if I modify the code to use google chrome as the browser, it does not work.

And can the macro be in such a way that It can extract data from multiple webpages at once?

So can anyone help create a code that will extract the historical data of how the multiple stock indices have performed every week in such a way that I can use it to get the data every week by just running the macro?

Just some example links-

https://finance.yahoo.com/quote/INDEX?p=INDEX&.tsrc=fin-srch

https://finance.yahoo.com/quote/VOO?p=VOO&.tsrc=fin-srch

Some codes i found online that might provide a base for it –

here, hereUse Excel VBA to Extract Data From a Webpage

Answers

VBA might not be the best tool. Anyway, just for fun, i’ve wrote down a really blunt code:

Option Explicit
Public Sub Download()
    
    'Declarations.
    Dim xmlhttp As New MSXML2.XMLHTTP60
    Dim StrURL As String
    Dim StrResult As String
    Dim StrMessageTitle As String
    Dim StrMessageContent As String
    Dim VarResult() As Variant
    Dim RngResult As Range
    Dim DblCounter As Double
    
    'Settings.
    Set RngResult = Range("A1")
    
    'YOU CAN SET HERE THE LINK TO THE CHOSEN PAGE. _
    THESE ARE THE LINKS FOR YOUR EXAMPLES (THE SECOND ONE IS TURNED OFF). _
    YOU CAN FIND THEM BY SEARCHING THE CONSOLE OF YOUR BROWSER OPENED ON THE GIVEN PAGE.
    StrURL = "https://finance.yahoo.com/quote/VOO?p=VOO&.tsrc=fin-srch"
    'StrURL = "https://finance.yahoo.com/quote/INDEX?p=INDEX&.tsrc=fin-srch&guccounter=1"
    
    'Clear the column of RngResult.
    RngResult.EntireColumn.ClearContents
    
    'Setting the request.
    xmlhttp.Open "GET", StrURL, False
    
    'Sendig the request.
    xmlhttp.send
    
    'Checking if there was a response.
    If xmlhttp.responseText <> "" Then
        
        'Example of single data extractions.
        StrMessageTitle = Split(xmlhttp.responseText, "<title>")(1)
        StrMessageTitle = Split(StrMessageTitle, "</title>")(0)
        StrMessageTitle = HtmlDecode(StrMessageTitle)
        
        StrMessageContent = Split(xmlhttp.responseText, "<fin-streamer class=""Fw(b) Fz(36px) Mb(-4px) D(ib)""")(1)
        StrMessageContent = Split(StrMessageContent, "</fin-streamer>")(0)
        StrMessageContent = Split(StrMessageContent, ">")(1)
        
        MsgBox StrMessageContent, vbOKOnly, StrMessageTitle
        
        
        'Reporting the entire response.
        StrResult = xmlhttp.responseText
        
        ReDim VarResult(1 To UBound(Split(StrResult, ">")), 1 To 1)
        
        For DblCounter = 0 To UBound(Split(StrResult, ">")) - 1
            
            VarResult(DblCounter + 1, 1) = Split(StrResult, ">")(DblCounter) & ">"
            
        Next
        
        Application.ScreenUpdating = False
        
        For DblCounter = 1 To UBound(VarResult)
            
            RngResult.Offset(DblCounter - 1, 0).Value2 = VarResult(DblCounter, 1)
            
        Next
        
        Application.ScreenUpdating = True
        
    End If
    
End Sub
Function HtmlDecode(str)
    Dim dom

    Set dom = CreateObject("htmlfile")
    dom.Open
    dom.Write str
    dom.Close
    HtmlDecode = dom.body.innerText
    
End Function

You’ll need the Microsoft XML v6.0 reference.

Credits: the function HtmlDecode was created by trincot and published here.

Something like this, I think, but the table isn’t being parsed correctly.

Sub ImportStockIndexData()
Dim i As Integer
Dim indexTickers() As Variant
Dim indexData As Variant
Dim dataRange As Range

' Define the ticker symbols for the stock indexes
indexTickers = Array("^GSPC", "^IXIC", "^DJI") ' Example tickers

' Set the range where the data will be imported
Set dataRange = ThisWorkbook.Sheets("Sheet1").Range("A1")

' Loop through each ticker symbol
For i = LBound(indexTickers) To UBound(indexTickers)
    ' Assemble the URL for the Yahoo Finance page of the stock index
    Dim url As String
    url = "https://finance.yahoo.com/quote/" & indexTickers(i) & "/history?p=" & indexTickers(i)
    
    ' Create a new instance of Internet Explorer
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False ' Set to True for debugging purposes
    
    ' Navigate to the Yahoo Finance page
    IE.Navigate url
    
    ' Wait for the page to load
    Do While IE.Busy Or IE.ReadyState <> 4
        DoEvents
    Loop
    
    ' Get the table containing the historical data
    Dim table As Object
    Set table = IE.Document.getElementById("mrt-node-Col1-1-HistoricalDataTable")
    
    ' Check if the table exists
    If Not table Is Nothing Then
        ' Get the data from the table
        indexData = table.outerHTML
        
        ' Import the data into Excel
        dataRange.Value = indexData
        
        ' Move the data range to the next column
        Set dataRange = dataRange.Offset(0, 1)
    Else
        MsgBox "Table not found for ticker " & indexTickers(i)
    End If
    
    ' Close Internet Explorer
    IE.Quit
    Set IE = Nothing
    
    ' Wait for a moment before processing the next ticker
    Application.Wait (Now + TimeValue("0:00:02"))
Next i
End Sub

Remember, under Tools > References, set a reference to ‘Microsoft Internet Controls’.

Please signup or login to give your own answer.

Click here to cancel reply.

Html – Using VBA to extract financial data from webpage in to excel

Answers