What your data can and cannot tell you about execution

panel = cu.make_prediction_markets(n_markets=60, steps=32, seed=11)
for name, table in panel.items():
    db.create_table(name, table.schema, time_column="ts_init")
    db.append(name, table, note="panel load")
db.snapshot("panel-v1", tables=list(panel), note="execution study input")
print(f"{panel['book_deltas'].num_rows:,} book rows across {panel['resolutions'].num_rows} markets")
panel["book_deltas"].to_pandas().head()

expiry_ns = int(db.sql("SELECT max(expiration_ns) AS e FROM instruments").to_pandas()["e"][0])
tops = db.sql(
    f"""
    SELECT ts_init AS ts, instrument_id,
           max(CASE WHEN side = 'buy'  THEN price END) AS bid,
           max(CASE WHEN side = 'sell' THEN price END) AS ask,
           max(CASE WHEN side = 'buy'  THEN size  END) AS bid_size,
           max(CASE WHEN side = 'sell' THEN size  END) AS ask_size
    FROM h5i('book_deltas', 'panel-v1')
    WHERE outcome = 0 AND ts_init <= to_timestamp_nanos({expiry_ns})
    GROUP BY ts_init, instrument_id
    ORDER BY ts_init, instrument_id
    """
).to_pandas()
tops["mid"] = (tops.bid + tops.ask) / 2
tops["microprice"] = (tops.bid * tops.ask_size + tops.ask * tops.bid_size) / (
    tops.bid_size + tops.ask_size
)
tops["imbalance"] = (tops.bid_size - tops.ask_size) / (tops.bid_size + tops.ask_size)
tops["lean_bp"] = (tops.microprice - tops.mid) * 10_000
tops["spread_bp"] = (tops.ask - tops.bid) * 10_000
print(f"{len(tops):,} snapshots")
print(tops[["bid", "ask", "mid", "microprice", "imbalance", "lean_bp", "spread_bp"]].describe().round(3).to_string())

tops["level"] = tops.mid.round(1).clip(0.1, 0.9)
by_level = tops.groupby("level").agg(
    snapshots=("mid", "size"),
    spread_bp=("spread_bp", "mean"),
    abs_lean_bp=("lean_bp", lambda s: s.abs().mean()),
    depth=("bid_size", "mean"),
)
print(by_level.round(1).to_string())

submit_at = sorted({value.as_py() for value in panel["book_deltas"].column("ts_init")})[8]
signals = backtest.signal_table(
    [
        {
            "ts": submit_at + dt.timedelta(microseconds=1),
            "instrument_id": "EVENT-0030",
            "outcome": 0,
            "side": "buy",
            "quantity": 25.0,
            "tag": "probe",
        }
    ]
)
backtest.create_signal_table(db, "signals")
db.append("signals", signals)


def config(run_id: str, **execution) -> backtest.BacktestConfig:
    return backtest.BacktestConfig(
        run_id=run_id,
        data=backtest.DataConfig(signals="signals", snapshot="panel-v1"),
        portfolio=backtest.PortfolioConfig(starting_cash=100_000.0),
        execution=backtest.ExecutionConfig(fee_kind="kalshi", fee_rate=FEE_RATE, **execution),
        metadata={"study": "execution-fidelity"},
    )


queue_claim = backtest.inspect(db, config("queue-probe", queue_position=True))
print(f"ok: {queue_claim.ok}")
print(f"fidelity: {queue_claim.fidelity}")
for issue in queue_claim.errors:
    print(f"  error   {issue.code}: {issue.message}")
for issue in queue_claim.warnings:
    print(f"  warning {issue.code}: {issue.message}")

try:
    backtest.execute(db, config("queue-probe", queue_position=True))
except ValueError as error:
    print(f"execute refused: {error}")

accepted = backtest.inspect(db, config("baseline"))
print(f"ok: {accepted.ok}  fidelity: {accepted.fidelity}")
for issue in accepted.warnings:
    print(f"  warning {issue.code}: {issue.message}")
baseline = backtest.execute(db, config("baseline"))
print(f"\nfilled at: {baseline.fills.to_pandas().price.iloc[0]:.4f}")
print(f"explain:   {baseline.explain()['fidelity']}, {baseline.explain()['status_counts']}")

