Data Reconciliation Between Bank, ERP and Spreadsheets
When bank, ERP and spreadsheet figures never quite agree, finance pays in hours. Here is why the differences appear and how to automate most of the matching safely.

Every finance team knows the feeling of a reconciliation that is almost right. The bank says one figure, the ERP (enterprise resource planning) system says another, and the spreadsheet someone keeps on the side says a third. Finding the difference takes an afternoon, and next week it happens again.
Reconciliation is one of the best candidates for automation in finance, because most of the matching follows clear rules. The trick is to understand why the numbers drift apart before building anything.
Why the numbers never agree on their own
Take a homeware retailer in Amman with nine stores, accounts at two banks, card payments through a payment acquirer, and an online store using a payment gateway. Cash is deposited daily, cards settle in batches, and online orders settle a few days later. Each source records the same money differently.
| Cause of difference | What it looks like |
|---|---|
| Timing | Card sales recorded in the ERP on Thursday reach the bank on Sunday or Monday |
| Netting | The acquirer pays one amount per batch after deducting fees, while the ERP holds each sale at its full value |
| Grouping | One bank deposit covers cash from three days of a store’s sales |
| Missing entries | Bank charges, interest or a direct debit appear on the statement but have not been posted |
| Reference problems | A customer transfer arrives with no invoice number, or with the name of a person rather than the company |
| Genuine errors | A receipt posted to the wrong customer, a duplicated entry, a typing mistake in an amount |
Only the last row is a real error. The others are predictable patterns, and predictable patterns can be handled by rules. That is where automation earns its place.
Designing the matching rules
Automated reconciliation works by applying matching rules in order, from the most certain to the least. A practical hierarchy looks like this:
- Exact match on reference and amount. A bank line with an invoice number and an amount that both match one open item. These can be matched automatically with high confidence.
- Exact amount, close date, same counterparty. No reference, but the amount matches one open item from the same customer within a few days. Usually safe to match, often with a quick review.
- Known groupings. A card settlement that equals the sum of a day’s card sales for a store, less the agreed fee rate. Rules can calculate the expected net amount and match the batch.
- Recurring items. Bank charges, rent, salaries and loan instalments that appear every month with similar amounts and descriptions. Rules can propose the posting.
- Everything else goes to a person. Partial payments, unexplained receipts and amounts that match more than one open item should never be matched by guesswork.
Start with conservative rules and loosen them only when the results prove reliable. It is better to send a few extra items for review than to match something incorrectly.
Getting the data in without retyping
Automation needs digital data from every source. Many banks in the region provide statement downloads as CSV (comma-separated values) or Excel files. Some also offer corporate clients standard electronic statement formats, such as the older MT940 format from the SWIFT banking network or the newer camt.053 format under the ISO 20022 standard. These follow a fixed structure, so software reads them more reliably than a spreadsheet export whose columns can change. Card acquirers and payment gateways usually offer settlement reports listing each transaction in a batch. Ask your banks and providers what they offer, and whether downloads can be scheduled rather than done by hand.
From there, the options depend on your systems. Many ERPs, including Odoo, SAP Business One and Microsoft Dynamics, include bank reconciliation features with configurable matching rules. Where the ERP falls short, or where data from spreadsheets and external reports must be combined, Power Query, a small database with scripts, or a workflow tool can prepare and match the data before it reaches the ERP.
Before switching anything on, run one month’s reconciliation by hand with the new rules written down beside you. It shows which rules match cleanly, which produce false matches, and which sources need better references, such as asking key customers to quote invoice numbers on their transfers.
Spreadsheets deserve a specific mention. If a store manager keeps a daily cash sheet that never enters a system, it will always be the hardest source to reconcile. Replacing that sheet with a simple structured form, even a shared one, makes it machine-readable and removes a whole category of differences.
Handling exceptions and keeping control
Automation handles the bulk of the matching, but its bigger benefit is that the remaining exceptions become visible and manageable. For the retailer, a daily exception list might show twenty open items out of several hundred bank lines, each with its age and a suggested reason.
- Review exceptions daily, not monthly. A difference found the next day is easy to explain. The same difference found three weeks later can take hours.
- Assign ownership by type. Store managers explain cash deposit differences, the e-commerce team explains gateway differences, and finance handles bank charges and unidentified receipts.
- Keep an audit trail. Record which rule or person matched each item, and when. This matters for internal control and for external auditors.
- Separate duties. The person who records receipts should not be the only person who approves reconciliation adjustments.
- Watch the trend. If exceptions grow, a rule may have broken, a provider may have changed a report format, or a store process may have slipped.
For the retailer, the practical effect is that store managers hear about a short cash deposit the next morning, while they still remember the day, rather than at month-end when nobody can reconstruct what happened.
Reconciliation as a daily routine, not a month-end crisis
A good place to start is the source that causes most differences today, often card settlements or one busy bank account. Write the matching rules for that source, run them by hand for a month, then automate them. Once matching is automated, month-end close shortens and finance knows each morning what has arrived and what is missing.
Our Intelligent Automation service designs and builds these data reconciliation workflows: bringing bank, ERP and other sources together, defining matching rules your finance team understands, and leaving clear exception lists that keep control where it belongs.



