Your current code relies on Basic Authentication, but Screener.in doesn’t use that for accessing this API endpoint. Instead, you need to first log in via their web form to grab the required Session ID (stored in cookies) and CSRF Token, then include these in your API request headers. Let’s walk through fixing this step by step.

1. Key Issues with Your Current Code #

• Screener.in doesn’t support Basic Auth for this API—you need to authenticate via a standard login flow to get valid session cookies and a CSRF token.
• Your request is missing critical headers like X-CSRFToken and the session cookie that the API expects to validate your identity.

2. Updated VBA Workflow #

Here’s a revised script that:

1. Fetches the login page to extract the CSRF Token
2. Sends a login POST request to obtain the session cookie
3. Uses both the CSRF Token and session cookie to access the target API

Option Explicit

Sub FetchScreenerData()
    Dim loginUrl As String, targetApiUrl As String
    Dim user As String, pwd As String
    Dim sessionCookie As String, csrfToken As String
    Dim apiResponse As String
    
    ' Configuration
    user = "rajesh2.gade@gmail.com"
    pwd = "Rain@123"
    loginUrl = "https://www.screener.in/login/"
    targetApiUrl = "https://www.screener.in/api/company/6596449/quick_ratios/"
    
    ' Step 1: Grab CSRF Token from login page
    csrfToken = GetCsrfToken(loginUrl)
    If csrfToken = "" Then
        Debug.Print "Failed to retrieve CSRF Token"
        Exit Sub
    End If
    
    ' Step 2: Log in to get session cookie
    sessionCookie = LoginAndGetSession(loginUrl, user, pwd, csrfToken)
    If sessionCookie = "" Then
        Debug.Print "Login failed"
        Exit Sub
    End If
    
    ' Step 3: Access target API with valid session and CSRF token
    apiResponse = GetApiData(targetApiUrl, sessionCookie, csrfToken)
    Debug.Print "API Response:" & vbCrLf & apiResponse
End Sub

' Extract CSRF Token from login page's meta tags
Private Function GetCsrfToken(url As String) As String
    Dim oHttp As Object, htmlDoc As Object
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    Set htmlDoc = CreateObject("HTMLFile")
    
    oHttp.Open "GET", url, False
    oHttp.send
    
    If oHttp.Status = 200 Then
        htmlDoc.Write oHttp.responseText
        htmlDoc.Close
        
        ' Scan meta tags for the CSRF token
        Dim metaTag As Object
        For Each metaTag In htmlDoc.getElementsByTagName("meta")
            If metaTag.getAttribute("name") = "csrf-token" Then
                GetCsrfToken = metaTag.getAttribute("content")
                Exit For
            End If
        Next metaTag
    End If
    
    Set oHttp = Nothing
    Set htmlDoc = Nothing
End Function

' Send login request and extract session cookie from response headers
Private Function LoginAndGetSession(loginUrl As String, user As String, pwd As String, csrfToken As String) As String
    Dim oHttp As Object, postData As String
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    
    ' Prepare form data for login (URL-encoded)
    postData = "username=" & EncodeUrl(user) & "&password=" & EncodeUrl(pwd) & "&csrfmiddlewaretoken=" & csrfToken
    
    oHttp.Open "POST", loginUrl, False
    oHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    oHttp.setRequestHeader "Referer", loginUrl
    oHttp.setRequestHeader "Cookie", "csrftoken=" & csrfToken ' Include CSRF token in cookie
    oHttp.send postData
    
    ' Extract sessionid cookie on successful login (302 redirect is normal)
    If oHttp.Status = 302 Then
        Dim headers As String, headerLines() As String, line As String
        headers = oHttp.getAllResponseHeaders
        headerLines = Split(headers, vbCrLf)
        
        For Each line In headerLines
            If InStr(1, line, "Set-Cookie:", vbTextCompare) > 0 Then
                If InStr(1, line, "sessionid=", vbTextCompare) > 0 Then
                    LoginAndGetSession = Split(Split(line, ";")(0), ": ")(1)
                    Exit For
                End If
            End If
        Next line
    End If
    
    Set oHttp = Nothing
End Function

' Fetch API data using valid session and CSRF token
Private Function GetApiData(apiUrl As String, sessionCookie As String, csrfToken As String) As String
    Dim oHttp As Object
    Set oHttp = CreateObject("Microsoft.XMLHTTP")
    
    oHttp.Open "GET", apiUrl, False
    oHttp.setRequestHeader "Accept", "application/json"
    oHttp.setRequestHeader "X-CSRFToken", csrfToken
    oHttp.setRequestHeader "Cookie", sessionCookie & "; csrftoken=" & csrfToken
    oHttp.send
    
    If oHttp.Status = 200 Then
        GetApiData = oHttp.responseText
    Else
        GetApiData = "API Request Failed. Status: " & oHttp.Status & " - " & oHttp.statusText
    End If
    
    Set oHttp = Nothing
End Function

' Helper function to URL-encode text
Private Function EncodeUrl(text As String) As String
    Dim objUrl As Object
    Set objUrl = CreateObject("Microsoft.XMLHTTP")
    EncodeUrl = objUrl.URLencode(text)
    Set objUrl = Nothing
End Function

3. Important Notes #

• CSRF Token Extraction: The script looks for the <meta name="csrf-token" content="..."> tag on the login page. If Screener.in updates their page structure, you may need to adjust this selector.
• Cookie Handling: Both the sessionid cookie (from login) and csrftoken cookie must be included in the API request headers to validate your session.
• Login Redirect: A successful login returns a 302 redirect status—this is expected, and we extract the session cookie from the response headers.
• URL Encoding: The username and password are URL-encoded to handle special characters correctly.

If you still run into issues, check the Debug.Print outputs for status codes and errors, and verify that your login credentials work manually on the Screener.in website.

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