O'Reilly logo

FX Derivatives Trader School by Giles Jewitt

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Practical D

Generating Tenor Dates in Excel

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:

  • Increment a date to the next business day.
  • Decrement a date to the previous business day.

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:

  • Calculate the spot date from a horizon date.
  • Calculate the horizon date from a spot date.

This can be achieved using VBA functions that increment and decrement a given number of business days. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required