---
name: bank-reconciliation
description: Reconciles a month's bank statement export to the books, pairs each bank line with a ledger entry by amount and date, lists bank-only items (fees, interest, unknown charges) and outstanding items (uncleared cheques, deposits in transit), and proves the adjusted balances agree. Use when an owner or bookkeeper asks to reconcile a bank or card account at month end, or to find why the bank and the books do not agree.
metadata:
  ahl-category: money
  ahl-jobs: bookkeeping, reporting
  ahl-industries: retail-shop, restaurant, accounting-firm, plumbing, salon
  ahl-connectors: quickbooks-online, xero
  ahl-workflows: money--02-month-end-match-bank-export-to-invoices, money--1822-accounting-firm-monthly-close-client-questions, money--2355-any-till-reconciliation
  ahl-human-check: The owner or bookkeeper explains every bank-only line and confirms the reconciliation; only the bookkeeper posts entries.
  ahl-proposed-status: approved
  ahl-version: 1
  ahl-sources: |
    Internal Revenue Service — What kind of records should I keep — https://www.irs.gov/businesses/small-businesses-self-employed/what-kind-of-records-should-i-keep — 2026-10-06
---

# Bank reconciliation

Pairs the bank's lines with the books' lines for one account and one period, then shows the two balances agree once timing items are counted. The agent prepares the reconciliation; a person explains the leftovers and posts any entry.

## Inputs

- `bank.csv`: `date, description, amount` from the bank's export (deposits positive, payments negative). See [examples/bank.csv](examples/bank.csv).
- `ledger.csv`: `date, ref, description, amount` for the same account from the accounting system's register. See [examples/ledger.csv](examples/ledger.csv).
- The statement ending balance and the book ending balance for the same date.

The owner exports both files; the intern and the agent never log in to the bank. If the register comes from the accounting system, read it through the AHL Action Gateway or the bookkeeper's export. The agent holds only its agent token and stores no credentials in files or prompts. Before anything reaches a hosted model, strip full account and card numbers from descriptions (keep at most the last four digits).

## Steps

1. **Check signs and dates.** Some banks export debits and credits in two columns; turn them into one signed `amount`. Dates must be `YYYY-MM-DD`.
2. **Run the script:**
   ```bash
   node scripts/reconcile.mjs examples/bank.csv examples/ledger.csv --bank-ending 4946.28 --book-ending 5234.24
   ```
   It pairs lines with the exact same amount whose dates are within 4 days (`--days N` to change it, for example when cheques clear slowly), choosing the closest date.
3. **Read the three lists:**
   - **On the statement, not in the books**: bank fees, interest, card-processor deductions, automatic debits nobody recorded, or charges nobody recognises.
   - **In the books, not on the statement**: cheques not yet cashed and deposits in transit. Anything older than the previous statement is suspicious: a lost cheque, a deposit never made, or a duplicate entry.
   - **Proof**: statement balance plus outstanding items must equal book balance plus statement-only items. A difference other than $0.00 means a wrong amount, a transposed figure, a duplicate or a missing line.
4. **Find near-misses.** For a non-zero difference, look for a bank-only and a book-only line whose amounts differ by the difference, or by a difference divisible by 9 (two swapped digits). Suggest the pair; do not change either record.
5. **Write questions, not entries.** For each statement-only line, write one plain question for the owner ("A $38.62 card purchase at a convenience store on Sept 30: business or personal, and what for?"). Suggest an account only from the business's own chart of accounts.
6. **Flag at once** any charge nobody recognises, any duplicate payment, or any payment to a new payee. That goes to the owner the same day, not in the month-end pack.

## Output

The script's report (compare [examples/expected-output.md](examples/expected-output.md)), then a short table of questions:

```
| Bank line | Question for the owner | Suggested account (to confirm) |
| 2026-09-30 MONTHLY SERVICE FEE -$15.00 | Expected monthly fee? | Bank charges |
```

Keep the statement and the reconciliation together: the IRS lists deposit information, cancelled cheques and credit card statements among the supporting documents a business keeps (IRS, What kind of records should I keep).

