Automating Balance and Position Reconciliation with Crypto-Pandas
If you have ever spent hours hopping between exchange dashboards, reconciling balances by hand, or attempting to reconcile your spot and derivatives positions, then you know the agony.. The solution lies in automation. And that is where Crypto-Pandas, in combination with ccxt, comes into play.
Why Balance and Position Reconciliation Matters
Before diving into the code, let’s talk about why reconciliation is so important:
- Unified view of assets: Most exchanges separate spot balances, derivatives positions, and contract sizes. Without merging these views, you only see fragments of your financial reality.
- Risk management: If you don’t know your true exposure, you can’t properly manage leverage or position sizing. A small miscalculation here can lead to huge losses.
- Capital allocation: Traders need to know exactly where their funds are, whether in spot, margin, or futures, to decide how much capital is available for new trades.
- Efficiency: Manually adding things up is slow, error-prone, and distracting. Automation frees you up to focus on strategy, not bookkeeping.
Now let’s see how Crypto-Pandas makes this easier.
Step 1: Setting Up the Environment
We start by importing the libraries and configuring the exchange connection.
import os
import ccxt
import pandas as pd
from crypto_pandas import CCXTPandasExchange
from dotenv import load_dotenv
# Load environment variables
load_dotenv()
# Initialize exchange
exchange = ccxt.okx({
“apiKey”: os.environ[“OKX_SANDBOX_API_KEY”],
“secret”: os.environ[“OKX_SANDBOX_API_SECRET”],
“password”: os.environ[“OKX_SANDBOX_API_PASSWORD”],
})
# Enable sandbox mode
exchange.set_sandbox_mode(True)
# Wrap exchange with CCXTPandasExchange
pandas_exchange = CCXTPandasExchange(exchange=exchange)
# Load markets, balances, and positions
markets = pandas_exchange.load_markets()
balance = pandas_exchange.fetch_balance()
positions = pandas_exchange.fetch_positions()
Breaking It Down
Environment variables: Instead of hardcoding API keys and secrets, we use dotenv to load them from a .env file. This is safer and cleaner.
Sandbox mode: We are connecting to the OKX exchange in sandbox mode. This lets us test without risking real money, which is perfect for experimenting.
Crypto-Pandas wrapper: By wrapping ccxt with CCXTPandasExchange, data is returned as Pandas DataFrames instead of raw JSON. That makes it far easier to work with.
Loading data: We now have three key datasets:
markets: metadata about assets
balance: current spot balances
positions: open derivative positions
At this point, we have the raw data. The next step is to make it usable.
Step 2: Renaming and Cleaning Data
Once balances and positions are fetched, we need to align them into a common structure.
# Rename symbol to base
balance = balance.rename(columns={“symbol”: “base”, “free”: “amount”})
print(balance.to_markdown(index=False))
Step 3: Merging Markets and Positions
Now let’s align positions with market data.
# Merge markets onto positions
positions = positions.merge(markets[[“symbol”, “base”]])
positions[“amount”] = positions[“contracts”].where(
positions[“side”] == “long”, other=-positions[“contracts”]
)
positions[“amount”] *= positions[“contractSize”]
print(positions.to_markdown(index=False))
Why This Matters
Merging with markets: The positions DataFrame doesn’t automatically know which base asset each contract belongs to. By merging it with markets, we map contracts like BTC-USDT swaps to their base currency (BTC).
Long vs. short handling: Positions can be long or short. Long contracts are treated as positive exposure, while short contracts become negative exposure. This is done with the .where() function.
Scaling by contract size: In derivatives, one contract may not equal one unit of the asset. The contractSize field makes sure the numbers reflect real exposure.
After this, our positions DataFrame shows exactly how much of each asset we are effectively holding, whether it is spot or leveraged.
Step 4: Reconciling Everything
With balances and positions standardized, we can combine them and calculate totals.
# Concat and sum by base
delta = (
pd.concat([balance, positions], ignore_index=True)
.groupby(“base”, as_index=False)[“amount”]
.sum()
)
print(delta.to_markdown(index=False))
What We Achieve
- Concatenation: We stack balances and positions into one DataFrame.
- Grouping and summing: Grouping by base (BTC, ETH, etc.) shows the net position across both spot balances and derivative contracts.
- Final result: A clean summary table of your real exposure in each asset.
This is the payoff. Instead of juggling numbers across exchange tabs, you now have a single view that tells the whole story.
Why This Workflow Works
Taking It Further
Portfolio tracking: Combine multiple exchanges and wallets into one unified view.
PnL calculation: Add price feeds to calculate profit and loss.
Risk metrics: Use reconciled balances to track leverage ratios and margin requirements in real time.
Automation: Set alerts to notify you on Slack or Telegram when exposure crosses a threshold.
Crypto-Pandas makes messy exchange data clean and structured, unlocking all kinds of possibilities.
The Next Step for a Better Trading Experience
Reconciliation does not need to be a nightmare. By combining ccxt with Crypto-Pandas and Pandas, you can automate the process and transform hours of manual work into seconds of clean results.
The code we walked through is short, elegant, and powerful. It renames and standardizes balance data, merges positions with market data, adjusts for long and short contracts, multiplies by contract sizes, and reconciles everything into a clear net position view.
For traders, this is not just a coding exercise, it is a survival tool. It ensures every decision is based on reality, not on incomplete or misleading data. And in markets where precision matters, that is the edge you need.
If you have been struggling to reconcile balances and positions, this workflow is the answer. Automate it once and give yourself the freedom to focus on finding your edge in the market.
GitHub Repository: GitHub Repo
Install crypto-pandas: Pypi
Follow us on YouTube at Sigma Quantiphi for tutorials
Visit Sigma Quantiphi: https://sigmaquantiphi.com/
Try it in your browser in Jupyter Notebook: Launch Notebooks
