SAMERA::QUANT > SUBSCRIBE
← BACK TO RESEARCH_LOG 2026-11-17 · 8 MIN
DATA CORRELATION ratio r 0.92 ● FREE

Building a ±10% spot orderbook collector without a 24/7 WebSocket server

Public APIs do not serve historical spot orderbook depth. How a 5-minute Worker Cron aggregates ±10% bid/ask ratios into D1.

01 / Why public historical APIs do not have ±10% spot orderbooks

Exchange REST /depth endpoints only return a snapshot of the current second. Once a limit order is filled or canceled, that book state is gone from free public endpoints.

Binance Data Vision does publish bookDepth, but only for perpetual futures and only out to ±5%. To reconstruct multi-year ±10% spot limit-wall dynamics, you either pay institutional vendors (Tardis, Kaiko) or start collecting your own snapshots.

02 / Core formula: quantity ratio vs. USD net imbalance

Testing across spot venues shows that the normalized base-quantity ratio within ±10% of mid-price tracks structural support/resistance cleaner than raw USD notional alone:

ratio_10      = (B - A) / (B + A)          // B = bid qty within -10%, A = ask qty within +10%
orderbook_liq = 7.3 * ratio_10             // zero-centered scale (0 when bids == asks)
ob_net_10_usd = Bid_USD_10 - Ask_USD_10    // aggregated across OKX / Bybit / Coinbase spot

03 / Building the database without a 24/7 WebSocket server

For 1h–4h swing or intraday systems, you do not need tick-by-tick WebSocket ingestion. A 5-minute Cloudflare Worker Cron fetches public REST orderbooks in parallel, computes the ±10% totals on the fly, and stores one compact row into D1 — 288 snapshots per day at near-zero cost.

FieldCalculationRole in system
orderbook_liq±10% qty ratio × 7.3Spots orderbook skew in range-bound regimes
ob_net_10_usd±10% bid − ask USDTracks limit-wall additions & pulls
spot_cvd_1h1h taker buy − sell USDFilters fake walls during strong trends
CONNECTED FEED: BYBIT.V5 LAT: --ms UTC --:--:-- UTC+8 --:--:--