n8n + Zerodha Kite + Google Sheets: An End-of-Day Portfolio Roll-Up for an Indian SMB CFO

An Indian SMB CFO running ₹6.4 crore of corporate treasury across 38 holdings refused to use Smallcase, INDmoney, or any third-party portfolio dashboard. His reason was practical: he did not want a vendor with read-only access to a treasury balance the auditor would later question. We built a 16-node n8n flow that uses his own Zerodha Kite Connect API key, pulls the day's holdings and P&L at 16:00 IST, writes it into a private Google Sheet, and sends a one-line Slack DM with the day's net change. He now sees his portfolio before he leaves the office. Zero third-party servers in the path.

TL;DR — what this portfolio roll-up does

At 16:00 IST every weekday, the n8n workflow reads the corporate Zerodha account's holdings via Kite Connect's /portfolio/holdings endpoint, calculates day P&L (current price minus previous close × quantity), groups by sector tag (manually maintained in a Sheet), benchmarks each group against a Nifty sector index, and writes the results to a "EOD_holdings" Google Sheet. A separate Code node generates a 4-line Slack DM: total day P&L, top winner, top loser, sector outlier. The CFO reads it before leaving for the day.

Why this matters now — December 2025

Two reasons. Zerodha's Kite Connect API crossed 5,000 active developers in October 2025, and the team published better Python and JS SDKs in 2025. Most importantly, the Kite Connect daily access-token problem — tokens expire every day at 06:00 IST and require interactive login — has known workarounds (TOTP automation) that finally work reliably. Combined with the rise in conservative CFOs after the October 2024 SEBI changes requiring board sign-off on any third-party financial-data sharing, the case for self-hosted treasury reporting is the strongest it has been.

A r/algotrading thread from November 2025 on Kite Connect token-refresh approaches has 200+ comments — the consensus is automated TOTP-based daily login, exactly the pattern we use.

The 4-block workflow

The Kite Connect authentication flow (the painful part)

