| """Exchange inflows and outflows and Bitcoin: the labelled version of the whale test. |
| |
| The whale study (fig15) could not tell a sale from an exchange shuffling its |
| own coins. Here every transaction touching 22 publicly labelled exchange |
| wallets (outputs/onchain/exchange_addresses.csv: Binance, OKX, Bitfinex, |
| Robinhood, Crypto.com, Coincheck, Bitbank, Gate, BitMEX, Huobi) is split by |
| the sending side: |
| |
| inflow BTC received by an exchange from addresses not known to belong to |
| that exchange: coins arriving, possibly to be sold |
| outflow BTC leaving a known exchange address for anywhere else |
| internal an exchange paying itself; dropped |
| |
| The wallets are mostly cold storage, so "inflow" still includes an exchange |
| topping up cold storage from its own unlabelled hot wallets. That noise only |
| dilutes a real effect; it cannot create one. |
| |
| Data: runtime/cache/exchange_flows.csv, written by the Colab cell |
| notebooks/colab_exchange_flows_cell.py from Google's public BigQuery dataset. |
| Method: whale_event_study.event_study and lead_lag, unchanged. |
| |
| Usage: |
| python scripts/exchange_flow_study.py |
| """ |
| from __future__ import annotations |
|
|
| import json |
| import sys |
| from pathlib import Path |
|
|
| import numpy as np |
| import pandas as pd |
|
|
| sys.path.insert(0, str(Path(__file__).resolve().parent)) |
| from trump_post_event_study import bars |
| from whale_event_study import PLACEBO, SEED, draw, event_study, first_in_burst, lead_lag |
|
|
| ROOT = Path(__file__).resolve().parents[1] |
| FLOWS = ROOT / "runtime" / "cache" / "exchange_flows.csv" |
| GROUPS = { |
| "Inflow 100 - 1,000 BTC": ("inflow", 100, 1_000), |
| "Inflow 1,000 BTC or more": ("inflow", 1_000, np.inf), |
| "Outflow 100 - 1,000 BTC": ("outflow", 100, 1_000), |
| "Outflow 1,000 BTC or more": ("outflow", 1_000, np.inf), |
| } |
| COLORS = {"Inflow 100 - 1,000 BTC": "#f2a37f", "Inflow 1,000 BTC or more": "#c24a12", |
| "Outflow 100 - 1,000 BTC": "#8fb8ea", "Outflow 1,000 BTC or more": "#0b3d91", |
| PLACEBO: "#8b8a85"} |
|
|
|
|
| def flows() -> pd.DataFrame: |
| if not FLOWS.exists(): |
| raise SystemExit(f"missing {FLOWS.relative_to(ROOT)}: run the Colab cell " |
| "notebooks/colab_exchange_flows_cell.py and copy its CSV here") |
| d = pd.read_csv(FLOWS, keep_default_na=False) |
| d["ts"] = pd.to_datetime(d["block_timestamp"], utc=True) |
|
|
| def received(s: str) -> dict: |
| return {k: float(v) for k, v in (x.rsplit(":", 1) for x in s.split(";"))} if s else {} |
|
|
| out = d["out_exchanges"].map(received) |
| senders = d["in_exchanges"].map(lambda s: set(s.split(";")) if s else set()) |
| d["inflow"] = [sum(v for k, v in o.items() if k not in s) for o, s in zip(out, senders)] |
| d["internal"] = [sum(v for k, v in o.items() if k in s) for o, s in zip(out, senders)] |
| |
| d["outflow"] = np.where(senders.map(len) > 0, |
| (d["total_btc"] - out.map(lambda o: sum(o.values()))).clip(lower=0), 0.0) |
| return d.drop_duplicates("hash").sort_values("ts").reset_index(drop=True) |
|
|
|
|
| def main() -> None: |
| sys.stdout.reconfigure(encoding="utf-8") |
| rng = np.random.default_rng(SEED) |
| d = flows() |
| close = bars() |
| start = max(close.index.min(), d["ts"].min()) + pd.Timedelta(days=2) |
| end = min(close.index.max(), d["ts"].max()) - pd.Timedelta(days=22) |
| print(f"{len(d):,} transactions: inflow {d['inflow'].sum():,.0f} BTC, outflow " |
| f"{d['outflow'].sum():,.0f} BTC, internal {d['internal'].sum():,.0f} BTC (dropped)") |
|
|
| groups = [] |
| for name, (side, lo, hi) in GROUPS.items(): |
| hit = d[(d[side] >= lo) & (d[side] < hi)] |
| groups.append((name, first_in_burst(hit["ts"]), d.loc[d[side] >= 100, "ts"])) |
| results, curves, rel = event_study(close, groups, rng, start, end) |
| lead = lead_lag(close, {side: d.loc[d[side] > 0].set_index("ts")[side] |
| for side in ("inflow", "outflow")}, start, end) |
|
|
| (ROOT / "outputs" / "exchange_flow_study.json").write_text(json.dumps({ |
| "method": ("transactions touching 22 labelled exchange wallets (BigQuery crypto_bitcoin); " |
| "inflow = BTC received from addresses not labelled as the same exchange, outflow = " |
| "BTC a labelled exchange sent elsewhere; event study and lead-lag as " |
| "whale_event_study.py"), |
| "transactions": int(len(d)), "span": [str(d["ts"].min()), str(d["ts"].max())], |
| "btc_totals": {k: float(d[k].sum()) for k in ("inflow", "outflow", "internal")}, |
| "groups": results, "lead_lag_hourly": lead}, indent=2)) |
|
|
| fi = lead["inflow"]["hour_of_week_removed"]["flow_predicts_volatility"] |
| fo = lead["outflow"]["hour_of_week_removed"]["flow_predicts_volatility"] |
| draw(results, curves, rel, COLORS, "flow (events)", |
| "Exchange inflows and outflows on the Bitcoin blockchain and the BTC price", |
| "Transactions touching 22 publicly labelled exchange wallets (Binance, OKX, Bitfinex, Robinhood, " |
| "Crypto.com, …), timed by block; 5-minute BTC bars; exchange-to-itself moves dropped.\n" |
| f"Hourly lead-lag, weekly pattern removed: inflow → next hour's volatility t = {fi['t']:+.1f} " |
| f"(+{fi['r2_gain_pts']:.2f} pts R²); outflow → next hour's volatility t = {fo['t']:+.1f} " |
| f"(+{fo['r2_gain_pts']:.2f} pts R²).", |
| "fig16_exchange_flows.png") |
|
|
|
|
| if __name__ == "__main__": |
| main() |
|
|
