Categories: Computational Finance Derivatives Editors Choice EXCEL Examples Implied Volatility Options Pricing Risk volatility surface

2 mins read# Creating the implied volatility surface dataset for building

The downloaded implied volatility dataset from your volatility data sources generally includes the following information:

**Figure 1 Raw Implied Volatility dataset**

For a quarterly sample data snapshot on a daily basis it is not uncommon to end up with a few thousand rows. To build a volatility surface dataset we need a much smaller focus.

One option is to cut the dataset by Maturity (**expiry, T**) and Strikes (**K**) as shown below. Options are sorted using date and period filters and we get all combinations of implied volatilities by strikes for a given expiry date.

**Figure 2 – Volatility Surface data cut – option one**

A second possible cut is to focus on all possible implied volatility values by expiry dates for a given strike price. This is the approach that we will opt for in our model building exercise in Excel.

**Figure 3 Volatility surface data cut – option two**

We use the filter option in Excel to select a given date and then sort the data set by our maturity by strike combination. The end result on your sheet should look something like the following panel:

**Figure 4 Volatility surface data cut – option 2 revisited**

The date yields 9 maturity buckets which are then translated into yearly figures by dividing each of them by 360. The assumption here is that there are 360 days in a year.

**Figure 5 Volatility surface data cut – maturity buckets**

On 31 Jan 2014, there are 13 available strike prices for call options on NVIDIA.

**Figure 6 Volatility surface data cut – available strikes**

When we use the combination of maturity buckets and strike prices we get a grid of implied volatilities.

**Figure 7 Implied volatility blank grid for volatility surface**

Our final step is to fill in the grid based on implied volatility data from our data source. The final grid produced below becomes the starting point for our next lesson.

**Figure 8 Volatility Surface – Implied volatilities from source data**

This is raw implied volatility data provided by our data provider. If we plot a surface with these volatilities, it will be relatively flat as seen below:

**Figure 9 Sample Raw Implied volatility surface before calculation of local volatilities**

Published by

May 23, 2014 1:07 pm

What is the best way of evaluating portfolio performance allocation strategies? Should we just compare risk, return or risk adjusted…

February 5, 2019 12:51 pm

6 mins read Introducing Project Plain speak. Currently a work in progress Plain speak focuses on bringing intelligent financial reporting…

January 22, 2019 6:08 am

9 mins read What can we learn about oil markets from the last ten years? The next decade. The final…

January 19, 2019 3:47 pm

5 mins read What does the data say about future direction of crude oil markets. We look at OPEC spare…

January 19, 2019 12:44 pm

5 mins read What factors would impact crude oil supply side equation in 2019? Russian non compliance, lower breakeven for…

January 19, 2019 5:14 am

5 mins read How would you model demand for crude oil? What are the key components? What is the long…

January 18, 2019 4:46 pm

This website uses cookies.