Reconciling a Bank Statement CSV With Your Ledger in SQL

At the end of the month the bank says one number and the books say another. The difference is made of items that are in one and not the other: a fee the bank charged that nobody entered, a payment recorded in the ledger on the 30th that cleared on the 2nd, a refund booked twice. Finding them is reconciliation, and when both sides arrive as CSV files, it is a join with a few twists. This article does it in SQL on two small files, shows a naive match that quietly misses a pair, fixes it, and ends with the one check that proves the job is done: the unmatched items explain the difference. It is the last move in more analysis moves for CSV data, and its mechanics are those of comparing two CSV files to find differences and reconciling two CSV exports of the same data.

The two files

A bank statement of seven lines, and a ledger of six, covering the first days of October. The bank has a fee the ledger does not, a refund the ledger does not, and two equal payments from the same customer on one day:

date,description,amount
2026-10-01,CARD PAYMENT ACME LTD,1200.00
2026-10-02,TRANSFER BETA CORP INV 5521,-89.90
2026-10-02,BANK FEE,-12.00
2026-10-03,CARD PAYMENT CY FOODS,150.00
2026-10-05,CARD PAYMENT DELTA INC,200.00
2026-10-05,CARD PAYMENT DELTA INC,200.00
2026-10-07,REFUND ECHO LLC,-45.00

posted,reference,amount
2026-10-01,INV 5518 Acme Ltd,1200.00
2026-10-02,INV 5521 Beta Corp,-89.90
2026-10-04,INV 5530 Cy Foods,150.00
2026-10-05,INV 5533 Delta Inc,200.00
2026-10-06,INV 5535 Delta Inc,200.00
2026-10-09,INV 5540 Fox Ltd,75.00

The totals disagree. The bank sums to 1,603.10 and the ledger to 1,735.10, a difference of 132.00. The aim is to account for every cent of that.

Two ways to match, and why one fails

Number the rows so each can be referred to, then look for pairs. The first attempt is what most people write: join on equal amount and dates within three days, rank each bank row's candidates by closeness and take the best.

CREATE TABLE bank AS SELECT row_number() OVER () AS bid, * FROM read_csv('bank.csv');
CREATE TABLE ledger AS SELECT row_number() OVER () AS lid, * FROM read_csv('ledger.csv');

CREATE TABLE cand AS
SELECT b.bid, l.lid, abs(date_diff('day', b.date, l.posted)) AS gap
FROM bank b JOIN ledger l ON b.amount = l.amount AND abs(date_diff('day', b.date, l.posted)) <= 3;

Then keep each bank row's closest ledger row, and each ledger row's closest bank row. On the sample this matched four pairs and left two items unmatched on the ledger side and three on the bank side. Look at the leftovers: a Delta payment of 200.00 on 5 October in the bank, and a Delta invoice of 200.00 on 6 October in the ledger. They are clearly the same payment. The greedy method failed because both bank rows prefer the same ledger row (the one posted on 5 October, gap 0), only one of them can have it, and the loser never gets to consider its second choice, the ledger row from 6 October.

That is the standard failure of a one-pass match: it resolves conflicts by dropping the loser and not by reassigning. A correct greedy algorithm iterates, or solves an assignment problem, and for most reconciliations a simpler idea is enough.

Pair the n-th with the n-th

When several rows share an amount, the right pairing is usually by order: the first bank payment of 200.00 with the first ledger entry of 200.00, the second with the second. Number each row within its amount, ordered by date, and join on amount and that number:

CREATE TABLE matched AS
WITH b AS (SELECT *, row_number() OVER (PARTITION BY amount ORDER BY date, bid) AS k FROM bank),
     l AS (SELECT *, row_number() OVER (PARTITION BY amount ORDER BY posted, lid) AS k FROM ledger)
SELECT b.bid, l.lid, abs(date_diff('day', b.date, l.posted)) AS gap
FROM b JOIN l ON b.amount = l.amount AND b.k = l.k AND abs(date_diff('day', b.date, l.posted)) <= 3;

Each row now has exactly one possible partner, so there is no conflict to resolve. The two Delta payments pair as first-to-first (5 October to 5 October) and second-to-second (5 October to 6 October, a gap of one day). The summary of what matched and what did not:

