Reconciling a trading bot's internal ledger with exchange balances

Our first trading systems tracked balances as numbers in memory, updated on each fill. After a few months, they were off by small amounts nobody could explain: a fee paid in a different asset, a funding payment, a manual transfer, a missed fill during a disconnect. Small differences matter: risk limits, PnL, tax reports and position sizing all depend on these numbers.

The fix was boring and effective: an internal double-entry ledger, built from authoritative events, reconciled with the exchange on a schedule. This guide describes the design.

Why double entry

In double-entry bookkeeping, every movement is recorded as at least two entries that sum to zero across accounts. A trade that buys 0.1 BTC for 6,500 USDT with a 6.5 USDT fee becomes:

The structure makes errors visible: entries that do not balance are rejected, and every balance can be explained by its entries.

Schema

CREATE TABLE ledger_transactions (
  id            bigserial PRIMARY KEY,
  source        text NOT NULL,            -- 'trade', 'fee', 'funding', 'transfer', 'adjustment'
  external_id   text NOT NULL,            -- exchange trade id, income id, transfer id
  venue         text NOT NULL,
  occurred_at   timestamptz NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (venue, source, external_id)    -- idempotent ingestion
);

CREATE TABLE ledger_entries (
  transaction_id bigint NOT NULL REFERENCES ledger_transactions(id),
  account        text NOT NULL,
  asset          text NOT NULL,
  amount         numeric(38, 18) NOT NULL
);

A constraint trigger (or the ingestion code in one database transaction) checks that entries of each transaction sum to zero per asset. Amounts use numeric, never floating point.

The unique constraint on (venue, source, external_id) makes ingestion idempotent: replaying the same fill or income record twice does nothing.

Sources of entries

Ingest from the exchange's authoritative records, not from your own intentions:

• Trades from the account trade history (or execution reports, de-duplicated by trade id).
• Fees from each trade, in whatever asset they were charged.
• Funding and other income for derivatives from the income or ledger history.
• Transfers between accounts, sub-accounts and wallets, and deposits and withdrawals.
• Adjustments: anything else the exchange records (rebates, airdrops, delisting conversions, liquidation fees).

Each source has a sync job with a cursor (last processed time or id), so the ledger catches up after any outage.

Reconciliation

On a schedule (every 5 minutes for active accounts, plus end of day), compare ledger balances with exchange balances per asset:

SELECT asset, sum(amount) AS ledger_balance
FROM ledger_entries
WHERE account = 'exchange:binance:spot'
GROUP BY asset;

diffs = {}
for asset in all_assets:
    d = exchange_balances.get(asset, Decimal(0)) - ledger_balances.get(asset, Decimal(0))
    if abs(d) > TOLERANCE[asset]:
        diffs[asset] = d

Tolerances are tiny (dust levels), and timing matters: an order filling between the two reads creates a transient difference. We re-check differences after 30 seconds before alerting.

Handling differences

Every difference has a cause. The common ones, in our experience:

1. A missing source: a new income type, an airdrop, a conversion of small balances. Add the source to ingestion.
2. A missed event: trades or transfers during an outage of a sync job. The cursor-based sync usually catches up; if not, backfill.
3. Timing: resolves on re-check.
4. Rounding: exchange displays rounded balances but tracks more precision internally; compare with raw values.

Track how long each difference stays open and who owns it; an unexplained difference older than a day is treated as an incident. Never "fix" a difference by writing a balance directly. Record an explicit adjustment transaction with a reason and a reference, reviewed by a second person. The ledger stays explainable.

Using the ledger

Once balances reconcile, everything downstream gets simpler: PnL by strategy (tag transactions with strategy ids), fee and funding reports, tax lots, and risk limits based on numbers you trust. Our risk process refuses to increase exposure for an account whose last reconciliation failed.

Reporting from the ledger

With a ledger that reconciles, reports become queries. Daily statements per account, fees by venue and asset, funding by contract, and PnL by strategy all come from the same entries, so they always agree with each other. We publish a daily summary to the team channel with the reconciliation status at the top: green when every account matched at end of day, with a list of explained adjustments otherwise.

Performance and retention

Ledger tables grow quickly with active trading. We partition entries by month, index by account and asset, and keep materialised daily balances per account and asset so reconciliation does not sum years of entries every five minutes. Old partitions move to cheaper storage but are never deleted within the retention period required for accounting and tax purposes.

Common mistakes

Key takeaways

• Record every movement as balanced double entries.
• Ingest from exchange records idempotently, with cursors per source.
• Reconcile frequently and re-check before alerting.
• Explain every difference; adjust only with reviewed, explicit entries.
• Block risk increases when reconciliation fails.
• Partition and pre-aggregate the ledger so reconciliation stays fast as history grows.
• Publish reconciliation status daily so drift is visible to the whole team, not just the on-call engineer.

References

• Binance Spot API, account trade list — https://developers.binance.com/docs/binance-spot-api-docs/rest-api/account-endpoints
• Binance USD-M futures, income history — https://developers.binance.com/docs/derivatives/usds-margined-futures/account/rest-api/Get-Income-History
• PostgreSQL numeric type — https://www.postgresql.org/docs/current/datatype-numeric.html
• Martin Fowler, accounting patterns — https://martinfowler.com/eaaDev/AccountingNarrative.html