DEV Community

Seller Tools
Seller Tools

Posted on

Getting a bank statement into a spreadsheet without losing a cent

Getting a bank statement into a spreadsheet without losing a cent


Every month, thousands of small-business owners do the same thing: open a bank statement PDF,
open a spreadsheet, and start typing. Sixty transactions means about three hundred typed
values. Human entry error runs somewhere around 1% per value in careful conditions, and nobody
is careful at 11pm on a Sunday.

The good news is that most bank statement PDFs are not images. They contain a real text layer,
which means you do not need OCR, a subscription, or a cloud upload to get the rows out. You
need to understand how the text is positioned on the page.

This post is the practical version of that: how the file is structured, how to pull the rows
out with Python, how to know whether the result is right, and where the approach stops working.

1. A PDF is not a table, it is a pile of labels on a canvas

A PDF does not know what a row is. It knows that at coordinate (312.4, 688.1) there is the
string -64.28, and at (72.0, 688.1) there is 2026-10-03. Everything else — which of those
belongs to which column, whether a line is a transaction or the "Page 2 of 4" footer — is
something a human eyeball infers.

That is why naive extraction fails:

  • pdfplumber's .extract_table() uses ruling lines. Many statements have no vertical lines between columns, so it returns one wide column or nothing.
  • .extract_text() returns lines in reading order, but a statement page often has a header block, then the transaction table, then a summary block, and the reading order can interleave them.
  • Debit and credit are frequently two separate columns with only one of them filled per row. If you concatenate them you get empty cells in the middle of otherwise clean data.

The reliable approach is positional clustering: take every word with its bounding box,
group the words by their vertical centre into lines, then assign each word to a column based on
its x position.

import pdfplumber
from collections import defaultdict

def page_rows(page, tolerance=3.0):
    words = page.extract_words(use_text_flow=False, keep_blank_chars=False)
    lines = defaultdict(list)
    for w in words:
        key = round(w["top"] / tolerance)
        lines[key].append(w)
    for key in sorted(lines):
        yield sorted(lines[key], key=lambda w: w["x0"])
Enter fullscreen mode Exit fullscreen mode

Three words that sit within three points of each other vertically are on the same visual line.
That single heuristic handles 90% of statement layouts, and it fails loudly rather than
silently: when a row is broken, you see it as a row with two cells instead of four.

2. Find the column anchors once, not per row

Do not hard-code x coordinates per file — statements get re-issued with slightly different
margins. Instead, detect the header line (Date, Description, Amount, Balance, or a
local equivalent), take the x position of each header word, and use the midpoints between them
as column boundaries for the rest of the document.

def column_bounds(header_words):
    xs = [w["x0"] for w in header_words]
    xs.sort()
    return [(a + b) / 2 for a, b in zip(xs, xs[1:])]

def cell_for(word, bounds):
    for i, edge in enumerate(bounds):
        if word["x0"] < edge:
            return i
    return len(bounds)
Enter fullscreen mode Exit fullscreen mode

If your bank prints Debit and Credit as separate columns, keep them separate in the output
too. Collapsing them into one signed amount is a decision, and it should be made after you see
what is actually in each column — not before.

3. The sign problem is the expensive one

Statements express money out in at least four ways:

Form What you must not do
-64.28 keep it
(64.28) parentheses mean negative, not "note"
64.28 DR trailing DR means money out
64.28 in a Debit column, blank in Credit the column decides, not the number

A converter that drops the DR suffix, or that treats (64.28) as 64.28, does not produce a
slightly wrong spreadsheet. It produces one where every refund looks like a payment, and the
closing balance never reconciles. If your output has a - where the source had ( or DR,
that is the first thing to test.

Dates have the same class of problem. 07/05/2026 is 5 July on a US statement and 7 May on a
UK one. Your statement's own header usually tells you; if it does not, look at the statement
period on page one — a statement covering 1 April to 30 April cannot contain a 5 July
transaction.

4. The only validation that matters

Row count is a weak check: two errors that cancel out keep the count the same. The check that
catches nearly everything is the printed balance:

opening balance + sum(money in) - sum(money out) = closing balance
Enter fullscreen mode Exit fullscreen mode

If that identity holds to the cent on a statement with 200 transactions, the signs, the
decimal separators and the page breaks were all read correctly. If it is off by 0.01, you have
found a rounding problem worth understanding before you hand the file to an accountant. This is
also the reason to keep the running balance column even if you do not need it: it is a
per-row checksum. When the running balance stops matching after row 57, you know exactly which
row to look at.

5. Where this approach stops

  • Scanned statements. No text layer, no positional clustering. You are in OCR territory, and OCR misreads digits in a way that positional extraction does not: 8 for 3, 0 for 6. Never trust an OCR result that has not passed the balance check.
  • Multi-line descriptions. A description wrapped onto a second line has no date and no amount. Decide the rule up front: attach it to the row above, or emit it as a continuation row. Silently dropping it loses the merchant name, which is the one field people actually read.
  • Page breaks. The transaction list runs across pages and the first row of page 2 often has no repeated header. If you only look for the header once per page, you lose that row.
  • Statements with sub-tables. Some business accounts print a "pending" block above the transaction list. Both blocks look identical to a row-grouping function; only the section heading distinguishes them.

6. A complete, small implementation

import csv, re, sys
import pdfplumber

NUM = re.compile(r"^\(?-?[\d,]+\.\d{2}\)?(?:\s?(?:DR|CR))?$")

def to_amount(text):
    neg = text.strip().startswith("(") or text.strip().endswith("DR")
    value = float(re.sub(r"[^\d.]", "", text))
    return -value if neg else value

def convert(pdf_path, out_csv):
    rows = []
    with pdfplumber.open(pdf_path) as pdf:
        for page in pdf.pages:
            for line in page_rows(page):
                cells = [w["text"] for w in line]
                if len(cells) < 3 or not re.match(r"\d{1,2}[/-]\d{1,2}[/-]\d{2,4}", cells[0]):
                    continue
                amounts = [c for c in cells if NUM.match(c)]
                if not amounts:
                    continue
                rows.append({
                    "date": cells[0],
                    "description": " ".join(
                        c for c in cells[1:] if not NUM.match(c)),
                    "amount": to_amount(amounts[-1]),
                })
    with open(out_csv, "w", newline="", encoding="utf-8") as fh:
        w = csv.DictWriter(fh, fieldnames=["date", "description", "amount"])
        w.writeheader()
        w.writerows(rows)
    print(f"{len(rows)} rows -> {out_csv}")
Enter fullscreen mode Exit fullscreen mode

It is about forty lines, it runs locally, and it handles the common case: a text-layer PDF with
one amount column. It will not handle every bank, and it does not attempt the balance check —
add that before you trust it.

7. What a finished tool adds

If you would rather not maintain the parser, the two things worth paying for are the ones you
cannot see from a demo: handling the layouts that break, and the balance check that tells you
when something was read wrong. I built a small converter in that shape —
the free Lite edition converts one
PDF of up to 20 rows so you can test it against a statement you already know the total of, and
the full version does batches and
keeps the running balance column. Both run on your machine; neither uploads the file.

Whichever route you take — forty lines of Python or a tool — the rule is the same: do not
trust the spreadsheet until the closing balance matches.
Everything else is formatting.


Related, from the same small store: if the converted statement is going into a seller's books
rather than an accountant's inbox, Seller Profit & Tax Calculator
2026
is the next step — one
row per order, fees and refunds taken out, then profit per sale and the quarterly tax reserve
(free Lite edition).

Top comments (0)