📊 Money Data · 8 min read

Bank Statement to CSV: Why the Totals Don't Add Up

A bank's CSV export is not a clean table. It normally has two or three lines of account information before the header row and a totals or closing-balance row after the last transaction, and its amounts may be split into separate debit and credit columns or quoted as text. Parse it as a plain table and one of three things happens: it stops with a field-count error, the debits come out doubled, or the amounts stay text and never sum at all. All three are avoidable, and each one is measurable.

Below: what the export actually contains, the exact numbers we got from a four-transaction statement, and the four checks that tell you whether a parsed file is trustworthy before you build anything on top of it.

What a statement export actually contains

RFC 4180, the CSV specification, defines the format as an optional header line followed by records — and explicitly says the presence or absence of that header must be told to the parser from outside the file. That is the root of the problem here: a bank export is a valid CSV whose first line is not the header, and nothing inside the file says so.

In practice the preamble looks like this — the bank name, the masked account number, and the statement period, one item per line:

ACME BANK — STATEMENT EXPORT
Account: ****4821
Period: 2026-09-01 to 2026-09-30
Date,Description,Debit,Credit,Balance
2026-09-02,"PAYMENT TO SUPPLIER, SHENZHEN",4200.00,,15800.00
...

And the ending looks like this — a row whose first cell is a label, not a date, and whose numeric cells repeat the column totals:

Ending balance,,10235.00,9750.00,19515.00

Both blocks are ordinary CSV records. Neither is a transaction. That is the whole trap.

What we measured

We built a 372-byte export with three preamble lines, a five-column header, four transactions (two debits, two credits) and a closing row, then read it with pandas 2.3.3 on Python 3.13.5 on 2026-11-02. Script and fixtures are kept under seo_geo_ops/nc_measure_20261102/.

The doubling is the dangerous one, because nothing about it looks wrong. The column parses as float64, every value is a plausible amount, and the total is wrong by a factor of two. If you upload that file to a dashboard and ask "how much did we spend", the answer will be exactly double, stated with confidence.

Fix 1: tell the parser where the header is

In pandas, skiprows is the parameter that exists for this. Count the preamble lines — three in our fixture — and skip them so the header row becomes row zero. The same idea in Excel is Data → From Text/CSV, where the preview lets you delete the top rows and set the header row by hand before importing. Both are the same instruction: the file has structure, and you are telling the tool what it is.

If the preamble length differs between banks (it will), do not hardcode a count. Open the file in a text editor, find the line that contains the field names, and use its line number. A file that opens in one column is the same symptom seen from another angle — the one-column diagnosis covers the delimiter half of that problem.

Fix 2: drop the totals row

The closing row is not always labelled the same way, so filtering by the first cell is more robust than filtering by position: keep only rows whose date column parses as a date, and the labels fall out automatically.

df = pd.read_csv("bank_raw.csv", skiprows=3)
df = df[pd.to_datetime(df["Date"], errors="coerce").notna()]
df[["Debit", "Credit", "Balance"]] = df[["Debit", "Credit", "Balance"]].apply(pd.to_numeric, errors="coerce")

Note the second line: errors="coerce" turns any surviving label into NaN instead of raising, which then makes a wrong column type visible at once. A column of amounts that arrives as object instead of float64 is the single most useful signal that something in that column was not a number.

Fix 3: debit and credit are directions, not amounts

Two columns with blanks in opposite rows is a signed quantity split in half. In our fixture the Debit column had one value and three blanks, the Credit column had three values and one blank, and adding the columns together produced 39,970.00 against a true net movement of −485.00. Build the signed column once and use it everywhere:

df["Amount"] = df["Credit"].fillna(0) - df["Debit"].fillna(0)
print(df["Amount"].sum())   # -485.0

Some exports go the other way and give you a single amount column plus a type column ("DEBIT" / "CREDIT"), or a single signed column. All three shapes are common; the check is the same, and it is arithmetic, not opinion: opening balance plus the signed sum must equal the closing balance.

Fix 4: amounts that are quoted text

This one surprised us. We wrote three amounts — "1,234.56", "12,345.00" and "1.234,56" — quoted, as many exports do. Passing thousands="," did nothing: the column stayed object and the values came through unchanged. pandas parses numbers from the raw field; a quoted field containing a comma is a string, and the parameter never gets a chance.

The consequence is nasty because Python will happily sum strings. Our naive sum returned 1,234.5612,345.001.234,56 — concatenation, not addition, presented as a result. The repair is explicit: strip quotes and separators first, then convert, then count your NaNs.

raw = df["Amount"].astype(str).str.replace('"', "", regex=False).str.replace(",", "", regex=False)
df["Amount"] = pd.to_numeric(raw, errors="coerce")
print(df["Amount"].isna().sum())   # must be 0 before you trust the total

Careful with a comma-decimal locale: there the comma is the decimal point, so removing every comma turns 1.234,56 into 1.23456. Decide which convention the file uses — one file, one convention — and say so in your notes. The separator and decimal-comma trap is the same decision seen from the export side.

Four checks before you trust any total

1. Row count. The parsed rows should equal the transaction count the statement reports. If the statement says 143 and you have 144, you kept the totals row.
2. Balance arithmetic. Opening balance + credits − debits = closing balance. If it does not, a row is missing, duplicated, or signed the wrong way.
3. Column types. Amounts must be numeric, dates must be dates. Any object column holding money will fail you later, quietly.
4. Date range. Earliest and latest transaction dates must sit inside the period stated in the preamble. A transaction from the previous month means the export included a carry-over you did not expect.

One extra habit that saves real money: never let a leading-zero account or reference number become a number. Card and account references are identifiers, not quantities — keeping leading zeros is the same class of bug, and it also destroys matching against other systems.

If the bank only gives you a PDF

Many banks export PDF by default and CSV only from a desktop session. Extracting a table from a PDF is a different job with a different failure mode: the numbers arrive as text positioned on a page, and columns are inferred from coordinates. Extracting a table from a PDF into CSV covers that path, including how to verify the extraction before you aggregate it.

Whatever the source, keep the original file next to the parsed one. When a total is questioned six months later, the export is the evidence — a derived spreadsheet is not.

Frequently Asked Questions

Sources

• RFC 4180, Common Format and MIME Type for CSV Files, section 2: the file grammar is an optional header line followed by records; "the presence or absence of the header line must be indicated via the optional header parameter" of the media type, i.e. it cannot be inferred from the file alone. Section 2 also states that fields containing commas should be enclosed in double quotes — the reason quoted amounts arrive as text. Retrieved 2026-11-02.
• pandas 2.3.3 documentation, pandas.read_csv: the skiprows parameter (line numbers to skip, or a callable), thousands (thousands separator) and decimal (decimal character), and header. Retrieved 2026-11-02.
• Local measurement, 2026-11-02, Python 3.13.5 / pandas 2.3.3, fixtures and script at seo_geo_ops/nc_measure_20261102/: a 372-byte statement export with 3 preamble lines, header, 4 transactions and a closing row; default read_csv raised ParserError: Expected 1 fields in line 4, saw 5; skiprows=3 returned shape (5, 5); debit sum including the closing row 20,470.00 versus 10,235.00 after filtering; Debit+Credit 39,970.00 versus a signed net of −485.00; a quoted-amount fixture stayed object even with thousands="," and naive summation returned the concatenated string 1,234.5612,345.001.234,56.