SELECT 'matched' AS kind, count(*) AS n, sum(b.amount) AS amount FROM matched m JOIN bank b USING (bid)
UNION ALL SELECT 'bank only', count(*), sum(amount) FROM bank WHERE bid NOT IN (SELECT bid FROM matched)
UNION ALL SELECT 'ledger only', count(*), sum(amount) FROM ledger WHERE lid NOT IN (SELECT lid FROM matched);

kind         n  amount
matched      5  1660.1
bank only    2   -57.0
ledger only  1    75.0

Five pairs matched. Two bank rows and one ledger row are left, and they are the real differences:

bank only:    2026-10-02  BANK FEE         -12.00
              2026-10-07  REFUND ECHO LLC  -45.00
ledger only:  2026-10-09  INV 5540 Fox Ltd  75.00

The proof: the leftovers explain the gap

The job is not finished when you have a list. It is finished when the list adds up to the difference between the two totals. The ledger exceeds the bank by 1,735.10 minus 1,603.10, which is 132.00. The ledger-only item is 75.00, and the bank-only items are minus 57.00, so removing them from each side gives matched totals that agree (1,660.10 on both). In arithmetic: ledger total minus ledger-only equals bank total minus bank-only, and 1,735.10 minus 75.00 is 1,660.10, and 1,603.10 minus the bank-only minus 57.00 is also 1,660.10. The check is a single query on the totals, and if it does not balance, a row is in the wrong bucket, a duplicate was double-counted or a join dropped rows.

Do the same accounting for every period and keep the result. A reconciliation report is a table: opening difference, matched amount, bank-only items, ledger-only items and closing difference, each with a count. Anything in the bank-only column is something to enter in the ledger (the fee, the refund), and anything in the ledger-only column is a timing item that should clear next period, or an error to chase. The Fox invoice dated 9 October is probably a payment that has not cleared yet.

What makes real data harder

The sample has few rows with unique amounts, so amounts nearly suffice. Real statements do not.

Many payments share amounts: subscriptions of 29.00, a fee of 12.00 each month, refunds of the same size. Pairing the n-th with the n-th works while the sequences line up, and breaks when one side is missing an item in the middle, because every later pair shifts by one. When that happens, add more to the key: a reference number extracted from the description (INV 5521 appears in both the bank line and the ledger line), a customer name or an account code. Extracting the invoice number with a regex (regexp_extract(description, 'INV (\d+)', 1)) and joining on it first, then falling back to amount and date for the remainder, matches most rows exactly and leaves the hard ones for review. Match in stages from the most certain key to the least, and record the stage that matched each pair, so a person can audit it.

Amounts have formats: 1,200.00, 1200,00, (89.90) for negatives, separate debit and credit columns. Normalize to a signed decimal before joining, and never join on floats; use a decimal type or integer cents, since 0.1 + 0.2 is not 0.3 in binary (why a CSV file has no types and what that costs you). Currencies, bank fees netted from a deposit, and one bank line that pays several invoices (a batch) produce many-to-one matches that equality on amount cannot find: a payment of 400.00 against invoices of 150.00 and 250.00 needs a subset-sum search, or a person.

Dates drift by weekends and holidays, hence the tolerance. Three days is a common choice for cards, with wider windows for cheques and international transfers. A tolerance that is too wide pairs the wrong equal amounts; too narrow, and a payment that cleared over a long weekend looks missing.

When amounts alone are not enough to match

If more than a few percent of rows remain unmatched, the method has hit its limit, and the leftovers need a human. Produce the exceptions as a worklist, sorted by amount, with the nearest candidate on the other side and the gap, so each can be resolved in seconds. Do not widen the rules until the list is empty: a reconciliation that forces matches is worse than an honest list of items that need a decision. For recurring work, write the stages as a script with tests on a small fixture like the one above, keep each month's matched pairs, and alert when the unexplained difference is not zero (writing data quality checks for a CSV).

If the files contain personal data, treat the working tables with the same care as the originals, since a bank statement is sensitive (handling CSV files full of customer data under GDPR). The SQL functions used are documented in DuckDB's window function and date function pages.