Bank statement PDFs to Excel: 3 free tools tested on 5 layouts

If you copy bank statement PDFs into Excel by hand, a free Python tool can do the copying, and the statement itself can check the result. We made five sample statements in five common layouts and ran three free table readers on them. Camelot read all 154 transactions exactly. The other two each lost whole statements under their default settings.

The check is the useful part. Where a statement prints a balance after each line, the previous balance plus the amount must equal the next balance.

A script that tests that on every row catches a dropped line, a misread amount or a debit booked as a credit. This guide shows where each tool broke and why, gives you the script, and says what the check cannot catch. Documentation and prices were read on October 21, 2026.

Our test: five statements, three free tools

We wrote a Python script that drew five statements as PDFs from transactions we generated, so every extracted row could be scored against the truth. Every name and amount in them is made up. Each one tests a different layout problem:

S1, a ruled grid, US dates, separate Withdrawals and Deposits columns, two pages with the header repeated, 48 transactions.
S2, borderless columns, one signed Amount column (-687.18), and the date printed only on the first transaction of each day, 26 transactions.
S3, a UK-style layout with horizontal rules only, dates without a year (02 Sep), Paid out and Paid in columns, “balance brought forward” and “carried forward” rows, and long descriptions that wrap onto a second line, 34 transactions.
S4, European formats, borderless: 1.234,56, dates as 14.09.2026, and debits written with a trailing minus (421,12-), 22 transactions.
S5, a ruled grid with one Amount column where credits carry CR and debits carry nothing, and a balance that goes overdrawn and prints as 412.37 DR, 24 transactions.

Each tool’s tables went through the same parser, which we wrote from the five layouts before running any tool. It finds the header row, maps the columns, turns the amounts into signed numbers and joins wrapped lines. A transaction counted as right only when its date, description, amount and balance all matched.

Tool and settings	Right, of 154	Where it failed
Camelot 2.0.0, lattice, then stream when lattice finds no table	154	nowhere
tabula-py 2.10.0, default settings	120	found no table at all in S3
tabula-py, `guess=False, stream=True`	0	read all 154 amounts and balances, but put the date and description in one column
pdfplumber 0.11.10, default settings	72	found tables only in the ruled grids (S1 and S5)
pdfplumber, `text` strategy	1	cut words in two; split amounts across cells in S5

Our parser is tuned to these five layouts, so the scores flatter all three tools. Real statements add logos and footers, and some arrive as scans. Read the table as a ranking of how each tool copes with layout, not as an accuracy rate you can expect.

Each tool fails at the same thing: finding the columns

All three tools read the text a PDF already contains. Their real job is deciding which words belong in which column, and each failure above traces to how a tool makes that guess.

Lines or whitespace. pdfplumber’s table finder defaults to lines for both directions, using the page’s drawn lines as cell borders. Statements S2 and S4 have no lines, so it found nothing. S3 has rules between rows but none between columns, and that was not enough either.

Its text strategy draws imaginary column borders from where words line up. On our files that went worse: it split “Granite Ridge Bank” into “Granite Rid” and “ge Ban”, and split 2,955.21 CR across two cells.

Two modes, and a fallback between them. Camelot’s lattice mode “detects the grid from the ruled lines”, and its stream mode uses “text-alignment / whitespace heuristics” for borderless tables. Lattice found the grids in S1 and S5 and nothing in S2, S3 and S4. Falling back to stream for those three is why Camelot read everything.

The guessed table area. tabula-py’s guess option, on by default, makes it “guess the portion of the page to analyze”. On S3 the guess found no table. With guess=False it read every amount and balance in all five statements, but it merged the date and description columns.

Giving it the column borders by hand fixed that. With columns=[95, 330, 405, 480], the x positions in points of the borders between S3’s columns, it read all 34 S3 transactions. The columns option takes “X coordinates of column boundaries”, so you need one list per bank layout.

One more trap showed up in our own checks. On a version of S2 printed newest first, Camelot’s stream mode returned the same table twice, overlapping, which doubled 26 transactions to 52. The balance check flagged it, and the script below now drops a table whose rows were already read on the same page.

