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)
                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
' ... 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:


Matthieu Liatard
Remember this!
Se souvenir de cela
Tester mes connaissances

Bonjour :)
Avec Question-Player, apprenez de nouvelles choses et n'oubliez plus rien. Jamais. Et en plus c'est gratuit.

Pour nous aider, vous pouvez créer un compte:

Login / Créer un compte
Notez cet article :

Up next

Articles similaires