Bank Reconciliation in Excel With Python

How to structure a repeatable bank-to-ledger reconciliation workflow in Excel using Python for candidate matching, deterministic ranking, confidence thresholds, and exception review.

Bank reconciliation is a good candidate for Python when the workbook has moved beyond exact lookup logic and needs tolerance rules, description similarity, one-to-one assignment, confidence thresholds, and a reviewable exception queue.

The goal should not be to automate every transaction at any cost. A better design is to automate the explainable matches and make the unresolved cases easier for an accountant to review.

Boardflare’s Bank Reconciliation Exception Review shows that operating model: source transactions and matching policy remain visible in Excel, Python scores and assigns eligible pairs, and the workbook publishes both accepted matches and unresolved exceptions.

Start with explicit matching policy

A reconciliation workflow needs policy before it needs code.

For each potential bank-to-GL pair, decide what makes it eligible for consideration. The template uses three visible dimensions:

• date difference;
• amount difference;
• description similarity.

Date and amount tolerances define candidate eligibility. Description similarity contributes to confidence only after the candidate passes those hard limits.

That distinction is important. If two transactions differ by $500, a very similar description should not allow the pair to bypass a $1 amount-tolerance policy.

Put the durable policy where reviewers can see it. In the template, date tolerance, amount tolerance, and the auto-match confidence threshold are worksheet inputs.

Separate exact matches from tolerance matches

This workflow deliberately treats exact same-date, same-amount pairs as a separate class from tolerance candidates so the amount of judgment applied by the automation remains visible.

The template gives exact same-day/same-amount candidates 100% confidence and labels them Exact. Other eligible pairs are scored as Tolerance matches.

That produces more useful operating metrics:

• exact matches;
• tolerance matches;
• unmatched bank transactions;
• unmatched GL transactions;
• total exception exposure.

A reconciliation report that says “95% matched” without distinguishing exact and tolerance matches hides important information about how much judgment the automation is applying.

Make candidate scoring explainable

The teaching model uses a simple confidence score:

50% amount closeness
25% date closeness
25% normalized description similarity

That is not a universal scoring formula. Its value is that the policy is inspectable.

For production use, you might add stronger signals such as:

• check or payment reference;
• bank trace ID;
• customer/vendor identifier;
• account code;
• settlement batch;
• currency;
• posting type.

The right features depend on the transaction domain. The important rule is that users should be able to understand why a transaction auto-matched and which policy allowed it.

One-to-one assignment matters

Candidate generation is not the same as reconciliation.

A bank transaction may have several plausible ledger candidates, and a ledger row must not be consumed by multiple bank transactions in a one-to-one workflow.

The template therefore sorts eligible candidates deterministically and accepts a pair only if:

1. its confidence meets the current threshold;
2. the bank transaction has not already been matched;
3. the GL transaction has not already been matched.

The deterministic ordering uses confidence first, then amount difference, date difference, and transaction IDs as tie-breakers.

This makes the fixture reproducible and avoids results changing merely because input rows happen to be traversed in a different order.

It also creates an important exception case: a transaction can have an above-threshold candidate and still remain unmatched because that candidate was already assigned to a higher-ranked transaction. That is different from having no eligible candidate or having only below-threshold candidates.

For more complex real-world reconciliation, you may need many-to-one or many-to-many assignment. That is a different problem and should be modeled explicitly rather than forced into a one-to-one heuristic.

Keep exceptions as first-class output

A reconciliation application is incomplete if it produces only the accepted matches.

The unresolved items are often where the accounting work actually happens.

A useful exception queue should include enough context to answer:

• Which side is unmatched?
• What is the transaction ID, date, description, and amount?
• Was there a plausible candidate?
• Why was it not accepted?

The Boardflare template retains the best eligible candidate when one exists and distinguishes three cases:

• the best candidate is below the current confidence threshold;
• no candidate satisfies the date/amount policy; or
• an above-threshold candidate was already consumed by a higher-ranked one-to-one match.

That turns the exception queue into a review tool rather than a list of failures.

Use widgets to filter review, not redefine the policy

The reconciliation notebook includes a dropdown for:

• all exceptions;
• bank-only exceptions;
• GL-only exceptions.

That control changes the task-pane view but not the durable reconciliation result published to Excel.

This is a useful UI pattern. Matching policy stays in visible worksheet inputs; a notebook widget helps the operator work through the resulting queue without silently changing the reconciliation rules.

Validate both policy and assignment

A reconciliation can look plausible even when its threshold or one-to-one assignment logic is wired incorrectly. Validation should therefore cover both.

The baseline uses a 72% auto-match threshold and produces 10 accepted pairs: 7 exact and 3 tolerance matches. One bank transaction and two GL transactions remain unresolved, representing $2,679.75 of absolute exception exposure.

A threshold scenario lowers the cutoff to 45%. One lower-confidence bank/GL pair becomes accepted, increasing the result to 11 accepted pairs and leaving one unmatched $780 GL transaction.

A second scenario deliberately makes two bank rows compete for the same GL row. The higher-ranked pair is accepted and the other bank row remains in the exception queue with the reason Candidate assigned to higher-ranked match.

Those scenarios test different things. The first proves that worksheet policy changes the actual assignment result. The second proves that the one-to-one constraint and deterministic ranking are enforced rather than merely displayed.

Measure exposure, not only row counts

Ten small exceptions and one $5 million exception are not operationally equivalent.

The template reports absolute exception exposure in addition to exception counts. Production workflows can extend this with:

• materiality bands;
• aging;
• account or entity concentration;
• unmatched cash direction;
• close-calendar priority;
• reviewer assignment.

This helps the exception queue support risk-based review rather than simple row processing.

What not to automate casually

A starter reconciliation model should not be mistaken for a complete accounting control system.

The template intentionally does not implement:

• posting;
• approval workflow;
• audit sign-off;
• persistent reviewer dispositions;
• many-to-many matching;
• FX logic;
• settlement-batch accounting;
• check-clearing rules.

Those requirements affect auditability and control ownership. Add them only with a clear design for permissions, evidence, and review history.

App mode also does not solve those problems. It changes presentation, not workbook permissions or approval controls. See App Mode and Sharing for that boundary.

The operating model

A strong recurring reconciliation workflow separates two roles.

The technical/accounting-systems owner maintains:

• source-table contract;
• matching rules;
• confidence policy;
• assignment logic;
• validation fixtures;
• published outputs.

The accountant/operator updates transaction data, applies approved policy settings, reviews accepted matches, and works the unresolved exception queue.

That is where Python adds the most value: not by making accounting judgment disappear, but by turning a repetitive matching process into a controlled queue with explicit policy and deterministic automation.

Use the Bank Reconciliation Exception Review to inspect the working workbook, baseline outputs, threshold scenario, and one-to-one contention test.