This project provides an automated data reconciliation pipeline to audit user balances for BTC, ETH, and USDT. It identifies financial discrepancies by comparing reported ending balances against the net effect of 30-day transaction histories.
The script processes transaction logs and wallet snapshots to ensure data integrity through the following steps:
- Balance Reconciliation: Compares the reported ending balance against the sum of deposits, withdrawals, and trades.
- Implied Opening Calculation: Subtracts the last 30 days of activity from the current balance to flag users where transaction history does not mathematically align.
- Withdrawal Risk Analysis: Identifies "stuck" or unprocessed withdrawals where a transaction was initiated but is not fully reflected in the final balance.
- Integrity Filtering: Detects duplicate records and incorrectly posted transactions, such as negative deposit amounts or zero-value trades.
The most recent audit analyzed a dataset of 171,451 users with the following findings:
| Metric | Value |
|---|---|
| Mismatched Users | 143,467 (~83.68%) |
| Stuck Withdrawal Risk (Sum) | 87,936.13 |
| Duplicate Trade Rows | 673 |
| Bad Operation Rows | 114 |
| Max Individual Risk | 49,999.99 |
Crypto_balances_checkup.ipynb: Main Jupyter Notebook containing the reconciliation logic.data.xlsb: Source data file containing balances, operations, and trades (required)./extra_tables/: Output directory where flagged discrepancies are exported as CSV files for manual review.
- Python 3.11+
- Pandas & NumPy
- pyxlsb (for processing
.xlsbfiles)