## Edge cases

- **One deposit, many book lines** (a card batch recorded per sale): add the book lines for that day and compare the total; mark it a grouped match in your notes.
- **Processor deposits net of fees**: the bank shows net, the books may show gross. Use the payout-reconciliation skill first.
- **Opening balances do not agree with last month's reconciliation**: stop; someone changed a closed period. Tell the bookkeeper.
- **Foreign currency or loan accounts**: out of scope; hand to the bookkeeper.

## Never

- Log in to a bank, move money, or create, edit, delete or post any entry.
- Mark a line reconciled in the accounting system.
- Decide a charge is personal, an owner draw or a loan; ask.
- Hide a difference by inventing an adjusting line.
- Put full account or card numbers in any output.

## Hand over

The bookkeeper reads the proof line first, then each statement-only line and its question, and each outstanding item older than one statement. They confirm the pairs on a sample, post the entries they approve, and mark the account reconciled in their own system. Unknown charges go to the owner the same day.

date,description,amount
2026-09-02,CARD SETTLEMENT BATCH 0901,1842.17
2026-09-05,CHECK 1188,-640.00
2026-09-08,ACH BLUEWATER UTIL,-212.43
2026-09-15,CARD SETTLEMENT BATCH 0914,2210.55
2026-09-19,ACH PAYROLL SVC,-3105.80
2026-09-29,CHECK 1190,-95.00
2026-09-30,MONTHLY SERVICE FEE,-15.00
2026-09-30,INTEREST PAID,0.41
2026-09-30,POS DEBIT QUIKMART 4471,-38.62

# Bank reconciliation (date window 4 days)

Matched: 6 of 9 bank lines.

## On the statement, not in the books (owner or bookkeeper to explain)
- 2026-09-30 MONTHLY SERVICE FEE: -$15.00
- 2026-09-30 INTEREST PAID: $0.41
- 2026-09-30 POS DEBIT QUIKMART 4471: -$38.62

## In the books, not on the statement (outstanding)
- 2026-09-28 1191 Tri-County Linen: -$180.25 (outstanding payment)
- 2026-09-30 DEP-0930 Cash deposit Sept 30: $415.00 (deposit in transit)

## Proof
Statement ending balance $4946.28 + outstanding items $234.75 = $5181.03
Book ending balance $5234.24 + statement-only items -$53.21 = $5181.03
Difference $0.00: reconciles once a person confirms the statement-only items.

date,ref,description,amount
2026-09-01,DEP-0901,Card sales Sept 1,1842.17
2026-09-02,1188,Elm Street Rentals - storage unit,-640.00
2026-09-08,EFT-77,Bluewater Utilities,-212.43
2026-09-14,DEP-0914,Card sales Sept 14,2210.55
2026-09-19,PR-0919,Payroll Sept 15-19,-3105.80
2026-09-25,1190,Cedar Sign Co - banner,-95.00
2026-09-28,1191,Tri-County Linen,-180.25
2026-09-30,DEP-0930,Cash deposit Sept 30,415.00

#!/usr/bin/env node
// Bank reconciliation: pairs bank statement lines with book (ledger) lines, then
// checks that the adjusted bank balance equals the adjusted book balance.
// Usage: node scripts/reconcile.mjs <bank.csv> <ledger.csv> --bank-ending 1234.56 --book-ending 1200.00 [--days 4]
// Local files only, no network. Amounts are integer cents; deposits positive, payments negative.
import fs from "node:fs";

// Default 4 days: a cheque or card settlement often clears a few days after the
// book date. The owner or bookkeeper can widen or narrow it with --days.
let DATE_WINDOW_DAYS = 4;