Kite Connect access tokens expire daily at 06:00 IST. They are not refreshable in the traditional sense — you must complete the interactive login flow each day. This is by design (SEBI compliance) and there is no way around it. The workaround: automate the login via TOTP using your Kite TOTP secret (from the Kite mobile app's 2FA setup).

Here is the daily login sub-workflow in 4 steps:

The actual n8n node JSON (3 critical Kite Connect calls)

Node 1 — Generate session token (after request_token capture)

{
    "parameters": {
      "method": "POST",
      "url": "https://api.kite.trade/session/token",
      "sendHeaders": true,
      "headerParameters": {
        "parameters": [
          { "name": "X-Kite-Version", "value": "3" },
          { "name": "Content-Type", "value": "application/x-www-form-urlencoded" }
        ]
      },
      "sendBody": true,
      "bodyContentType": "form-urlencoded",
      "bodyParameters": {
        "parameters": [
          { "name": "api_key", "value": "={{ $env.KITE_API_KEY }}" },
          { "name": "request_token", "value": "={{ $json.request_token }}" },
          { "name": "checksum", "value": "={{ $json.checksum }}" }
        ]
      },
      "options": { "timeout": 15000 }
    },
    "name": "Generate access_token",
    "type": "n8n-nodes-base.httpRequest",
    "typeVersion": 4.2,
    "position": [680, 200]
  }

The checksum is computed in an upstream Code node:

const crypto = require('crypto');
  const apiKey = $env.KITE_API_KEY;
  const apiSecret = $env.KITE_API_SECRET;
  const requestToken = $input.item.json.request_token;
  const checksum = crypto
    .createHash('sha256')
    .update(apiKey + requestToken + apiSecret)
    .digest('hex');
  return [{ json: { ...$input.item.json, checksum } }];

Node 2 — Read holdings (GET /portfolio/holdings)

{
    "parameters": {
      "method": "GET",
      "url": "https://api.kite.trade/portfolio/holdings",
      "sendHeaders": true,
      "headerParameters": {
        "parameters": [
          { "name": "X-Kite-Version", "value": "3" },
          { "name": "Authorization", "value": "token {{ $env.KITE_API_KEY }}:{{ $credentials.kiteAccess.access_token }}" }
        ]
      },
      "options": { "timeout": 15000 }
    },
    "name": "Get all holdings",
    "type": "n8n-nodes-base.httpRequest",
    "typeVersion": 4.2,
    "position": [240, 300]
  }

The Authorization header format is exact: token {api_key}:{access_token}. Note the colon, no spaces. This is the most common cause of 403 errors when you start with Kite.

Node 3 — Compute day P&L (Code node)

{
    "parameters": {
      "jsCode": "const holdings = $input.first().json.data;\nconst rows = holdings.map(h => {\n  const dayPnL = (h.last_price - h.close_price) * h.quantity;\n  const totalPnL = (h.last_price - h.average_price) * h.quantity;\n  const dayPctChange = ((h.last_price - h.close_price) / h.close_price) * 100;\n  return {\n    date: new Date().toISOString().slice(0, 10),\n    symbol: h.tradingsymbol,\n    exchange: h.exchange,\n    qty: h.quantity,\n    avg: h.average_price,\n    last: h.last_price,\n    prev_close: h.close_price,\n    day_pnl: Math.round(dayPnL),\n    day_pct: Math.round(dayPctChange * 100) / 100,\n    total_pnl: Math.round(totalPnL),\n    market_value: Math.round(h.last_price * h.quantity)\n  };\n});\nconst totalDayPnL = rows.reduce((s, r) => s + r.day_pnl, 0);\nconst topWinner = rows.sort((a, b) => b.day_pnl - a.day_pnl)[0];\nconst topLoser = rows.sort((a, b) => a.day_pnl - b.day_pnl)[0];\nreturn [\n  { json: { rows, summary: { totalDayPnL, topWinner, topLoser, count: rows.length } } }\n];"
    },
    "name": "Compute day P&L",
    "type": "n8n-nodes-base.code",
    "typeVersion": 2,
    "position": [460, 300]
  }

The Kite holdings response includes last_price, close_price (yesterday's close), average_price (your buy avg), and quantity. Day P&L is straightforward arithmetic. We round to whole rupees because no CFO cares about paise.

The Slack DM template

The Slack DM is intentionally short — one line of headline, three lines of detail. CFOs do not read long Slack messages on a phone walking to the parking lot.

EOD treasury — 10 Dec 2025
  
  Day P&L: +₹78,420 (+0.12%) on ₹6.41 Cr book
  Winner: HDFCBANK +₹14,200 (+1.8%)
  Loser: TATASTEEL -₹8,140 (-2.1%)
  Sector flag: PSU Bank +1.4% vs Nifty PSU Bank +0.6% — outperform
  
  Sheet: [EOD_holdings - row 412]

The "Sheet" link goes to the row in Google Sheets where today's snapshot lives — useful for audit trail. We use Sheets' #gid=...&range=A412 URL pattern.

The pre-flight checklist

• Zerodha Kite Connect subscription active (paid by client, not us)
• API key + secret + TOTP secret stored in n8n env vars (NOT in workflow JSON)
• Daily token-refresh sub-workflow tested for 5 consecutive days without manual intervention
• Holiday calendar Sheet seeded with NSE holidays for the calendar year
• Backup notification path (email) configured if Slack DM fails
• EOD_holdings Sheet has columns matching the Code node output exactly
• Sector mapping Sheet has a row per holding (manual upkeep — review monthly)
• Error workflow set — token-refresh failure must alert before market open
• Hetzner box has full-disk encryption enabled
• CFO has been briefed on what to do if the report does not arrive (manual /portfolio call)

Common mistakes — symptoms first, fixes second

Symptom: "403 Forbidden on every API call after 06:00 IST." Cause: access_token expired at 06:00, daily refresh sub-workflow did not run. Fix: check the sub-workflow execution log. Most common root: TOTP secret rotated but n8n env var not updated. Re-pair TOTP, update env var.

Symptom: "Sheet writes succeed but P&L numbers look wrong by 5-15%." Cause: close_price from Kite is yesterday's adjusted close (after corporate actions). For pre-bonus or pre-split holdings, your average_price is unadjusted. Fix: handle corporate actions explicitly — keep an "adjustments" Sheet, apply before computing P&L.

Symptom: "TOTP login works manually but fails when n8n calls it." Cause: TOTP is time-based, server clock drift > 30 seconds. Fix: enable NTP on the Hetzner box (timedatectl set-ntp true) and verify with timedatectl status showing "synchronized: yes".

Symptom: "Slack DM appears in CFO's channel late at night, not at 16:00." Cause: cron timezone defaulted to UTC. Fix: set the Schedule Trigger timezone to Asia/Kolkata explicitly. Same problem we covered in the Saturday roll-up post.

Symptom: "Some holdings missing from the EOD report." Cause: Kite /portfolio/holdings returns equity holdings only. Mutual funds are at /portfolio/holdings?type=mf. Bonds via the orderbook history. Fix: separate fetch for each holding type, merge in Code node before P&L.

Symptom: "Day P&L is positive in the Sheet but negative in Kite mobile app." Cause: Kite mobile app shows portfolio P&L (current vs avg) by default; our Code node outputs day P&L (current vs prev close). Two different numbers. Fix: compute and report both. The CFO wanted day P&L; we made it explicit.

Mini case study — Indian SMB CFO, 4 months live

The first install ran at a 60-person Pune-headquartered manufacturing company from August 12, 2025. Four months in: 87 weekday EOD reports delivered, 2 missed (both due to the daily token refresh failing — once on a TOTP rotation, once on an NTP drift after a server reboot). The CFO's exact email after week 6: "I have stopped opening Smallcase. I trust this report more because I know exactly where the numbers come from."

The same Pune client has now asked for a sister flow — intraday alerts when any holding moves >3% in 30 minutes. We are building it as a separate workflow on the same Kite Connect credential.

For another financial-data automation we shipped, see Radiant Finance — a different project using webhook-driven flows for lead distribution, but the same self-hosted ownership philosophy.

When not to build this

Skip this if (a) your treasury is under ₹50 lakh — the monthly Kite Connect fee makes Smallcase Premium cheaper, (b) your CFO is not technical enough to know what to do when the daily token-refresh fails — manual recovery is part of the cost of self-hosting, or (c) you do not have an existing self-hosted server or are not willing to maintain one — n8n Cloud cannot reliably hold the Kite TOTP refresh state. We turned down two clients in 2025 who wanted this on cloud-only infra.

For our broader position on AI automation services, the Softechinfra services overview for full-stack work, and Hrishikesh's CTO writeups, see related links.

FAQ

Why Zerodha and not Upstox or Angel One?

Kite Connect is the most mature retail-broker API in India by a wide margin — clearer docs, better SDKs, better community support. Upstox API is solid but has fewer maintained SDKs in 2025. Angel SmartAPI works but has fewer worked examples online. For first-time integrations, Kite is lowest risk.

Can I run this without paying for Kite Connect?

No. Kite Connect is a paid product separate from your trading account. The free Kite web/mobile interface does not expose an API. Workarounds (web scraping, browser automation against Kite web) are explicitly against Zerodha's terms.

What about historical P&L tracking — can n8n compute month-on-month?

Yes. Append every EOD snapshot to the EOD_holdings Sheet (we never overwrite). A separate weekly workflow reads the last 30 days and computes month-to-date P&L, sector contribution, win/loss ratio. We have built this for one client — it adds 5 nodes and no incremental cost.

Is the daily token refresh against Zerodha's terms?

Read the Kite Connect terms carefully. Programmatic login via TOTP is permitted for personal/internal use of your own account. It is not permitted to log in on behalf of another user. We only build this for clients using their own API credentials.

What if my TOTP backup phone is lost?

Painful. You re-enrol TOTP via Zerodha's official process (KYC-bound, 1-3 working days). During that time, the workflow runs blind. Plan for it: have a backup TOTP-app device, store recovery codes in your password manager.

Can this push to Tally for accounting reconciliation?

Yes. Tally Prime supports XML import for journal entries. Our Pune client's flow includes a daily Tally voucher write (DR Investment, CR Bank for buys; reverse for sells). The Tally XML pattern is the same one we use in the Tally + Shopify post.

Is there a managed n8n Cloud version of this workflow?

We strongly recommend self-hosted for treasury data. n8n Cloud encrypts secrets at rest but routes execution through their EU/US infrastructure. For a self-hosted Hetzner box in Singapore (low-latency for India), the secret never leaves your control.

For more on Vivek Singh's perspective on building self-hosted financial workflows for SMBs, see the founder portfolio.