The running balance is a free answer key

A statement with a running balance checks itself in a way an invoice does not. Each row’s balance must equal the previous balance plus that row’s amount. The first row checks against the opening balance and the last against the closing balance, when the statement prints them.

To see what that check catches, we took Camelot’s correct output for S1 and planted one fault at a time, then ran the check.

Fault planted in S1’s 48 rows	Flagged by the balance check
One row in the middle dropped	yes, at the next row
The last row dropped	yes, against the closing balance
One amount off by $9.00	yes
A withdrawal read as a deposit	yes
Two rows swapped	yes, at three rows
One row read twice	yes
One date wrong	no
One description cut short	no

The same check worked on the tools’ real failures. pdfplumber’s text strategy lost 10 of S3’s 34 rows in 7 gaps, and the check flagged the row after each gap. It also flagged all 8 rows where that strategy got an amount wrong in S5.

What the check cannot see matters just as much. It tests money, not words or dates. pdfplumber’s text strategy read all 48 amounts and balances in S1 correctly while cutting 47 of the 48 descriptions, and the check passed it.

So the check tells you the money is complete. Read a sample of descriptions and dates yourself before you trust a statement.

1. Ask your bank for a CSV first

The fastest PDF converter is the one you do not need. If your online banking offers a download of your activity, start there. QuickBooks’ own help page tells you to try a QFX, QBO or OFX file if your bank offers one of those but not CSV.

Use the PDF route for what downloads do not cover: older months, a closed account, or statements a client sends you. Check your bank’s download page for how far back it goes. We did not test any bank’s export, because that needs a real account.

2. Check that each PDF has a text layer

Open a statement and try to select a word. Camelot’s documentation gives the same test: “If you can click and drag to select text in your table in a PDF viewer, then your PDF is text-based.” Its built-in parsers need that text, and pdfplumber says it works best on machine-generated rather than scanned PDFs.

For a scan, add a text layer first with ocrmypdf scan.pdf scan.pdf. OCRmyPDF uses the open-source Tesseract engine, and its own page warns that “the OCR accuracy may not match that of commercial OCR solutions”. Our invoice test found two stray characters on a clean, computer-made scan, so run the balance check on every OCR’d statement. The script below reports a PDF with no text and skips it rather than guessing.

3. Run the script on a folder of statements

The script reads every PDF in a folder and writes statements.xlsx with two sheets. Transactions has one row per transaction with a check column. Files has one row per statement with how many rows need a look. It uses Camelot for the tables and pdfminer.six to read the year and the opening and closing balances from the page text.

We ran it on Linux with Python 3.11. Camelot’s default backend needs no system dependencies, and our test machine had no Ghostscript. Set it up with:

python3 -m venv statements-env
statements-env/bin/pip install camelot-py pdfminer.six pandas openpyxl

Save this as statement_to_excel.py:

"""Turn PDF bank statements into one Excel sheet of transactions, and check
every row against the running balance the bank printed.
Usage: python statement_to_excel.py <folder>"""
import re, sys
from datetime import datetime
from pathlib import Path
import camelot, pandas as pd
from pdfminer.high_level import extract_text

ROLES = [("balance", r"balance"), ("debit", r"withdraw|paid out|debit|money out"),
         ("credit", r"deposit|paid in|credit|money in"), ("amount", r"amount"),
         ("date", r"^(date|booking|posted)"), ("desc", r"desc|detail|transaction|text|payee|memo")]

def money(s):
    s = re.sub(r"[$€£\s]|USD|EUR|GBP", "", str(s)).upper()
    if not s: return None
    sign = 1
    if s.endswith(("DR", "OD")): sign, s = -1, s[:-2]
    elif s.endswith("CR"): s = s[:-2]
    if s.endswith("-"): sign, s = -1, s[:-1]
    if s.startswith("-"): sign, s = -1, s[1:]
    if s.startswith("(") and s.endswith(")"): sign, s = -1, s[1:-1]
    if re.fullmatch(r"\d{1,3}(\.\d{3})*,\d{2}", s): s = s.replace(".", "").replace(",", ".")
    elif re.fullmatch(r"\d{1,3}(,\d{3})*\.\d{2}|\d+\.\d{2}", s): s = s.replace(",", "")
    else: return None
    return sign * float(s)

