Building a Reconciliation Automation Script: Bill Parsing, Field Normalization, and Diff Algorithms

Building a Reconciliation Automation Script: Bill Parsing, Field Normalization, and Diff Algorithms

Reconciliation as a process takes ten minutes to explain: pull the bill, compare against local ledgers, handle differences. Writing the code is where reality intrudes. Is the bill a compressed archive or plain text, is the encoding GBK or UTF-8, do amounts carry currency symbols, does WeChat return cents while Alipay returns yuan, how are differences classified and persisted, and how do you safely rerun a failed job?

This article provides adaptable code. For the process and methodology, see the reconciliation and bill download handbook; here we focus on turning it into a program.

1. Three Traps in the Bill Files

Settle these three before writing any parsing code, or everything downstream is wasted work.

Trap one: the file is not plain CSV. Domestic channel bills are usually ZIP archives containing a CSV. The inner filename may include a date, so never hardcode it; use a tolerant rule such as "the first CSV inside the archive".

Trap two: inconsistent encoding. Some bills are GBK. Reading them as UTF-8 throws or produces mojibake, and amounts then parse into nonsense characters. Try UTF-8 first and fall back to GBK.

Trap three: header and footer lines. Bills often begin with summary rows such as export time and merchant ID, and may end with a totals row. Feeding them straight into csv.DictReader counts them as data, so both the record count and the total amount come out wrong.

import csv, io, zipfile

def load_bill_rows(path: str) -> list[dict]:
    """Read a bill, tolerating zip/csv and UTF-8/GBK encodings."""
    if path.endswith('.zip'):
        with zipfile.ZipFile(path) as zf:
            name = next(n for n in zf.namelist() if n.lower().endswith('.csv'))
            content = zf.read(name)
    else:
        with open(path, 'rb') as fh:
            content = fh.read()

    for enc in ('utf-8-sig', 'gbk'):
        try:
            text = content.decode(enc)
            break
        except UnicodeDecodeError:
            continue
    else:
        raise ValueError(f'unrecognized bill encoding: {path}')

    # Locate the header: the first line containing an order number keyword
    lines = text.splitlines()
    start = next(
        i for i, line in enumerate(lines)
        if ('merchant_order_no' in line.lower()) or ('out_trade_no' in line.lower())
    )
    reader = csv.DictReader(io.StringIO('\n'.join(lines[start:])))
    return [row for row in reader if _looks_like_data(row)]

def _looks_like_data(row: dict) -> bool:
    """Drop totals and blank rows: a data row must carry an order number."""
    first = next(iter(row.values()), '') or ''
    return bool(first.strip()) and not first.startswith('total')

Two deliberate choices. The header is located by keyword rather than a fixed line index, because the number of summary lines changes with the export. Data rows are filtered by requiring an order number, which is more robust than counting columns.

2. Field Normalization

This is the most important step in the whole script. Three channels use different field names, amount units and time formats, and without normalization there is nothing to compare.

Header labels in Chinese bills are localised, so keep them in a single constants block rather than scattering them through the code. The pick helper below references them positionally.

from decimal import Decimal
from datetime import datetime

def normalize(row: dict, channel: str) -> dict:
    def pick(*keys):
        for k in keys:
            v = row.get(k) or row.get(k.strip())
            if v not in (None, ''):
                return str(v).strip()
        return ''

    raw_amount = pick(*AMOUNT_KEYS, 'amount')
    amount = int((Decimal(raw_amount) * 100).quantize(Decimal('1'))) \
        if channel in ('alipay', 'unionpay') else int(Decimal(raw_amount))

    ts = pick(*TIME_KEYS, 'trade_time')
    trade_time = _parse_time(ts)

    return {
        'channel':      channel,
        'order_no':     pick(*ORDER_KEYS, 'order_no'),
        'channel_txn':  pick(*TXN_KEYS),
        'amount':       amount,          # normalized to minor units
        'trade_time':   trade_time,
        'raw_status':   pick(*STATUS_KEYS),
    }

AMOUNT_KEYS = ('\u8ba2\u5355\u91d1\u989d', '\u603b\u91d1\u989d', '\u91d1\u989d')
ORDER_KEYS  = ('\u5546\u6237\u8ba2\u5355\u53f7',)
TXN_KEYS    = ('\u5fae\u4fe1\u8ba2\u5355\u53f7', '\u652f\u4ed8\u5b9d\u4ea4\u6613\u53f7',
               '\u4ea4\u6613\u6d41\u6c34\u53f7')
TIME_KEYS   = ('\u4ea4\u6613\u65f6\u95f4', '\u652f\u4ed8\u65f6\u95f4',
               '\u4ea4\u6613\u521b\u5efa\u65f6\u95f4')
STATUS_KEYS = ('\u4ea4\u6613\u72b6\u6001', '\u652f\u4ed8\u72b6\u6001')

