Bank Reconciliation Exception Review
Turn a recurring bank-to-ledger reconciliation into a controlled operator workflow. Worksheet data and matching policy remain visible in Excel; Python scores eligible one-to-one candidates, publishes accepted matches, and leaves unresolved transactions in an exception queue for review.
Result preview
With the supplied 11 bank transactions, 12 GL transactions, 2-day date tolerance, $1.00 amount tolerance, and 72% auto-match threshold, the template accepts 10 pairs: 7 exact and 3 tolerance matches. One bank transaction and two GL transactions remain for review, representing $2,679.75 of absolute exception exposure.
What this template does
- Reads separate bank-statement and general-ledger transaction tables from Excel.
- Applies visible date, amount, and confidence policies before any transaction can auto-match.
- Accepts exact matches first by confidence ranking and resolves remaining eligible candidates one-to-one.
- Publishes exact/tolerance counts, bank match rate, and exception exposure.
- Creates an exception queue with the best available candidate and a reason when an item is not auto-matched.
- Publishes accepted reconciliation pairs to a separate
Reconciledworksheet for downstream review.
Why Python
Transaction reconciliation becomes awkward in formulas once a workbook needs candidate generation, text similarity, tolerances, deterministic ranking, one-to-one assignment, and reason-coded exceptions. Python can express those policies directly while leaving source rows, thresholds, and review outputs in the workbook.
This fixture deliberately uses only pandas plus Python’s standard-library text similarity so it remains portable in the offline browser runtime. The matching policy is explicit rather than opaque: date and amount determine candidate eligibility, description similarity contributes to confidence, and accepted candidates are processed in a deterministic order.
Try it live
Edit bank or GL transactions, change date/amount tolerances, or lower the confidence threshold. Accepted pairs and the exception queue recalculate from the same worksheet policy.
Operating workflow
Author: a finance-systems, controllership, or accounting analyst maintains the matching rules, source-table contract, confidence policy, validation scenarios, and published reconciliation outputs.
Workbook user: an accountant imports or updates bank and ledger activity, adjusts approved policy thresholds when appropriate, then works the unresolved exception queue and reviews the accepted-match output.
For a recurring close workflow, save the finished notebook to open in App mode and verify that the intended accountant can complete the review without author intervention. App mode focuses presentation; it does not change workbook permissions, source access, approval controls, or trust.
Download the Excel template
Inputs and assumptions
Bank Statement!A5:D16 contains 11 bank-side transactions and GL Transactions!A5:D17 contains 12 ledger-side transactions. Each table has a unique transaction ID, date, description, and signed amount. Dashboard!C5:D8 contains a 2-day date tolerance, $1.00 amount tolerance, and 72% auto-match confidence threshold.
The fixture assumes one-to-one matching. Candidate pairs must first fall within both worksheet tolerances. Exact same-day/same-amount candidates receive 100% confidence. Other eligible candidates combine amount closeness, date closeness, and normalized description similarity into a visible confidence rule.
Notebook implementation
The notebook binds both source tables and the matching policy once:
inputs = bf.inputs(
bank=bf.ref("Bank Statement!A5:D16", headers=True),
ledger=bf.ref("GL Transactions!A5:D17", headers=True),
controls=bf.ref("Dashboard!C5:D8", headers=True),
)It publishes three workbook-facing outputs: summary, exceptions, and matches. A Marimo selector filters the task-pane review table between all, bank-only, and GL-only exceptions without changing the durable worksheet exception output.
How the calculation/model works
The notebook first rejects pairs outside the amount or date tolerances. Exact same-date/same-amount pairs receive confidence 1.0. For other eligible candidates, confidence weights amount closeness at 50%, date closeness at 25%, and description similarity at 25%. Candidates are sorted by descending confidence, then by amount difference, date difference, and transaction IDs so the fixture is deterministic. A candidate is accepted only when it meets the confidence threshold and neither side has already been matched.
Unmatched rows retain their best eligible candidate when one exists. The exception queue reports whether that candidate fell below the current confidence threshold or whether no candidate satisfied the date/amount policy.
Validation / expected results
The baseline verifies 10 accepted pairs, 7 exact matches, 3 tolerance matches, 1 bank exception, 2 GL exceptions, a 90.91% bank match rate, and $2,679.75 of exception exposure. A scenario lowers the auto-match threshold from 72% to 45%; the lower-confidence B011/G011 pair then clears policy, increasing accepted pairs to 11 and tolerance matches to 4 while leaving only the unmatched $780 GL transaction in the queue.
The release gate executes both states in the real Boardflare/Univer runtime so the scenario must recompute rather than merely matching static fixture values.
Limitations
This starter workflow does not implement many-to-one or many-to-many matching, settlement batches, foreign exchange, check-clearing logic, posting, approvals, audit sign-off, or persistent user dispositions. Description similarity is intentionally simple and should not be treated as a probabilistic model. Production reconciliation should add domain-specific identifiers, account rules, materiality, approval controls, and an auditable disposition process before transactions are cleared automatically.
When to use this approach
Use it when accounting teams repeatedly reconcile two transaction lists and most rows can be resolved under explainable tolerances while a smaller subset needs human review. It is especially useful when a technical owner can maintain the policy and workbook contract while another person operates the queue each period.