def day(s, year, day_first):
    s = re.sub(r"\s+", " ", s).strip()
    fmts = ["%d/%m/%Y", "%m/%d/%Y"] if day_first else ["%m/%d/%Y", "%d/%m/%Y"]
    for f in fmts + ["%d.%m.%Y", "%Y-%m-%d", "%b %d, %Y", "%d %b %Y"]:
        try: return datetime.strptime(s, f).date()
        except ValueError: pass
    for f in ("%b %d", "%d %b", "%m/%d"):  # no year printed: take it from the statement
        try: return datetime.strptime(f"{s} {year}", f + " %Y").date()
        except ValueError: pass

def tables(pdf, flavor):
    out, seen = [], set()
    for t in camelot.read_pdf(str(pdf), pages="all", flavor=flavor):
        rows = [tuple(r) for r in t.df.values.tolist() if any(c.strip() for c in r)]
        if all((t.page, r) in seen for r in rows): continue  # stream mode can find a table twice
        seen.update((t.page, r) for r in rows); out.append([list(r) for r in rows])
    return out

def roles(row):
    found = {}
    for i, cell in enumerate(row):
        for role, pat in ROLES:
            if role not in found and re.search(pat, cell.strip(), re.I):
                found[role] = i; break
    return found if {"date", "balance"} <= found.keys() else None

def parse(tables, year):
    """tables: lists of rows of cell strings, in page order. Returns the transactions
    and the first "brought forward" balance, if a table has one."""
    tables = [[[re.sub(r"\s+", " ", c).strip() for c in row] for row in t] for t in tables]
    rows = [r for t in tables for r in t]
    head = next((r for r in map(roles, rows) if r), {})
    column = lambda k: [r[head[k]] for r in rows if k in head and len(r) > head[k]]
    day_first = any(int(m.group(1)) > 12 for c in column("date")  # 25/09/2026 is day first
                    if (m := re.match(r"(\d\d)/\d\d/\d{4}", c)))
    has_cr = any(re.search(r"\dCR$", c.replace(" ", ""), re.I) for c in column("amount"))
    cols, out, last_date, brought = None, [], None, None
    for row in (r for t in tables for r in [None] + t):
        if row is None or (r := roles(row)):  # a new table or a header row
            cols, joinable = r if row else cols, False; continue
        if not cols or len(row) <= max(cols.values()): continue
        get = lambda k: row[cols[k]] if k in cols else ""
        desc, bal = get("desc"), money(get("balance"))
        if "amount" in cols:
            amt = money(get("amount"))
            if amt is not None and has_cr:  # CR marks credits, so an unmarked amount is a debit
                amt = abs(amt) if re.search(r"CR\s*$", get("amount"), re.I) else -abs(amt)
        else:
            out_, in_ = money(get("debit")), money(get("credit"))
            amt = None if out_ is None and in_ is None else (in_ or 0) - (out_ or 0)
        d = day(get("date"), year, day_first) if get("date") else None
        if d and last_date and (last_date - d).days > 180:  # a December to January statement
            d = d.replace(year=d.year + 1)
        if amt is None:
            if bal is not None and not out and brought is None and re.search(r"brought forward", desc, re.I):
                brought = bal
            if joinable and bal is None and desc and not d:  # a wrapped description line
                out[-1]["description"] += " " + desc; continue
            joinable = False; continue  # balance brought or carried forward, page totals, other text
        out.append({"date": d or last_date, "description": desc, "amount": round(amt, 2), "balance": bal})
        last_date, joinable = d or last_date, True
    return out, brought

