Search code examples
excelvbawebxmlhttprequest

Excel VBA: get Yahoo Finance data on Mac

I am trying to get data from Yahoo Finance on Excel on Mac.

As far as I know, the usual approach to get web data on Mac is WebQuery. However, sometimes it works without issues, sometimes throws an error 1004 for the same set of tickers it worked before without issue. Text of the error: "Microsoft Excel cannot access the file %link%. There are several possible reasons:" error

I have no clue why does that happen. The only suggestion is because the URL does not contain a cookie / crumb Yahoo needs.

For testing purposes, I used WinHttpRequest on Windows. It works - Excel successfully gets the data. There's an alternative on Mac - Tim Hall's WebHelpers. I was able to get the cookie and the crumb on Mac with this great set of tools. But when I try downloading the CSV from Yahoo the response.Content has this string: {"finance":{"result":null,"error":{"code":"Not Acceptable","description":"HTTP 406 Not Acceptable"}}}.

Generally, I have several questions:

  1. Is there a way to add a cookie to the WebQuery approach? Still, I am not sure if that works and helps to evade the error.
  2. Why does Response return Error 406? Particularly this code snippet:
     client.BaseUrl = tickerURL
     request.Method = HttpGet
     request.Format = PlainText
     request.AddCookie "Cookie", cookie
     request.AddHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:46.0) Gecko/20100101 Firefox/46.0"

     Set response = client.Execute(request)
     resultFromYahoo = response.Content

Here's a code to receive Yahoo Finance data using either WinHttpRequest on Windows or Tim Hall's package on Mac:

Sub getYahooFinanceData(stockTicker As String, StartDate As String, EndDate As String, frequency As String, cookie As String, crumb As String)
' forked from:
' http://investexcel.net/multiple-stock-quote-downloader-for-excel/
    

    Dim resultFromYahoo As String
    Dim objRequest
    Dim csv_rows() As String
    Dim tickerURL As String


    'Make URL
    tickerURL = "https://query1.finance.yahoo.com/v7/finance/download/" & stockTicker & _
        "?period1=" & StartDate & _
        "&period2=" & EndDate & _
        "&interval=" & frequency & "&events=history" & "&crumb=" & crumb
    '***************************************************
               
         
               
    'Get data from Yahoo
    #If Mac Then

        Dim client As New WebClient
        Dim response As WebResponse
        Dim request As New WebRequest
        
        client.BaseUrl = tickerURL
        request.Method = HttpGet
        request.Format = PlainText
        request.AddCookie "Cookie", cookie
        request.AddHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:46.0) Gecko/20100101 Firefox/46.0"

        Set response = client.Execute(request)
        DoEvents
'' ERROR 406 on MAC ''
        If response.StatusCode = Ok Then
            resultFromYahoo = response.Content
        Else
            MsgBox "An error occured while getting data for " & stockTicker & "'", vbInformation
            Exit Sub
        End If
        
        
    #Else
        Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
        With objRequest
            .Open "GET", tickerURL, False
            .SetRequestHeader "Cookie", cookie
            .Send
            .WaitForResponse
            resultFromYahoo = .ResponseText
        End With
    #End If
    

    '***************************************************
    csv_rows() = Split(resultFromYahoo, Chr(10))
  
End Sub
Solution

  • Finally came to a solution! Found the answer in similar topic related to Python: https://stackoverflow.com/a/68259438/8524164

    In short, we need to modify user-agent and other request parameters to emulate a real browser. Instead of this one line:

        request.AddHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:46.0) Gecko/20100101 Firefox/46.0"

    We need to add 5 lines:

        request.UserAgent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36"
    request.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"
    request.AddHeader "Accept-Language", "en-US,en;q=0.5"
    request.AddHeader "DNT", "1"
    request.AddHeader "Connection", "close"

    The final sub:

    Sub getYahooFinanceData(stockTicker As String, StartDate As String, EndDate As String, frequency As String, cookie As String, crumb As String)
' forked from:
' http://investexcel.net/multiple-stock-quote-downloader-for-excel/

Dim resultFromYahoo As String
Dim objRequest
Dim csv_rows() As String
Dim tickerURL As String

'Make URL
tickerURL = "https://query1.finance.yahoo.com/v7/finance/download/" & stockTicker & _
    "?period1=" & StartDate & _
    "&period2=" & EndDate & _
    "&interval=" & frequency & "&events=history" & "&crumb=" & crumb
'***************************************************
             
'Get data from Yahoo
#If Mac Then

    Dim client As New WebClient
    Dim response As WebResponse
    Dim request As New WebRequest
    
    client.BaseUrl = tickerURL
    request.Method = HttpGet
    request.Format = PlainText
    request.AddCookie "Cookie", cookie
    request.UserAgent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36"
    request.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"
    request.AddHeader "Accept-Language", "en-US,en;q=0.5"
    request.AddHeader "DNT", "1"
    request.AddHeader "Connection", "close"

    Set response = client.Execute(request)
    DoEvents

    If response.StatusCode = Ok Then
        resultFromYahoo = response.Content
    Else
        MsgBox "An error occured while getting data for '" & stockTicker & "'", vbInformation
        Exit Sub
    End If
    
    
#Else
    Set objRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    With objRequest
        .Open "GET", tickerURL, False
        .SetRequestHeader "Cookie", cookie
        .Send
        .WaitForResponse
        resultFromYahoo = .ResponseText
    End With
#End If


'***************************************************
csv_rows() = Split(resultFromYahoo, Chr(10))

End Sub