Softechinfra
Productivity

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

A 16-node n8n flow that pulls treasury holdings from Zerodha Kite Connect at 4 pm IST, computes day P&L vs sector benchmarks, and DMs the CFO on Slack. Real Kite Connect API calls, the daily token-refresh problem solved, and a lean all-in monthly bill.

Softechinfra EditorialSoftechinfra Editorial
May 15, 202615 min read
n8n + Zerodha Kite + Google Sheets: An End-of-Day Portfolio Roll-Up for an Indian SMB CFO

Picture an Indian SMB CFO running ₹6.4 crore of corporate treasury across 38 holdings who refuses to use Smallcase, INDmoney, or any third-party portfolio dashboard. The reason is practical: he does not want a vendor with read-only access to a treasury balance the auditor may later question. This post builds 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 sees his portfolio before he leaves the office, with zero third-party servers in the path.

16
n8n nodes (incl. token-refresh sub-flow)
16:00 IST
Daily run, 30 min after market close
38
Holdings tracked across treasury account

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

AUTH
Block 1: Daily token refresh
Sub-workflow runs at 08:00 IST. Uses pyotp-equivalent JS to generate Kite TOTP, completes login, captures access_token, stores in n8n credentials.
CRON
Block 2: 16:00 EOD trigger
Schedule trigger fires Mon-Fri 16:00 Asia/Kolkata. Skipped on holidays via a holidays Sheet lookup.
API
Block 3: Holdings + LTP fetch
GET /portfolio/holdings returns 38 rows. POST /quote/ltp gets last-traded prices. Code node computes day P&L per holding.
REP
Block 4: Sheet write + Slack DM
Append-only write to EOD_holdings sheet. Slack DM with 4-line summary. Email fallback if Slack post fails.

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:

1
GET kite.zerodha.com/connect/login → capture request_token
Hit the login URL with your api_key. Kite redirects to its login form. The redirect URL you registered receives a request_token query parameter on success. We use Puppeteer in a Code node to drive this headlessly with the user_id, password, and TOTP filled in programmatically.
2
POST /session/token with checksum → get access_token
Once you have request_token, POST it to /session/token along with checksum = SHA-256(api_key + request_token + api_secret). Response includes access_token, valid until next 06:00 IST.
3
Store access_token in n8n credentials via API
n8n's credential API allows programmatic update. The sub-workflow updates the "Kite Connect" credential with the new token. The 16:00 EOD workflow reads from the same credential, so token refresh is transparent.
4
Alert on failure
If TOTP secret rotates, login fails, etc — DM the CFO immediately at 08:01 IST so he knows the EOD report won't fire. Manual fallback: he can re-pair the TOTP and trigger a re-run.

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

Node 1 — Generate session token (after request_token capture)

json
{
    "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:

javascript
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)

json
{
    "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)

json
{
    "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.

code
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.

Treat your Kite API secret like the keys to your trading account. Anyone with api_key + api_secret + your TOTP secret can place orders on your behalf. Store the api_secret only in n8n env vars (encrypted at rest if you use Hetzner with full-disk encryption). Rotate quarterly. Never commit to git, never paste in Slack. We have a per-client KMS-style policy where the secret lives in the client's own server, not ours.

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.

Example: what to plan for in the first months

For a hypothetical 60-person manufacturing company, almost every weekday report should arrive on time. The misses to plan for come from the daily token refresh failing, usually after a TOTP rotation or an NTP drift after a server reboot. The point of the build is trust: the CFO knows exactly where every number comes from.

A natural next step is a sister flow: intraday alerts when any holding moves more than 3% in 30 minutes, built 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. It adds about 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. Add 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.

Want this portfolio roll-up built for your treasury team?

We ship the Kite Connect EOD flow — n8n on your infra, Sheets dashboard, Slack DM, daily token refresh, full audit trail — in 7 working days. Suitable for any Indian SMB CFO managing ₹50 lakh+ treasury who wants ownership of their data. We work only with your own Kite Connect credentials.

Book a 20-min Call

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

Tags:
n8nZerodha Kite ConnectTreasuryCFOGoogle SheetsSlackWorkflow AutomationIndian SMB
Share this post:
Softechinfra Editorial

Softechinfra Editorial

Articles published by Softechinfra without a personal byline.

How we pick topics, use AI, check claims and correct mistakes: our editorial policy.