> For the complete documentation index, see [llms.txt](https://docs.koinju.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.koinju.io/data/volatility-index.md). # Volatility index ## SQL API ### `public_data.volatility_index` Minute-resolution OHLC bars of the volatility index published by an exchange. Currently the primary source is **Deribit's DVOL** for `BTC` and `ETH` — a 30-day forward-looking implied-volatility index analogous to the VIX in traditional finance. #### Columns | Column | Type | Description | | ----------- | ---------------------- | --------------------------------------------- | | `exchange` | LowCardinality(String) | Exchange that publishes the index (`deribit`) | | `market` | LowCardinality(String) | Underlying asset (`BTC`, `ETH`) | | `timestamp` | DateTime64(9, 'UTC') | Bar timestamp | | `open` | Decimal(76, 20) | Open value of the index for the bar | | `high` | Decimal(76, 20) | High value | | `low` | Decimal(76, 20) | Low value | | `close` | Decimal(76, 20) | Close value | The index is quoted in **annualised volatility percentage points**: a value of `55.21` means a 55.21% annualised IV. ### Example Queries #### 1. Latest BTC and ETH DVOL ```sql SELECT timestamp, market, close FROM public_data.volatility_index WHERE exchange = 'deribit' AND market IN ('BTC', 'ETH') AND timestamp >= now() - INTERVAL 24 HOUR ORDER BY timestamp DESC, market ASC LIMIT 50 ``` #### 2. 1-hour resampled DVOL The raw bars are at 1-minute resolution. Aggregate to whatever interval you need: ```sql SELECT toStartOfHour(timestamp) AS hour, market, argMin(open, timestamp) AS open, max(high) AS high, min(low) AS low, argMax(close, timestamp) AS close FROM public_data.volatility_index WHERE exchange = 'deribit' AND market = 'BTC' AND timestamp >= now() - INTERVAL 7 DAY GROUP BY hour, market ORDER BY hour ASC ``` Functions used: [`toStartOfHour`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofhour), [`argMin`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmin), [`argMax`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmax). #### 3. Variance risk premium (DVOL forecast vs forward-realised) Compare the DVOL forecast on day `D` against the realised vol that actually unfolded over the next 30 days `(D, D+30]`. The difference is the **ex-post variance risk premium** — what a delta-hedged option seller pocketed (or paid) on a 30-day book opened at `D`. This is the textbook implied-vs-realised comparison and the basis for short-vol carry strategies. The trick is the forward window — `ROWS BETWEEN 1 FOLLOWING AND 30 FOLLOWING` flips the usual trailing rolling-stddev around so each row's realised value is computed from the 30 days **after** it. The result drops 30 days from the recent end (you can't measure the realised vs a forecast whose window hasn't elapsed yet). ```sql WITH dvol AS ( SELECT toStartOfDay(timestamp) AS day, avg(close) AS dvol_close FROM public_data.volatility_index WHERE exchange = 'deribit' AND market = 'BTC' AND timestamp >= toDate(now()) - INTERVAL 13 MONTH AND timestamp < toDate(now()) - INTERVAL 30 DAY GROUP BY day ), returns AS ( SELECT toStartOfDay(start) AS day, toFloat64(close) AS close FROM api.ohlcv(candle_duration_in_minutes = 1440) WHERE exchange = 'binance' AND market = 'BTC-USDT' AND start >= toDate(now()) - INTERVAL 13 MONTH - INTERVAL 1 DAY ), log_returns AS ( SELECT day, log(close / lagInFrame(close, 1) OVER (ORDER BY day)) AS r FROM returns ), forward_rv AS ( SELECT day, 100 * sqrt(365) * stddevSamp(r) OVER ( ORDER BY day ROWS BETWEEN 1 FOLLOWING AND 30 FOLLOWING ) AS rv_next_30d FROM log_returns ) SELECT d.day, d.dvol_close AS dvol_forecast, f.rv_next_30d AS realised_next_30d, d.dvol_close - f.rv_next_30d AS premium FROM dvol d JOIN forward_rv f USING (day) WHERE f.rv_next_30d IS NOT NULL ORDER BY d.day DESC ``` Functions used: [`toStartOfDay`](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#tostartofday), [`toDate`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#todate), [`toFloat64`](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#tofloat64), [`log`](https://clickhouse.com/docs/sql-reference/functions/math-functions#log), [`sqrt`](https://clickhouse.com/docs/sql-reference/functions/math-functions#sqrt), [`lagInFrame`](https://clickhouse.com/docs/sql-reference/window-functions#laginframe), [`stddevSamp`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevsamp). #### Output (rolling 365 days, BTC) A short summary across one year of forecasts: | metric | value | | -------------------------- | -------------------------- | | mean premium | +4.2 vol pts | | median premium | +9.3 vol pts | | days with negative premium | 93 / 365 (≈25%) | | largest positive premium | +24.1 vol pts (2025-12-07) | | largest negative premium | −45.1 vol pts (2026-01-28) | Two things to read from this: 1. **Mean is positive but small; the median is materially higher.** That's the variance risk premium signature — most days the forecast over-prices what unfolds, but a handful of tail months pull the mean down. Selling 30-day vol on a typical day is profitable; doing it indiscriminately is not. 2. **Tail events show up clearly.** Late January 2026 had realised vol over 80% while DVOL was sitting around 38% — the forecast missed by 40+ vol points. A delta-hedged short straddle opened on those days lost roughly that spread, scaled by vega and time. The kind of event short-vol books exist to survive (or fail to). #### Notes * The query uses **calendar days** (`stddevSamp` over 30 forward bars). For trading-day RV, replace the daily candles with 5-trading-day-week filters upstream. * The rolling 30-day stddev is sample (Bessel-corrected). Use [`stddevPop`](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/stddevpop) if you prefer the population estimator — the 1/29 vs 1/30 normalization difference is well under a basis point for our window sizes. * If you want the **forward annualised RV in basis-point premium terms** (instead of vol points), divide by `dvol_forecast` and multiply by 1e4. Useful for ranking days by relative misforecast magnitude. ## REST API ## Get volatility index OHLC > Returns OHLC (open / high / low / close) of an exchange's published\ > volatility index for a given underlying. Currently the primary\ > source is Deribit's DVOL (BTC and ETH).\ > \ > This endpoint is limited to 10000 records per request.
```json {"openapi":"3.1.1","info":{"title":"Koinju Market Data API","version":"1.0.0"},"servers":[{"url":"https://api.koinju.io","description":"Koinju API Server"}],"security":[{"apiKeyAuth":[]}],"components":{"securitySchemes":{"apiKeyAuth":{"type":"apiKey","in":"header","name":"x-api-key","description":"Required for /ohlcv and /trade endpoints.\nPass your API key in the x-api-key header.\nPublic /market/* endpoints do not require authentication.\n"}}},"paths":{"/volatility-index":{"get":{"summary":"Get volatility index OHLC","description":"Returns OHLC (open / high / low / close) of an exchange's published\nvolatility index for a given underlying. Currently the primary\nsource is Deribit's DVOL (BTC and ETH).\n\nThis endpoint is limited to 10000 records per request.\n","tags":["market_data"],"parameters":[{"name":"exchange","in":"query","required":true,"description":"Exchange that publishes the index (e.g. `deribit`).","schema":{"type":"string"}},{"name":"market","in":"query","required":true,"description":"Underlying asset (e.g. `BTC`, `ETH`).","schema":{"type":"string"}},{"name":"start_datetime","in":"query","required":true,"description":"Window start. Accepts any format parseable by `parseDateTime64BestEffort`\n— e.g. a plain date (`2026-03-01`, treated as midnight UTC) or a full\nISO 8601 timestamp (`2026-03-01T12:00:00Z`).\n","schema":{"type":"string","format":"date-time"}},{"name":"end_datetime","in":"query","required":true,"description":"Window end. Same format as `start_datetime`.","schema":{"type":"string","format":"date-time"}}],"responses":{"200":{"description":"OK","content":{"application/json":{"schema":{"type":"array","items":{"type":"object","properties":{"time":{"type":"string","format":"date-time","description":"Bar timestamp (UTC)"},"exchange":{"type":"string","description":"Exchange that publishes the index"},"market":{"type":"string","description":"Underlying asset"},"open":{"type":"number","format":"decimal","description":"Open value of the index for the bar"},"high":{"type":"number","format":"decimal","description":"High value"},"low":{"type":"number","format":"decimal","description":"Low value"},"close":{"type":"number","format":"decimal","description":"Close value"}}}}}}}}}}}} ```