Volatility and Risk Metrics

Volatility measures how much a stock's price fluctuates. High volatility means large price swings — higher potential returns but also higher potential losses. Low volatility means stable, predictable price movement. Understanding volatility is fundamental to risk management, options pricing, and portfolio construction.

Investors and traders use volatility to:

• Compare risk across different stocks
• Size positions (invest less in volatile stocks)
• Price options (more volatile = more expensive options)
• Set stop-loss levels

What Is Annualised Volatility?

The standard volatility measure used in finance is the annualised standard deviation of daily returns. The formula:

Annualised Volatility = Standard Deviation(daily returns) × √252

Why multiply by √252? There are approximately 252 trading days in a year. To convert daily volatility to annual volatility, you multiply by the square root of the number of periods. This is because variances (not standard deviations) scale linearly with time — so STDDEV × √252 converts from daily to annual scale.

Step 1 — Rolling 20-Day Volatility

20-day (one month) rolling volatility is the most common short-term volatility measure. It captures recent risk without averaging over stale data from months ago.

First, we compute daily returns using LAG(). Then we calculate the standard deviation of those returns over a 20-day rolling window and annualise the result.

WITH daily_returns AS (
    SELECT
        company_id,
        price_date,
        close_price,
        (close_price - LAG(close_price) OVER (PARTITION BY company_id ORDER BY price_date))
        / NULLIF(LAG(close_price) OVER (PARTITION BY company_id ORDER BY price_date), 0) AS daily_return
    FROM stock_prices
),
rolling_vol AS (
    SELECT
        company_id,
        price_date,
        close_price,
        daily_return,
        -- Standard deviation of daily returns over 20 days, annualised
        ROUND((STDDEV(daily_return) OVER (
                PARTITION BY company_id
                ORDER BY price_date
                ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
            ) * SQRT(252) * 100)::NUMERIC, 2) AS annualized_vol_20d,
        ROUND((STDDEV(daily_return) OVER (
                PARTITION BY company_id
                ORDER BY price_date
                ROWS BETWEEN 59 PRECEDING AND CURRENT ROW
            ) * SQRT(252) * 100)::NUMERIC, 2) AS annualized_vol_60d
    FROM daily_returns
    WHERE daily_return IS NOT NULL
)
SELECT
    c.ticker, c.sector,
    rv.price_date,
    rv.close_price,
    rv.annualized_vol_20d,
    rv.annualized_vol_60d,
    -- When short-term vol > long-term vol, the stock is getting more volatile
    CASE WHEN rv.annualized_vol_20d > rv.annualized_vol_60d
         THEN 'Increasing volatility'
         ELSE 'Decreasing volatility' END AS vol_regime
FROM rolling_vol rv
JOIN companies c ON c.company_id = rv.company_id
WHERE rv.annualized_vol_20d IS NOT NULL
ORDER BY c.ticker, rv.price_date DESC;

What This Returns

annualized_vol_20d is the current 1-month rolling volatility. A value of 25 means the stock has 25% annualised volatility — roughly consistent with large-cap tech stocks. Small-cap or speculative stocks might show 60-80%+. The comparison between 20d and 60d volatility shows whether volatility is increasing or decreasing.