Spaces:

---

BeiingHuman

/

Statements-Reconciliation

Like 0

Running

What this changes

Bank matching was done by pasting the ledger into a GPT prompt and asking the model for the match table. Every figure in the report was therefore model narration, not a computation.

On VCC's August 2026 Struxtion ledger the report claimed 1,450 ERP rows and 313 bank lines for files that hold 4,042 and 588, and confirmed about a tenth of the real transactions — including none of the 24 on 08/17, the date the customer flagged.

Matching now happens in pandas. The model writes only the introduction and the next steps; every table is rendered from computed data. With no ledger in the prompt there is nothing to outgrow, so ERP batching and batch-report consolidation are gone from the bank path. The vendor path is untouched.

Results on the August files

571 transactions compares with the 556 the customer counted by hand.

Why the export needed normalising

The Infor export is a print report, not a data extract — the August workbook carries 18,446 merged-cell ranges:

• date and payee arrive merged in the Date column while the declared Name or Reference column sits empty; July kept them apart, so both layouts are detected rather than assumed
• the entry number is blank on 28% of rows, and continuation rows print the entry number where the payee belongs
• 58% of entries span several distribution lines; the bank clears the entry, so lines are summed per entry
• debits and credits coexist on one entry (retainage), so the net is what clears

Transaction boundaries come from entry-number and payee changes together, because neither is reliable alone. Grouping strategies were compared against the 588 known-true bank lines rather than chosen by eye.

Other fixes

• Use the entry reference the bank already carries. Payment Instruction Ref. 0000262036 appears on 375 of 588 August lines and was unused. It is exact and needs no tolerance.
• Date is a tie-breaker, not a filter. Measured on reference-only matches, the GL-to-clearing lag runs -27 to +19 days — the bank often clears before the GL posts. The old prompt asked for ±3 business days.
• Check the statement against its own control totals. Opening balance plus extracted movement must equal the closing balance. On the real August statement this ties to the penny; fed the 313 rows that were actually read, it fails and the report says so. This is the guard that would have caught the incident in August.
• Skip pandas Unnamed: N placeholders when resolving columns. "Unnamed" contains "name" and was winning the payee lookup.
• trim_erp_columns is no longer applied to the bank path — it was dropping the Entry column, which is both the roll-up key and the bank join key.

Testing

Run against the customer's real files. July's differently-shaped export needs no code changes (627 ERP vs 626 bank lines). Both statements' parsed debits, credits and ending balances tie to the penny against the PDFs' stated totals.

Not in this change

• The Azure under-extraction itself. prebuilt-bankStatement.us is a consumer-statement model being handed a 43-page commercial statement, and it read 313 of 588 lines. This PR adds detection, not a replacement parser. That is the next piece of work.
• Vendor reconciliation, which still uses the original single-prompt path.

🤖 Generated with Claude Code

Adversarial review of this diff found 6 issues; the 4 that affect what the customer reads are fixed in e0cb588:

• roll-up rows reported the first entry's amount, so every roll-up rendered as a matched pair whose sides disagreed (8 in August, 27 in July — all tie now)
• a payment could match its own reversal, since candidates are keyed on magnitude; reversals are now flagged and ranked last
• _name_close compared the payee against the full description including Payment Instruction boilerplate and so returned False for obvious pairs
• unmatched totals were signed, netting reversal credits against debits (-$8,033,687.59 reported vs $9,764,417.37 gross)

Coverage unchanged: 91.6% ERP / 86.7% bank (August), 78.3% / 70.6% (July).

Two findings left open, neither customer-visible:

• pass 2 carries 370 of 510 matches on amount + date proximity with no payee check, while 58 of 588 bank lines share an amount with another line
• no ERP-side control total, so rows dropped by date-parse failures are silent — the same asymmetry this PR fixes on the bank side