Let’s break this down step by step—first fixing your login code, then tackling the API integration and signal upload part.

1. Fixing the VBA Login Code #

Your current login script has a few critical issues that are causing it to fail. Let’s go through them and fix the code:

Key Issues in Your Original Code: #

• Duplicate HTTPS in URL: https://https://kite.zerodha.com/ is invalid—you’ve repeated the protocol.
• Incorrect Element IDs: Zerodha’s login form uses user_id and password as field IDs, not User and Password.
• Premature Visibility Toggle: Hiding IE before the login form loads can cause timing issues, and your ReadyState check doesn’t wait long enough for the form to be fully accessible.
• Unreliable Form Index: Using Forms(0) might not always target the correct login form—better to reference it by name or ID if possible.

Corrected Login Code #

Sub ZerodhaKiteLogin()
    Dim ie As Object
    Dim loginForm As Object
    
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    
    ' Fix the URL (removed duplicate https://)
    ie.Navigate "https://kite.zerodha.com/" & ActiveCell.Value
    
    ' Wait for page to fully load (more robust check)
    Do While ie.Busy Or ie.ReadyState <> 4
        DoEvents
    Loop
    
    ' Wait an extra second to ensure form elements are rendered
    Application.Wait Now + TimeValue("00:00:01")
    
    ' Target the login form correctly (Zerodha's login form has name="login_form")
    Set loginForm = ie.Document.Forms("login_form")
    
    ' Use correct field IDs
    loginForm.all("user_id").Value = "aaa"
    loginForm.all("password").Value = "bbbb"
    
    ' Submit the form
    loginForm.submit
    
    ' Optional: Wait for post-login page to load
    Do While ie.Busy Or ie.ReadyState <> 4
        DoEvents
    Loop
    
    ' Now you can proceed to get the request token needed for API access
End Sub

2. Integrating Zerodha Kite Connect API & Workflow #

Once you’re logged in, you’ll need to get a request token from the post-login redirect URL, then exchange it for an access token using your API key and secret. Here’s how to build out the full workflow:

Step 1: Get Request Token After Login #

After the login form submits, Zerodha redirects to a URL containing the request token. Add this to your login sub to extract it:

' Add this after the post-login wait loop
Dim requestToken As String
requestToken = Split(Split(ie.LocationURL, "request_token=")(1), "&")(0)
ie.Quit ' Close IE once we have the token
Set ie = Nothing

Step 2: Exchange Request Token for Access Token #

Use VBA’s MSXML2.XMLHTTP object to call the Kite Connect API and get your access token:

Sub GetAccessToken(apiKey As String, apiSecret As String, requestToken As String)
    Dim http As Object
    Dim response As String
    Dim postData As String
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    postData = "api_key=" & apiKey & "&request_token=" & requestToken & "&action=login"
    
    http.Open "POST", "https://api.kite.trade/session/token", False
    http.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    ' Generate checksum using your API secret (you'll need a VBA SHA256 function here)
    http.SetRequestHeader "X-Kite-Version", "3"
    http.SetRequestHeader "X-Checksum", GenerateSHA256(apiSecret & postData & apiSecret)
    
    http.Send postData
    response = http.responseText
    
    ' Parse the JSON response to get access_token (use a VBA JSON parser like VBA-JSON)
    ' Example with VBA-JSON:
    Dim json As Object
    Set json = JsonConverter.ParseJson(response)
    Dim accessToken As String
    accessToken = json("data")("access_token")
End Sub

Note: You’ll need to add a SHA256 checksum function and a JSON parser (like VBA-JSON) to your VBA project for this to work.

Step 3: Fetch Real-Time Data & Write to I2 #

Use the access token to call the market data API and write the real-time price to cell I2:

Sub FetchRealTimeData(accessToken As String, apiKey As String, instrumentToken As String)
    Dim http As Object
    Dim response As String
    Dim json As Object
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    ' Fetch LTP (Last Traded Price) for the instrument
    http.Open "GET", "https://api.kite.trade/instruments/quotes?i=" & instrumentToken, False
    http.SetRequestHeader "Authorization", "token " & apiKey & ":" & accessToken
    http.Send
    
    response = http.responseText
    Set json = JsonConverter.ParseJson(response)
    
    ' Extract LTP and write to I2
    Range("I2").Value = json("data")(instrumentToken)("last_price")
End Sub

Step 4: Calculate Signal & Upload to J2 #

Once you have the data in I2, run your calculation logic, then write the signal price to J2. If you need to send this signal back to Zerodha (e.g., place an order), use the order API:

Sub SendSignalOrder(accessToken As String, apiKey As String)
    Dim http As Object
    Dim postData As String
    Dim signalPrice As Double
    
    ' Get your calculated signal price from J2
    signalPrice = Range("J2").Value
    
    ' Build order data (adjust parameters as per your strategy)
    postData = "variety=regular&exchange=NSE&tradingsymbol=INFY&transaction_type=BUY&quantity=1&product=MIS&order_type=LIMIT&price=" & signalPrice
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", "https://api.kite.trade/orders/regular", False
    http.SetRequestHeader "Authorization", "token " & apiKey & ":" & accessToken
    http.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    
    http.Send postData
End Sub

Important Notes #

• API Permissions: Ensure your Zerodha API app has the correct permissions (market data, order placement) enabled.
• Error Handling: Add error handling to all subs to catch timeouts, invalid credentials, or API errors.
• VBA References: You’ll need to enable references to Microsoft XML, v6.0 and add the VBA-JSON module to parse API responses.

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