Measure quoted spreads with DuckDB

pip install requests duckdb
export VIKE_API_KEY=...
python pm-spread-duckdb.py             # real run: needs the key, writes polymarket-btc-5m-l1-<date>.parquet
python pm-spread-duckdb.py --dry-run   # prints the requests it would make; needs neither duckdb nor a key

"""Quoted-spread statistics for BTC 5-minute Polymarket markets from one downloaded day of l1_quotes, read with DuckDB.

Run:  VIKE_API_KEY=... python pm-spread-duckdb.py    (--dry-run lists the requests and makes none)
Needs `pip install duckdb` for the real run; --dry-run does not.
"""
import os
import sys
from urllib.parse import urlencode

NAME = "pm-spread-duckdb"
BASE = "https://data.vike.io"
DRY = "--dry-run" in sys.argv


def fail(why):
    sys.exit(f"{NAME}: {why}")


def call(path, method="GET", **params):
    url = BASE + path + ("?" + urlencode(params) if params else "")
    if DRY:
        print(method, url)
        return None
    key = os.environ.get("VIKE_API_KEY") or fail("set VIKE_API_KEY")
    import requests

    r = requests.request(method, url, headers={"X-API-Key": key}, timeout=60)
    if r.status_code != 200:
        fail(f"{method} {path} answered HTTP {r.status_code}: {r.text[:200]}")
    return r.json()


def download(url, path):
    """The whole file in one request, streamed to disk. DuckDB then reads the LOCAL copy, never the URL."""
    if DRY:
        print("GET", url)
        return
    import requests
    from urllib.parse import urlparse

    # The key must only ever go to our own host, whatever URL the API handed back.
    if urlparse(url).hostname != "data.vike.io":
        fail(f"refusing to send the API key to {url}")
    with requests.get(url, headers={"X-API-Key": os.environ["VIKE_API_KEY"]}, stream=True, timeout=300) as r:
        if r.status_code != 200:
            fail(f"GET {url} answered HTTP {r.status_code}")
        with open(path, "wb") as f:
            for chunk in r.iter_content(chunk_size=1 << 20):
                f.write(chunk)


def main():
    # The family layout: BTC 5-minute markets only, a much smaller file than the flat day of l1_quotes.
    family = dict(asset="btc", tenor="5m", stream="l1_quotes")
    index = call("/v1/polymarket/archive/datasets", limit=1000, **family)
    day = "2026-09-01"  # --dry-run has no response to read
    if not DRY:
        if not index["datasets"]:
            fail("the archive index lists no btc/5m l1_quotes file")
        day = max(d["date"] for d in index["datasets"])

    meta = call("/v1/polymarket/archive/url", date=day, **family)
    url = f"{BASE}/archive/venue=polymarket/asset=btc/tenor=5m/date={day}/l1_quotes.parquet"  # --dry-run: the shape /archive/url returns
    if not DRY:
        url = meta["url"]
    path = f"polymarket-btc-5m-l1-{day}.parquet"
    download(url, path)
    if DRY:
        return
    if os.path.getsize(path) != meta["bytes"]:
        fail(f"{path} is {os.path.getsize(path)} bytes, the archive says {meta['bytes']} (truncated download?)")

    import duckdb

    # bid/ask are Decimal(9,4); cast so the arithmetic is plain floats. One row per top-of-book CHANGE, so these
    # are statistics over quote updates, not time-weighted spreads.
    rows = duckdb.execute(
        """
        SELECT token_id, count(*) AS quotes,
               median(CAST(ask AS DOUBLE) - CAST(bid AS DOUBLE)) AS median_spread,
               quantile_cont(CAST(ask AS DOUBLE) - CAST(bid AS DOUBLE), 0.9) AS p90_spread
        FROM read_parquet(?)
        WHERE bid > 0 AND ask > 0 AND ask >= bid
        GROUP BY token_id ORDER BY quotes DESC LIMIT 5
        """,
        [path],
    ).fetchall()
    if not rows:
        fail(f"{path} holds no usable quotes")
    print(f"{day}, BTC 5m, five most-quoted tokens")
    for token_id, quotes, median, p90 in rows:
        print(f"  {token_id[:12]}...  {quotes:>7} quotes  median spread {median:.4f}  p90 {p90:.4f}")


if __name__ == "__main__":
    try:
        main()
    except Exception as e:  # a shape the recipe does not expect fails by name, not by traceback
        fail(f"{type(e).__name__}: {e}")