Parse PDF into Excel with Python: A Bank Statement Example

Quick Answer: For clean ruled tables, camelot.read_pdf() or tabula.read_pdf() returns pandas DataFrames you can write with to_excel() or to_csv() in a few lines. For bank statements, where descriptions wrap and debits and credits sit in separate columns, read words with their coordinates using pdfplumber, assign each amount to a column by its x-position, join continuation lines, and sign the amounts yourself.

Parsing a PDF into Excel with code means reading the text a PDF draws on the page, rebuilding rows from it, and writing those rows to an .xlsx or .csv file. Python has three mature open-source libraries for this: pdfplumber, Camelot and tabula-py. All three need a PDF with a real text layer; a scanned page needs OCR first.

Which Python library should you use?

All three are MIT-licensed. pdfplumber is built on pdfminer.six and currently supports Python 3.10 to 3.14; Camelot 2.0 also requires Python 3.10 or later. For licence questions in a commercial product, see our tabula-py licence explainer.

How do you parse a simple PDF table to Excel?

When the PDF holds a ruled table, a library's table finder usually does the whole job.

Camelot

import camelot

tables = camelot.read_pdf("report.pdf", pages="1-end", flavor="lattice")
print(tables[0].parsing_report)          # accuracy and whitespace scores
tables[0].df.to_excel("report.xlsx", index=False)
tables.export("report.csv", f="csv")     # one CSV per table

Switch flavor to "stream" when the table has no grid lines. Camelot 2.0 can also stitch a table that continues across pages and filter out low-confidence detections.

tabula-py

import tabula

tabula.convert_into("report.pdf", "report.csv", output_format="csv", pages="all")

pdfplumber

import pdfplumber
import pandas as pd

with pdfplumber.open("report.pdf") as pdf:
    rows = []
    for page in pdf.pages:
        table = page.extract_table({"vertical_strategy": "text", "horizontal_strategy": "text"})
        if table:
            rows.extend(table)
pd.DataFrame(rows[1:], columns=rows[0]).to_excel("report.xlsx", index=False)

The "text" strategies tell pdfplumber to infer columns from text alignment instead of drawn lines. Writing .xlsx from pandas needs openpyxl installed.

Why do bank statements break generic table parsers?

A statement looks like a table but behaves like a ledger. Four features defeat the generic approach:

• Wrapped descriptions. A merchant name and reference continue on a second line with no date or amount, which a table finder emits as a separate, mostly empty row.
• Split amount columns. Withdrawals and deposits are printed in different columns as positive numbers. The sign is encoded by position, not by a minus.
• Partial dates. Many banks print 03/14 without a year, leaving the year to the statement period, which can span December and January.
• Page furniture. Headers, page totals, "continued" lines and legal text repeat on every page.

A parser built for this reads words with coordinates and decides for itself what a row is. Our explainer on how statement parsing works covers the concepts; below is the code.

How do you convert a PDF bank statement to CSV with Python?

This script handles the common layout of Date | Description | Withdrawals | Deposits | Balance. It finds the header on each page, uses the right edge of each header word as that column's anchor (amounts are right-aligned), and walks the lines.

import re
import pdfplumber
import pandas as pd

DATE_RE = re.compile(r"^\d{2}/\d{2}$")
AMOUNT_RE = re.compile(r"^\(?-?\$?[\d,]+\.\d{2}\)?$")
YEAR = 2026  # take this from the statement period

def to_number(text):
    negative = text.startswith(("(", "-"))
    value = float(re.sub(r"[^\d.]", "", text))
    return -value if negative else value

def lines_of(page, tolerance=3):
    """Group words into visual lines by their top coordinate."""
    lines = []
    for word in sorted(page.extract_words(), key=lambda w: (w["top"], w["x0"])):
        if lines and abs(lines[-1][0] - word["top"]) <= tolerance:
            lines[-1][1].append(word)
        else:
            lines.append((word["top"], [word]))
    return [sorted(words, key=lambda w: w["x0"]) for _, words in lines]

def column_anchors(lines):
    for words in lines:
        labels = {w["text"].lower(): w["x1"] for w in words}
        if {"withdrawals", "deposits", "balance"} <= labels.keys():
            return {"out": labels["withdrawals"], "in": labels["deposits"], "bal": labels["balance"]}
    return None

