Bank reconciliation usually fails for one of two reasons:
- statement imports are inconsistent (bad mapping, bad parsing)
- matching is done manually with no controlled workflow
If you want the product overview first:
The five fields that matter in a bank statement import
No matter how noisy the CSV export is, you need to map to a consistent internal shape:
- date (transaction date)
- description (bank narrative)
- reference (if present)
- amount (signed, consistent convention)
- running balance (optional but useful for validation)
If any of these are inconsistent, matching suggestions will be unreliable and manual review will explode.
Rule 1: make date parsing deterministic
CSV exports in the UAE often contain mixed formats (and sometimes Excel-formatted values).
Operational guardrails:
- treat ISO (
YYYY-MM-DD) as the fast path - support common alternatives (
DD/MM/YYYY,MM/DD/YYYY,DD-MM-YYYY) - reject dates you cannot parse cleanly (fail fast)
Do not “guess” date formats silently. Silent guesses become silent errors.
Rule 2: normalize amount signs (and handle parentheses)
Bank statement exports differ:
- some use separate debit/credit columns
- some use a signed amount column
- some represent negatives as
(123.45)
You need one internal convention:
- money in = positive
- money out = negative
If your team cannot explain the sign convention, reconciliation will always be slow.
Rule 3: keep the raw description (don’t over-clean)
It’s tempting to normalize descriptions aggressively, but over-cleaning destroys the data that helps matching:
- vendor names
- invoice references
- bank fee labels
- transfer identifiers
A good pattern:
- store the raw description
- derive a normalized version for matching/search, but keep raw intact
Rule 4: treat reference fields as optional (but valuable)
Many statements have references that are sometimes empty, sometimes inconsistent, and sometimes incredibly useful.
Do not rely on the reference being present.
But when it is present, it often reduces manual review dramatically.
Rule 5: validate before import (fail fast)
Before importing, run validation checks:
- required mappings exist (date + amount at minimum)
- parsed dates are valid
- parsed amounts are valid numbers (not empty strings)
- after normalization, you still have a meaningful number of lines
You are better off rejecting a bad import early than “importing something” and cleaning up later.
After import: sanity checks that catch the worst issues early
Once imported, do quick sanity checks:
- statement start/end date look correct
- opening/closing balances (if present) are plausible
- total lines imported matches expectations
- outliers (very large amounts) are reviewed explicitly
What to do next
If reconciliation is slow in your team, fix the import consistency first.
Then introduce a controlled matching workflow: