Reconciling Bank Statements Against a Database Table

The bank says the account holds 148,320.55. The ledger says 150,112.05. The difference is 1,791.50, and somebody has to explain every cent of it before the books can close. Doing this by eye in a workbook with two sheets and a lot of VLOOKUP works for 40 transactions and falls apart at 4,000. A database does the matching quickly and, more usefully, keeps a record of what was matched to what. This article builds a reconciliation in SQL with Excel as the place where a person reviews the exceptions. It belongs to Excel reports that finance teams rely on, and its staging step follows staging Excel data in a table first.

What a reconciliation proves

A reconciliation answers a specific question: why does the bank balance differ from the ledger balance? The answer is a list. Some differences are timing: a cheque written and recorded in the ledger but not yet presented at the bank; a deposit made on the last day and credited the next. Some are items the bank knows about and the ledger does not: fees, interest, a direct debit nobody recorded. Some are errors: a transposed amount, a duplicate entry. Each has an owner and a next step, and once they are all listed the adjusted balances agree.

The test of a good reconciliation is that every item on both sides is either matched to an item on the other side or listed as a reconciling item with a reason. Nothing is left unexplained. A report that shows only the totals and a difference of zero is a claim; the list of matched and reconciling items is the evidence.

Load the statement into staging

Bank files come as CSV, OFX, MT940 or an Excel download. Read it into a data frame, fix the types and load it into a staging table, never straight into a comparison sheet.

import pandas as pd

df = pd.read_excel("statement_2026-09.xlsx", sheet_name="Transactions",
                   dtype={"reference": "string"}, parse_dates=["value_date"])
df.columns = ["value_date", "reference", "description", "debit", "credit", "balance"]
df["amount"] = (df["credit"].fillna(0) - df["debit"].fillna(0)).round(2)
df = df.dropna(subset=["value_date"])

The amount is signed: money in is positive, money out is negative, so the sum of the column is the net movement. Keep it as a decimal, not a float, when you load it:

CREATE TABLE bank_stmt (
  line_no     integer PRIMARY KEY,
  value_date  date NOT NULL,
  reference   text,
  description text,
  amount      numeric(14,2) NOT NULL,
  matched_ledger_id bigint
);

Load with COPY or executemany. Check the load against the file: the row count and the sum of amount must equal the statement's own totals (opening balance plus the sum of movements equals the closing balance). If they do not, the file was read wrongly, and matching will only compound it (validating row counts after an Excel import).

Statement quirks: dates in the bank's locale, amounts with thousands separators or as separate debit and credit columns, references truncated to a fixed width, descriptions spread over several lines. Normalize in the loader and record any rule you applied.

The ledger side

The ledger table holds the cash account postings: an id, a posting date, a reference, an amount and a status.

CREATE VIEW cash_ledger AS
SELECT id, posted_on, reference, description, amount
FROM ledger
WHERE account = '1000-cash' AND posted_on <= DATE '2026-09-30' AND reversed_by IS NULL;

Agree the cut-off with finance: ledger items up to the statement date, and which date to use (posting date or value date). Exclude reversed entries or net them with their reversals. The ledger's sign convention must match the statement's (money in positive).

Match in passes

Matching is a sequence of rules from the strictest to the loosest. Each pass marks matched items so they are not used again.

Pass 1: exact reference and amount. Where the bank reference equals the ledger reference and the amounts agree.

UPDATE bank_stmt b
SET matched_ledger_id = l.id
FROM cash_ledger l
WHERE b.matched_ledger_id IS NULL
  AND l.reference = b.reference
  AND l.amount = b.amount
  AND NOT EXISTS (SELECT 1 FROM bank_stmt x WHERE x.matched_ledger_id = l.id);

The NOT EXISTS guard stops a ledger item being claimed by two statement lines. If several candidates share a reference and amount, the update picks any one that satisfies the join, so make the rule deterministic by ordering through a DISTINCT ON subquery on the lowest id. For repeating payments (monthly rent of the same amount), reference alone is not unique; add the date window in pass 2.

Pass 2: same amount, date within a window. Where the amounts agree and the dates are within, say, three days, for items without a usable reference.

