{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# Multi-exchange BTC volatility surface\n\nOne SQL query against `api.option_chains` returns the latest snapshot across **Deribit, Binance, OKX, and Bybit** at once. The same dataframe drives four side-by-side surface plots — plus an ATM term-structure and a single-expiry smile that put the four venues on one axis — with no per-exchange repeated round-trips.\n\nReplace the connection placeholders with your Koinju SQL credentials (see [How to connect](https://docs.koinju.io/how-to-connect)).",
   "id": "b3bf08dc028a1191"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-05-19T09:13:00.916920817Z",
     "start_time": "2026-05-19T09:12:58.142307465Z"
    }
   },
   "cell_type": "code",
   "source": "%pip install -q clickhouse-connect pandas numpy scipy plotly",
   "id": "128dc9482115adf2",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-05-19T09:13:03.011584245Z",
     "start_time": "2026-05-19T09:13:00.928650782Z"
    }
   },
   "source": "import clickhouse_connect\nimport numpy as np\nimport plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\nfrom scipy.interpolate import griddata\n\nconn = clickhouse_connect.get_client(\n    host=\"\",\n    port=8443,\n    username=\"\",\n    password=\"\",\n    database=\"api\",\n)",
   "id": "2c707edaea942e5c",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## One query, four exchanges\n\nWe grab the latest 5-minute snapshot per exchange and project the analysis-ready columns: strike, DTE (days to expiry), `underlying_price` (spot), and `moneyness = abs(strike − spot)` so the at-the-money strike is a pure sort later. `toFloat64(...)` casts the `Decimal(38, 18)` columns to plot-ready floats server-side — no pandas cast needed. The `WITH` clause anchors every exchange to the freshest snapshot timestamp present in the table; `dte BETWEEN 2 AND 150` crops same-day and far-dated expiries server-side.",
   "id": "3702c49b96f0f47d"
  },
  {
   "cell_type": "code",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-05-19T09:13:03.193203683Z",
     "start_time": "2026-05-19T09:13:03.066436745Z"
    }
   },
   "source": "df = conn.query_df(\"\"\"\nWITH (\n  SELECT max(timestamp)\n  FROM api.option_chains\n  WHERE underlying_asset = 'BTC' AND timestamp > now() - INTERVAL 30 MINUTE\n) AS latest_ts\nSELECT\n  exchange,\n  toDate(expiration)                                AS expiry,\n  toFloat64(strike)                                 AS strike,\n  toUInt32(date_diff('day', timestamp, expiration)) AS dte,\n  toFloat64(mark_iv)                                AS iv,\n  toFloat64(underlying_price)                       AS spot,\n  toFloat64(abs(strike - underlying_price))         AS moneyness\nFROM api.option_chains\nWHERE underlying_asset = 'BTC'\n  AND mark_iv != 0\n  AND timestamp >= latest_ts - INTERVAL 5 MINUTE  -- snapshots refresh every 5 minutes\n  AND dte BETWEEN 2 AND 150\nORDER BY exchange, expiry, strike\n\"\"\")",
   "id": "16c61a931faca791",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Interpolate each exchange's surface onto a shared grid\n\nListed strikes and expiries differ across venues. Interpolating onto a common `(dte, strike)` grid makes the four surfaces directly comparable. We crop the strike axis to a fixed `[40k, 160k]` band to drop deep-wing noise; far-dated expiries (>150 DTE) are already cropped server-side. Those quotes are usually stale or wide and add visual noise without information.",
   "id": "b978481db05884f2"
  },
  {
   "cell_type": "code",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-05-19T09:13:03.268807538Z",
     "start_time": "2026-05-19T09:13:03.208774256Z"
    }
   },
   "source": "GRID_N = 80\n# Client-side strike crop: a constant you can re-tune without re-querying.\n# `spot` is in the result set, so this could also be a SQL moneyness\n# filter; DTE is already cropped server-side in the query above.\nSTRIKE_MIN, STRIKE_MAX = 40_000, 160_000\n\ndf_f = df[df['strike'].between(STRIKE_MIN, STRIKE_MAX)]\n\nx_grid = np.linspace(df_f['dte'].min(), df_f['dte'].max(), GRID_N)\ny_grid = np.linspace(df_f['strike'].min(), df_f['strike'].max(), GRID_N)\nxx, yy = np.meshgrid(x_grid, y_grid)\n\ndef surface_for(exchange):\n    sub = df_f[df_f['exchange'] == exchange]\n    pts = sub[['dte', 'strike']].to_numpy()\n    vals = sub['iv'].to_numpy()\n    z = griddata(pts, vals, (xx, yy), method='linear')\n    # 'linear' is NaN outside each venue's convex hull; a 'nearest' pass\n    # fills those edges so the surface is plottable across the whole grid.\n    z_fill = griddata(pts, vals, (xx, yy), method='nearest')\n    return np.where(np.isnan(z), z_fill, z)",
   "id": "54cddd576db9aba7",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Same colour scale across all four panels — eyeballing skew/term-structure differences is then a one-glance comparison."
   ],
   "id": "1e618fe694535edd"
  },
  {
   "cell_type": "code",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-05-19T09:13:05.830483708Z",
     "start_time": "2026-05-19T09:13:03.289500057Z"
    }
   },
   "source": "exchanges = sorted(df_f['exchange'].unique())\nn = len(exchanges)\ncols = 2 if n > 1 else 1\nrows = (n + cols - 1) // cols\n\nfig = make_subplots(\n    rows=rows, cols=cols,\n    specs=[[{'type': 'surface'}] * cols for _ in range(rows)],\n    subplot_titles=[e.title() for e in exchanges],\n    horizontal_spacing=0.05, vertical_spacing=0.08,\n)\niv_min, iv_max = df_f['iv'].min(), df_f['iv'].max()\nfor i, ex in enumerate(exchanges):\n    fig.add_trace(\n        go.Surface(x=xx, y=yy, z=surface_for(ex), colorscale='Viridis',\n                   cmin=iv_min, cmax=iv_max, showscale=(i == 0),\n                   colorbar=dict(title='Mark IV (%)') if i == 0 else None),\n        row=(i // cols) + 1, col=(i % cols) + 1,\n    )\nscene_layout = dict(xaxis_title='DTE (days)', yaxis_title='Strike (USD)', zaxis_title='Mark IV (%)')\nfig.update_layout(\n    title=f'BTC vol surface — latest 5-min snapshot, {n} exchanges',\n    height=450 * rows,\n    **{f'scene{i+1 if i else \"\"}': scene_layout for i in range(n)},\n)\nfig.show()",
   "id": "b9d37efc4530e697",
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "22316701",
   "source": "## ATM term structure across venues\n\nThe surfaces show the whole sheet at once; the term structure isolates one slice — at-the-money IV against time to expiry. For each `(exchange, expiry)` we keep the strike closest to spot, then draw one line per venue. An upward slope is contango (longer-dated vol richer); a downward slope is backwardation, the classic near-term event/stress signature.",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "id": "1ac131b9",
   "source": "atm = (df.sort_values('moneyness')\n         .drop_duplicates(['exchange', 'dte'])\n         .sort_values(['exchange', 'dte']))\n\nfig = go.Figure()\nfor ex in sorted(atm['exchange'].unique()):\n    s = atm[atm['exchange'] == ex]\n    fig.add_trace(go.Scatter(x=s['dte'], y=s['iv'],\n                             mode='lines+markers', name=ex.title()))\nfig.update_layout(\n    title='BTC ATM term structure — IV at the strike nearest spot',\n    xaxis_title='DTE (days)', yaxis_title='ATM mark IV (%)',\n    height=480, legend_title='Exchange',\n)\nfig.show()",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-05-19T09:13:06.344066080Z",
     "start_time": "2026-05-19T09:13:06.004187766Z"
    }
   },
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "id": "afbf6d70",
   "source": "## Volatility smile at a single expiry\n\nFixing the expiry and sweeping the strike gives the smile/skew. We pick, per venue, the listed expiry closest to 30 DTE (they land on the same date here), crop to the same strike band as the surface, and overlay the four. Steeper wings mean richer tail pricing; a vertical gap between venues at the same strike is cross-venue dispersion — a candidate arbitrage once funding, basis and leg-out cost are netted out.",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "id": "10e77978",
   "source": "TARGET_DTE = 30\nchosen = (df.assign(gap=(df['dte'] - TARGET_DTE).abs())\n            .sort_values('gap')\n            .drop_duplicates('exchange')[['exchange', 'dte']])\nsmile = (df.merge(chosen, on=['exchange', 'dte'])\n           .query('strike >= @STRIKE_MIN and strike <= @STRIKE_MAX')\n           .sort_values('strike'))\n\nfig = go.Figure()\nfor ex in sorted(smile['exchange'].unique()):\n    s = smile[smile['exchange'] == ex]\n    fig.add_trace(go.Scatter(x=s['strike'], y=s['iv'], mode='lines+markers',\n                             name=f\"{ex.title()} ({int(s['dte'].iloc[0])}d)\"))\nfig.add_vline(x=df['spot'].median(), line_dash='dash', line_color='gray',\n              annotation_text='~spot')\nfig.update_layout(\n    title=f'BTC volatility smile — expiry nearest {TARGET_DTE} DTE',\n    xaxis_title='Strike (USD)', yaxis_title='Mark IV (%)',\n    height=480, legend_title='Exchange',\n)\nfig.show()",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2026-05-19T09:13:06.516754214Z",
     "start_time": "2026-05-19T09:13:06.361147173Z"
    }
   },
   "outputs": [],
   "execution_count": null
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}