M-Pesa Reconciliation That Scales: From Paybill Statement to Trial Balance

Month end. Finance sends you a spreadsheet. "The M-Pesa statement says we received KES 4,182,300 but the system says KES 4,176,850. Please investigate." You spend two days in a CSV, find eleven mismatched rows, fix three by hand, and shrug at the rest.

This happens because of one architectural mistake: you built a payments table, not a ledger. A payments table can tell you what you think happened. Only a double-entry ledger plus a three-way match can prove it.

Here is the reconciliation design I now use on every Kenyan project.

The three sources that must agree

Source	What it proves	How you get it
Safaricom statement	Money actually moved in the float	Daily export from M-Pesa Business Hub (Excel/CSV/PDF)
Callback events	Your system was told about it	Your payment_events table
Ledger entries	Your books reflect it	Your ledger_entries table

A break in statement-versus-events means a lost callback. A break in events-versus-ledger means a bug in your settlement worker. Those are entirely different incidents and you want your report to say which one it is.

Normalising the statement

The Business Hub export gives you columns like Receipt No., Completion Time, Initiation Time, Details, Transaction Status, Paid In, Withdrawn, Balance, Reason Type, Other Party Info and A/C No.. It is human-formatted, so the amounts carry thousands separators and the dates are locale-flavoured.

import pandas as pd
from decimal import Decimal

def load_statement(path: str) -> pd.DataFrame:
    df = pd.read_csv(path, dtype=str).fillna("")
    df.columns = [c.strip().lower().replace(" ", "_").replace(".", "") for c in df.columns]

    def money(col):
        cleaned = df[col].str.replace(",", "", regex=False).str.strip()
        cleaned = cleaned.replace("", "0")
        # Withdrawals arrive negative already; keep the sign.
        return cleaned.map(lambda v: int(Decimal(v) * 100))

    out = pd.DataFrame({
        "receipt":    df["receipt_no"].str.strip().str.upper(),
        "completed":  pd.to_datetime(df["completion_time"], dayfirst=True, errors="coerce")
                        .dt.tz_localize("Africa/Nairobi"),
        "paid_in":    money("paid_in"),
        "withdrawn":  money("withdrawn"),
        "balance":    money("balance"),
        "status":     df["transaction_status"].str.strip().str.upper(),
        "reason":     df.get("reason_type", "").str.strip(),
        "account":    df.get("a/c_no", df.get("ac_no", "")).str.strip(),
        "party":      df.get("other_party_info", "").str.strip(),
    })
    out["net_minor"] = out["paid_in"] + out["withdrawn"]
    return out[out["status"] == "COMPLETED"]

Two rules I enforce in code review. Amounts are integers of cents, always. Timestamps are timezone-aware in Africa/Nairobi, always. Break either and your daily boundaries will drift and you will chase phantom breaks at 21:00 and 03:00 UTC.

The gap detector nobody uses: the Balance column

Every statement row carries the running float balance. That means the statement is self-verifying. Sort by completion time and assert that each row's balance equals the previous balance plus the net movement. If it does not, a transaction is missing from your extract - usually because the export window clipped it.

def find_statement_gaps(df):
    df = df.sort_values("completed").reset_index(drop=True)
    expected = df["balance"].shift(1) + df["net_minor"]
    bad = df[(expected.notna()) & (expected != df["balance"])]
    return bad[["receipt", "completed", "net_minor", "balance"]]

The same trick works live: the C2B confirmation callback contains OrgAccountBalance. Store it on every event row. If today's first callback balance does not equal yesterday's last callback balance plus everything in between, you missed a callback while you were asleep and you now know it before finance does.

Schema: a real ledger

