基于ISIN从Investing.com爬取股票数据的VBA爬虫开发问题

Let's walk through how to complete this scraper properly, fixing the gaps in your current code and addressing common hurdles with Investing.com. Your existing code only populates the search box but never triggers a search, plus Investing.com has basic anti-scraping measures and dynamic content that your current approach misses.

Key Issues in Your Current Code #

• You set the ISIN in the search input but don't submit the search or navigate to the stock's detail page
• XMLHTTP60 requests without proper headers are often blocked by Investing.com's anti-bot systems
• The initial homepage HTML doesn't include search results—those load dynamically after a search is submitted

Complete Working Implementation #

We'll use Investing.com's internal search API to get the stock's detail page URL directly, then scrape the data from that page. Here's the full code with explanations:

Sub Get_Stock_Data_From_ISIN()
    Dim Page As New XMLHTTP60
    Dim Doc As New HTMLDocument
    Dim searchResponse As String
    Dim detailUrl As String
    Dim targetElement As IHTMLElement
    Dim ISIN As String
    
    ' Get target ISIN (you can replace this with an InputBox for user input)
    ISIN = "US0378331005"
    
    ' --- Step 1: Fetch the stock's detail page URL via Investing.com's search API ---
    With Page
        .Open "GET", "https://www.investing.com/search/service/SearchInnerPage?query=" & ISIN & "&tab=all", False
        ' Mimic a Chrome browser to avoid being blocked
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
        .setRequestHeader "Accept", "application/json, text/plain, */*"
        .send
        searchResponse = .responseText
    End With
    
    ' Extract the detail page URL from the JSON response (basic string parsing)
    Dim startPos As Long, endPos As Long
    startPos = InStr(searchResponse, """url"":""") + 7
    endPos = InStr(startPos, searchResponse, """")
    detailUrl = Mid(searchResponse, startPos, endPos - startPos)
    ' Convert relative URL to full URL
    detailUrl = "https://www.investing.com" & detailUrl
    
    ' --- Step 2: Load the stock's detail page ---
    With Page
        .Open "GET", detailUrl, False
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
        .send
        Doc.body.innerHTML = .responseText
    End With
    
    ' --- Step 3: Scrape key stock data (adjust selectors if page structure changes) ---
    On Error Resume Next ' Handle cases where elements might not exist
    
    ' Current stock price
    Set targetElement = Doc.querySelector(".text-2xl")
    If Not targetElement Is Nothing Then
        Debug.Print "Current Price: " & targetElement.innerText
        ' Or write to a cell: Range("A1").Value = targetElement.innerText
    Else
        Debug.Print "Current Price element not found"
    End If
    
    ' Price change (green for up, red for down)
    Set targetElement = Doc.querySelector(".text-[#26a69a]")
    If Not targetElement Is Nothing Then
        Debug.Print "Price Change: " & targetElement.innerText
    Else
        Set targetElement = Doc.querySelector(".text-[#ef5350]")
        If Not targetElement Is Nothing Then
            Debug.Print "Price Change: " & targetElement.innerText
        End If
    End If
    
    ' Trading Volume
    Set targetElement = Doc.querySelector(".trading-history-widget__row:nth-child(3) .trading-history-widget__value")
    If Not targetElement Is Nothing Then
        Debug.Print "Volume: " & targetElement.innerText
    Else
        Debug.Print "Volume element not found"
    End If
    
    On Error GoTo 0 ' Reset error handling
    
    ' Clean up objects
    Set Page = Nothing
    Set Doc = Nothing
    Set targetElement = Nothing
End Sub

Important Notes #

1. Required References: In the VBA Editor, go to Tools > References and enable:
   • Microsoft XML, v6.0
   • Microsoft HTML Object Library
2. Page Structure Changes: Investing.com updates its UI periodically—if selectors like .text-2xl stop working, inspect the page with your browser's dev tools to find new selectors.
3. Anti-Scraping: Add a small delay between requests (e.g., Application.Wait Now + TimeValue("00:00:02")) to avoid getting your IP blocked.
4. JSON Parsing: For more reliable extraction of the detail URL, use a VBA JSON library like VBA-JSON instead of string parsing.

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