target = tops[(tops.ts == submit_at) & (tops.instrument_id == "EVENT-0030")].iloc[0]
print(f"EVENT-0030 at {submit_at}: ask {target.ask:.3f} for {target.ask_size:.1f} contracts")

rows = []
for quantity in (10.0, 25.0, 100.0, 250.0, 600.0):
    name = f"size-{int(quantity)}"
    table = backtest.signal_table(
        [
            {
                "ts": submit_at + dt.timedelta(microseconds=1),
                "instrument_id": "EVENT-0030",
                "outcome": 0,
                "side": "buy",
                "quantity": quantity,
                "tag": name,
            }
        ]
    )
    db.create_table(f"signals_{name}", table.schema, time_column="ts")
    db.append(f"signals_{name}", table)
    result = backtest.execute(
        db,
        backtest.BacktestConfig(
            run_id=name,
            data=backtest.DataConfig(signals=f"signals_{name}", snapshot="panel-v1"),
            portfolio=backtest.PortfolioConfig(starting_cash=1_000_000.0),
            execution=backtest.ExecutionConfig(fee_kind="kalshi", fee_rate=FEE_RATE),
        ),
    )
    fills = result.fills.to_pandas()
    orders = result.orders.to_pandas()
    rows.append(
        {
            "requested": quantity,
            "filled": float(orders.filled.sum()),
            "status": orders.status.iloc[0],
            "avg_price": float((fills.price * fills.quantity).sum() / fills.quantity.sum())
            if len(fills)
            else float("nan"),
            "sweeps": result.explain()["orders_sweeping_multiple_levels"],
        }
    )
depth = pd.DataFrame(rows)
depth["fill_ratio"] = depth.filled / depth.requested
print(depth.round(3).to_string(index=False))

try:
    backtest.ExecutionConfig(queue_position=True, slippage_ticks=1)
except ValueError as error:
    print(f"rejected at construction: {error}")

scenarios = []
for ticks in (0, 2, 5, 10):
    name = f"slip-{ticks}"
    result = backtest.execute(
        db,
        backtest.BacktestConfig(
            run_id=name,
            data=backtest.DataConfig(signals="signals", snapshot="panel-v1"),
            portfolio=backtest.PortfolioConfig(starting_cash=100_000.0),
            execution=backtest.ExecutionConfig(
                fee_kind="kalshi", fee_rate=FEE_RATE, slippage_ticks=ticks or None
            ),
        ),
    )
    fills = result.fills.to_pandas()
    scenarios.append(
        {
            "slippage_ticks": ticks,
            "fill_price": float(fills.price.iloc[0]),
            "cost_bp_vs_ask": (float(fills.price.iloc[0]) - target.ask) * 10_000,
            "fees": float(result.summary()["commissions"]),
        }
    )
print(pd.DataFrame(scenarios).round(4).to_string(index=False))

half_spread_bp = (target.ask - target.mid) * 10_000
fee_bp = FEE_RATE * target.ask * (1 - target.ask) * 10_000
slip_bp = scenarios[1]["cost_bp_vs_ask"]
budget = pd.DataFrame(
    [
        {"component": "half spread (mid to ask)", "cost_bp": half_spread_bp},
        {"component": "slippage, 2 ticks", "cost_bp": slip_bp},
        {"component": "fee at this price level", "cost_bp": fee_bp},
    ]
)
budget.loc[len(budget)] = {"component": "total", "cost_bp": budget.cost_bp.sum()}
print(budget.round(1).to_string(index=False))

fig, axes = plt.subplots(1, 2, figsize=(11, 4))
axes[0].plot(by_level.index, by_level.spread_bp, marker="o", color="#2c7fb8", label="spread")
axes[0].plot(
    by_level.index,
    [FEE_RATE * p * (1 - p) * 10_000 for p in by_level.index],
    marker="s",
    color="#c0392b",
    label="fee",
)
axes[0].set_title("Cost components by price level")
axes[0].set_xlabel("YES mid")
axes[0].set_ylabel("basis points of probability")
axes[0].legend(fontsize=8)
axes[1].bar(depth.requested.astype(str), depth.fill_ratio, color="#2c7fb8")
axes[1].axhline(1.0, color="black", lw=0.8)
axes[1].set_title("Fill ratio against one level of displayed depth")
axes[1].set_xlabel("contracts requested")
axes[1].set_ylabel("fraction filled")
fig.tight_layout()