Match bank statement entries to the cash book by UTR and cheque number

Why reference-first matching

A bank statement narration is a string such as NEFT DR-HDFCN52026090212345-SHARMA SUPPLIERS. The middle part is the transaction reference the bank assigned. If the accounts team recorded that reference on the payment voucher, the match is unambiguous, whatever the amount or date. Cheques work the same way with the cheque number. Amount and date are weaker evidence: they agree by coincidence often enough in a busy account to produce confident wrong pairs.

Reference formats differ between banks and payment types, and they are not always the same length, so the script does not validate them. It simply looks for the voucher's reference text inside the narration and ignores references too short to be distinctive.

The passes in pandas

Every entry, reference and name below is synthetic, and so is the narration layout.

import re
import pandas as pd

# Synthetic cash book (bank ledger in the accounts system) and bank statement for Sep 2026.
book = pd.DataFrame({
    "vch": ["PMT-101", "PMT-102", "PMT-103", "RCT-201", "RCT-202", "PMT-104", "RCT-203"],
    "date": ["2026-09-02", "2026-09-05", "2026-09-10", "2026-09-12", "2026-09-18",
             "2026-09-28", "2026-09-30"],
    "ref": ["HDFCN52026090212345", "CHQ 004512", "CHQ 004513", "UTIBR52026091244001",
            "", "CHQ 004514", "CASH DEP"],
    "amount": [-85000.0, -42500.0, -18000.0, 236000.0, 59000.0, -27500.0, 40000.0],
})
bank = pd.DataFrame({
    "date": ["2026-09-02", "2026-09-08", "2026-09-12", "2026-09-18", "2026-09-19",
             "2026-09-30", "2026-09-30"],
    "narration": ["NEFT DR-HDFCN52026090212345-SHARMA SUPPLIERS",
                  "CHQ PAID-004512-AGARWAL TRADERS",
                  "NEFT CR-UTIBR52026091244001-GUPTA ENTERPRISES",
                  "UPI/CR/626100884411/KAVYA RAO/SBIN/kavya.r@oksbi/INV 1188",
                  "IMPS/P2A/626200553300/ANIL MEHTA",
                  "CHGS NEFT/RTGS SEP26 INCL GST",
                  "INT CREDIT"],
    "amount": [-85000.0, -42500.0, 236000.0, 59000.0, 12000.0, -354.0, 1210.0],
})
for df in (book, bank):
    df["date"] = pd.to_datetime(df["date"])
book["book_id"], bank["bank_id"] = range(len(book)), range(len(bank))
matched = []

def take(pairs, rule):
    for b_id, k_id in pairs:
        matched.append((b_id, k_id, rule))

def open_rows():
    used_b = {m[0] for m in matched}
    used_k = {m[1] for m in matched}
    return book[~book["book_id"].isin(used_b)], bank[~bank["bank_id"].isin(used_k)]

# Pass 1: the book's reference appears inside the bank narration and amounts agree.
b, k = open_rows()
pairs = []
for r in b.itertuples():
    token = re.sub(r"^CHQ\s*", "", r.ref).strip()
    if len(token) < 6:
        continue
    hit = k[k["narration"].str.contains(re.escape(token)) & (k["amount"] == r.amount)]
    if len(hit) == 1:
        pairs.append((r.book_id, hit["bank_id"].iloc[0]))
take(pairs, "reference")

# Pass 2: same amount within 3 days, only where exactly one candidate exists on each side.
b, k = open_rows()
cand = b.merge(k, on="amount", suffixes=("_b", "_k"))
cand = cand[(cand["date_k"] - cand["date_b"]).dt.days.between(0, 3)]
cand = cand[~cand["book_id"].duplicated(keep=False) & ~cand["bank_id"].duplicated(keep=False)]
take(zip(cand["book_id"], cand["bank_id"]), "amount+date (review)")

m = pd.DataFrame(matched, columns=["book_id", "bank_id", "rule"])
print(m.merge(book[["book_id", "vch"]]).merge(bank[["bank_id", "narration"]])
      [["vch", "rule", "narration"]].to_string(index=False))

b, k = open_rows()
print("\nIn books, not yet in bank:\n", b[["vch", "ref", "amount"]].to_string(index=False))
print("In bank, not yet in books:\n", k[["narration", "amount"]].to_string(index=False))

# Bank reconciliation statement: start from the book balance and walk to the bank balance.
opening = 500000.0                                  # both agreed at 31 Aug 2026 (synthetic)
book_bal = opening + book["amount"].sum()
bank_bal = opening + bank["amount"].sum()
brs = book_bal - b["amount"].sum() + k["amount"].sum()
print(f"\nbook {book_bal:.2f} | less items only in books | add items only in bank = {brs:.2f}"
      f" | bank statement {bank_bal:.2f}")
assert round(brs, 2) == round(bank_bal, 2) == 680356.0

Four of seven book entries are matched. Three match on reference: one outgoing and one incoming NEFT transfer identified by their references, and cheque 004512 found by number in the "CHQ PAID" narration. The ₹59,000 receipt had no reference in the books, and the only bank credit of that amount within three days is a UPI payment carrying "INV 1188" in its note, so it is paired but labelled for review. Someone should confirm that invoice 1188 is the one RCT-202 was booked against.

Reading what is left

The unmatched items are the reconciliation, and each has a usual explanation:

The statement at the end of the script does the arithmetic a reviewer checks: start from the book balance of ₹6,62,000, remove items only in the books, add items only in the bank, and arrive at ₹6,80,356, which equals the bank's closing balance. If those two figures differ, something was matched wrongly or an entry is missing from one side, and no amount of explaining the open items will fix it.

The rules that keep false matches out

Amounts must agree in pass one too. A reference match with a different amount is a finding, not a match: a short payment, a bank charge deducted from a transfer, or a reference typed on the wrong voucher. Leaving it unmatched puts it on the list where someone will look at it.

Pass two insists on one-to-one. The merge on amount can produce many candidate pairs. Dropping any book entry or bank entry that appears in more than one candidate pair means the script never chooses between two plausible matches. That choice belongs to a person.

The date window runs forwards only. The bank date is compared as zero to three days after the book date, since a payment is normally recorded when made and cleared later. Widen the window for cheques if your clearing delays are longer, but keep it asymmetric.

A reference lookup formula for pass one

For pass one, with the bank narration in column B of the Bank sheet and the reference in column C of the Book sheet, this finds the bank row whose narration contains the reference and whose amount agrees:

=IFERROR(XMATCH(1, ISNUMBER(SEARCH(C2, Bank!$B$2:$B$500)) * (Bank!$C$2:$C$500 = D2)), "")

It returns the matching row's position, or blank. Wrap it in a check that the reference is at least six characters long, and add a count with SUM(ISNUMBER(SEARCH(...))*...) to confirm there is exactly one hit. Pass two is where Excel becomes awkward, because the one-to-one condition needs counts in both directions; many teams do pass one in Excel and the rest by hand, which works while the unmatched list stays short.

Making next month easier

The strongest improvement is upstream: record the bank reference on every payment and receipt voucher when it is booked. Salary payments that bounced return as credits and can be traced using the upload file described in building a salary bank upload file with control totals. Statements with many UPI lines benefit from categorising UPI narrations with a rules table before matching.

Matching records from two systems and explaining every difference is a skill that carries well beyond finance, and it is one of the practical exercises in our Data Analyst course.