Calculating Implied Volatility Surfaces Using Sql

Implied volatility (IV) is one of the most information-dense signals in options markets. Traders see it, quants model it, risk managers stress it, and data teams spend a disproportionate amount of time explaining why two IV numbers don’t match.

Most tutorials jump straight to Python, NumPy, or specialized quant libraries. In real financial systems, however, the volatility surface is born, validated, reconciled, and monitored in SQL long before it ever reaches a pricing model.

This article walks through how to construct an implied volatility surface using SQL, focusing on real production concerns rather than academic shortcuts.

1. What an Implied Volatility Surface Really Is

An implied volatility surface is a function:

IV = f(Strike, Expiration)

For a given underlying and valuation date, each listed option contract contributes a single point on that surface.

In practice, the surface is sparse, noisy, and discontinuous. SQL is the first line of defense against those problems.

2. A Realistic Options Table

options_quotes (
    underlying_symbol   VARCHAR,
    trade_date          DATE,
    expiration_date     DATE,
    strike              NUMERIC(10,4),
    call_put            CHAR(1),
    implied_volatility  NUMERIC(10,6),
    delta               NUMERIC(10,6),
    best_bid            NUMERIC(10,4),
    best_offer          NUMERIC(10,4),
    volume              INTEGER,
    open_interest       INTEGER
)

This structure hides real-world complexity: vendor models, delta conventions, and liquidity noise.

3. Defining the Surface Domain

WHERE underlying_symbol = 'AAPL'
  AND trade_date = DATE '2025-01-29'

Every surface is anchored to a single underlying and valuation date.

4. Cleaning the Input Data

4.1 Remove Invalid IVs

WHERE implied_volatility > 0

4.2 Enforce Tradability

AND best_bid > 0
AND best_offer > best_bid

4.3 Liquidity Filters

AND open_interest >= 10

5. Computing Time to Expiry

SELECT
    expiration_date,
    EXTRACT(DAY FROM expiration_date - trade_date) / 365.0 AS time_to_expiry
FROM options_quotes;

6. Identifying At-The-Money Options

WITH ranked AS (
    SELECT
        expiration_date,
        strike,
        implied_volatility,
        ABS(ABS(delta) - 0.5) AS delta_distance,
        ROW_NUMBER() OVER (
            PARTITION BY expiration_date
            ORDER BY ABS(ABS(delta) - 0.5)
        ) AS rn
    FROM options_quotes
)
SELECT *
FROM ranked
WHERE rn = 1;

7. Building the Strike–Expiry Grid

SELECT
    expiration_date,
    strike,
    implied_volatility
FROM options_quotes
ORDER BY expiration_date, strike;

8. Calculating Volatility Skew

SELECT
    expiration_date,
    strike,
    implied_volatility,
    implied_volatility
      - LAG(implied_volatility)
        OVER (PARTITION BY expiration_date ORDER BY strike)
      AS iv_slope
FROM options_quotes;

9. Term Structure of Volatility

SELECT
    time_to_expiry,
    AVG(implied_volatility) AS avg_iv
FROM options_quotes
GROUP BY time_to_expiry
ORDER BY time_to_expiry;

10. Surface Consistency Checks

Calendar Arbitrage

SELECT
    strike,
    expiration_date,
    implied_volatility,
    LEAD(implied_volatility)
      OVER (PARTITION BY strike ORDER BY expiration_date) AS next_iv
FROM options_quotes
WHERE implied_volatility > next_iv;

11. Why Two Vendor IV Surfaces Never Match

Two reputable vendors can publish different implied volatility surfaces and both be internally correct.

Forward Price Differences

SELECT
    vendor,
    expiration_date,
    AVG(forward_price) AS fwd
FROM options_quotes
GROUP BY vendor, expiration_date;

Delta Convention Mismatch

SELECT
    vendor,
    expiration_date,
    strike,
    delta
FROM options_quotes
WHERE ABS(delta) BETWEEN 0.24 AND 0.26
ORDER BY vendor;

Bid–Ask Treatment

SELECT
    vendor,
    AVG(implied_volatility) AS avg_iv,
    AVG(best_offer - best_bid) AS avg_spread
FROM options_quotes
GROUP BY vendor;

Model Dominated Expiries

SELECT
    expiration_date,
    COUNT(*) AS num_points
FROM options_quotes
GROUP BY expiration_date
HAVING COUNT(*) < 5;

  • THE LATEST