scripts/exchange_flow_study.py

"""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                                          # noqa: E402
from whale_event_study import PLACEBO, SEED, draw, event_study, first_in_burst, lead_lag  # noqa: E402

ROOT = Path(__file__).resolve().parents[1]
FLOWS = ROOT / "runtime" / "cache" / "exchange_flows.csv"
GROUPS = {   # name: (direction, min BTC, max BTC)
    "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)]
    # Leaving: what a known exchange sent that did not land on a known exchange address.
    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()