rows, current = [], None
with pdfplumber.open("statement.pdf") as pdf:
    for page in pdf.pages:
        lines = lines_of(page)
        anchors = column_anchors(lines)
        if anchors is None:
            continue  # summary or legal page without the transaction table
        for words in lines:
            amounts = [w for w in words if AMOUNT_RE.match(w["text"])]
            text = " ".join(w["text"] for w in words if w not in amounts)
            if DATE_RE.match(words[0]["text"]):
                if current:
                    rows.append(current)
                date = words[0]["text"]
                current = {"date": f"{date}/{YEAR}", "description": text[len(date):].strip(),
                           "amount": None, "balance": None}
                for a in amounts:
                    column = min(anchors, key=lambda k: abs(anchors[k] - a["x1"]))
                    value = to_number(a["text"])
                    if column == "out":
                        current["amount"] = -abs(value)
                    elif column == "in":
                        current["amount"] = abs(value)
                    else:
                        current["balance"] = value
            elif current and not amounts and text and not text.lower().startswith(("date ", "page ")):
                current["description"] += " " + text  # wrapped description line
if current:
    rows.append(current)

df = pd.DataFrame(rows)
df["date"] = pd.to_datetime(df["date"], format="%m/%d/%Y")
df.to_csv("statement.csv", index=False)
df.to_excel("statement.xlsx", index=False)

What each part does:

1. lines_of rebuilds visual lines, because extract_text() loses the x-positions you need to tell a withdrawal from a deposit.
2. column_anchors locates the header so the parser adapts if the columns shift between pages.
3. A line starting with a date opens a transaction; amounts on it are assigned to the nearest column anchor and signed (money out negative).
4. A line with text but no amounts, while a transaction is open, is a wrapped description and gets appended.
5. Lines with amounts but no date, such as page totals, are ignored.

What edge cases will a real statement throw at the parser?

The sample layout is the friendly case. Expect to extend the script for some of these as soon as you run it on more than one bank:

• Trailing markers instead of columns. Some statements print a single amount column with CR or DR after the number. Extend AMOUNT_RE to allow the suffix and sign the value from it rather than from position.
• Parentheses for negatives. (45.00) means minus 45 on many business statements. The to_number helper already treats a leading parenthesis as negative; make sure your regex accepts the closing one.
• Month names. Dates such as 14 Mar or Mar 14 need a different date pattern and format="%d %b %Y" when converting.
• One date, many transactions. A few banks print the date only on the first transaction of each day. Carry the last seen date forward when a line has amounts but no date.
• Two-up pages. Credit card statements sometimes print two columns of transactions side by side. Crop each half with page.crop((x0, top, x1, bottom)) and parse them separately.
• Password protection. pdfplumber.open("statement.pdf", password="...") opens encrypted files, which many banks issue.
• Running balance only. If there are no separate withdrawal and deposit columns, derive each amount as the difference between consecutive balances.

How do you debug a parser that drops rows?

Print the reconstructed lines for one page before any logic runs: for words in lines_of(pdf.pages[0]): print([w["text"] for w in words]). Most bugs become obvious. If two visual lines merge, lower the tolerance; if one line splits in two, raise it. If an amount lands in the wrong column, print the header anchors and the amount's x1 side by side; a bank that left-aligns its numbers needs x0 anchors instead of x1. Work through one problem page at a time, and keep a folder of sample statements you rerun after every change so a fix for one layout does not quietly break another.

How do you verify the parsed output?

Never trust a statement parser that has not been reconciled. Add this after building the DataFrame:

opening = 2450.18   # read from the statement summary
closing = 1987.42
assert round(opening + df["amount"].sum(), 2) == closing, "transactions do not reconcile"
balance_ok = (df["balance"].isna() | (df["balance"].round(2) == (opening + df["amount"].cumsum()).round(2)))
print(df[~balance_ok])  # rows where the running balance breaks

A break in the running balance points to the exact row the parser got wrong, usually a misassigned column or a missed wrapped line. Also handle the year boundary: if the statement period runs from December into January, assign January dates the later year.

When is writing your own parser not worth it?

The script above handles one layout. Each further bank means another header vocabulary, another date format, and new edge cases such as amounts with trailing CR, multi-currency columns or two-column page designs. If you are processing statements from many institutions, maintenance becomes the main cost. Our list of open-source statement converters shows what others have built. If you only need the files rather than the code, QuickBankConvert does the same job without programming: upload the statement and download CSV or Excel with signed amounts and the running balance. There is no API, so it suits people converting files rather than building pipelines; see the PDF to CSV page for the output columns.

Related guides

• The concepts behind statement parsing
• Open-source projects for statement conversion
• tabula-py licensing in plain terms