Read stock prices from webpage

[cpampas]

dim url As String
    Dim xmlhttp As Object
    Dim st As Integer
    Dim Content As String
    Dim searchString As String
    Dim position As Long, exchange as string

url="MMM"
exchange="NYSE"

    url = "https://www.google.com/finance/quote/" & UCase(symbol) & ":" & exchange
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", url, False
    xmlhttp.Send
   
 
    st = xmlhttp.Status ' Capture the HTTP status code


    If st = 200 Then
        Content = xmlhttp.responseText
        searchString = "YMlKec fxKbKc"
        position = InStr(Content, searchString) + Len(searchString) + 1


        If position > 14 And position <= Len(Content) Then
            FetchNumericValue = Trim(mid(Content, position + 1, 10))
            FetchNumericValue = Trim(Left(FetchNumericValue, InStr(FetchNumericValue, "$") - 2))
            FetchNumericValue = Trim(FetchNumericValue)

            msgbox(FetchNumericValue )
        End If
    Else
        MsgBox (st & " - " & xmlhttp.StatusText)
    End If

    Set xmlhttp = Nothing

i have been using the above code to extract from https://www.google.com/finance/quote/ the current price of stocks . the code runs nicely and fast with no issues, Some days nevertheless it does not work and points to an error "Access Denied" , on xmlhttp.Send
Why would it work some days and not other?

I would prefer not the option to navigate method as it takes way more time to read the data.
I appreciate your help

 

[Edgar_]

Google does not like scrapers, so it's best not to rely on it to get your data. Find a different source.

 

[Gasman]

I use www.alphavantage.co with an excel workbook for my stocks.

 

[arnelgp]

i tested your code on A2021 and it does not have any problem, only the search string is not found.

 

[cpampas]

Gasman, Interesting the www.alphavantage.co API, it might be the solution for me, in the meantime
ArnelGP, yes the search string is not working with the symbol i mentioned, I think BA would work https://www.google.com/finance/quote/BA:NYSE

if i use MSXML2.ServerXMLHTTP i can avoid the error but, i noticed that the xmlhttp.responseText contains text of a IE consent cookies page
I tried accepting all cookies in the internet options of IE, but the error persists.

Any toughts
Thanks

 

[arnelgp]

you can also use Yahoo Finance:

Function Get3MStockPrice() As Double
    Dim xmlHttp As Object
    Dim htmlDoc As Object
    Dim stockPrice As String
    Dim content As String
    Dim i As Long
    ' Create a new XMLHTTP object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    
    ' Define the URL for 3M's stock on Yahoo Finance
    xmlHttp.Open "GET", "https://finance.yahoo.com/quote/MMM?.tsrc=fin-srch", False
    xmlHttp.send
    
    ' Check if request was successful (status code 200)
    If xmlHttp.Status = 200 Then
        content = xmlHttp.responseText
        
        i = InStr(1, content, "data-symbol=""MMM""")
        If i <> 0 Then
            i = InStr(i, content, ">")
            If i <> 0 Then
                content = Mid$(content, i + 1)
                Get3MStockPrice = CDec(Left$(content, InStr(1, content, "<") - 1))
            End If
        End If
    Else
        MsgBox "Error: Unable to retrieve data."
    End If
    
    ' Clean up
    Set xmlHttp = Nothing
    Set htmlDoc = Nothing
End Function

 

[cpampas]

Arnel,
That works pretty well, until now with no issues
Many Thanks