Pairing inter-account transfers across bank statements with pandas

To pair inter-account transfers across bank statements, match each outflow to an inflow of the same absolute amount, in a different account, within about 3 days, and use each row only once. Work in integer cents. Transfers left unpaired are the finding: in bookkeeping a missing account in your setup, in divorce disclosure an account nobody has produced.

When you have transactions from several accounts belonging to one household or one business, a lot of the rows are the same money moving around. Here is a pandas version that handles the annoying cases.

What input does transfer matching need?

One DataFrame, one row per transaction, money out negative. This is the shape you get when you download several converted statements as one file with an account column.

import pandas as pd

tx = pd.DataFrame([
    ("2025-01-06", "Checking 2210", "TRANSFER TO SAV 7781",        -5000.00),
    ("2025-01-06", "Savings 7781",  "TRANSFER FROM CHK 2210",       5000.00),
    ("2025-03-02", "Checking 2210", "ONLINE TRANSFER REF 88341",   -2500.00),
    ("2025-04-15", "Checking 2210", "PAYMENT CARD 4409",           -1800.00),
    ("2025-04-16", "Card 4409",     "PAYMENT THANK YOU",            1800.00),
    ("2025-05-01", "Checking 2210", "PAYMENT TO CARD ENDING 9120",  -650.00),
    ("2025-05-03", "Checking 2210", "MOBILE DEPOSIT",                650.00),
], columns=["date", "account", "description", "amount"])
tx["date"] = pd.to_datetime(tx["date"])
tx["cents"] = (tx["amount"] * 100).round().astype(int)

Match on integer cents. Comparing floats for equality will eventually betray you.

The input for this script: several statements ticked and downloaded together, with the account on every row.

How do you find candidate transfer pairs in pandas?

outs = tx[tx.cents < 0].reset_index().rename(columns={"index": "out_id"})
ins  = tx[tx.cents > 0].reset_index().rename(columns={"index": "in_id"})
outs["key"] = -outs["cents"]
ins["key"]  =  ins["cents"]

cand = outs.merge(ins, on="key", suffixes=("_out", "_in"))
cand = cand[cand.account_out.ne(cand.account_in)]      # must change account
cand["lag"] = (cand.date_in - cand.date_out).dt.days
cand = cand[cand.lag.between(0, 3)]                    # receiver may post later

Look at the last two sample rows: a 650.00 card payment and a 650.00 deposit, two days apart, in the same account. Without the account rule they pair, and an unexplained card payment disappears from your list.

The rule that the money must change accounts is the most important line in the script. Without it, coincidences hide findings.

How do you make sure each row is paired only once?

Several outs can match several ins when amounts repeat, such as two monthly 500.00 transfers. Take the closest dates first and never reuse a row.

cand = cand.sort_values(["lag", "date_out"])
used_out, used_in, pairs = set(), set(), []
for r in cand.itertuples():
    if r.out_id in used_out or r.in_id in used_in:
        continue
    used_out.add(r.out_id); used_in.add(r.in_id)
    pairs.append((r.out_id, r.in_id))

paired = {i for p in pairs for i in p}

How do you list the transfers that never landed?

looks_like_transfer = tx.description.str.contains(
    r"TRANSFER|PAYMENT CARD|PAYMENT TO CARD|ZELLE|XFER", case=False)
unpaired = tx[looks_like_transfer & ~tx.index.isin(paired)]
print(unpaired[["date", "account", "description", "amount"]])

        date        account                  description  amount
2 2025-03-02  Checking 2210    ONLINE TRANSFER REF 88341 -2500.0
5 2025-05-01  Checking 2210  PAYMENT TO CARD ENDING 9120  -650.0

The result for the seven sample rows:

Out	Amount	Paired with	Lag
TRANSFER TO SAV 7781	-5,000.00	Savings 7781, 01/06	0 days
ONLINE TRANSFER REF 88341	-2,500.00	nothing
PAYMENT CARD 4409	-1,800.00	Card 4409, 04/16	1 day
PAYMENT TO CARD ENDING 9120	-650.00	nothing (same-account deposit rejected)

The keyword filter is a starting point, not a classifier. Also review every unpaired outflow over a threshold, since some banks describe transfers in ways no regex will guess.

What must be true before you trust the result?

Pairing assumes every statement is present and complete. A missing month in the receiving account creates false unpaired rows. Check two things first:

1. Each statement's rows rebuild its printed closing balance from its opening balance.
2. Each statement's opening balance equals the previous statement's closing balance for the same account.

def chain_breaks(stmts: pd.DataFrame) -> pd.DataFrame:
    """stmts: one row per statement with account, period_end, opening, closing."""
    s = stmts.sort_values(["account", "period_end"]).copy()
    s["prev_close"] = s.groupby("account")["closing"].shift()
    return s[s.prev_close.notna() & s.prev_close.ne(s.opening)]

Any row this returns is a missing or altered statement, and a question in its own right.

Key takeaways

• Match on amount and date, in integer cents, never on the description.
• Require the money to change accounts, and allow a lag of 0 to 3 days.
• Pair each row once, closest dates first.
• Prove statement completeness before matching, or gaps show up as false findings.

FAQ

Why not match on the transfer description?

Banks write transfers in their own shorthand, often with only a reference number. Amount, date and a change of account are the reliable keys.

How long a lag should transfer matching allow?

Three days covers most banks posting the receiving side late. Widen it if you see weekend or holiday gaps in your data.

Does this replace a forensic accountant?

No. It produces a list of transfers to explain. Interpreting them, and anything legal, belongs to the professionals on the case. Nothing here is legal advice.

The divorce use case, with the column layout reviewers expect and a reviewed worked month, is the guide to bank statements for divorce. Disclosure: I'm the founder of ZeraBooks. The ZeraBooks bank statement converter produces the input: PDF statements read, each account checked against its printed balances, downloaded together as one file with the account on every row. It doesn't do the pairing or any flagging; that is the reviewer's job.