Calculations › Market Overview
What is the volatility of the stock market benchmark (last 30 days)?
The query
-- KPI market-wide: volatility of the stock market benchmark (stddev of avg daily return, 30d)
WITH daily_avg AS (
SELECT to_timestamp(tp.datetime)::date AS date, AVG(tp.price) AS avg_price
FROM ticketprice tp JOIN ticketname tn ON tn.id = tp.ticketnameid
WHERE tn.tickettype = 'stock'
AND tp.datetime >= EXTRACT(EPOCH FROM (NOW() - INTERVAL '30 days'))::bigint
GROUP BY 1
),
rets AS (
SELECT date, (avg_price - LAG(avg_price) OVER (ORDER BY date)) / NULLIF(LAG(avg_price) OVER (ORDER BY date),0) * 100 AS ret
FROM daily_avg
)
SELECT STDDEV(ret) AS market_volatility
FROM rets WHERE ret IS NOT NULL;
Runs against Cryptobi's market tables — ticketname (symbols),
ticketprice (daily prices and volume) and symbolmetadata
(sector, exchange, market cap and other reference fields).
Current output
| Market volatility |
|---|
| 3.97 |
Live output, first 1 rows, refreshed periodically. Model estimates are trend projections from recent prices, not financial advice.
Run this on your own holdings, change it, or write your own in SQL or Python — then put it on a dashboard or have it emailed on a schedule. Free, no credit card.
Sign Up Free