Bank statements to reconciliation-ready transactions: a repeatable quality checklist (PDF → CSV/XLSX/JSON)

The quality-first goal

You want bank reconciliation to be boring. Not “we think it matches,” but “we know it matches,” because the transaction export you import (CSV, XLSX, JSON) has been normalized, validated, and de-duplicated in a repeatable way.

The hard part is not parsing a PDF once. The hard part is parsing it consistently across statement layouts, OCR quirks, and real-world exceptions (partial months, missing pages, negative formatting, and merchant naming chaos). This checklist gives you a workflow you can run every month-end that catches the issues that create drift.

Use it like a gate: if a gate fails, you fix and re-export before importing into your reconciliation tool.

---

What “reconciliation-ready” means (for exports)

Before you start, align on the output contract your reconciliation process expects:

• Dates: every transaction has a single posting date (ISO or import-friendly format), not a mix of posting vs transaction vs “header date.”
• Amounts: no currency symbol artifacts, no misread minus signs, and totals consistent with the statement’s ending balance math.
• Uniqueness: the same line doesn’t appear twice due to OCR duplication or multi-page overlap.
• Structure: merchant/payee fields are present (even if normalized), and descriptions follow a predictable pattern so you can match confidently.
• Completeness: the number of transaction rows matches what the statement implies, and missing-fee lines are surfaced, not silently dropped.

If any of those are violated, your import might succeed, but your reconciliation will fail.

---

A repeatable workflow: PDF → normalized transactions → exports

Here’s the workflow I recommend for a typical month-end closing cycle.

Step 1: Confirm statement scope (date range + pages)

Gate check

• Are you sure the PDF covers the exact period you intend to reconcile?
• Do you have all pages (including “quiet” pages that sometimes hold only totals)?
• If the statement includes multiple account segments, are you reconciling the correct segment?

Fix if needed

• Re-upload the correct statement version.
• If the PDF is truncated, you need the missing pages because missing transactions create “mystery gaps.”

Why this matters: OCR can still be perfect, but if the scope is wrong, totals will never tie.

Step 2: Run parsing and capture raw outputs

Your first run is not for “pretty CSV.” It is for observability.

When you generate your exports (CSV/XLSX/JSON), keep a copy of:

• raw OCR rows (if your workflow exposes them)
• the normalized transaction list
• the extracted statement totals (starting/ending balance, if present)

Why: when reconciliation drifts, you’ll want to debug at the level where the error was introduced.

Step 3: Validate totals math (ending balance tie-out)

Gate check

• Does the statement’s ending balance tie out based on starting balance plus/minus transaction net movement?

If it fails, you have three common causes:

1. a missing transaction line
2. a wrong sign (+/-) on one or more lines
3. incorrect date filtering (you included lines outside the intended date range)

Fix approach

• Compare the extracted transaction rows count against what the statement shows (including fees/adjustments).
• Look for “sign anomalies” (e.g., a withdrawal printed like a positive amount).
• Ensure your reconciliation import uses the same “posting date” field that your reconciliation logic assumes.

Tip: when sign issues happen, they often cluster around one statement section (like card payments vs refunds). That means a single normalization rule might be the culprit.

Step 4: Check for duplicates (the silent reconciliation killer)

Duplicates are brutal because they can make totals drift in subtle ways.

Gate check For each transaction, identify a uniqueness key you can reliably compute:

• posting date
• normalized amount (including sign)
• merchant/payee (normalized)
• a compact hash of the original description

Then verify:

• Is the same uniqueness key present more than once?

Fix approach

• If duplicates cluster around “continuation lines” (OCR sometimes reads a wrapped description as a new row), you can merge based on similarity.
• If duplicates are truly separate statement lines (rare), you should not merge. Instead, the exports must preserve them distinctly so reconciliation can match them properly.

Step 5: Normalize merchant/payee names (for matching)

Merchant naming causes a lot of reconciliation friction because reconciliation tools and ERPs vary in how they match.

Gate check

• Are merchant names consistently extracted?
• Are you stripping noise like extra spaces, punctuation artifacts, or OCR character confusions?
• Do refunds/chargebacks map to the same “base merchant” name that charge transactions use?

Fix approach

• Create a normalization rule for common OCR mistakes (example: “Ame rican Express” turning into “American Express”).
• Normalize known variant forms (e.g., “PAYPAL *XYZ” vs “PAYPAL XYZ”).

Even a simple normalization layer usually improves match rate dramatically.

Step 6: Verify date integrity (no off-by-one, no header date bleed)

Gate check

• Are all transaction dates valid and within the statement period?
• Did the extraction accidentally use the statement header date for some lines?
• Did month/day swap happen (common when statements mix formats)?

