OTC Treasury Reconciliation System

A working prototype of the reconciliation infrastructure used at licensed Indonesian crypto fintech.
Built with real operational logic: dual-leg settlement matching, conservative PnL recognition, and a live pricing engine.

→ GitHub · Pricer API · Live Dashboard ·Google Collab

// What this is

At a licensed OTC crypto desk, every transaction has two legs:

• Crypto leg — wallet transfer (USDT, USDC, BTC, or PAXG)
• Fiat leg — bank transfer (IDR)

PnL can only be recognized when both legs are confirmed. If you book revenue before the bank transfer clears, you are overstating income. If you wait too long, your month-end numbers do not match your actual cash position.

This system automates that matching, tracks settlement status across four account types (bank accounts, crypto wallets, market maker accounts, exchange accounts), and produces a clean PnL ledger — with a live pricing engine on top.

// Architecture

Three layers:

Ingestion (modular — not built in this prototype)
Bank statement PDF parsers, on-chain listeners (TRC20/ERC20/BTC), and manual CSV upload all normalize into the same transaction schema. In production, each data source gets its own adapter. For this project, the dataset is synthetic and represents normalized output.

Reconciliation engine (the core deliverable)
Matching logic pairs buy leg and sell leg via transaction ID. A settlement status tracker moves each transaction through four states: PENDING → SETTLED → RECONCILING → FAILED. PnL is recognized only when both legs have a confirmed settlement timestamp — the later of the two is the recognition date.

Output
Monthly PnL report, account ledger, live dashboard, and the pricer API.

// Dataset

FY 2024 synthetic data. 5,968 transactions across four currency pairs, generated with geometric Brownian motion for realistic rate simulation.

Pairs:

Raw CSVs and the data generation script are in the GitHub repo.

// Dashboard

The dashboard prototype connects to the synthetic dataset and shows:

• Monthly net PnL trend with pair breakdown
• Gross spread → tax → net PnL waterfall
• Settlement status distribution (SETTLED / PENDING / RECONCILING / FAILED)
• Top clients by PnL contribution
• Pair-level spread and volume analysis

The working dashboard is available via the project button above.

// Pricer

The pricing engine is a FastAPI app that accepts a live market maker rate and returns a buy/sell quote with spread and OJK tax applied. Parameters are editable without redeployment — no code change needed to update spreads when market conditions change.

How it works operationally:

A market maker sends their indicative rate. The pricer applies:

1. Pair-specific spread — varies by client tier (tighter for Tier A, wider for Tier C)
2. OJK regulatory tax — 0.21% on the client-facing IDR amount
3. Returns a buy quote, a sell quote, gross spread, tax amount, and estimated net PnL

Endpoints:

POST /quote     → receives MM rate, volume, pair, client tier
                  returns buy quote, sell quote, gross spread, tax, net PnL

GET  /params    → returns current spread config for all pairs and tiers
PUT  /params    → updates spread parameters (requires X-API-Key header)
GET  /health    → service status check

Spread configuration by client tier:

Pricing formula:

Buy quote  = MM rate × (1 + spread + 0.0021)
Sell quote = MM rate × (1 − spread − 0.0021)
Net PnL    = gross spread − tax

PnL is recognized when both settlement legs confirm — not at quote time.

View source on GitHub →. The API deployment is not currently promoted as a public live endpoint.

// Settlement logic

The most important business rule in the system — and the one most often implemented incorrectly in treasury operations.

No warehousing means every transaction is fully matched. The desk buys crypto from the market maker and sells it to the client simultaneously. There is no inventory position. This means:

• If the crypto leg settles but the fiat leg is still pending → PnL = 0
• If both legs settle on different dates → recognition date is the later timestamp
• Month-end cut: a trade done Dec 31 whose bank transfer clears Jan 2 is a January PnL item, not December

This creates a gap between trade date PnL and settlement date PnL that matters for monthly close. The reconciliation engine tracks both.

Settlement lags by pair:

PAXG is the operational outlier. Gold-backed tokens take longer on both legs — the wider spread exists partly to compensate for that extended settlement risk window.

// What’s not built

PDF bank statement parser
In production, the fiat leg confirmation comes from bank statement PDFs — BCA, Mandiri, and BNI each have different formats. A parser layer using pdfplumber with bank-specific adapters would replace the manual CSV upload step. Not built here, but the normalized schema it outputs is identical to what this system already consumes.

On-chain listener
The crypto leg confirmation comes from blockchain data — TRC20 for USDT, ERC20 for USDC and PAXG, native BTC chain. A listener using tronpy, web3.py, or the Blockstream API would monitor wallet addresses and write confirmed transactions into the same schema. The ingestion layer just needs to be built around it.

Real-time alerting
The system currently flags RECONCILING and FAILED transactions in the dashboard. A production version would push alerts when a transaction exceeds 8 hours without both legs confirmed — via Slack, email, or a webhook to the ops team’s ticketing system.

// Stack

Python · Pandas · FastAPI · Pydantic · Chart.js · Looker Studio · GitHub

Synthetic data. Does not represent any real entity or transaction.
Built by Gilang Fajar Wijayanto — Senior Treasury & Finance Operations