我正在尝试在 Mac 上的 Excel 上从雅虎财经获取数据。

据我所知，在 Mac 上获取网络数据的常用方法是 WebQuery。但是，有时它可以正常工作，有时会为之前没有问题的同一组代码抛出错误 1004。错误文本： "microsoft Excel cannot access the file %link%. There are several possible reasons:"

我不知道为什么会发生这种情况。唯一的建议是因为 URL 不包含雅虎需要的 cookie/面包屑。

出于测试目的，我在 Windows 上使用了 WinHttpRequest。它有效 - Excel 成功获取数据。 Mac 上有一个替代方案 - Tim Hall 的 WebHelpers。我能够使用这套很棒的工具在 Mac 上获取饼干和面包屑。 但是当我尝试从 Yahoo 下载 CSV 时，response.Content 有这个字符串：{"finance":{"result":null,"error":{"code":"Not acceptable","description":"HTTP 406 Not acceptable"}}}。

总的来说，我有几个问题：

1. 有没有办法向 WebQuery 方法添加 cookie？不过，我不确定这是否有效并有助于避免错误。
2. 为什么 Response 会返回错误 406？特别是这个代码片段：

     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

这是使用 Windows 上的 WinHttpRequest 或 Mac 上的 Tim Hall 软件包接收雅虎财经数据的代码：

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