Excel VBA爬取Investing.com遇403错误，求实现Google登录方案

问题分析 #

403错误是因为Investing.com的反爬机制识别出请求并非来自正常浏览器会话，且部分历史数据接口要求登录状态才能访问。直接用MSXML2.XMLHTTP60发起请求无法携带登录后的会话Cookie，也无法通过简单请求头绕过检测。

Google登录的实现思路 #

直接在VBA中实现Google OAuth2认证流程非常复杂（涉及令牌授权、回调验证等），最简便的方案是用浏览器自动化（如IE/Edge）模拟人工登录流程，保留登录后的会话状态，再进行数据爬取。

解决方案代码（IE自动化版） #

首先需添加引用：Tools > References 勾选 Microsoft Internet Controls 和 Microsoft HTML Object Library

Option Explicit

Sub Export_Table_With_Login()
    Dim ie As InternetExplorer
    Dim htmlDoc As MSHTML.HTMLDocument
    Dim ieTable As MSHTML.HTMLTable
    Dim element As MSHTML.HTMLElementCollection
    Dim dashboardSheet As Worksheet, dataSheet As Worksheet
    Dim curr_id As String, smlID As String, interval As String
    Dim startDate As String, endDate As String
    Dim I As Long
    
    ' 初始化工作表
    Set dashboardSheet = Tabelle1
    Set dataSheet = ThisWorkbook.Worksheets("Data")
    curr_id = dashboardSheet.Range("C6").Value
    smlID = dashboardSheet.Range("C7").Value
    interval = dashboardSheet.Range("C17").Value
    startDate = Format(dashboardSheet.Range("C10").Value, "mm/dd/yyyy")
    endDate = Format(dashboardSheet.Range("C11").Value, "mm/dd/yyyy")
    
    ' 初始化IE浏览器
    Set ie = New InternetExplorer
    ie.Visible = True ' 设为False可后台运行，调试时建议设为True
    
    ' 1. 打开Investing.com登录页面，选择Google登录
    ie.Navigate "https://www.investing.com/login"
    Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    ' 点击Google登录按钮（需根据页面元素实际情况调整，当前页面按钮class为 'googleLoginBtn'）
    ie.Document.querySelector(".googleLoginBtn").Click
    Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    ' 切换到Google登录弹窗
    Dim loginWindow As InternetExplorer
    For Each loginWindow In InternetExplorer
        If InStr(loginWindow.LocationURL, "accounts.google.com") > 0 Then
            Set ie = loginWindow
            Exit For
        End If
    Next loginWindow
    
    ' 输入Google账号（建议从单元格读取，避免硬编码）
    ie.Document.querySelector("input[type='email']").Value = dashboardSheet.Range("C19").Value ' 假设C19存账号
    ie.Document.querySelector("#identifierNext").Click
    Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    ' 输入Google密码（同理从单元格读取）
    Application.Wait Now + TimeValue("00:00:02") ' 等待密码框加载
    ie.Document.querySelector("input[type='password']").Value = dashboardSheet.Range("C20").Value ' 假设C20存密码
    ie.Document.querySelector("#passwordNext").Click
    Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    ' 2. 登录成功后，请求历史数据接口
    Dim historyUrl As String
    historyUrl = "https://www.investing.com/instruments/HistoricalDataAjax?curr_id=" & curr_id & _
                 "&smlID=" & smlID & "&st_date=" & Replace(startDate, "/", "%2F") & _
                 "&end_date=" & Replace(endDate, "/", "%2F") & "&interval_sec=" & interval & _
                 "&sort_col=date&sort_ord=DESC&action=historical_data"
    
    ie.Navigate historyUrl
    Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    ' 3. 提取表格数据
    Set htmlDoc = ie.Document
    Set ieTable = htmlDoc.getElementById("curr_table")
    
    I = 2
    dataSheet.Range("A1:G1").Value = Array("日期", "开盘", "最高", "最低", "收盘", "成交量", "涨跌幅")
    For Each element In ieTable.getElementsByTagName("tr")
        If element.Children.Length >= 7 Then ' 跳过表头空行
            dataSheet.Cells(I, 1) = element.Children(0).innerText
            dataSheet.Cells(I, 2) = element.Children(1).innerText
            dataSheet.Cells(I, 3) = element.Children(2).innerText
            dataSheet.Cells(I, 4) = element.Children(3).innerText
            dataSheet.Cells(I, 5) = element.Children(4).innerText
            dataSheet.Cells(I, 6) = element.Children(5).innerText
            dataSheet.Cells(I, 7) = element.Children(6).innerText
            I = I + 1
        End If
        DoEvents
    Next element
    
    ' 清理资源
    ie.Quit
    Set ie = Nothing
    Set htmlDoc = Nothing
    Set ieTable = Nothing
    MsgBox "数据提取完成！"
End Sub

注意事项 #

• 元素定位调整：页面元素的class/id可能随网站更新变化，需用浏览器开发者工具（F12）确认最新选择器。
• 验证码问题：Google登录可能触发验证码，此时自动化会失效，建议先手动登录一次Investing.com（保持浏览器会话）再运行脚本。
• 反爬限制：避免短时间内频繁请求，可添加Application.Wait延迟，防止账号被封禁。
• 密码安全：绝对不要在代码中硬编码账号密码，建议从Excel单元格读取（可隐藏单元格或加密工作簿）。

内容的提问来源于stack exchange，提问作者Ralph