# Object oriented VBA: a European option class using Black and Scholes pricing (Concrete example of user defined class)

How to create a VBA custom class that allows to price easily European options using Black and Scholes pricing formulas? The approach will be simplified so that as many people as possible can understand.

Object oriented VBA programming style is not an easy skill to acquire and even more so if you don’t practice. So the best advice once can possibly give you is practice, practice, practice! That being said, there is nothing like a set of examples to learn from your peers. Once you have understood the way object oriented VBA works, it can even be fun to write applications this way.

Today, I wanted to dig into slightly elaborate finance stuff, especially option pricing. I don’t want to scare non-finance people so I will focus on non-financial notions in the article piece. However for financial aficionados, the code pasted below features a fully functional Black & Scholes pricing method. Would be fun to add more pricing methods using Monte-Carlo instead, or non-flat volatility… Again don’t be scared if these terms are unknown to you, I will explain everything to understand the way the object works, so just focus on the programming aspect of it.

## The case study: the Option

An option is a financial product that often qualified of derivative, as it is a product that draws its value from another product called the underlying. More precisely, the option gives a right to purchase (call option) or sale (put option) another product at a pre-defined price (called the strike price or exercise price). If I buy a call option on an Apple stock expiring in 30 days with a strike price of USD180, it means I have the right (but not the obligation) to purchase an Apple stock at a predefined price of USD180 in 30 days. Saying that the option is “European” doesn’t means it is traded in Europe. Instead it is a style of option that you can only use on the day it expires, and not before (in this case it would be called an “American” option).

Typically, the option price depends on the time remaining to expiry, the strike price, the current price and volatility of the underlying stock, the forecasted dividend payments on the stock and the level of interest rates in the market.

Your boss just asked you to create a class that can represent a European option that contains all the data required to compute its price, given that the market parameters (the current price of the underlying stock, the volatility of the underlying price) are external data.

## How to structure the class variables and methods

We want to make this class as simple as possible. The good thing is that a class can be extremely simple! You should see the class as the blueprint for a big variable that contains several named variables itself, as well as functions. The most basic class can actually be just a list of public variable. In this case you are simply creating a custom data type, except it is much more flexible that the basic custom types you can create with VBA.

If you have some time ahead it could be good to have a look at another article I wrote comparing VBA custom variable types to VBA custom classes and their respective use in arrays and collections…

Otherwise, let’s just come back to our European option. We need the following data embedded into the option object:

- Dividends payments to be done before maturity,
- The expiry date,
- The option trade date,
- The Underlying name (which could be useful to get the relevant market data),
- The strike Price of the option,
- The nature of the option: call or put?
- The currency in which the trade was done,
- The pricing method to use.

In addition to the embedded variables we need to define a pricing function or method so that we can price the option from the object itself. This pricing function would require external data like the current spot price of the underlying, the volatility of the underlying and the interest rate level.

## The VBA custom classes for the Options and the Market Data

In the class module cMarketData we only have:

Public volatility As Double, Spot As Double, RiskFreeRate As Double

In the class module cEuropeanOption we have:

Public dividends As Double, _

expiry As Date, _

startDate As Date, _

Underlying As String, _

strikePrice As Double, _

isCallOption As Boolean, _

ccy As String, _

pricingMethod As String

Property Get GetTimetoMaturity(dateToday As Date) As Double

GetTimetoMaturity = (Me.expiry - dateToday) / 365

End Property

Public Function price(Underlying, dateToday As Date) As Double

Dim TimetoMaturity As Double

TimetoMaturity = Me.GetTimetoMaturity(dateToday)

Select Case Me.pricingMethod

Case "frozen"

price = 5

Case "BlackScholes"

If Me.isCallOption Then

Dim d1 As Double, d2 As Double

d1 = 1 / (Underlying.volatility * (TimetoMaturity) ^ (1 / 2)) * _

