Excel VBA Scraping: Fast No-Browser JSON Guide

Excel remains the lifeblood of the financial world. While Python is the standard for large-scale data engineering, millions of analysts still live inside spreadsheets. You need data—live stock prices, currency rates, competitor SKUs—directly in your cells, instantly, without switching windows.

However, 90% of VBA tutorials online are dangerously outdated. They suggest using InternetExplorer.Application or the older WinHttp object. Do not do this. IE is dead, and WinHttp often fails on modern websites requiring TLS 1.3 encryption.

In this comprehensive guide, I will show you the professional way to scrape with VBA: Headless HTTP Requests via MSXML2. This method runs silently in the background, handles modern encryption, and integrates with professional proxy networks.

1. Setting Up Your Environment (The Right Way)

Before writing a single line of code, we need to equip Excel with the right libraries. We are not recording a macro; we are building a robust script.

Figure 1: Inside the VBA Editor (Alt + F11), go to Tools > References.

Ensure you check these three critical references:

• Microsoft HTML Object Library: Allows VBA to parse HTML strings into DOM elements (optional if just using JSON).
• Microsoft XML, v6.0: The engine that sends requests. Note: Use v6.0, not v3.0, to ensure compatibility with modern HTTPS certificates.
• Microsoft Scripting Runtime: Required for using Dictionaries (essential for parsing JSON data structures).

2. Architecture: Why “ServerXMLHTTP” Wins

Why switch from Browser Automation to HTTP Requests? Speed and Stability.

When you automate a browser, it renders images, executes JavaScript, and loads ads. When you use ServerXMLHTTP, you only download the raw data. In Thordata’s internal benchmarks, fetching a stock price via XMLHTTP takes 0.3 seconds, whereas automating a Chrome window takes 4-8 seconds.

3. The Code: Basic GET Request (Headless)

Let’s create a subroutine that fetches a webpage. We will implement proper error handling and User-Agent spoofing to avoid immediate blocking.

Sub ModernScraper()
    Dim xmlHttp As New MSXML2.ServerXMLHTTP60
    Dim htmlDoc As New MSHTML.HTMLDocument
    Dim url As String

    url = "https://books.toscrape.com/"

    With xmlHttp
        ' False = Synchronous (Code waits for response)
        .Open "GET", url, False
        
        ' CRITICAL: Fake the headers to look like a real browser
        .setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) Chrome/120.0.0.0 Safari/537.36"
        .setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"
        .setRequestHeader "Accept-Language", "en-US,en;q=0.9"
        
        .send
        
        If .Status <> 200 Then
            MsgBox "Error: " & .Status & " - " & .statusText
            Exit Sub
        End If
        
        ' Load response text into the HTML Parser
        htmlDoc.body.innerHTML = .responseText
    End With

    ' Extract Data using standard DOM methods
    Debug.Print "Page Title: " & htmlDoc.Title

End Sub

4. The “Pro” Method: Parsing JSON APIs

This is where expert analysts differ from beginners. Most financial sites (Yahoo Finance, NASDAQ, Crypto exchanges) actually load their data via hidden API calls.

Why scrape HTML tables when you can get structured JSON?

Since VBA does not handle JSON natively, you must use the VBA-JSON library. Import the JsonConverter.bas module into your project.

Sub GetCryptoPrices()
    Dim xmlHttp As New MSXML2.ServerXMLHTTP60
    Dim Json As Object
    Dim rate As Double

    ' Target a real JSON API endpoint (e.g., CoinDesk)
    xmlHttp.Open "GET", "https://api.coindesk.com/v1/bpi/currentprice.json", False
    xmlHttp.send

    ' Parse JSON string to a Scripting.Dictionary
    Set Json = JsonConverter.ParseJson(xmlHttp.responseText)

    ' Navigate the JSON structure: Root -> bpi -> USD -> rate_float
    rate = Json("bpi")("USD")("rate_float")

    ' Write directly to Excel cells
    Range("A1").Value = "Bitcoin (USD)"
    Range("B1").Value = rate
    Range("C1").Value = Now

    Debug.Print "Success: BTC is " & rate

End Sub

5. Advanced: Handling Login Cookies (The “Cookie Relay”)

A common pain point: “I can scrape the public page, but how do I scrape data behind a login?”

VBA HTTP requests are stateless. They don’t “remember” you logged in. You must manually catch the `Set-Cookie` header from the login response and attach it to subsequent requests. Here is the blueprint:

Sub ScrapeWithLogin()
    Dim xmlHttp As New MSXML2.ServerXMLHTTP60
    Dim myCookie As String

    ' Step 1: POST Credentials to Login URL
    xmlHttp.Open "POST", "https://example.com/login", False
    xmlHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xmlHttp.send "username=myUser&password=myPass"

    ' Step 2: Extract the Session Cookie
    myCookie = xmlHttp.getResponseHeader("Set-Cookie")

    ' Step 3: Use Cookie to Get Private Data
    xmlHttp.Open "GET", "https://example.com/my-dashboard", False
    xmlHttp.setRequestHeader "Cookie", myCookie ' Pass the cookie back
    xmlHttp.send

    Debug.Print xmlHttp.responseText ' Contains logged-in content

End Sub

6. Integrating Thordata Proxies in VBA

Financial scraping often involves high-frequency requests. If you check stock prices every minute from your office IP, you will likely get banned. You need Residential Proxies.

While `ServerXMLHTTP` supports proxies, handling the “Username/Password” popup window in VBA is notoriously unstable and often interrupts the script. The best practice is to use IP Whitelisting via the Thordata dashboard.

Sub ScrapeWithThordata()
    Dim xmlHttp As New MSXML2.ServerXMLHTTP60

    With xmlHttp
        .Open "GET", "https://api.ipify.org", False
        
        ' Configuration for Thordata Proxy Gateway
        ' SXH_PROXY_SET_PROXY = 2
        .setProxy 2, "gate.thordata.com:12345"
        
        ' Using IP Whitelisting (Recommended): No credentials needed in code
        ' If using User/Pass auth, uncomment below:
        ' .setProxyCredentials "YOUR_USER", "YOUR_PASS"
        
        .send
        Debug.Print "Scraping via IP: " & .responseText
    End With

End Sub

Conclusion

VBA is not dead; it just needs to be used correctly. By abandoning browser automation in favor of `ServerXMLHTTP` and JSON parsing, you can build financial scrapers that are fast, lightweight, and integrated directly into your reporting workflow.

Ready to scale your Excel models? Start by integrating high-quality residential proxies to prevent data feed interruptions.