我最近在尝试用Excel VBA抓取Barchart上AI股票板块的数据表，参照了一段能正常运行的Python脚本逻辑，但VBA代码始终返回401未授权错误，想请大家帮忙看看问题出在哪。

问题详情 #

我先通过GetCookies函数获取了页面的XSRF-TOKEN，然后带着这个请求头去调用数据接口，但接口返回401状态码，而相同逻辑的Python代码却能正常拿到200响应和数据。

我的VBA代码 #

Sub GetBarchart()
    Dim Cookies As Dictionary, token As String
    Dim url As String
    Dim HttpObj As Object
    Set HttpObj = CreateObject("WinHttp.WinHttpRequest.5.1")
    Set Cookies = GetCookies("https://www.barchart.com/investing-ideas/ai-stocks")
    token = Cookies("XSRF-TOKEN")
    Debug.Print "token", token
    
    url = "https://www.barchart.com/proxies/core-api/v1/quotes/get?list=stocks.us.fixed.ai&fields=symbol,symbolName,lastPrice&hasOptions=true&page=1&limit=100&raw=1"
    
    HttpObj.Open "GET", url, False
    ' SetHeaders
    HttpObj.setRequestHeader "Accept", "application/json"
    HttpObj.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/140.0.0.0 Safari/537.36"
    HttpObj.setRequestHeader "X-XSRF-TOKEN", token
    HttpObj.send
    
    Debug.Print "status", HttpObj.status
    Debug.Print HttpObj.responseText
End Sub

Function GetCookies(sURL As String, Optional dbg As Boolean = False) As Variant
    Dim winHttp As Object
    Dim sResponseHeaders As String, sCookie As String
    Dim aHeaders As Variant
    Dim i As Long
    Dim cookieParts() As String, cookieNameVal() As String, cookieName As String, cookieVal As String
    Dim Cookies As New Dictionary
    
    Set winHttp = CreateObject("winHttp.winHttpRequest.5.1")
    winHttp.Open "GET", sURL, False
    winHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.99 Safari/537.36 Mozilla/5.0"
    winHttp.send
    
    ' Get all response headers
    sResponseHeaders = winHttp.getAllResponseHeaders
    aHeaders = Split(sResponseHeaders, vbCrLf)
    
    For i = LBound(aHeaders) To UBound(aHeaders)
        ' get all the cookies
        If InStr(1, aHeaders(i), "Set-Cookie:", vbTextCompare) > 0 Then
            sCookie = Replace(aHeaders(i), "Set-Cookie: ", "", 1, 1, vbTextCompare)
            cookieParts = Split(sCookie, ";")
            If UBound(cookieParts) >= 1 Then
                cookieNameVal = Split(cookieParts(0), "=")
                If UBound(cookieNameVal) >= 1 Then
                    cookieName = cookieNameVal(0)
                    cookieVal = Replace(cookieNameVal(1), "%3D", "=")
                    Cookies.Add cookieName, cookieVal
                End If
            End If
        End If
    Next i
    
    Set winHttp = Nothing
    Set GetCookies = Cookies
End Function

VBA运行输出 #

token          eyJpdiI6ImhwN1FvN25GS2YyYW9PV1VaSkhQNFE9PSIsInZhbHVlIjoicXd6THFvU3hqbHhUU0Y3MDU0Q3U0WnRIMVF1TDRmUmhKeW1hTGJ4VzllSUN6VG5TOWJKKzNTK1Y4dHFOY054RVVRb1dUQjlLa1RWZzd6SkZrLzF2ckUzd0JxZFFsL09VWkFIVFlGL3FwK001Qk5qTDVubFYzWFl0a2ZjM3ZtOUUiLCJtYWMiOiJmM2RjNGY0YzZlMzA0N2I1ZjY1YzRlZGU4NWY4ODE2NDFiN2E4OTUzODRlM2I0ZDBhYThmMWNjNmZmMDJkN2Q5IiwidGFnIjoiIn0=
status         401
{"code":401,"message":"Unauthorized"}

可行的Python对比代码 #

import json
import requests
from urllib.parse import unquote

headers = {
    "User-Agent": "Mozilla/5.0 (X11; Ubuntu; Linux x86_64) Gecko/20100101 Firefox/86.0"
}
url = "https://www.barchart.com/proxies/core-api/v1/quotes/get?list=stocks.us.fixed.ai&fields=symbol,symbolName,lastPrice&hasOptions=true&page=1&limit=100&raw=1"

with requests.Session() as s:
    # get all cookies
    s.get(
        "https://www.barchart.com/investing-ideas/ai-stocks",
        headers=headers,
    )
    # use one cookie as HTTP header
    headers["X-XSRF-TOKEN"] = unquote(s.cookies["XSRF-TOKEN"])
    print("headers", headers)
    response = s.get(url, headers=headers)
    data = response.json()
    #data = s.get(url, headers=headers).json()
    print ("status", response.status_code)
    for d in data["data"]:
        print("{:<8}{:<50}{}".format(d["symbol"], d["symbolName"], d["lastPrice"]))

Python运行输出 #

headers {'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64) Gecko/20100101 Firefox/86.0', 'X-XSRF-TOKEN': 'eyJpdiI6IkN4YVN3ZTFHdktMMkQrYVpYcUEzVXc9PSIsInZhbHVlIjoiWFBmM1R4M1Zvay9Rb0ZrbTRJcFI5YUl2UGRPYmtqTFpYaTlYTTVMUFBHL21FTXJVa2tCNGc1VVdmbmoxajBVNjJtK3FaNGdieE9mVmxFYkdkNVBMbWh2MWhNREQxcG0vSGNER3F6Z2k5NkhaVmlyUjFSb2ppbVBWc0Y5eWVwc1EiLCJtYWMiOiI1ODJmN2QzMDhiZTRjYWYyZDRiMDk0OGJmYjljOTU2Mjk3YzEzMzgwZDk0YmU4ODc2ZmQ5ODhiOTczYWU5MjIwIiwidGFnIjoiIn0='}
status 200
AAPL     Apple Inc                                         245.27
ACN      Accenture Plc                                     240.94
ADBE     Adobe Systems Inc                                 337.51
AI       C3.Ai Inc Cl A                                    17.91
AMD      Adv Micro Devices                                 214.90
AMZN     Amazon.com Inc                                    216.37
ANET     Arista Networks Inc                               154.10

我自己排查的点 #

1. 已经确认XSRF-TOKEN能正确获取到，并且替换了%3D为=
2. 请求头里的User-Agent也设置了，和Python的只是浏览器版本不同
3. 接口URL完全一致

我猜测可能是VBA的WinHttp没有像Python的requests.Session那样自动携带所有必要的Cookie？或者还有其他隐藏的请求头没加上？希望大家能给点思路，谢谢！