Financial Reconciliation

The problem

Finance teams routinely reconcile transaction records between internal systems. A bank statement arrives as one CSV; the ERP produces another. The underlying transactions should match, but the exports never look identical.

Amounts differ by fractions of a cent due to independent rounding. Column names follow each system's own conventions. Entity names carry trailing spaces or inconsistent casing from manual entry. Timestamps reflect when each system processed the record, not when the transaction occurred.

A line-by-line diff flags all of this as differences, burying the one transaction with a genuinely wrong amount under pages of formatting noise.

The data exports

Your bank sends a daily statement. Your ERP produces its own transaction log. You need to confirm they agree.

bank_transactions.csv (source)

txn_id,date,counterparty,amount,currency,type,posted_at
B-4001,2025-03-01, Acme Corp ,15000.00,USD,WIRE,2025-03-01T16:00:00Z
B-4002,2025-03-01,GLOBEX INC,8750.25,USD,ACH,2025-03-01T16:05:00Z
B-4003,2025-03-01,Initech Ltd,42000.00,EUR,WIRE,2025-03-01T16:10:00Z
B-4004,2025-03-02,Umbrella Corp,3200.50,GBP,WIRE,2025-03-02T09:00:00Z
B-4005,2025-03-02,Soylent Corp,910.00,USD,ACH,2025-03-02T09:15:00Z
B-4006,2025-03-02,Wayne Ent,6400.00,USD,WIRE,2025-03-02T10:00:00Z

erp_transactions.csv (target)

transaction_id,txn_date,entity_name,amount_usd,ccy,payment_type,processed_at
B-4001,2025-03-01,acme corp,15000.02,USD,WIRE,2025-03-01T18:30:00Z
B-4002,2025-03-01,Globex Inc,8750.25,USD,ACH,2025-03-01T18:35:00Z
B-4003,2025-03-01,Initech Ltd,42000.00,EUR,WIRE,2025-03-01T18:40:00Z
B-4004,2025-03-02,Umbrella Corp,6200.50,GBP,WIRE,2025-03-02T12:00:00Z
B-4005,2025-03-02,Soylent Corp,910.00,USD,ACH,2025-03-02T12:05:00Z

What you should notice:

  • Column names differ — txn_id vs transaction_id, amount vs amount_usd, counterparty vs entity_name
  • Counterparty formatting — bank has " Acme Corp " (extra spaces) and "GLOBEX INC" (uppercase); ERP has "acme corp" and "Globex Inc"
  • B-4001 has a tiny amount difference — 15000.00 vs 15000.02 (rounding)
  • B-4004 has a material discrepancy — 3200.50 vs 6200.50 (3000.00 gap)
  • B-4006 is missing from the ERP — a transaction the bank recorded but the ERP did not
  • Timestamps always differ — each system uses its own processing time

The reconciliation config

type: tabular
source: bank_transactions.csv
target: erp_transactions.csv
 
keys:
  - txn_id
 
column_mapping:
  txn_id: transaction_id
  date: txn_date
  counterparty: entity_name
  amount: amount_usd
  currency: ccy
  type: payment_type
 
ignore_columns:
  - posted_at
 
tolerance:
  amount: 0.05
 
string_rules:
  counterparty:
    - trim
    - case_insensitive

Why each section matters

column_mapping — the bank and ERP use different names for the same fields. Mapping aligns them so Reconlify can pair values correctly. Every other config section uses the bank-side (logical) column names.

ignore_columns — excludes posted_at from comparison. The bank and ERP record different processing timestamps for the same transaction, so comparing them would only produce noise. The mapped target column (processed_at) is excluded automatically.

tolerance — allows amount differences up to 0.05. This is the key feature for financial reconciliation.

string_rules — trims whitespace and ignores casing on counterparty names. The bank exports " Acme Corp " with spaces and "GLOBEX INC" in all caps. After trimming and lowercasing, these match the ERP's "acme corp" and "Globex Inc".

Why tolerance matters in financial systems

Financial systems frequently calculate amounts independently. A bank applies its own FX rates, fee calculations, and rounding rules. The ERP does the same with slightly different precision or timing. The result is small differences — often under a cent — that are not errors.

Without tolerance, every one of these rounding differences appears as a mismatch. On a file with thousands of transactions, this creates hundreds of false positives that obscure the real issues.

Tolerance tells Reconlify: if two amounts differ by no more than the threshold, treat them as equal. B-4001's difference of 0.02 is within the 0.05 tolerance and matches. B-4004's difference of 3000.00 far exceeds it and is flagged.

Choosing a tolerance value

Tolerance When to use
0.01 Systems should agree to the penny
0.05 Minor independent rounding expected
0.50 Comparing pre- and post-adjustment figures
No tolerance Omit the field for exact numeric comparison

Start tight and widen only if you see false positives from known rounding behavior.

Interpreting the results

The report summary:

{
  "source_rows": 6,
  "target_rows": 5,
  "missing_in_target": 1,
  "missing_in_source": 0,
  "rows_with_mismatches": 1,
  "mismatched_cells": 1
}

What this means

5 rows matched by key. B-4001 through B-4005 all exist on both sides.

B-4001 reconciles. The amount difference (0.02) is within tolerance. The counterparty matches after trimming and case normalization. " Acme Corp " → "acme corp" → match.

B-4002, B-4003, B-4005 reconcile. All values match exactly (after counterparty normalization on B-4002).

B-4004 is a real mismatch. The report sample shows:

{
  "key": { "txn_id": "B-4004" },
  "columns": {
    "amount": {
      "source": "3200.50",
      "target": "6200.50"
    }
  }
}

A 3000.00 discrepancy on a wire transfer needs investigation — a possible double-booking, a missing reversal, or an incorrect amount entry.

B-4006 is missing from the ERP. The bank recorded a 6400.00 USD wire to Wayne Ent, but the ERP has no corresponding transaction. This could be a timing issue (the ERP has not yet processed it), a failed import, or a booking that was never created.

The column_stats section confirms that amount is the only column with mismatches, so counterparty, currency, date, and payment type all agree across every matched row.

Typical reconciliation scenarios

  • Bank statement reconciliation — match bank transactions against internal records
  • Payment processor validation — compare payment gateway files against ERP entries
  • Invoice matching — verify vendor invoices against purchase orders
  • Month-end close — confirm period-close reports agree across systems
  • Inter-company reconciliation — match transactions between legal entities
  • Regulatory reporting — confirm reported figures match source data