Learn to pull open interest data from Binance, Bybit, and OKX using Python APIs, export it to Excel, and build analysis dashboards that reveal what big players are doing.
Open interest is one of the most powerful — and most ignored — signals in crypto derivatives trading. While everyone watches price charts and volume bars, the traders who consistently outperform track the money flowing into and out of open positions. The problem is that most platforms give you a single OI number and call it a day. Real open interest data analysis requires historical data, multi-timeframe comparisons, and a spreadsheet you can actually manipulate. That's where open interest analysis excel download workflows come in. Pulling data from exchange APIs and loading it into Excel gives you the analytical flexibility that no dashboard can match — slice it, pivot it, add your own indicators, and correlate it against price action on your own terms. This guide walks through the exact Python code to fetch OI data from Binance, Bybit, and OKX, export it to a formatted Excel file, and what patterns to look for once you've got the data in front of you.
Most retail traders treat open interest as a background indicator — something they glance at on CoinGlass and forget. That's a mistake. Open interest tells you how much speculative capital is currently committed to a market. When OI rises alongside price, new money is entering long positions and the trend has genuine fuel. When OI rises while price falls, new shorts are piling in. When OI drops sharply, a wave of liquidations or deliberate position closing is underway — and the market is about to breathe differently.
The reason experienced traders download and analyze OI in Excel rather than reading it on a platform dashboard is control. You can build rolling averages, calculate rate-of-change, flag anomalies, and cross-reference with funding rates or liquidation data in the same file. Platforms like Bybit and OKX have improved their built-in analytics significantly, but they still lock you into their visualization — you can't build a custom 48-hour OI velocity indicator in their UI. Your spreadsheet can do whatever you need it to do.
Three exchanges dominate the derivatives landscape and all offer solid public APIs for OI data: Binance Futures, Bybit, and OKX. Binance provides historical open interest through its futures data endpoint at https://fapi.binance.com/futures/data/openInterestHist — no API key required, supports intervals from 5m to 1d, and returns up to 500 data points per call. Bybit exposes historical OI at https://api.bybit.com/v5/market/open-interest with category linear for USDT perpetuals, also key-free for market data. OKX goes a step further with https://www.okx.com/api/v5/rubik/stat/contracts/open-interest-volume, which combines OI with volume data in a single response and supports a date range query.
Gate.io and Bitget also expose open interest via their public APIs if you trade on those platforms, but for most traders the Binance-Bybit-OKX trio covers the majority of relevant open interest since these three account for the bulk of crypto perpetual futures volume. Coinbase does not offer derivatives in the US, though its international arm has limited futures data. For this guide we'll focus on the big three since their data is the most liquid and therefore the most meaningful for signal generation.
All three endpoints shown in this guide are public — no API key or authentication required. Rate limits are generous for personal use: Binance allows 500 requests per minute on the futures data endpoints, Bybit allows 120/min for market data, and OKX allows 20 requests per 2 seconds.
The Binance futures data endpoint is the simplest to start with. It returns a list of timestamped OI snapshots for any perpetual futures symbol. The following function handles fetching, parsing, and basic error handling — everything you need before moving on to the Excel export step.
import requests
import pandas as pd
from datetime import datetime
def fetch_binance_open_interest(symbol='BTCUSDT', period='1h', limit=200):
url = 'https://fapi.binance.com/futures/data/openInterestHist'
params = {
'symbol': symbol,
'period': period, # 5m, 15m, 30m, 1h, 2h, 4h, 6h, 12h, 1d
'limit': limit # max 500
}
try:
response = requests.get(url, params=params, timeout=10)
response.raise_for_status()
except requests.exceptions.RequestException as e:
raise Exception(f'Binance API request failed: {e}')
data = response.json()
df = pd.DataFrame(data)
df['timestamp'] = pd.to_datetime(df['timestamp'], unit='ms')
df['sumOpenInterest'] = df['sumOpenInterest'].astype(float)
df['sumOpenInterestValue'] = df['sumOpenInterestValue'].astype(float)
df['oi_change_pct'] = df['sumOpenInterestValue'].pct_change() * 100
df = df.sort_values('timestamp').reset_index(drop=True)
return df
# Fetch 200 hours of BTC and ETH open interest
btc_oi = fetch_binance_open_interest('BTCUSDT', '1h', 200)
eth_oi = fetch_binance_open_interest('ETHUSDT', '1h', 200)
print(btc_oi[['timestamp', 'sumOpenInterestValue', 'oi_change_pct']].tail(5))The sumOpenInterest column is denominated in contracts (BTC for BTCUSDT), while sumOpenInterestValue is USD-denominated — use the latter for dollar-based comparisons across assets. The oi_change_pct column we calculate manually is the key signal: values above +2% in a single hour often precede breakout moves, and drops below -3% frequently coincide with liquidation cascades worth watching on a shorter timeframe.
Fetching data is the easy part. Making it usable in Excel requires proper formatting — otherwise you get a flat, unreadable dump. The function below fetches from Bybit's API and exports to a styled Excel file where each asset gets its own sheet, headers are highlighted, and columns auto-fit. This is the foundation for an open interest data analysis dashboard you can actually share with others or build charts from.
import requests
import pandas as pd
from openpyxl.styles import Font, PatternFill, Alignment
def fetch_bybit_open_interest(symbol='BTCUSDT', interval='60', limit=200):
url = 'https://api.bybit.com/v5/market/open-interest'
params = {
'category': 'linear',
'symbol': symbol,
'intervalTime': interval, # 5, 15, 30, 60, 120, 240, 360, 720, D, W, M
'limit': limit
}
response = requests.get(url, params=params, timeout=10)
data = response.json()
if data['retCode'] != 0:
raise Exception(f'Bybit error {data["retCode"]}: {data["retMsg"]}')
records = data['result']['list']
df = pd.DataFrame(records, columns=['openInterest', 'timestamp'])
df['timestamp'] = pd.to_datetime(df['timestamp'].astype(int), unit='ms')
df['openInterest'] = df['openInterest'].astype(float)
df = df.sort_values('timestamp').reset_index(drop=True)
df['oi_delta'] = df['openInterest'].diff()
df['oi_change_pct'] = df['openInterest'].pct_change() * 100
df['rolling_avg_24h'] = df['openInterest'].rolling(24).mean()
return df
def export_to_excel(dataframes: dict, filename='open_interest_analysis.xlsx'):
with pd.ExcelWriter(filename, engine='openpyxl') as writer:
for sheet_name, df in dataframes.items():
df.to_excel(writer, sheet_name=sheet_name, index=False)
ws = writer.sheets[sheet_name]
# Style header row
for cell in ws[1]:
cell.font = Font(bold=True, color='FFFFFF')
cell.fill = PatternFill(fgColor='1F4E79', fill_type='solid')
cell.alignment = Alignment(horizontal='center')
# Auto-fit columns
for col in ws.columns:
max_len = max(len(str(c.value or '')) for c in col) + 4
ws.column_dimensions[col[0].column_letter].width = max_len
print(f'Saved: {filename}')
# Fetch and export BTC + ETH from Bybit
btc = fetch_bybit_open_interest('BTCUSDT', '60', 200)
eth = fetch_bybit_open_interest('ETHUSDT', '60', 200)
sol = fetch_bybit_open_interest('SOLUSDT', '60', 200)
export_to_excel({
'BTC_Open_Interest': btc,
'ETH_Open_Interest': eth,
'SOL_Open_Interest': sol
}, 'bybit_oi_dashboard.xlsx')The rolling_avg_24h column is particularly useful in Excel — you can chart the raw OI line against the 24-hour average to visually identify periods where OI is running hot above its mean. That elevated state is where volatility events cluster. The oi_delta column (absolute change in contracts per hour) is better for spotting sudden position size shifts than the percentage change alone, especially in large-cap markets where 0.5% change is actually millions of dollars.
Once the single-symbol workflow is solid, the natural next step is batch-downloading multiple assets into a single Excel workbook — one sheet per coin. OKX's statistics endpoint is ideal for this because it returns both OI and volume in one call, saving a second API request. The function below fetches 14 days of hourly data for a list of currencies and exports everything with built-in error handling so a failure on one asset doesn't kill the whole batch.
import requests
import pandas as pd
import time
from datetime import datetime, timedelta
def fetch_okx_oi_history(ccy='BTC', period='1H', days_back=14):
url = 'https://www.okx.com/api/v5/rubik/stat/contracts/open-interest-volume'
end_ts = int(datetime.now().timestamp() * 1000)
start_ts = int((datetime.now() - timedelta(days=days_back)).timestamp() * 1000)
params = {
'ccy': ccy,
'begin': str(start_ts),
'end': str(end_ts),
'period': period # 5m, 1H, 1D
}
response = requests.get(url, params=params, timeout=10)
data = response.json()
if data['code'] != '0':
raise Exception(f'OKX error: {data["msg"]}')
cols = ['timestamp', 'oi_coins', 'oi_usd', 'volume_usd']
df = pd.DataFrame(data['data'], columns=cols)
df['timestamp'] = pd.to_datetime(df['timestamp'].astype(int), unit='ms')
for col in ['oi_coins', 'oi_usd', 'volume_usd']:
df[col] = df[col].astype(float)
df['oi_to_volume_ratio'] = df['oi_usd'] / df['volume_usd'].replace(0, float('nan'))
df = df.sort_values('timestamp').reset_index(drop=True)
return df
def batch_export_okx(currencies, output='multi_asset_oi.xlsx'):
results = {}
for ccy in currencies:
try:
df = fetch_okx_oi_history(ccy, '1H', 14)
results[ccy] = df
print(f'OK {ccy}: {len(df)} rows')
except Exception as e:
print(f'FAIL {ccy}: {e}')
time.sleep(0.3) # Respect OKX rate limit
with pd.ExcelWriter(output, engine='openpyxl') as writer:
for ccy, df in results.items():
df.to_excel(writer, sheet_name=ccy, index=False)
print(f'\nExported {len(results)} assets to {output}')
batch_export_okx(['BTC', 'ETH', 'SOL', 'BNB', 'XRP', 'DOGE'])The oi_to_volume_ratio column this script adds is one of the most useful custom metrics you can track. When it climbs above 8-10x, the market is holding a lot of open positions relative to its daily trading activity — leverage is high, and a catalyst will cause outsized moves. When it drops below 2-3x, the market is actively churning positions and OI is declining as contracts close. Tracking this ratio over time in Excel gives you a leverage gauge that no standard platform dashboard displays.
Raw OI numbers mean very little without context. The analysis comes from relationships — OI direction versus price direction, OI acceleration versus recent norms, and cross-asset OI divergences. Platforms like VoiceOfChain provide real-time open interest signals already interpreted for you, but understanding the underlying data structure lets you validate those signals and customize your own triggers. Here are the four core OI-price relationships that drive most actionable setups:
| OI Trend | Price Trend | Signal Type | What It Means |
|---|---|---|---|
| Rising | Rising | Bullish continuation | New long positions entering — trend has real capital behind it |
| Rising | Falling | Bearish continuation | New short positions building — sellers adding conviction |
| Falling | Rising | Weak rally | Short squeeze / covering, not new longs — watch for reversal |
| Falling | Falling | Potential capitulation | Long liquidations clearing — market may stabilize or reverse |
The most tradeable setup is the divergence case: price making new highs while OI is flat or declining. That's a sign the rally is being driven by short covering or spot buyers rather than leveraged futures longs, which means there's less forced selling if price reverses. Conversely, price dropping with OI rising sharply is the setup where cascading liquidations become possible — new shorts are adding at lower prices, and a bounce will squeeze them hard. Building these flags in Excel using conditional formatting (highlight cells where oi_change_pct > 3 and price is falling) automates the scanning work significantly.
Open interest analysis excel download workflows give you something no trading platform can: total analytical freedom. Once you have raw OI data from Binance, Bybit, and OKX loaded into a spreadsheet you control, you can build exactly the indicators and dashboards that match your trading style — leverage ratios, rolling anomaly detection, cross-asset comparisons, or simple OI-price divergence flags. The Python code in this guide is production-ready as a starting point. Run it manually while you're learning the patterns, then automate it with a scheduler once you know which metrics you actually use. Pair the spreadsheet analysis with real-time signal tools like VoiceOfChain to cover both the analytical depth and the speed that discretionary derivatives trading requires.