To build a volatility surface or quote prices based on market tenors, the expiry dates corresponding to each tenor must be calculated. In Excel, dates are internally stored as integers with 0 = Jan 1, 1900, 1 = Jan 2, 1900, and so on. Current dates are therefore over 40,000 (e.g., June 11, 2014 is 41,801). Within VBA code, dates can be represented using variables with type Long.
First, VBA functions are required to:
Note that these functions don't take holidays into account. The built-in VBA function Weekday is used to check the input day of the week:
Function nextBusinessDay(InputDate As Long) As Long If Weekday(InputDate) = 7 Then 'Input Date = Saturday nextBusinessDay = InputDate + 2 ElseIf Weekday(InputDate) = 6 Then 'Input Date = Friday nextBusinessDay = InputDate + 3 Else nextBusinessDay = InputDate + 1 End If End Function Function previousBusinessDay(InputDate As Long) As Long If Weekday(InputDate) = 1 Then 'Input Date = Sunday previousBusinessDay = InputDate - 2 ElseIf Weekday(InputDate) = 2 Then 'Input Date = Monday previousBusinessDay = InputDate - 3 Else previousBusinessDay = InputDate - 1 End If End Function
Functions are also required to:
This can be achieved using VBA functions that increment and decrement a given number of business days. ...