Fix approach

• Build a rule: reject transactions whose posting date falls outside the statement range by more than a small tolerance.
• If you detect a formatting mismatch (MM/DD vs DD/MM), apply a consistent conversion for the entire statement.

When date integrity fails, reconciliation tools will look for the transaction on the wrong day, and that looks like a “missing” transaction.

Step 7: Ensure sign and amount correctness (including negatives)

Gate check

• Do credits (deposits, refunds) and debits (withdrawals, purchases) have correct sign?
• Are there any amounts missing decimal points or misread as integers?

Fix approach

• Pay special attention to OCR patterns: sometimes parentheses around amounts represent negatives; OCR might drop parentheses.
• If your CSV/XLSX exporter includes formatting that strips leading minus signs, validate the numeric representation.

Practical tip: round amounts consistently to the statement currency precision before matching.

Step 8: Confirm completeness (count and categories)

This is where you prevent “drift that you can’t explain.”

Gate check

• Is the number of extracted transaction rows consistent with what the statement shows?
• If the statement groups categories (like “card payments,” “fees,” “interest”), does your export contain those categories as separate lines?

Fix approach

• If a category is systematically missing, it is usually a parsing layout issue (e.g., the statement uses a different column layout for fees).
• Adjust parsing/normalization rules and re-export.

Step 9: Export validation (CSV, XLSX, JSON in sync)

You don’t just want correctness in one format.

Gate check

• The transaction list length matches across CSV, XLSX, and JSON.
• Each transaction has the same posting date and amount across all formats.
• The numeric fields are truly numeric (not strings with hidden characters) in CSV/XLSX.

Fix approach

• If JSON shows the correct values but CSV differs, you have an export conversion problem.

Why this matters: recon tools might ingest CSV, while your internal review might read XLSX or JSON. Divergence creates “you fixed one thing but not the other” chaos.

---

Worked example (compact, but realistic)

Let’s say you’re reconciling a monthly statement where the ending balance is $12,340.22.

What you do

1. You parse the PDF and generate your CSV/XLSX/JSON exports.
2. You validate totals math.

Gate results

• Totals gate: fails by $85.00.
• Duplicate gate: no duplicates.
• Date integrity: all dates within range.

Debug the drift

In this scenario, you usually find one of two issues:

• a fee line parsed as a credit instead of a debit, OR
• a transaction description wraps and OCR produced a second row (but your uniqueness key didn’t catch it because one field differed).

You inspect the top candidates:

• amounts that appear only once but look “out of pattern” compared to nearby lines
• transactions near statement boundaries (first/last page)

After correction, totals gate passes, and your reconciliation import no longer drifts.

That’s the workflow in action: each gate narrows the search quickly.

---

A checklist you can run in under 20 minutes

Use this as a literal runbook.

1. Scope: correct date range, full pages, correct account segment.
2. Totals gate: ending balance math ties out.
3. Duplicates gate: no repeated uniqueness keys.
4. Date integrity: all posting dates valid and in-range.
5. Sign/amount correctness: credits/debits correct, decimals correct.
6. Completeness: row count and categories present.
7. Export sync: CSV/XLSX/JSON match.

If any gate fails, stop importing and fix the parsing/normalization step.

---

Common failure patterns (so you can spot them faster)

• Header date bleed: a handful of lines share the statement header date.
• Minus sign loss: withdrawals appear as positive amounts.
• OCR row splitting: wrapped descriptions produce extra rows.
• Category layout changes: fees/interest sections follow a different layout.
• Currency precision mismatch: one format rounds differently.

---

FAQs

1) What’s the fastest gate to run first?

Start with scope (pages + date range) and totals math. It gives you the biggest signal with the least effort.

2) Should I dedupe automatically?

Only dedupe if you can prove the duplicates represent the same statement line. Otherwise, you risk deleting legitimate transactions.

3) CSV and JSON match, but reconciliation still fails. Why?

Reconciliation tools often match on merchant/date rules. Validate normalization for merchant/payee and the exact posting date field the tool uses.

4) How do I handle refunds and chargebacks?

Normalize them to the same merchant “base” where possible and ensure sign is correct. Then your matching rate improves.

5) Do I need to validate XLSX?

Yes, because formatting conversion can subtly change numeric representations. Export sync is part of a robust workflow.

---

Next step

If you want a reconciliation workflow that survives messy PDFs, focus on gates and export contracts. That’s the difference between “we can reconcile this time” and “we reconcile every time.”

If you’re already using ParseMyStatement for PDF → exports, the above checklist maps cleanly onto the outputs you import during month-end closing.