Application.WorksheetFunction.Ln(Underlying.Spot / Me.strikePrice) + _

(Underlying.RiskFreeRate + (Underlying.volatility ^ 2) / 2) * TimetoMaturity)

d2 = d1 - (Underlying.volatility * (TimetoMaturity) ^ (1 / 2))

price = Application.WorksheetFunction.Norm_S_Dist(d1, True) _

* (Underlying.Spot * (1 - dividends)) - Application.WorksheetFunction.Norm_S_Dist(d2, True) * _

Me.strikePrice * Exp(-Underlying.RiskFreeRate * TimetoMaturity)

Else

d1 = 1 / (Underlying.volatility * (TimetoMaturity) ^ (1 / 2)) * _

(Application.WorksheetFunction.Ln(Underlying.Spot / Me.strikePrice) _

+ (Underlying.RiskFreeRate + (Underlying.volatility ^ 2) / 2) * TimetoMaturity)

d2 = d1 - (Underlying.volatility * (TimetoMaturity) ^ (1 / 2))

price = -Application.WorksheetFunction.Norm_S_Dist(-d1, True) * _

(Underlying.Spot * (1 - dividends)) + Application.WorksheetFunction.Norm_S_Dist(-d2, True) * _

Me.strikePrice * Exp(-Underlying.RiskFreeRate * TimetoMaturity)

End If

End Select

End Function

## The VBA module using the Option class and Market Data class

Sub testOptions()

Dim OptionSPX As New cEuropeanOption ' A put

Dim OptionSPXCall As New cEuropeanOption ' A call

Dim SPXMarket As New cMarketData

' The array that we will use to display the results

Dim PriceCollection() As Double

Dim Alldates() As Date

' First we declare the two options that we are looking at

With OptionSPX

.startDate = #1/1/2018#

.expiry = #5/1/2020#

.dividends = 0

.Underlying = "SPX"

.strikePrice = 2500

.isCallOption = False

.ccy = "USD"

.pricingMethod = "BlackScholes"

End With

With OptionSPXCall

.startDate = #1/1/2018#

.expiry = #5/1/2020#

.dividends = 0

.Underlying = "SPX"

.strikePrice = 2500

.isCallOption = True

.ccy = "USD"

.pricingMethod = "BlackScholes"

End With

' then we declare some market data

With SPXMarket

.volatility = 0.1

.Spot = 2550

.RiskFreeRate = 0.01

End With

'Then we dimension the array that will be used, note that we take

' a much larger dimension than needed

ReDim PriceCollection(0 To 100, 1 To 3)

ReDim Alldates(0 To 100, 1 To 1)

i = 0

' The we price the two options at different dates...

While DateAdd("m", i, OptionSPX.startDate) < OptionSPX.expiry

PriceCollection(i, 1) = OptionSPX.GetTimetoMaturity(DateAdd("m", i, OptionSPX.startDate))

PriceCollection(i, 2) = OptionSPX.price(SPXMarket, DateAdd("m", i, OptionSPX.startDate))

PriceCollection(i, 3) = OptionSPXCall.price(SPXMarket, DateAdd("m", i, OptionSPX.startDate))

Alldates(i, 1) = DateAdd("m", i, OptionSPX.startDate)

i = i + 1

Wend

' ... and 1 day before maturity

PriceCollection(i, 1) = OptionSPX.GetTimetoMaturity(OptionSPX.expiry - 1)

PriceCollection(i, 2) = OptionSPX.price(SPXMarket, OptionSPX.expiry - 1)

PriceCollection(i, 3) = OptionSPXCall.price(SPXMarket, OptionSPX.expiry - 1)

Alldates(i, 1) = OptionSPX.expiry - 1

' And we print it in worksheet Sheet2

Sheet2.Range("A1:A30").Value = Alldates

Sheet2.Range("B1:D30").Offset(0, 0).Value = PriceCollection

End Sub

After reworking a little bit the Excel results, we obtain the following output: