Bank Statement OCR Organizer

Problem Addressed

Scanned bank statements often appear in litigation, audit, or personal asset review. They usually exist as PDF page images, not queryable tables. Manual transcription of date, memo, debit, credit, balance, and counterparty name is slow and error-prone, and it is hard to preserve the source page for each row. This skill targets that workflow: it converts scanned bank statement PDFs into structured Excel workbooks, with one worksheet per bank.

How It Works

The pipeline is engineering-focused: it first checks the visual OCR API configuration, then uses PyMuPDF to rasterize PDFs into 200 DPI PNG pages. scripts/batch_worker.py processes pages in parallel and writes per-page OCR results to JSON. overrides.json is used to correct edge cases such as misidentified bank names, mismatched page counts, or file mapping issues. Finally, scripts/generate_excel.py generates an Excel workbook named after the original PDF with an AI-organized suffix.

The generated workbook includes bank-specific worksheets with:

• A title row: {owner} - {bank} statement details
• A source row: PDF name and record count
• Common columns: Date, Memo, Income, Expense, Balance, Counterparty, and similar fields
• A Source page column
• Summary statistics: total records, income/expense counts and amounts, net flow

Scope and Cautions

This is not a zero-review OCR tool. Scan quality, fonts, stamps, occlusions, and missing cross-page fields can affect accuracy. Similar bank names are a real failure mode; for example, CMB may be misread as CCB or ICBC, so the workflow requires checking the bank mapping and account holder for each page. If a page has no owner field, the pipeline uses forward inheritance from the previous page. One PDF may contain multiple account holders, and the output worksheets reflect distinct owners. It is suitable for generating a reviewable draft, not for direct use as financial or legal evidence without human confirmation.