# bankrecon Bank reconciliation for finance teams. bankrecon imports a bank statement (HDFC / ICICI / SBI style CSV or XLSX, MT940, or ISO 20022 CAMT.053) and the cash book, matches them with explainable rules (one-to-one, one-to-many, many-to-one and small many-to-many groups), classifies unbooked bank items (charges, GST, interest, TDS, returns) with suggested journal entries, produces the Bank Reconciliation Statement (BRS) with a hard tie-out check, and carries outstanding items forward to the next period with aging. ## Features - **Inputs**: bank CSV/XLSX with built-in `hdfc`, `icici`, `sbi` profiles (plus custom profiles in config), MT940 (`:61:`/`:86:`, structured `?NN` subfields, reversal marks), CAMT.053 XML, cash book CSV, prior-period outstanding file. - **Normalisation**: money-in is positive on both sides (bank credit = book debit); Indian number and date formats; reference extraction for UTR, NEFT/RTGS ids, UPI/IMPS RRNs, cheque numbers and invoice numbers (configurable regex); party-name normalisation. - **Matching passes** (order configurable, every match records its rule and reasons): reference + amount, cheque number + amount (up to 90 days to clear), amount + date window (unique candidate only), fuzzy narration tie-breaker, one-to-many (subset-sum with pruning, fewest items preferred), many-to-one, many-to-many (bounded), bank reversal pairs. - **Ambiguity is never auto-matched**: if several candidates are equally good the item is left for a human. - **Auto-classification** of bank-only items with suggested journal entries (bank charges, GST on charges, interest credit, TDS on interest, cash withdrawals, returned cheques and payments, otherwise Suspense). - **BRS** from the cash book balance to the bank balance; asserted to tie, otherwise the difference is shown with diagnostics. - **Carry-forward** in SQLite: unmatched items from the previous period join the next run automatically; aging buckets and stale-cheque flags (> 90 days). - **Review workflow**: interactive TUI or one-shot flags to accept/reject suggestions, match manually, add notes; every action goes to an audit log. - **Outputs**: Excel workbook (BRS, matched pairs with rule, unmatched both sides, suggested JEs), CSV, JSON; `bankrecon explain M-0012`. ## Install ```bash uv venv .venv && uv pip install -p .venv/bin/python -e '.[dev]' source .venv/bin/activate # or call .venv/bin/bankrecon directly ``` Requires Python 3.12+. Dependencies: click, rich, openpyxl. ## Quickstart The repository ships a realistic two-month data set in `examples/` (regenerate it with `bankrecon sample --out examples`). September 2026 has 500 bank lines and 480 cash book entries; October continues from it. The same September statement is provided as `bank_hdfc.csv`, `bank_icici.csv`, `bank_sbi.csv`, `bank_hdfc.xlsx`, `bank.mt940` and `bank.camt053.xml`. ```bash export BANKRECON_DB=/tmp/demo.db # default is ./bankrecon.db # September: first period, so give the opening cash book balance and the items outstanding at 31 Aug bankrecon run --bank examples/2026-09/bank.mt940 --ledger examples/2026-09/cashbook.csv \ --period 2026-09 --opening-book 4498444.95 \ --prior-outstanding examples/2026-09/prior_outstanding.csv --xlsx out/2026-09.xlsx bankrecon review # accept / reject suggestions, match by hand bankrecon brs # Bank Reconciliation Statement bankrecon explain M-0440 # why were these items matched? # October: openings and outstanding items come from the database bankrecon run --bank examples/2026-10/bank.camt053.xml --ledger examples/2026-10/cashbook.csv --period 2026-10 bankrecon outstanding --aging ``` ## Usage with real output ### `bankrecon run` ``` $ bankrecon run --bank examples/2026-09/bank.mt940 --ledger examples/2026-09/cashbook.csv \ --period 2026-09 --opening-book 4498444.95 --prior-outstanding examples/2026-09/prior_outstanding.csv Period 2026-09: 500 bank lines, 480 ledger lines, 9 carried-forward items Matching results ┏━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ Rule ┃ Matches ┃ Awaiting review ┃ ┡━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ reference │ 328 │ 0 │ │ cheque │ 86 │ 0 │ │ amount_date │ 23 │ 0 │ │ fuzzy │ 2 │ 2 │ │ one_to_many │ 6 │ 0 │ │ many_to_one │ 5 │ 0 │ │ many_to_many │ 2 │ 2 │ │ bank_reversal │ 1 │ 0 │ └───────────────┴─────────┴─────────────────┘ Unmatched: 38 bank, 17 book. Suggested journal entries: 38. BRS: TIED 4 suggested match(es) need review: `bankrecon review --period 2026-09` ``` Options: `--format auto|csv|xlsx|mt940|camt053` (auto looks at the extension), `--profile hdfc|icici|sbi|`, `--opening-bank`, `--opening-book`, `--closing-book`, `--prior-outstanding FILE`, `--xlsx`, `--csv-dir`, `--json`, `--strict` (exit status 2 if the BRS does not tie). Opening bank balance is taken from the flag, else the statement (MT940 `:60F:`, CAMT `OPBD`, or the first CSV balance minus the first movement), else the previous period. Opening book balance comes from the flag or the previous period. ### `bankrecon review` Run without flags it walks through each suggested match, then offers manual matching: ``` $ bankrecon review --user akshay # (a)ccept (r)eject (n)ote (s)kip (q)uit ``` One-shot flags for scripting: `--accept M-0438`, `--reject M-0452 --note "credit note still pending"`, `--match B-0249=L-0265 --note "confirmed with sales"`, `--note-on L-0480 --note "..."`, `--show-audit`. ``` $ bankrecon review --show-audit ┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ ┃ When ┃ User ┃ Action ┃ Target ┃ Detail ┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ │ 2026-10-16T21:23:04 │ konkan │ run │ 2026-09 │ {'matches': 453, 'carried': 9} │ 2026-10-16T21:23:05 │ akshay │ accept │ M-0438 │ {'note': ''} │ 2026-10-16T21:23:05 │ akshay │ accept │ M-0439 │ {'note': ''} │ 2026-10-16T21:23:05 │ akshay │ accept │ M-0451 │ {'note': ''} │ 2026-10-16T21:23:05 │ akshay │ reject │ M-0452 │ {'note': 'Credit note still pending', 'was': 'suggested', 'bank': │ │ │ │ │ ['B-0223', 'B-0234'], 'book': ['L-0223', 'L-0213']} ``` Rejecting a match releases its items (they appear as unmatched and carry forward); manual matches must balance to the paisa. ### `bankrecon brs` Prints the statement (excerpt below, after the review above) and exits with status 2 if it does not tie; `--format json` and the output flags work here too. ``` $ bankrecon brs BANK RECONCILIATION STATEMENT - 2026-09 ======================================================================================================================== Balance as per cash book 64,52,046.84 Add: cheques issued but not yet presented 22,73,353.78 L-0038 02-Sep PV/26-27/0028 Cheque 450327 issued to Bhavani Transport Co against B 3,22,110.95 L-0088 04-Sep PV/26-27/0051 Cheque 450326 issued to Omkar Raw Materials against BI 1,09,024.98 L-0236 14-Sep PV/26-27/0118 Cheque 450336 issued to Uday Fabricators against BILL- 1,66,496.73 ... Add: bank credits not yet booked in the cash book 3,46,900.46 B-0011 01-Sep NEFT CR-HDFC0009999-UNKNOWN REMITTER-SBINR5202609019032792 [unclassi 24,887.77 B-0012 01-Sep NEFT CR-ICIC0009999-UNKNOWN REMITTER-UTIBR5202609016527997 [unclassi 17,856.26 B-0028 02-Sep NEFT CR-SBIN0009999-UNKNOWN REMITTER-UTIBR5202609028735259 [unclassi 9,693.89 B-0030 02-Sep NEFT CR-PUNB0009999-UNKNOWN REMITTER-SBINR5202609027706513 [unclassi 24,115.93 B-0107 07-Sep NEFT RETURN-SBINR5202609043405436-ACCOUNT CLOSED [returned_payment] 48,504.58 ... Less: deposits booked but not yet credited by the bank -73,429.49 L-0213 11-Sep RV/26-27/0117 Receipt from Bharat Polymers Ltd - invoice INV-26/1145 21,564.81 L-0223 12-Sep RV/26-27/0121 Receipt from Bharat Polymers Ltd - invoice INV-26/1150 21,976.62 L-0266 15-Sep RV/26-27/0143 Counter receipt 2 29,888.06 Less: book receipts with no bank credit (possible duplicate entries) -77,242.65 L-0358 18-Sep RV/26-27/0194 Receipt from Evergreen Organics against INV-26/1166 77,242.65 Less: bank debits not yet booked in the cash book -1,59,328.60 B-0029 02-Sep ATM CASH WDL SELF 0209 [cash_withdrawal] 5,966.31 ... ------------------------------------------------------------------------------------------------------------------------ Balance as per bank (computed) 87,62,300.34 Balance as per bank statement 87,62,300.34 Difference 0.00 ======================================================================================================================== STATUS: TIED wrote out/2026-09-final.xlsx ``` ### `bankrecon explain` ``` $ bankrecon explain M-0440 ╭──────────────────────────────────────────────────────────── Match ─────────────────────────────────────────────────────────────╮ │ M-0440 rule: one_to_many status: auto confidence: 0.85 │ │ one bank line equals the sum of several book entries │ ╰────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯ * bank item B-0106 for 1,17,011.39 equals the sum of 5 book items (12,421.70 + 36,715.56 + 37,916.03 + 16,741.19 + 13,216.91 = 1,17,011.39) * Group dates within ±10 days of 2026-09-07; 22 candidates searched, smallest group size 5 (max 6) * Exactly one combination of this size fits ┏━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┓ ┃ Id ┃ Side ┃ Date ┃ Amount ┃ Narration ┃ References ┃ ┡━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━┩ │ B-0106 │ bank │ 2026-09-07 │ 1,17,011.39 │ BY CASH DEPOSIT-CDM BRANCH 0123 │ │ │ L-0102 │ book │ 2026-09-06 │ 12,421.70 │ RV/26-27/0058 Cash sales receipt counter 5 │ │ │ L-0101 │ book │ 2026-09-05 │ 36,715.56 │ RV/26-27/0057 Cash sales receipt counter 4 │ │ │ L-0092 │ book │ 2026-09-04 │ 37,916.03 │ RV/26-27/0049 Cash sales receipt counter 3 │ │ │ L-0060 │ book │ 2026-09-03 │ 16,741.19 │ RV/26-27/0031 Cash sales receipt counter 2 │ │ │ L-0042 │ book │ 2026-09-02 │ 13,216.91 │ RV/26-27/0022 Cash sales receipt counter 1 │ │ └────────┴──────┴────────────┴─────────────┴────────────────────────────────────────────┴────────────┘ Bank total 1,17,011.39 Book total 1,17,011.39 ``` `explain` also accepts a bank/book item id and tells you whether it is matched or why it is outstanding. ### Carry-forward across periods and aging ``` $ bankrecon run --bank examples/2026-10/bank.camt053.xml --ledger examples/2026-10/cashbook.csv --period 2026-10 Period 2026-10: 371 bank lines, 361 ledger lines, 57 carried-forward items Matching results ┏━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓ ┃ Rule ┃ Matches ┃ Awaiting review ┃ ┡━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩ │ reference │ 198 │ 0 │ │ cheque │ 85 │ 0 │ │ amount_date │ 35 │ 0 │ │ fuzzy │ 2 │ 2 │ │ one_to_many │ 6 │ 0 │ │ many_to_one │ 5 │ 0 │ │ many_to_many │ 3 │ 3 │ │ bank_reversal │ 1 │ 0 │ └───────────────┴─────────┴─────────────────┘ Unmatched: 65 bank, 25 book. Suggested journal entries: 65. BRS: TIED 5 suggested match(es) need review: `bankrecon review --period 2026-10` $ bankrecon outstanding --aging Outstanding at end of 2026-10 ┏━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━━━┓ ┃ Age (days) ┃ Items ┃ Net amount ┃ ┡━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━━━┩ │ 0-30 │ 58 │ -24,38,491.27 │ │ 31-60 │ 31 │ -5,09,400.77 │ │ 61-90 │ 0 │ 0.00 │ │ 90+ │ 1 │ -10,498.12 │ └────────────┴───────┴───────────────┘ ... ``` The 57 carried-forward items are the September outstanding items (cheques not yet presented, deposits in transit, unbooked bank charges, and anything left unmatched). Cheques that cleared in October and the charges booked on 1-3 October were matched against them; the opening balances were taken from the September closing balances and the BRS still ties. The one cheque dated 12 May is flagged stale. ### Other commands - `bankrecon export --xlsx report.xlsx --csv-dir out/csv --json report.json` re-exports a period after review. - `bankrecon sample --out examples [--seed N]` regenerates the sample data. - Global options: `--db FILE` (env `BANKRECON_DB`), `--config FILE.toml` (env `BANKRECON_CONFIG`). ## Input formats | Input | Notes | | --- | --- | | Bank CSV/XLSX | Header row is auto-detected, so preamble lines (SBI) and footers are fine. Profile = column names + date format. | | MT940 | `:60F:`/`:62F:` balances, `:61:` with value and entry date, `C/D/RC/RD`, `:86:` free text or `?NN` structured. The entry date is used when present. | | CAMT.053 | Any namespace version. `CdtDbtInd` is read as the effect on the account (also for `RvslInd=true`). One `Ntry` is one line even if it carries several `TxDtls`. | | Cash book CSV | Columns `Date, Voucher No, Narration, Cheque No, Debit, Credit, Party` (case/spacing flexible). Debit = money in. | | Prior outstanding | `side` (`bank`/`book`), `date`, `reference`, `narration`, `cheque_no`, `amount` (money in positive, so unpresented cheques are negative). | ## Configuration A TOML file passed with `--config` overrides pass order, windows, group-size bounds, the invoice regex, the account names used in journal entries, classification rules and custom bank profiles. See `docs/configuration.md` and the annotated `examples/bankrecon.toml`. ## Architecture ``` src/bankrecon/ models.py Txn, Match, RawLine (signed amounts: money in is positive on both sides) normalize.py dates, amounts, reference extraction, party names, token-set similarity, INR formatting config.py defaults + TOML loading importers/ tabular.py (CSV/XLSX profiles), mt940.py, camt053.py, ledger.py loaders.py RawLine/BookLine -> Txn, prior-outstanding file, period helpers engine.py the matching passes and the pruned subset-sum search classify.py rule-based classification of bank-only items -> journal entries recon.py derived state: unmatched sets, categories, aging, BRS assembly brs.py BRS model and tie-out diagnostics store.py SQLite: periods, txns, matches, notes, audit log pipeline.py run_period: import -> carry forward -> match -> store review.py accept / reject / manual match / notes with audit logging exporters.py Excel, CSV, JSON, text BRS cli.py click commands samplegen.py, sampleio.py synthetic data with planted scenarios, writers for all formats docs/ matching.md, brs.md, configuration.md examples/ two months of generated data (+ truth.json describing the planted scenarios) ``` ## Tests ```bash .venv/bin/pytest -q ``` 107 tests cover MT940/CAMT.053 parsing, reference regexes, each matching pass (including subset-sum bounds and ambiguity), the BRS tie-out identity as a property over generated data (also after random reviewer rejections), carry-forward across two periods, classification and journal entries, Excel/CSV/JSON output, `explain`, and the review workflow (flags and interactive prompts). A ground-truth test checks that every planted scenario in the September example is matched correctly and that the deliberately ambiguous pair is left alone. ## Limitations - Single currency (INR, two decimals); no FX. - Statement and ledger are matched per calendar month; the ledger date is compared to the bank booking date, so weekend and holiday shifts rely on the date windows. - Many-to-many search is bounded (groups of up to 3 per side, 12 candidates) and always needs human review. - A CAMT.053 entry with several `TxDtls` is treated as one bank line. - Cheques older than the clearing window (90 days by default) are not auto-matched; they are flagged as stale instead. - Re-running a period replaces its stored matches, including manual ones (the audit log keeps the history). Re-running an earlier period does not re-flow into later periods. - The review UI is a prompt loop, not a full-screen TUI.