How to Form the CBOE VIX by R Based on Excel Data

Question

knitr::opts_chunk$set(echo = TRUE)

Objectives

The goal of this case study is to learn how volatility indices are computed and how volatility indices differ as an underlying when pricing derivatives to common stocks or stock-indices.

library(data.table)
library(ggplot2)
library(vctrs)

sp500_options <- fread("sp500_options_2010.csv")

sp500_options[, MID_PRICE := (ASK_PRICE + BID_PRICE)/2]
sp500_options[, STRIKEPRICE := STRIKEPRICE / 1000]

sp500_options[order(STRIKEPRICE), lag_STRIKEPRICE := shift(STRIKEPRICE, n =1, type = "lag"), by = .(UNDERLYING, Time, MATURITYDATE, OPTIONTYPE)]
sp500_options[order(STRIKEPRICE), lead_STRIKEPRICE := shift(STRIKEPRICE, n =1, type = "lead"), by = .(UNDERLYING, Time, MATURITYDATE, OPTIONTYPE)]

# Example
example_Time <- "4-Jan-10"
example_MATURITYDATE <- 20100220

Step 1: Select the options to be used in the VIX calculation

Calculate days to expiry

sp500_options[, DATE_START := as.Date(Time, "%d-%b-%y")]
sp500_options[, DATE_END   := as.Date(as.character(MATURITYDATE), "%Y%m%d")]

sp500_options[, DAYS_TO_EXPIRY := DATE_END - DATE_START]

Including Plots You can also embed plots, for example:

data <- sp500_options[MATURITYDATE == example_MATURITYDATE & Time == example_Time]

plot <- ggplot(data, aes(x=STRIKEPRICE, y=MID_PRICE)) + geom_point(aes(color=OPTIONTYPE)) 

plot

We now need to estimate the Forward.

data_long <- sp500_options[,c("UNDERLYING", "Time", "MATURITYDATE", "STRIKEPRICE", "MID_PRICE", "OPTIONTYPE")]

data_wide <- dcast(data_long, UNDERLYING + Time + MATURITYDATE + STRIKEPRICE ~ OPTIONTYPE , value.var="MID_PRICE")


data_wide[, FORWARD := .SD[which.min(abs(C-P)),STRIKEPRICE], by = .(UNDERLYING, Time, MATURITYDATE)]
data_wide <- data_wide[FORWARD == STRIKEPRICE, ]
data_wide[, FORWARD := FORWARD + (C - P)]

sp500_options <- merge(sp500_options, data_wide[, c("UNDERLYING", "Time", "MATURITYDATE", "FORWARD")], by = c("UNDERLYING", "Time", "MATURITYDATE"))


sp500_options[STRIKEPRICE < FORWARD, K_zero := max(STRIKEPRICE), by = .(UNDERLYING, Time, MATURITYDATE)]

sp500_options[, K_zero := max(K_zero, na.rm=T), by = .(UNDERLYING, Time, MATURITYDATE)]

data <- sp500_options[MATURITYDATE == example_MATURITYDATE & Time == example_Time]

plot <- ggplot(data, aes(x=STRIKEPRICE, y=MID_PRICE)) + geom_point(aes(color=OPTIONTYPE)) + geom_vline(aes(xintercept = K_zero)) 

plot

We only use Out-of-the-money options....

sp500_options <- sp500_options[(OPTIONTYPE == "P" & STRIKEPRICE <= K_zero) |(OPTIONTYPE == "C" & STRIKEPRICE >= K_zero) ]

data <- sp500_options[MATURITYDATE == example_MATURITYDATE & Time == example_Time]

plot <- ggplot(data, aes(x=STRIKEPRICE, y=MID_PRICE)) + geom_point(aes(color=OPTIONTYPE)) + geom_vline(aes(xintercept = K_zero)) 

plot

Step 2: Find double zero bid prices

We only use options till first double zero bid prices

sp500_options[order(STRIKEPRICE), lag_BID_PRICE := shift(BID_PRICE, n =1, type = "lag"), by = .(UNDERLYING, Time, MATURITYDATE, OPTIONTYPE)]

sp500_options[lag_BID_PRICE == 0 & BID_PRICE == 0, min_DOUBLE_ZERO_BID_PRICE := min(STRIKEPRICE), by = .(UNDERLYING, Time, MATURITYDATE, OPTIONTYPE)]

sp500_options[lag_BID_PRICE == 0 & BID_PRICE == 0, max_DOUBLE_ZERO_BID_PRICE := max(STRIKEPRICE), by = .(UNDERLYING, Time, MATURITYDATE, OPTIONTYPE)]


# replace NA's
sp500_options[, min_DOUBLE_ZERO_BID_PRICE := max(new_vctr(min_DOUBLE_ZERO_BID_PRICE), na.rm=T), by = .(UNDERLYING, Time, MATURITYDATE, OPTIONTYPE)]

sp500_options[, max_DOUBLE_ZERO_BID_PRICE := max(new_vctr(max_DOUBLE_ZERO_BID_PRICE), na.rm=T), by = .(UNDERLYING, Time, MATURITYDATE, OPTIONTYPE)]

sp500_options[min_DOUBLE_ZERO_BID_PRICE < 0, min_DOUBLE_ZERO_BID_PRICE := -1 * min_DOUBLE_ZERO_BID_PRICE]

sp500_options <- sp500_options[(OPTIONTYPE == "C" & STRIKEPRICE < min_DOUBLE_ZERO_BID_PRICE) | (OPTIONTYPE == "P" & STRIKEPRICE > max_DOUBLE_ZERO_BID_PRICE) ]

sp500_options <- sp500_options[BID_PRICE > 0]

data <- sp500_options[MATURITYDATE == example_MATURITYDATE & Time == example_Time]

plot <- ggplot(data, aes(x=STRIKEPRICE, y=MID_PRICE)) + geom_point(aes(color=OPTIONTYPE)) + geom_vline(aes(xintercept = K_zero))

plot

# add missing terms to sum..
vix <- sp500_options[! is.na(lead_STRIKEPRICE) & ! is.na(lag_STRIKEPRICE) , .(vix = sum( 1/STRIKEPRICE^2 * (lead_STRIKEPRICE - lag_STRIKEPRICE)/2 * MID_PRICE)),  by = .(UNDERLYING, Time, DAYS_TO_EXPIRY)]

Step 3: Calculate the 30-day weighted average

# interpolate Vix(x) with x days closest to 30 days, see CBOE document STEP 3:

As you can see above code left some steps. How can I finish this code?