How to automate financial reconciliation with Python and PDF

The problem: hours lost cross-referencing data

Companies that handle high transaction volumes face the same bottleneck every week: someone opens bank statement PDFs, copies values into a spreadsheet, cross-references with the internal system, and spends hours hunting for discrepancies. It's repetitive work, prone to human error, and generates zero strategic value.

The technical approach

The solution uses three main components:

1. pdfplumber to extract tables from bank PDFs with precision, including PDFs with varying layouts between banks
2. openpyxl to read and write Excel spreadsheets while preserving existing formatting
3. Reconciliation logic in pure Python, with configurable rules per transaction type

Handling PDF layout variation

The biggest challenge isn't extraction itself — it's inconsistency. The same bank changes statement layout depending on the period, account type, or branch. The solution: identify table headers dynamically instead of using fixed coordinates, with fallbacks for the most common patterns.

The result

A process that took 3 to 4 hours per week now takes under 5 minutes. The system runs locally, with no cloud dependency, and generates an Excel report with highlighted discrepancies and an observation field for the analyst.

At Chiarelli Labs

This is the type of automation in our Track 1: repetitive, well-scoped process, deliverable in 2 to 4 weeks. If you have a similar process, get in touch.