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.
| Field | Calculation | Role in system |
|---|---|---|
| orderbook_liq | ±10% qty ratio × 7.3 | Spots orderbook skew in range-bound regimes |
| ob_net_10_usd | ±10% bid − ask USD | Tracks limit-wall additions & pulls |
| spot_cvd_1h | 1h taker buy − sell USD | Filters fake walls during strong trends |