def balances(text):
    """Opening and closing balance as printed on the statement, if found."""
    num = r"\s*(?:EUR|USD|GBP)?\s*[$€£]?\s*(-?[\d.,]+\d(?:\s*(?:CR|DR|-))?)"
    o = re.search(r"(?:opening|beginning|old|previous) balance" + num + "|balance brought forward" + num, text, re.I)
    c = re.search(r"(?:closing|ending|new) balance" + num, text, re.I)
    return (money(o.group(1) or o.group(2)) if o else None), (money(c.group(1)) if c else None)

def check(txns, opening=None, closing=None):
    """Flag every row where the previous balance plus the amount is not the printed balance."""
    prev = opening
    for t in txns:
        if t["balance"] is None: t["check"] = "no balance read"
        elif prev is None: t["check"] = "first row, no opening balance to check against"
        elif round(prev + t["amount"], 2) == t["balance"]: t["check"] = "ok"
        else: t["check"] = f"break: expected {prev + t['amount']:.2f}"
        prev = t["balance"]
    if txns and closing is not None and txns[-1]["balance"] != closing:
        note = f"last balance differs from closing balance {closing:.2f}"
        txns[-1]["check"] = note if txns[-1]["check"] == "ok" else txns[-1]["check"] + "; " + note
    return txns

if __name__ == "__main__":
    folder = Path(sys.argv[1]); pdfs = sorted(folder.glob("*.pdf"))
    if not pdfs: sys.exit(f"No PDF files in {folder}")
    rows_out, summary = [], []
    for pdf in pdfs:
        try:
            text = extract_text(pdf)
            if not text.strip():
                summary.append({"file": pdf.name, "rows": 0, "problems": "",
                                "result": "no text layer: run ocrmypdf on it first"}); continue
            year = (re.findall(r"\b20\d\d\b", text) or [datetime.now().year])[0]
            opening, closing = balances(text)
            for flavor in ("lattice", "stream"):  # ruled tables first, then whitespace columns
                txns, brought = parse(tables(pdf, flavor), year)
                if txns: break
            if txns and txns[0]["date"] and txns[-1]["date"] and txns[0]["date"] > txns[-1]["date"]:
                txns.reverse()  # newest first: check the balances oldest first
            txns = check(txns, opening if opening is not None else brought, closing)
        except Exception as e:
            summary.append({"file": pdf.name, "rows": 0, "problems": "", "result": f"error: {e}"}); continue
        problems = sum(t["check"] != "ok" for t in txns)
        summary.append({"file": pdf.name, "rows": len(txns), "problems": problems,
                        "result": "no rows found" if not txns else "ok" if problems == 0 else "check by hand"})
        rows_out += [{"file": pdf.name} | t for t in txns]
    out = folder / "statements.xlsx"
    with pd.ExcelWriter(out) as xl:
        pd.DataFrame(rows_out).to_excel(xl, sheet_name="Transactions", index=False)
        pd.DataFrame(summary).to_excel(xl, sheet_name="Files", index=False)
    print(pd.DataFrame(summary).to_string(index=False))
    print(f"\n{len(rows_out)} transactions written to {out}")

Run it with statements-env/bin/python statement_to_excel.py statements/. On a folder holding our five statements and a scanned copy of S2’s first page, it printed:

       file  rows problems                                  result
     s1.pdf    48        0                                      ok
s2-scan.pdf     0          no text layer: run ocrmypdf on it first
     s2.pdf    26        0                                      ok
     s3.pdf    34        0                                      ok
     s4.pdf    22        0                                      ok
     s5.pdf    24        0                                      ok

154 transactions written to statements/statements.xlsx

We compared the Transactions sheet with our generated data: all 154 rows matched on every field. The script rewrites statements.xlsx on every run, so close the workbook in Excel first. A PDF that crashes Camelot gets an error: row instead of stopping the run, and an empty folder stops with a message.

4. Fix what the check column flags

Any value other than ok in the check column is a row to look at. These are the causes we know of, and the setting that fixes each:

first row, no opening balance to check against. The script looks for “opening”, “beginning”, “old” or “previous balance” followed by an amount, or a “balance brought forward” row. If your bank writes “Balance on 1 September”, add that wording to the pattern in balances().
A break at every row. The signs are probably wrong. The script reads a leading or trailing minus, brackets or DR as a debit. When any amount in an Amount column ends in CR, it treats every unmarked amount as a debit. If your bank marks debits some other way, add that marker to money().
No rows found. The header was not recognized. The script needs a header cell matching “Date” (or “Booking” or “Posted”) and one matching “Balance”. Add your bank’s words to ROLES.
Wrong day and month. The script reads 03/09/2026 as March 9 unless some date in the column has a first number above 12, which proves the day comes first. A statement from early in a month can have no such date. If your bank always writes the day first, add the line day_first = True right after the has_cr = ... line in parse().
No balance column at all. Then every row says no balance read. Compare the sum of the amounts with the statement’s own totals instead.

Dates without a printed year take the first year (2000 to 2099) found in the statement’s text. December-to-January statements are handled by moving a date forward a year when it falls more than 180 days before the row above it.

5. Load the result into your accounting software

QuickBooks Online accepts a CSV in two shapes: three columns (Date, Description, Amount) or four (Date, Description, Credit, Debit). The Transactions sheet already holds the three-column version. Delete the file, balance and check columns, then save the sheet as CSV.

The same page lists what makes an upload fail. Keep every date in one format. Remove any zeros and leave those cells blank. Fix descriptions that are only numbers.

On a Mac, save as a Windows CSV.

When to pay for extraction instead

The free route costs setup time, and some upkeep per bank layout. A new bank means a new header word in ROLES or a new date format, and each change needs a test run on a statement you know. Our payback formula counts that upkeep against the hours of typing it saves.

Paid table extraction removes the layout work but not the check. Amazon’s AnalyzeDocument with the Tables feature costs $0.015 a page for the first million pages a month in US West (Oregon). New customers get 100 pages a month free for three months.

A hypothetical year of 12 three-page statements would cost 54 cents at that price. We did not test it, because it needs a paid account. Its output still needs the same mapping from columns to amounts and the same balance check.

Two other routes have limits worth knowing before you choose. Excel’s own PDF import, through Power Query, is listed for Excel on Windows, with .NET Framework 4.5 or later, and not for Excel for Mac. Our invoice guide covers its settings; we have not tested it on statements.

Free converter websites take the file onto someone else’s server. A statement carries your account number and every payment you made that month, so read the site’s terms before you upload one.

Our view: download CSV where the bank offers it, run the script on the PDF-only months, and treat any row the balance check flags as unread until a person has looked at it.

How we made this

On October 21, 2026, we generated five sample bank statements as PDFs with the ReportLab library from transactions we made up, plus a newest-first copy of one and an image-only copy of one page to stand in for a scan. We ran Camelot, tabula-py and pdfplumber on them through one parser and scored all 154 transactions, planted eight faults in one statement to test the balance check, and ran the script in this guide on the same files, on Linux with Python 3.11. We read each tool’s documentation, QuickBooks’ CSV import rules, Amazon’s Textract pricing and Microsoft’s Excel data source list that day. We did not test any bank’s own export, any paid API or Excel’s PDF import, and our statements are cleaner than real ones, so treat the scores as a best case.

Sources

Camelot, README (lattice and stream flavors, text-based PDFs, dependencies), version 2.0.0
tabula-py, read_pdf reference (guess, stream, columns), version 2.10.0
pdfplumber, README (table strategies, scanned PDFs), version 0.11.10
OCRmyPDF, Introduction
QuickBooks, Format CSV files in Excel to get bank transactions into QuickBooks, read October 21, 2026
Amazon Web Services, Amazon Textract pricing, read October 21, 2026
Microsoft Support, Power Query data sources in Excel versions, read October 21, 2026
28creation test, October 21, 2026: five generated statements, 154 transactions, scored by our own script

Bank statement PDFs to Excel: 3 free tools tested on 5 layouts

The short read

What you can do with this