WITH cand AS (
  SELECT DISTINCT ON (b.line_no) b.line_no, l.id AS ledger_id
  FROM bank_stmt b
  JOIN cash_ledger l
    ON l.amount = b.amount
   AND l.posted_on BETWEEN b.value_date - 3 AND b.value_date + 3
  WHERE b.matched_ledger_id IS NULL
    AND NOT EXISTS (SELECT 1 FROM bank_stmt x WHERE x.matched_ledger_id = l.id)
  ORDER BY b.line_no, abs(l.posted_on - b.value_date), l.id
)
UPDATE bank_stmt b SET matched_ledger_id = c.ledger_id FROM cand c WHERE b.line_no = c.line_no;

DISTINCT ON takes, for each statement line, the nearest-dated candidate. Two statement lines with the same amount can still claim the same ledger item in one pass, so run this pass in a loop or restrict to unambiguous cases (exactly one candidate on each side) and leave the rest for a person.

Pass 3: fuzzy description. Where text matches: the payee name in the description. Use ILIKE or trigram similarity (pg_trgm) for candidates, and mark them as "suggested", not matched, for human confirmation.

Record the pass that produced each match (a match_rule column), so the report can say how many were exact and how many were judgment calls.

Many to one and one to many

Real statements contain batches. A deposit of 3,210.00 on the statement covers five ledger receipts. A single ledger payment is split into two bank debits. Neither fits the one to one passes. Handle them separately and explicitly: group the ledger items by deposit batch id if one exists, or search for subsets that sum to the bank amount within the window (a subset-sum problem; limit it to small candidate sets). Where no rule applies, leave it for a person with the candidates shown. An automated match that silently picks the wrong subset is worse than an unmatched item.

The unmatched lists

After the passes, two lists remain.

-- On the statement, not in the ledger
SELECT line_no, value_date, reference, description, amount
FROM bank_stmt WHERE matched_ledger_id IS NULL ORDER BY value_date;

-- In the ledger, not on the statement
SELECT l.id, l.posted_on, l.reference, l.description, l.amount
FROM cash_ledger l
WHERE NOT EXISTS (SELECT 1 FROM bank_stmt b WHERE b.matched_ledger_id = l.id)
ORDER BY l.posted_on;

Classify each item with a reason and an owner:

Write the classification back to the table (a reason column), so the next month's reconciliation starts from the carried forward items.

Prove the balance

The check that closes a reconciliation:

Statement closing balance
  + deposits in transit (ledger only, receipts)
  - outstanding payments (ledger only, payments)
= adjusted bank balance

Ledger closing balance
  + statement-only credits (to post)
  - statement-only debits (to post)
= adjusted ledger balance

Adjusted bank balance = adjusted ledger balance

In SQL, compute each term from the lists and compare to the cent. If they differ, the matching has an error: an item counted twice, an item missed, a date outside the window. The difference localizes by splitting the period. Do not force the balance with a plug amount.

Review in Excel

The review is where Excel earns its place. Export a workbook for the reviewer with sheets for the summary (the proof above), matched items (with rule and ids), statement-only items and ledger-only items with reason columns, and the README. Highlight unclassified items. The reviewer fills in or corrects reasons, which are read back into the table. Use real number and date cells, a frozen header and filters (building multi-sheet Excel reports). Hold the final version, signed off, in the evidence record.

Testing

Make a test set with every case: an exact match, a match by amount and date, two identical amounts on the same day, a ledger item with no statement line, a statement fee with no ledger entry, a batch deposit, a negative amount, a reversed ledger entry and a statement line outside the period. Assert the matches, the unmatched lists and the proof. Run the test whenever a rule changes. Matching rules are exactly the kind of logic that drifts unless it is tested.

When automated matching is not enough

Automated matching is not enough when references are unreliable (many banks truncate or reformat them), when batches dominate, or when the volume of exceptions is high because upstream recording is poor. Then the effort belongs upstream: capture the bank reference at posting, record deposits with batch ids, and post fees from the statement automatically. It is also not enough as a replacement for a control: a person who understands the business must review the reconciling items and sign off. See the PostgreSQL manual on DISTINCT ON and the pg_trgm module for the matching tools used above.