Skip to content
juancespedes.dev
ES: Leer esta página en español
All projects
Case studyAnonymized

PDF energy invoice extractor for two formats

A Python desktop application that turns PDF energy invoices from two companies with different formats into structured data, exports them to Excel and cross-checks them against the corporate database.

Project facts

Role
Sole developer, from template analysis to packaging
Period
Feb 2026 to Jun 2026
Public links
Internal system: the code is private, and names, data and diagrams were left out or recreated.

  • 73

    real invoices pinned as a regression corpus, with 26 fields checked in each oneSource: Repository regression suite, June 2026

  • 4 in 1

    earlier projects, one per company, merged into a single codebaseSource: Git history and folders of the earlier versions

  • 40+

    extraction patterns, each one audited against a robustness matrixSource: Repository pattern coverage audit, 2026

The problem

Every billing cycle, dozens of PDF energy invoices from two counterparties with completely different formats had to be keyed in and checked by hand, then compared with the values stored in the corporate database. It was slow, error-prone and spread across four scripts that each did part of the job.

My role

  • Analyzed each counterparty's templates, including the reliquidation credit note.
  • Merged four earlier projects into an architecture with one package per company and the same internal structure.
  • Built the graphical interface and the packaging for business users without Python installed.
  • Wrote the regression suite and the pattern coverage audit.

Constraints

  • Non-technical business users with no Python on their machines.
  • Third-party PDFs with encoding glitches and templates that change without notice.
  • A real zero must be distinguishable from a concept the invoice does not include.

Decisions

  1. 01

    One package per company with the same structure

    Each counterparty has its own extractor, processing and export, and the interface picks the engine at runtime. Supporting a new format means adding a package.

    Ruled out

    • A single extractor full of per-company conditionals
  2. 02

    Extraction by page coordinates

    In the tabular format, text is rebuilt by grouping lines by their vertical position, which keeps together the columns that a flat extraction mixes up.

    Ruled out

    • Extract plain text and split it with regular expressions only
  3. 03

    A golden corpus before touching any pattern

    73 real invoices frozen in a snapshot make the suite fail on any value change, and the snapshot is only regenerated after a manual check.

    Ruled out

    • Test with a handful of sample invoices
  4. 04

    Fields that were not extracted are data too

    Every invoice carries the list of concepts its template did not include, so a new template shows up in the Excel file instead of turning into silent zeros.

    Ruled out

    • Fill anything not found with zero

Architecture

The graphical interface takes a PDF or a folder, picks the company engine and processes in the background. Each engine extracts, cleans and exports to Excel, and cross-checks the values against the corporate database.

  1. Input

    • PDF invoicesA single invoice or a whole folder

  2. Interface

    • Desktop applicationCustomTkinter, processing on a separate thread

  3. Engines

    • Company A enginePDF to CSV and more than 40 patterns
    • Company B enginePage coordinates and line items

  4. Outputs and reference

    • Multi-sheet ExcelPer invoice or consolidated per batch
    • Corporate PostgreSQLReference values for the cross-check

How data moves

  1. PDF invoicestoDesktop applicationPicked by the user
  2. Desktop applicationtoCompany A engineBy invoice type
  3. Desktop applicationtoCompany B engineBy invoice type
  4. Corporate PostgreSQLtoCompany A engineValues to compare
  5. Corporate PostgreSQLtoCompany B engineValues to compare
  6. Company A enginetoMulti-sheet ExcelInvoices, concepts and parameters
  7. Company B enginetoMulti-sheet ExcelMaster workbook with comparison

Results

  • Reviewing a cycle went from keying in invoice after invoice to picking a folder and checking the differences flagged in the Excel file.
  • Differences against the corporate database come out field by field in a comparison sheet.
  • A Windows executable runs without installing Python or any dependency.

What I would do differently

  • I would separate the extraction core from the interface from the start, so it could also run from the command line or on a schedule on a server.
  • I would build the golden corpus before merging the earlier projects; it would have made the merge faster and safer.

Technologies

  • Python
  • pdfminer.six
  • pandas
  • PostgreSQL
  • Excel
  • CustomTkinter
  • PyInstaller