def _parse_time(value: str) -> datetime:
    for fmt in ('%Y-%m-%d %H:%M:%S', '%Y%m%d%H%M%S'):
        try:
            return datetime.strptime(value, fmt)
        except (ValueError, TypeError):
            continue
    raise ValueError(f'cannot parse trade time: {value!r}')

Two points deserve emphasis. Convert amounts with Decimal before multiplying by 100, never float, because errors like 0.1 + 0.2 != 0.3 become a missing cent in reconciliation. Enumerate multiple time formats, since WeChat bills use compact timestamps while Alipay uses delimited ones, and hardcoding a single format always breaks.

3. The Three-Layer Diff Algorithm

The diff is set arithmetic keyed by order number. Never nest loops, because performance collapses at tens of thousands of rows.

def reconcile(local: list[dict], remote: list[dict]) -> dict:
    # Layer one: counts and totals
    local_total = sum(r['amount'] for r in local)
    remote_total = sum(r['amount'] for r in remote)
    summary = {
        'local_count':  len(local),
        'remote_count': len(remote),
        'local_amount': local_total,
        'remote_amount': remote_total,
        'balanced': len(local) == len(remote) and local_total == remote_total,
    }

    # Layer two: record-level matching
    local_map  = {r['order_no']: r for r in local}
    remote_map = {r['order_no']: r for r in remote}
    only_local  = [local_map[k]  for k in local_map.keys()  - remote_map.keys()]
    only_remote = [remote_map[k] for k in remote_map.keys() - local_map.keys()]

    # Layer three: amount and time validation
    amount_diff = [
        {**local_map[k], 'remote_amount': remote_map[k]['amount']}
        for k in local_map.keys() & remote_map.keys()
        if local_map[k]['amount'] != remote_map[k]['amount']
    ]

    return {
        'summary': summary,
        'only_local': only_local,
        'only_remote': only_remote,
        'amount_diff': amount_diff,
    }

The three difference classes map to three responses:

• only_remote is an overage, typically a lost callback, so trigger a query and post the entry;
• only_local is a shortage, possibly a forged callback or a misjudgment, so freeze the order and investigate;
• amount_diff is an amount mismatch, usually a unit or discount allocation error, so fix and rerun.

4. Discrepancy Table and Output

Differences belong in a database table, not just logs, so they form a traceable ledger:

CREATE TABLE `recon_discrepancies` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `recon_date` date NOT NULL,
  `channel` varchar(16) NOT NULL,
  `order_no` varchar(32) NOT NULL,
  `diff_type` varchar(24) NOT NULL COMMENT 'overage/shortage/amount_diff',
  `local_amount` bigint DEFAULT NULL,
  `remote_amount` bigint DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '0 open, 1 in progress, 2 closed',
  `owner` varchar(64) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `closed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_date_channel_order` (`recon_date`, `channel`, `order_no`, `diff_type`)
);

The unique index exists so the job can be rerun safely: the same order and difference type on the same day and channel produces exactly one row, and a rerun uses INSERT ... ON DUPLICATE KEY UPDATE instead of inserting duplicates. That matters enormously when a job fails and is retried.

5. Scheduling and Alerting

Reconciliation runs on T+1 in the morning, usually between 06:00 and 09:00, because bills are generated overnight. Three scheduling rules:

1. The previous day's bill may be generated late, so the job must detect a missing bill and retry in thirty minutes rather than failing outright;
2. The job must be idempotent, producing no duplicates on rerun;
3. Results must be pushed, not merely written to logs.

Alert at two levels: imbalance at layer one, meaning counts or totals disagree, which is a structural failure; and differences above baseline, meaning trend anomalies. A baseline needs two or three weeks of data, so start with layer one only.

6. Five Issues You Will Actually Hit

Encoding. GBK and UTF-8 coexist; use the fallback above rather than supporting only one.

Amount precision. Bill amounts are strings, and float() introduces error, so route everything through Decimal.

Duplicate order numbers. A small number of bills contain several rows for one order number, for example in split settlement. Aggregate by order number before diffing or the comparison reports false differences.

Time zone day boundaries. The bill date uses the channel's time zone. If orders are stored in UTC, convert carefully or cross-day orders appear as differences on two days.

Memory footprint. A month of bills can reach hundreds of thousands of rows. Design for chunk reads or per-day batching from the start rather than waiting for an out-of-memory failure.

The three technical articles relate like this: order numbers and idempotency guarantee nothing is missed or duplicated, the callback framework keeps state correct, and the reconciliation script guarantees eventual consistency. The first two appear in payment order number design and idempotency tables and implementing a multi-channel payment callback framework. For refund and settlement operations, see the refund and settlement operations handbook. More options are collected under the Payment category.

Source: 16IDC original. Product capabilities, pricing and eligibility requirements follow the relevant providers’ official documentation and contract terms.