Bank statement analysis in pandas: netting out own-account transfers before you average deposits

To analyze bank statements correctly in pandas, load every account the borrower owns into one frame with an Account column, pair each outflow in one account with the matching inflow in another, drop those pairs from the deposit totals, and only then compute monthly averages, NSF counts and balances. Skip the pairing and deposits are overstated. ZeraBooks Convert produces the input file, checked against each statement's printed balances.

I'm on the ZeraBooks team. We don't do the analysis itself; our converter turns statement PDFs into a spreadsheet and proves each one. The step that's easiest to skip after conversion is the transfer pairing, so this post is the code for it.

The input

Convert can download several statements together as one file with an Account column. Assume a CSV like this:

Account,Date,Description,Type,Amount
Checking ...2214,2026-07-03,ONLINE TRANSFER FROM SAV ...8830,Transfer,4000.00
Savings ...8830,2026-07-03,ONLINE TRANSFER TO CHK ...2214,Transfer,-4000.00
Checking ...2214,2026-07-05,DEPOSIT CLIENT 1187,Deposit,6200.00
Checking ...2214,2026-08-14,NSF RETURNED ITEM FEE,Fee,-35.00
...

Amounts are signed: money in positive, money out negative. Type comes from the section the statement printed the line under, which helps but isn't enough on its own. Plenty of banks print an incoming transfer as a plain deposit.

import pandas as pd

df = pd.read_csv("statements.csv", parse_dates=["Date"], dtype={"Amount": "float64"})
df["Amount"] = df["Amount"].round(2)
df["Month"] = df["Date"].dt.to_period("M")

For anything you'll hand to a lender I'd use Decimal, but cents-rounded floats are fine for exploration.

Pairing transfers across accounts

A transfer between two of the borrower's own accounts shows up twice: negative in one account, positive in the other, same amount, within a day or two. The pairing below is greedy and conservative: exact amount, different account, opposite sign, dates within a window.

def pair_transfers(df: pd.DataFrame, window_days: int = 3) -> pd.DataFrame:
    df = df.copy()
    df["pair_id"] = pd.NA
    outs = df[df["Amount"] < 0].sort_values("Date")
    ins = df[df["Amount"] > 0]
    used = set()
    pid = 0
    for i, o in outs.iterrows():
        cands = ins[
            (ins["Amount"] == -o["Amount"])
            & (ins["Account"] != o["Account"])
            & ((ins["Date"] - o["Date"]).abs().dt.days <= window_days)
            & (~ins.index.isin(used))
        ]
        if len(cands):
            j = (cands["Date"] - o["Date"]).abs().idxmin()
            df.loc[[i, j], "pair_id"] = pid
            used.add(j)
            pid += 1
    return df

df = pair_transfers(df)

It's O(n√óm), which is fine for a few thousand rows. Two things it deliberately does not do: match on description text, which varies wildly between banks, and match partial amounts. A 4,000.00 outflow and a 3,999.00 inflow aren't a pair; that's a question for the borrower.

What about transfers from an account you don't have statements for, like a personal account? They won't pair. Flag inflows whose description looks like a transfer and send them to a human:

looks_like_transfer = df["Description"].str.contains(
    r"TRANSFER|XFER|FROM SAV|FROM CHK", case=False, regex=True)
unpaired_suspects = df[looks_like_transfer & df["pair_id"].isna() & (df["Amount"] > 0)]

The summary

Now restrict to the operating account and compute what an underwriter asks for:

op = df[df["Account"] == "Checking ...2214"]

summary = op.groupby("Month").agg(
    all_deposits=("Amount", lambda s: s[s > 0].sum()),
    withdrawals=("Amount", lambda s: -s[s < 0].sum()),
)
summary["own_transfers_in"] = (
    op[(op["Amount"] > 0) & op["pair_id"].notna()].groupby("Month")["Amount"].sum()
).reindex(summary.index, fill_value=0)
summary["business_deposits"] = summary["all_deposits"] - summary["own_transfers_in"]

nsf = op["Description"].str.contains(r"NSF|RETURNED ITEM|OVERDRAFT", case=False)
summary["nsf_items"] = op[nsf].groupby("Month").size().reindex(summary.index, fill_value=0)

With a fictional catering company's July to September 2026 checking statements, that produces:

Month	All deposits	Own transfers in	Business deposits	Withdrawals	NSF
2026-07	30,500.00	4,000.00	26,500.00	28,900.00	0
2026-08	26,200.00	0.00	26,200.00	31,050.00	1
2026-09	35,400.00	6,500.00	28,900.00	33,275.50	0

Average business deposits: 81,600.00 / 3 = 27,200.00. Average of all deposits: 92,100.00 / 3 = 30,700.00. That 3,500.00 a month gap is the whole reason for the pairing step.

Balances: take them from the statement

Don't compute month-end balances from a cumulative sum of transactions and call it done. Take the printed closing balance from each statement and use the cumulative sum as a check:

opening = 12000.00
closing_printed = {"2026-07": 13600.00, "2026-08": 8750.00, "2026-09": 10874.50}

running = opening
for m, row in summary.iterrows():
    running = round(running + row["all_deposits"] - row["withdrawals"], 2)
    assert running == closing_printed[str(m)], f"{m}: computed {running}"

12,000.00 + 30,500.00 ‚àí 28,900.00 = 13,600.00, then 8,750.00, then 10,874.50. If an assertion fails, a page or a line is missing from the input and every figure above it is suspect.

Lowest daily balance is a different story. You can't reconstruct it reliably from transactions alone if the statement has same-day ordering you don't know; read it from the statement's daily balance section.

Where the input comes from

ZeraBooks Convert turns bank and card statement PDFs, scans included, into rows, checks each statement against its printed opening and closing balance to the cent, and lets you download many statements at once with an Account column or a tab per account. It doesn't score applicants or flag NSFs; the analysis is yours. It's in the ZeraBooks subscription at $79 a month billed annually or $149 monthly, after a 7-day trial.

The non-code version, with what lenders, family law teams and auditors each look for, is our guide to bank statement analysis.

Disclosure: I work at ZeraBooks.