FEBRUARY 15, 2026 · RESEARCH

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

Architecture flow diagram showing three layers: ingestion, reconciliation engine, and output

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.

Metric	Value
Total transactions	5,968
Settlement rate	92.8%
Total volume	IDR 18.2T (~$1.15B USD)
Net PnL (settled)	IDR 41.4B (~$2.6M USD)
Avg blended spread	30.3 bps
Regulatory tax rate	0.21% (OJK)

Pairs:

Pair	Volume share	Avg spread	Notes
USDT/IDR	50%	20 bps	Highest frequency, tightest spread
USDC/IDR	35%	22 bps	Second stablecoin
BTC/IDR	10%	81.6 bps	Volatile, wider spread, higher risk per settlement
PAXG/IDR	5%	103.3 bps	Gold-backed, longest settlement lag

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

// Dashboard

The Looker Studio dashboard connects directly to the 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

Open in Looker Studio → (link available once published)

// 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:

Pair-specific spread — varies by client tier (tighter for Tier A, wider for Tier C)
OJK regulatory tax — 0.21% on the client-facing IDR amount
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:

Pair	Tier A	Tier B	Tier C
USDT/IDR	15 bps	20 bps	25 bps
USDC/IDR	18 bps	22 bps	27 bps
BTC/IDR	55 bps	75 bps	100 bps
PAXG/IDR	70 bps	95 bps	130 bps

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 → · Live API → (link available once deployed)

// 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:

Pair	Crypto leg	Fiat leg	Total window
USDT/IDR	0.1–1.0 hrs	1–4 hrs	Up to 5 hrs
USDC/IDR	0.1–1.0 hrs	1–4 hrs	Up to 5 hrs
BTC/IDR	0.5–2.0 hrs	2–8 hrs	Up to 10 hrs
PAXG/IDR	1.0–4.0 hrs	4–24 hrs	Up to 28 hrs

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

Component	Detail
Data generation	Geometric Brownian Motion via numpy
Dashboard	Self-contained HTML, data baked in as JS objects
BI reports	Looker Studio connected to Google Sheets
Pricer	FastAPI + Uvicorn (deployment pending)

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