SABRVolCurve Example VBA
 VBA Example - SABRVolCurve ' ################################################################################## ' The first function here SABRVolCurve(), contains a series of ' function calls leading upto the main function call, the second function ' within this file ( SABRVolCurvePart() ). ' which contains the answer that we are looking for. ' The first function here is simply an example of how to construct the parameters ' in order acquire either a string Key (that is to be passed to other functions) ' or a computed result. ' If you are viewing this source code from the chm or web help file you can use the ' outlining features to collapse certain sections of the code for better readability. ' ################################################################################## ' Add a reference to the CTQL_VBA_API.xla file via the Tools->Reference menu (within the VBA editor).' This holds VBA class objects for communicating with the CTQuantToolsXL20.dll Excel Addin' This XLA does not use COM and has access to all the financial objects created via the spreadsheet functions.' Some global parameter in order to append to user defined keys.' We use it here to ensure that we have unique Keys (in the case several of our examples' use the same key-name)' In normal use, a user defined string will be used and so this variable will be pointless.Global nCTVolatiltyCurvesGlobal As Long  Public Function VB_EX_SABRVolCurve() As String nCTVolatiltyCurvesGlobal = nCTVolatiltyCurvesGlobal + 1 On Error GoTo err_Generic ' Creates a centralized valuation date object. Dim MyValuationDate As String MyValuationDate = _ ValueDateObjPart() ' UK date calendar used within the UK stock exchange. Dim MyCALUKExchange As String MyCALUKExchange = _ CALUKExchangePart() ' EURO calendar used for holiday adjustments. Dim MyEuroCal As String MyEuroCal = _ CALEUROPart() ' Creates a Deposit template which is almost identical to a Libor ' Index, but without the YieldCurve information. Dim MyDepoTPL As String MyDepoTPL = _ CreateDepoTemplatePart( _ MyCALUKExchange, _ MyEuroCal) ' Creates a Swap template which is almost identical to the definition ' of the parameters of a swap contract, but without the swap duration, ' buysell, and YieldCurve information. Dim MySwapTPL As String MySwapTPL = _ CreateSwapTemplatePart( _ MyEuroCal, _ MyDepoTPL) ' Creates a SABR curve to model the dynamics of the volatility ' curve (smile). Dim MySABRVolCurve As String MySABRVolCurve = _ SABRVolCurvePart( _ MyValuationDate, _ MyDepoTPL, _ MySwapTPL) ' This is the result we are looking for. VB_EX_SABRVolCurve = MySABRVolCurve Exit Functionerr_Generic: MsgBox "Error: " & Err.Number & vbCrLf & Err.Description  End Function ' ///////////////////////////////////////////////////////////////////Private Function SABRVolCurvePart( _ MyValuationDate As String, _ MyDepoTPL As String, _ MySwapTPL As String) As String ' Create example range for parameter SABRVolCurve_ATMRange Dim SABRVolCurve_ATMRange As Variant SABRVolCurve_ATMRange = vRange.RangeFromStr _ ( _ CStr("{") + _ CStr("'Opt\Und' | '3M' | '6M' | '9M' | '12M' | '2Y' | '4Y' | '6Y' | '8Y' | '10Y' | '12Y' | '14Y' | '16Y' ;") + _ CStr("'3M' | 18 | 18.01 | 18.02 | 18.02 | 18.03 | 18.04 | 18.05 | 18.06 | 18.07 | 18.07 | 18.08 | 18.09 ;") + _ CStr("'6M' | 18.1 | 18.11 | 18.12 | 18.12 | 18.13 | 18.14 | 18.15 | 18.16 | 18.17 | 18.17 | 18.18 | 18.19 ;") + _ CStr("'9M' | 18.19 | 18.2 | 18.21 | 18.22 | 18.22 | 18.23 | 18.24 | 18.25 | 18.26 | 18.26 | 18.27 | 18.28 ;") + _ CStr("'12M' | 18.29 | 18.29 | 18.3 | 18.31 | 18.32 | 18.32 | 18.33 | 18.34 | 18.34 | 18.35 | 18.36 | 18.37 ;") + _ CStr("'2Y' | 18.37 | 18.38 | 18.39 | 18.4 | 18.41 | 18.42 | 18.42 | 18.43 | 18.44 | 18.44 | 18.45 | 18.46 ;") + _ CStr("'4Y' | 18.46 | 18.47 | 18.48 | 18.48 | 18.49 | 18.5 | 18.5 | 18.51 | 18.52 | 18.53 | 18.53 | 18.54 ;") + _ CStr("'6Y' | 18.55 | 18.55 | 18.56 | 18.57 | 18.57 | 18.58 | 18.59 | 18.6 | 18.6 | 18.61 | 18.62 | 18.62 ;") + _ CStr("'8Y' | 18.63 | 18.64 | 18.64 | 18.65 | 18.66 | 18.66 | 18.67 | 18.68 | 18.69 | 18.69 | 18.7 | 18.71 ;") + _ CStr("'10Y' | 18.71 | 18.72 | 18.73 | 18.74 | 18.75 | 18.75 | 18.76 | 18.77 | 18.78 | 18.79 | 18.79 | 18.8 ;") + _ CStr("'12Y' | 18.81 | 18.81 | 18.82 | 18.83 | 18.84 | 18.84 | 18.85 | 18.86 | 18.87 | 18.87 | 18.88 | 18.88 ;") + _ CStr("'14Y' | 18.89 | 18.9 | 18.91 | 18.92 | 18.92 | 18.93 | 18.94 | 18.95 | 18.96 | 18.96 | 18.97 | 18.98 ;") + _ CStr("'16Y' | 18.99 | 18.99 | 19 | 19.01 | 19.02 | 19.03 | 19.04 | 19.05 | 19.05 | 19.06 | 19.07 | 19.07 ;") + _ CStr("'18Y' | 19.08 | 19.08 | 19.09 | 19.1 | 19.11 | 19.11 | 19.12 | 19.13 | 19.13 | 19.14 | 19.15 | 19.16 ;") + _ CStr("'20Y' | 19.16 | 19.17 | 19.18 | 19.18 | 19.19 | 19.2 | 19.21 | 19.21 | 19.22 | 19.22 | 19.23 | 19.24 ;") + _ CStr("'22Y' | 19.25 | 19.26 | 19.26 | 19.27 | 19.28 | 19.29 | 19.29 | 19.3 | 19.31 | 19.32 | 19.32 | 19.33") + _ CStr("}") _ ) ' Create example range for parameter SABRVolCurve_ALPHARange Dim SABRVolCurve_ALPHARange As Variant SABRVolCurve_ALPHARange = vRange.RangeFromStr _ ( _ CStr("{") + _ CStr("'Opt\Und' | '3M' | '6M' | '9M' | '12M' | '2Y' | '4Y' | '6Y' | '8Y' | '10Y' | '12Y' | '14Y' | '16Y' ;") + _ CStr("'3M' | 0.4 | 0.4 | 0.4 | 0.4 | 0.41 | 0.41 | 0.41 | 0.41 | 0.41 | 0.41 | 0.41 | 0.42 ;") + _ CStr("'6M' | 0.42 | 0.42 | 0.42 | 0.42 | 0.42 | 0.42 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 ;") + _ CStr("'9M' | 0.43 | 0.44 | 0.44 | 0.44 | 0.44 | 0.44 | 0.44 | 0.45 | 0.45 | 0.45 | 0.45 | 0.45 ;") + _ CStr("'12M' | 0.45 | 0.45 | 0.46 | 0.46 | 0.46 | 0.46 | 0.46 | 0.46 | 0.46 | 0.47 | 0.47 | 0.47 ;") + _ CStr("'2Y' | 0.47 | 0.47 | 0.47 | 0.47 | 0.48 | 0.48 | 0.48 | 0.48 | 0.48 | 0.48 | 0.49 | 0.49 ;") + _ CStr("'4Y' | 0.49 | 0.49 | 0.49 | 0.49 | 0.49 | 0.49 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 ;") + _ CStr("'6Y' | 0.51 | 0.51 | 0.51 | 0.51 | 0.51 | 0.51 | 0.52 | 0.52 | 0.52 | 0.52 | 0.52 | 0.52 ;") + _ CStr("'8Y' | 0.52 | 0.53 | 0.53 | 0.53 | 0.53 | 0.53 | 0.53 | 0.53 | 0.54 | 0.54 | 0.54 | 0.54 ;") + _ CStr("'10Y' | 0.54 | 0.54 | 0.54 | 0.55 | 0.55 | 0.55 | 0.55 | 0.55 | 0.55 | 0.55 | 0.56 | 0.56 ;") + _ CStr("'12Y' | 0.56 | 0.56 | 0.56 | 0.56 | 0.56 | 0.57 | 0.57 | 0.57 | 0.57 | 0.57 | 0.57 | 0.57 ;") + _ CStr("'14Y' | 0.57 | 0.58 | 0.58 | 0.58 | 0.58 | 0.58 | 0.58 | 0.58 | 0.59 | 0.59 | 0.59 | 0.59 ;") + _ CStr("'16Y' | 0.59 | 0.59 | 0.59 | 0.6 | 0.6 | 0.6 | 0.6 | 0.6 | 0.6 | 0.6 | 0.61 | 0.61 ;") + _ CStr("'18Y' | 0.61 | 0.61 | 0.61 | 0.61 | 0.61 | 0.62 | 0.62 | 0.62 | 0.62 | 0.62 | 0.62 | 0.62 ;") + _ CStr("'20Y' | 0.63 | 0.63 | 0.63 | 0.63 | 0.63 | 0.63 | 0.64 | 0.64 | 0.64 | 0.64 | 0.64 | 0.64 ;") + _ CStr("'22Y' | 0.65 | 0.65 | 0.65 | 0.65 | 0.65 | 0.65 | 0.65 | 0.65 | 0.66 | 0.66 | 0.66 | 0.66") + _ CStr("}") _ ) ' Create example range for parameter SABRVolCurve_BETARange Dim SABRVolCurve_BETARange As Variant SABRVolCurve_BETARange = vRange.RangeFromStr _ ( _ CStr("{") + _ CStr("'Opt\Und' | '3M' | '6M' | '9M' | '12M' | '2Y' | '4Y' | '6Y' | '8Y' | '10Y' | '12Y' | '14Y' | '16Y' ;") + _ CStr("'3M' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'6M' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'9M' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'12M' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'2Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'4Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'6Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'8Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'10Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'12Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'14Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'16Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'18Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'20Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 ;") + _ CStr("'22Y' | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7 | 0.7") + _ CStr("}") _ ) ' Create example range for parameter SABRVolCurve_RHORange Dim SABRVolCurve_RHORange As Variant SABRVolCurve_RHORange = vRange.RangeFromStr _ ( _ CStr("{") + _ CStr("'Opt\Und' | '3M' | '6M' | '9M' | '12M' | '2Y' | '4Y' | '6Y' | '8Y' | '10Y' | '12Y' | '14Y' | '16Y' ;") + _ CStr("'3M' | 0.3 | 0.3 | 0.3 | 0.31 | 0.31 | 0.31 | 0.31 | 0.31 | 0.31 | 0.31 | 0.31 | 0.32 ;") + _ CStr("'6M' | 0.32 | 0.32 | 0.32 | 0.32 | 0.32 | 0.32 | 0.33 | 0.32 | 0.32 | 0.33 | 0.33 | 0.33 ;") + _ CStr("'9M' | 0.33 | 0.33 | 0.32 | 0.33 | 0.32 | 0.33 | 0.33 | 0.33 | 0.33 | 0.33 | 0.33 | 0.33 ;") + _ CStr("'12M' | 0.33 | 0.34 | 0.33 | 0.33 | 0.33 | 0.33 | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 ;") + _ CStr("'2Y' | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 | 0.34 | 0.35 | 0.35 ;") + _ CStr("'4Y' | 0.35 | 0.35 | 0.36 | 0.36 | 0.36 | 0.36 | 0.36 | 0.36 | 0.36 | 0.36 | 0.36 | 0.36 ;") + _ CStr("'6Y' | 0.37 | 0.36 | 0.37 | 0.37 | 0.37 | 0.37 | 0.37 | 0.37 | 0.37 | 0.37 | 0.37 | 0.37 ;") + _ CStr("'8Y' | 0.38 | 0.38 | 0.38 | 0.37 | 0.37 | 0.37 | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 ;") + _ CStr("'10Y' | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 | 0.37 | 0.38 | 0.37 | 0.38 | 0.38 | 0.38 ;") + _ CStr("'12Y' | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 | 0.38 | 0.39 | 0.39 | 0.39 | 0.39 ;") + _ CStr("'14Y' | 0.39 | 0.39 | 0.39 | 0.39 | 0.39 | 0.39 | 0.4 | 0.39 | 0.39 | 0.39 | 0.4 | 0.4 ;") + _ CStr("'16Y' | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.4 | 0.41 | 0.41 ;") + _ CStr("'18Y' | 0.41 | 0.41 | 0.41 | 0.41 | 0.41 | 0.41 | 0.42 | 0.42 | 0.41 | 0.42 | 0.42 | 0.42 ;") + _ CStr("'20Y' | 0.42 | 0.42 | 0.42 | 0.43 | 0.43 | 0.43 | 0.42 | 0.43 | 0.42 | 0.43 | 0.43 | 0.43 ;") + _ CStr("'22Y' | 0.42 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43 | 0.43") + _ CStr("}") _ ) ' Key value to use as a handle for the created object Dim MySABRVolCurve As String MySABRVolCurve = "MySABRVolCurve" & "_" & CStr(nCTVolatiltyCurvesGlobal) ' When creating this object for the first time, set this parameter ' to a positive value. Dim Reload As Long Reload = 1 ' A tag used to identify this curve (case insensitive) if placed ' within a Volatility curve collection ( via the GroupedVolCurves() ' function ). Dim CurveName As String CurveName = "MySABRVolCurve" ' Number of days between the Exercise date of the options and ' the STARTDATE of the instrument. Dim SettleDays As Long SettleDays = 2 ' Is the input volatility entered as a percentage value (true), ' or the raw volatility value (false). Dim DivideVolBy100 As Boolean DivideVolBy100 = true ' DayCounter for converting dates into year fractions. Dim DayCount As String DayCount = "30360" ' Interpolation method to use when interpolating the curve for ' vols, - LINEAR, LOGLINEAR, CUBIC. Dim InterpType As String InterpType = "LINEAR" ' Excel function call is : "CT.CRV.SABRVolCurve()" ' Creates a SABR curve to model the dynamics of the volatility ' curve (smile). Dim rSABRVolCurve As String ' We call the CreateCTVolatiltyCurves() function via the CTQL module exposed from the CTQL_VBA_API.xla addin. Dim oCTVolatiltyCurves As CTVolatiltyCurves Set oCTVolatiltyCurves = CTQL.CreateCTVolatiltyCurves() rSABRVolCurve = oCTVolatiltyCurves.SABRVolCurve( _ MySABRVolCurve, _ Reload, _ CurveName, _ MyValuationDate, _ SettleDays, _ SABRVolCurve_ATMRange, _ SABRVolCurve_ALPHARange, _ SABRVolCurve_BETARange, _ SABRVolCurve_RHORange, _ DivideVolBy100, _ MyDepoTPL, _ MySwapTPL, _ DayCount, _ InterpType) SABRVolCurvePart = rSABRVolCurve End Function |
High level view of the code structure (resulting in the final function call to SABRVolCurve() )