CREATE TABLE ledger_entries (
    id            BIGSERIAL PRIMARY KEY,
    entry_group   UUID        NOT NULL,        -- one journal, many lines
    account       TEXT        NOT NULL,        -- 'mpesa_float', 'revenue', 'fees', 'customer_liability'
    direction     CHAR(2)     NOT NULL CHECK (direction IN ('DR','CR')),
    amount_minor  BIGINT      NOT NULL CHECK (amount_minor > 0),
    currency      CHAR(3)     NOT NULL DEFAULT 'KES',
    event_id      BIGINT      REFERENCES payment_events(id),
    booked_on     DATE        NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE statement_lines (
    id             BIGSERIAL PRIMARY KEY,
    shortcode      TEXT        NOT NULL,
    receipt        TEXT        NOT NULL,
    completed_at   TIMESTAMPTZ NOT NULL,
    net_minor      BIGINT      NOT NULL,
    balance_minor  BIGINT      NOT NULL,
    reason_type    TEXT,
    account_ref    TEXT,
    match_state    TEXT        NOT NULL DEFAULT 'UNMATCHED',
    event_id       BIGINT      REFERENCES payment_events(id),
    UNIQUE (shortcode, receipt)
);

Every journal must balance. Enforce it, do not hope for it:

SELECT entry_group,
       SUM(CASE WHEN direction = 'DR' THEN amount_minor ELSE -amount_minor END) AS imbalance
FROM ledger_entries
WHERE booked_on = CURRENT_DATE
GROUP BY entry_group
HAVING SUM(CASE WHEN direction = 'DR' THEN amount_minor ELSE -amount_minor END) <> 0;

That query returning zero rows is a hard gate in my nightly job. If it fails, the reconciliation does not run at all, because reconciling against broken books is worse than not reconciling.

Matching, in three passes

Pass 1 - exact receipt match. This clears 95 percent. MpesaReceiptNumber from the STK callback and TransID from C2B are the same identifier that appears in the statement's Receipt No. column.

UPDATE statement_lines sl
SET match_state = 'MATCHED', event_id = pe.id
FROM payment_events pe
WHERE sl.match_state = 'UNMATCHED'
  AND pe.provider = 'daraja'
  AND pe.provider_ref = sl.receipt
  AND pe.amount_minor = sl.net_minor;

Pass 2 - amount mismatch on the same receipt. Same receipt, different amount, means your settlement logic mis-parsed something. Flag as AMOUNT_MISMATCH, never auto-fix.

Pass 3 - fuzzy, for statement lines with no event at all. Match on amount plus a time window plus the last four digits of the payer number. Anything matched here is a lost callback, and it should raise a ticket even though it auto-resolves, because the underlying delivery problem is still there.

def fuzzy_match(unmatched_lines, orphan_intents, window_minutes=15):
    results = []
    for line in unmatched_lines:
        candidates = [
            i for i in orphan_intents
            if i["amount_minor"] == line["net_minor"]
            and abs((i["created_at"] - line["completed_at"]).total_seconds()) <= window_minutes * 60
            and i["msisdn"][-4:] == line["party"][-4:]
        ]
        if len(candidates) == 1:
            results.append(("LOST_CALLBACK", line, candidates[0]))
        elif len(candidates) > 1:
            results.append(("AMBIGUOUS", line, candidates))
        else:
            results.append(("UNIDENTIFIED_RECEIPT", line, None))
    return results

The break categories you must name

Generic "mismatch" is useless. Give every break a name and an owner:

Category	Cause	Action
LOST_CALLBACK	Money in the statement, no event	Auto-settle from statement, ticket the delivery failure
UNIDENTIFIED_RECEIPT	Customer paid with a wrong or blank account reference	Suspense account, customer support queue
AMOUNT_MISMATCH	Partial or over payment	Book the actual, raise a balance or credit
ORPHAN_INTENT	Your system thinks it was paid, statement disagrees	Urgent. Possible forged callback or a bug. Reverse.
CHARGE	Safaricom transaction charge withdrawal	Book to a fees expense account, not to revenue
REVERSAL	Reason Type indicates a reversal	Contra-entry against the original journal
FLOAT_MOVEMENT	Utility-to-working transfer, bank settlement	Balance sheet movement only, never P&L

That fees row deserves emphasis. Paybill charges appear on the statement as separate withdrawal lines. If you book gross receipts as revenue and ignore the charge lines, your statement will never tie and your gross margin will be quietly overstated all year.

Run it daily, not monthly

The single highest-leverage change most Kenyan SMEs can make is moving from a monthly reconciliation to a daily one. A break found today has context: you remember the deploy, the outage, the customer. A break found in thirty days is archaeology.

def daily_close(day):
    assert no_unbalanced_journals(day), "books do not balance, aborting"

    stmt = load_statement(fetch_statement(day))
    gaps = find_statement_gaps(stmt)
    upsert_statement_lines(stmt)

    exact_match()
    breaks = classify_breaks(day)

    opening = closing_balance(day - timedelta(days=1))
    movement = sum(l["net_minor"] for l in stmt.to_dict("records"))
    closing = stmt.iloc[-1]["balance"]

    report = {
        "date": str(day),
        "statement_gaps": len(gaps),
        "lines": len(stmt),
        "matched": count_by_state("MATCHED"),
        "breaks": {k: len(v) for k, v in breaks.items()},
        "opening_minor": opening,
        "movement_minor": movement,
        "closing_minor": closing,
        "ties": opening + movement == closing,
    }
    publish(report)
    if not report["ties"] or report["breaks"].get("ORPHAN_INTENT"):
        page_oncall(report)

Two habits that pay for themselves

First, make your account reference machine-readable. INV-88421 beats "John's order". Add a check character so a typo fails loudly rather than matching someone else's invoice. Customers do mistype, and a self-validating reference converts a mystery into a rejection you can act on.

Second, store the raw statement file forever, alongside a hash. When a dispute goes to Safaricom support eight months later, "here is the file we ingested, here is its SHA-256, here is the ledger journal it produced" ends the conversation in one email.

Reconciliation is not an accounting chore you bolt on at the end. It is the test suite for your money system. If it is not automated and not running daily, you do not actually know how much money you have.