function parseCsv(path) {
  let text;
  try { text = fs.readFileSync(path, "utf8"); } catch { console.error(`Cannot read ${path}. Check the file name.`); process.exit(2); }
  const rows = [];
  let row = [], field = "", q = false;
  for (let i = 0; i < text.length; i++) {
    const c = text[i];
    if (q) { if (c === '"' && text[i + 1] === '"') { field += '"'; i++; } else if (c === '"') q = false; else field += c; }
    else if (c === '"') q = true;
    else if (c === ",") { row.push(field); field = ""; }
    else if (c === "\n" || c === "\r") { if (c === "\r" && text[i + 1] === "\n") i++; row.push(field); field = ""; if (row.some((x) => x !== "")) rows.push(row); row = []; }
    else field += c;
  }
  if (field !== "" || row.length) { row.push(field); if (row.some((x) => x !== "")) rows.push(row); }
  const [head, ...data] = rows;
  return data.map((r) => Object.fromEntries(head.map((h, i) => [h.trim(), (r[i] ?? "").trim()])));
}
const cents = (s) => { const n = Number(String(s).replace(/[$,]/g, "")); if (!Number.isFinite(n)) { console.error(`Not an amount: "${s}"`); process.exit(2); } return Math.round(n * 100); };
const money = (c) => (c < 0 ? "-" : "") + "$" + (Math.abs(c) / 100).toFixed(2);
const day = (d) => Date.parse(d + "T00:00:00Z") / 86400000;

const args = process.argv.slice(2);
const opt = (name) => { const i = args.indexOf(name); if (i < 0) return undefined; const v = args[i + 1]; args.splice(i, 2); return v; };
const bankEnding = opt("--bank-ending"), bookEnding = opt("--book-ending"), days = opt("--days");
if (days !== undefined) DATE_WINDOW_DAYS = Number(days);
if (args.length < 2 || bankEnding === undefined || bookEnding === undefined) {
  console.error("Usage: node scripts/reconcile.mjs <bank.csv> <ledger.csv> --bank-ending N --book-ending N [--days N]");
  process.exit(2);
}
const bank = parseCsv(args[0]).map((r) => ({ ...r, c: cents(r.amount), d: day(r.date) })).sort((a, b) => a.d - b.d);
const book = parseCsv(args[1]).map((r) => ({ ...r, c: cents(r.amount), d: day(r.date), used: false }));

const pairs = [], bankOnly = [];
for (const b of bank) {
  let best = null;
  for (const l of book) if (!l.used && l.c === b.c && Math.abs(l.d - b.d) <= DATE_WINDOW_DAYS && (!best || Math.abs(l.d - b.d) < Math.abs(best.d - b.d))) best = l;
  if (best) { best.used = true; pairs.push([b, best]); } else bankOnly.push(b);
}
const bookOnly = book.filter((l) => !l.used);

console.log(`# Bank reconciliation (date window ${DATE_WINDOW_DAYS} days)\n`);
console.log(`Matched: ${pairs.length} of ${bank.length} bank lines.\n`);
console.log("## On the statement, not in the books (owner or bookkeeper to explain)");
if (!bankOnly.length) console.log("- none");
for (const b of bankOnly) console.log(`- ${b.date} ${b.description}: ${money(b.c)}`);
console.log("\n## In the books, not on the statement (outstanding)");
if (!bookOnly.length) console.log("- none");
for (const l of bookOnly) console.log(`- ${l.date} ${l.ref} ${l.description}: ${money(l.c)} (${l.c > 0 ? "deposit in transit" : "outstanding payment"})`);

const bankEnd = cents(bankEnding), bookEnd = cents(bookEnding);
const outstanding = bookOnly.reduce((s, l) => s + l.c, 0);
const bankItems = bankOnly.reduce((s, b) => s + b.c, 0);
const adjBank = bankEnd + outstanding, adjBook = bookEnd + bankItems;
console.log("\n## Proof");
console.log(`Statement ending balance ${money(bankEnd)} + outstanding items ${money(outstanding)} = ${money(adjBank)}`);
console.log(`Book ending balance ${money(bookEnd)} + statement-only items ${money(bankItems)} = ${money(adjBook)}`);
console.log(adjBank === adjBook ? "Difference $0.00: reconciles once a person confirms the statement-only items." : `Difference ${money(adjBank - adjBook)}: does NOT reconcile. Look for a wrong amount, a duplicate or a missing line.`);