Skip to content

Bank reconciliation

Upload a bank statement CSV, auto-match lines against journal entries / payments / ledger entries on the same financial account, and clean up the mismatches by hand.

1. Overview

BankStatementImport is one uploaded CSV. BankStatementLine is one row of that statement. Every line starts status = 'unmatched' and can end up in one of three states:

stateDiagram-v2
    [*] --> unmatched
    unmatched --> matched: auto-match OR manual match
    unmatched --> ignored: user dismisses (e.g. bank charges)
    matched --> unmatched: unmatch (rare)

A line's matchedEntryType is one of:

  • journal_entry — matched to a JournalEntry.id.
  • payment — matched to a Payment.id.
  • ledger_entry — matched to a LedgerEntry.id.

See the schema at supabase/migrations/20260418150000_bank_reconciliation.sql.

2. Statement formats

Two parsers live in src/lib/bankStatementParser.ts:

  • parseSimpleCsv — the Indian retail CSV shape. One parser handles all major Indian retail banks by sniffing header aliases. Known-working: HDFC, ICICI, SBI, plus Axis, Kotak, Yes Bank, JK Bank in practice (header aliases are broad enough).
  • parseMt940 — the SWIFT MT940 tag-based format used by Indian corporate banking portals (HDFC CMS, ICICI Cash Management, SBI e-TFRS).

The upload UI has a Format dropdown (Auto / CSV / MT940). "Auto" picks based on extension: .csv → CSV, .mt940 / .sta / .swift → MT940.

Reference: src/lib/bankStatementParser.ts:1-13.

Header aliases

src/lib/bankStatementParser.ts:109-122 — the matcher is case-insensitive and trims quotes.

Logical column Matching headers
Date Txn Date, Transaction Date, Value Date, Date, Posting Date
Description Description, Narration, Particulars, Remarks, Details, Transaction Remarks
Debit Debit, Debit Amount, Withdrawal, Withdrawal Amt., Dr, Dr Amount
Credit Credit, Credit Amount, Deposit, Deposit Amt., Cr, Cr Amount
Balance (optional) Balance, Closing Balance, Running Balance, Balance Amount, Balance (INR)

Dates

Default dd/MM/yyyy; also accepts yyyy-MM-dd and dd-MM-yyyy. The parser tolerates separator drift (HDFC uses /, SBI uses -) by trying both.

Amounts

parseAmount (bankStatementParser.ts:153) strips ₹ / Rs / INR prefixes and thousands separators. Empty / - / . → 0.

Row handling

  • Blank rows between real data → silently skipped.
  • Bank-inserted "opening balance" rows (both debit and credit zero) → silently skipped.
  • Rows where debit AND credit are both non-zero → accepted, debit wins (HDFC double-populates occasionally).
  • Errors are collected in parsed.errors[] (per-row) — the import still succeeds with the good rows.

MT940

parseMt940() handles the SWIFT MT940 tag format. Recognised tags:

Tag Purpose
:20: Statement reference (metadata)
:25: Account identification (metadata)
:28C: Statement / sequence number (metadata)
:60F: / :60M: Opening balance (final / intermediate)
:61: One transaction — value date, D/C mark, amount, trans type
:86: Narrative for the preceding :61: (continuation lines glued)
:62F: / :62M: Closing balance (final / intermediate)

Indian-bank quirks the parser handles:

  • Multi-line :86: narratives — HDFC CMS splits long descriptions across continuation lines (no tag prefix). We concatenate them with a single space.
  • ":86:>N " continuation markers — some banks emit each continuation with a leading >1, >2, ... token. Those are stripped.
  • Paise-encoded balances — a handful of banks emit :60F: / :62F: with the amount in whole paise (no decimal). When the amount field has no , or . and exceeds 10 crore, we divide by 100.
  • DDMMYY value-dates — SWIFT specifies YYMMDD, but some Indian banks emit DDMMYY. If the YYMMDD interpretation lands more than one year in the future, we try the DDMMYY fallback.
  • Reversal marks — RC (reversal of credit) is treated as a debit, RD (reversal of debit) as a credit.

Per-line errors (unparseable :61: body, bad value date) are recorded in parsed.errors[]; the import continues with the good lines. Whole-file errors (empty input, no recognisable tags) throw. The UI surfaces thrown errors via toast.error() on file pick.

3. Auto-match heuristic

suggestMatches (bankStatementParser.ts:370-408) is a pure function, exercised by the unit tests in bankStatementParser.test.ts.

Parameters per line:

  • amountToleranceInr (default ₹1) — absolute difference on unsigned amounts.
  • dateToleranceDays (default 3) — both sides.
  • Direction check — a credit on the bank (money in) must match a positive candidate; a debit (money out) must match a negative one.

Outcome per line:

Candidates within tolerance Status
Exactly 1 matched → linked automatically
> 1 multiple → flagged for manual choice
0 none → flagged unmatched

The endpoint

POST /finance/bank-imports/:id/auto-match runs the heuristic against every unmatched line on the import. Single-candidate lines are promoted to matched and linked in-place.

4. UI

Finance page → Reconcile tab (Finance.tsx:9988) renders BankReconciliationPanel.tsx (src/components/finance/BankReconciliationPanel.tsx).

Flow:

  1. Upload — pick a financial account (bank/cash leaf ledger), choose the Format (Auto / CSV / MT940), then drop the file. The UI parses client-side, shows a preview, and on confirm POST /finance/bank-imports with the parsed lines.
  2. Auto-match — button fires POST /finance/bank-imports/:id/auto-match. UI shows matched / multiple / unmatched counts.
  3. Manual match — for multiple and none, the user picks a candidate; POST /finance/bank-imports/:id/lines/:lineId/match links it.
  4. Ignore — dismiss a line you won't reconcile (e.g. bank charges posted by the bank itself). Status flips to ignored.

The banner copy (BankReconciliationPanel.tsx:300) documents the matcher tolerance inline for operators:

The system parses the rows, then the auto-match step looks for journal entries and payments on the same bank account within ±₹1 and ±3 days. Anything with exactly one candidate is linked automatically; multi-candidate and unmatched rows need a manual decision.

5. Endpoints

All handled by handleBankImports (src/lib/api.ts:27148).

Method Path Permission Purpose
GET /finance/bank-imports finance.view List imports (newest first).
POST /finance/bank-imports finance.edit Create an import from parsed lines.
GET /finance/bank-imports/:id finance.view Import header.
GET /finance/bank-imports/:id/lines finance.view All lines of an import.
POST /finance/bank-imports/:id/auto-match finance.edit Run the auto-match pass.
POST /finance/bank-imports/:id/lines/:lineId/match finance.edit Manually match one line to a candidate.

6. Permissions

Per PERMISSIONS.md §4.1, reconciliation is a finance.edit action. There is no dedicated finance.bank.reconcile permission. RLS policies on BankStatementImport + BankStatementLine use auth_user_has_permission('finance.edit') for writes and finance.view for reads (supabase/migrations/20260418150000_bank_reconciliation.sql:85-117).

7. Tests

src/lib/bankStatementParser.test.ts — parser + auto-match unit tests. Fixtures cover:

  • HDFC / ICICI / SBI header variants.
  • Currency symbol stripping and thousands separators.
  • Blank-row padding and "both debit + credit" rows.
  • MT940 happy path, multi-line :86: concatenation, >N continuation markers, empty statements, malformed input, paise-encoded balances, DDMMYY date fallback, and RC/RD reversal marks.
  • suggestMatches direction / tolerance cases.

Run: npx vitest run src/lib/bankStatementParser.test.ts.