QuantLib-Python: Term Structure Simulation Using HW1F Model

This post is presenting Python program, which uses QuantLib tools for simulating yield term structure for the chosen one-factor interest rate model. Further comparison results are also showing, that simulation method is able to replicate the initial yield curve, without any notable differences.

The idea is this: create yield curve object by using current market data (flat forward) and 1-D stochastic process for short rate dynamics (Hull-White). Then, use separate method (GeneratePaths) for generating desired amount of paths for a chosen stochastic process. Next, integrate short-rate for all simulated paths and calculate average zero-coupon bond prices. Finally, create a new yield curve object by using previously simulated zero-coupon bond prices and compare the resulting set of discount factors with the ones requested from the original yield curve. It should be noted, that there is a separate class (Grid), which is used for hosting all schedule-related information (such as schedule, dates and times) and their conversions (from schedule to times, from schedule to dates) in one compact place.

From the screenshot below we can conclude, that as

• the data and the other parameters are within "sensible ranges", 
• the number of paths is large enough and 
• discretization error is minimized by selecting a small enough time step, 

simulated yield curve is able to replicate the initial yield curve without any notable differences. Some relevant issues around this particular topic has been chewed in here and here. The same stuff (and a lot more) has also been published in QuantLib Python Cookbook by the blog author Gouthaman Balaraman and QuantLib lead developer Luigi Ballabio.

Finally, outside of being a nice QuantLib exercise itself, there is not much point to simulate zero-coupon bond prices. Needless to say, the essence of Monte Carlo method (simulate a path, create term structure from it, price a product) can be used for much more interesting valuation problems.

Thanks for reading my blog.
-Mike

%config IPCompleter.greedy = True
import math as Math
from QuantLib import *
import numpy as Numpy
import matplotlib.pyplot as Matplotlib

def main():

    # create grid object for 30Y, having time step of 1 day
    startDate = Date(3, December, 2018)
    endDate = Date(3, December, 2048)
    tenor = Period(1, Days)
    grid = Grid(startDate, endDate, tenor)

    # create yield curve and Hull-White one-factor interest rate model
    curve = YieldTermStructureHandle(FlatForward(startDate, 0.04875825, Actual365Fixed()))
    reversionSpeed = 0.05
    rateVolatility = 0.00586
    process = HullWhiteProcess(curve, reversionSpeed, rateVolatility)

    # request paths from generator method
    nPaths = 25000
    paths = GeneratePaths(process, grid.GetTimeGrid(), nPaths)

    # container for simulated zero-coupon bonds
    zeros = Numpy.zeros(shape = (grid.GetSize()))
    dt = grid.GetDt()
    gridSize = grid.GetSize()

    # process short-rate path integrations for all simulated paths
    for i in range(nPaths):
        integral = 0.0
        for j in range(gridSize):
            integral = integral + paths[i, j]
            if(j == 0):
                # zero-coupon bond price today is 1.0
                zeros[j] = 1.0 * nPaths
            else:
                zeros[j] = zeros[j] + Math.exp(-integral * dt)

    # calculate averages for all simulated zero-coupon bond prices
    zeros = zeros / nPaths

    # create yield term structure object from simulated bond prices
    times = grid.GetTimes()
    dates = grid.GetDates()
    simulatedCurve = DiscountCurve(dates, zeros, Actual365Fixed(), NullCalendar())

    # get discount factors for simulated and original yield curves
    dfs = Numpy.zeros(shape = (gridSize))
    simulatedDfs = Numpy.zeros(shape = (gridSize))
    for i in range(gridSize):
        simulatedDfs[i] = simulatedCurve.discount(times[i])
        dfs[i] = curve.discount(times[i])

    # plot simulated and original discount factors
    Matplotlib.title('discount factors')
    Matplotlib.plot(times, simulatedDfs, linestyle = 'dashed', label = 'simulated curve')
    Matplotlib.plot(times, dfs, linestyle = 'solid', label = 'original curve')
    Matplotlib.legend()
    Matplotlib.show()

    # plot difference between simulated and original discount factors in basis points
    Matplotlib.title('difference (bps)')
    Matplotlib.plot(times, (dfs - simulatedDfs) * 10000)
    Matplotlib.show()

    
# path generator method for uncorrelated and correlated 1-D stochastic processes
def GeneratePaths(process, timeGrid, n):

    # correlated processes, use GaussianMultiPathGenerator
    if(type(process) == StochasticProcessArray):
        times = []; [times.append(timeGrid[t]) for t in range(len(timeGrid))]        
        nGridSteps = (len(times) - 1) * process.size()
        sequenceGenerator = UniformRandomSequenceGenerator(nGridSteps, UniformRandomGenerator())
        gaussianSequenceGenerator = GaussianRandomSequenceGenerator(sequenceGenerator)
        pathGenerator = GaussianMultiPathGenerator(process, times, gaussianSequenceGenerator, False)        
        paths = Numpy.zeros(shape = (n, process.size(), len(timeGrid)))
        
        # loop through number of paths
        for i in range(n):
            # request multiPath, which contains the list of paths for each process
            multiPath = pathGenerator.next().value()
            # loop through number of processes
            for j in range(multiPath.assetNumber()):
                # request path, which contains the list of simulated prices for a process
                path = multiPath[j]
                # push prices to array
                paths[i, j, :] = Numpy.array([path[k] for k in range(len(path))])
        # resulting array dimension: n, process.size(), len(timeGrid)
        return paths

    # uncorrelated processes, use GaussianPathGenerator
    else:
        sequenceGenerator = UniformRandomSequenceGenerator(len(timeGrid), UniformRandomGenerator())
        gaussianSequenceGenerator = GaussianRandomSequenceGenerator(sequenceGenerator)
        maturity = timeGrid[len(timeGrid) - 1]
        pathGenerator = GaussianPathGenerator(process, maturity, len(timeGrid), gaussianSequenceGenerator, False)
        paths = Numpy.zeros(shape = (n, len(timeGrid)))
        for i in range(n):
            path = pathGenerator.next().value()
            paths[i, :] = Numpy.array([path[j] for j in range(len(timeGrid))])
        # resulting array dimension: n, len(timeGrid)
        return paths


# class for hosting schedule-related information (dates, times)
class Grid:
    def __init__(self, startDate, endDate, tenor):
        # create date schedule, ignore conventions and calendars
        self.schedule = Schedule(startDate, endDate, tenor, NullCalendar(), 
            Unadjusted, Unadjusted, DateGeneration.Forward, False)
        self.dayCounter = Actual365Fixed()
    def GetDates(self):
        # get list of scheduled dates
        dates = []
        [dates.append(self.schedule[i]) for i in range(self.GetSize())]
        return dates
    def GetTimes(self):
        # get list of scheduled times
        times = []
        [times.append(self.dayCounter.yearFraction(self.schedule[0], self.schedule[i])) 
            for i in range(self.GetSize())]
        return times
    def GetMaturity(self):
        # get maturity in time units
        return self.dayCounter.yearFraction(self.schedule[0], self.schedule[self.GetSteps()])
    def GetSteps(self):
        # get number of steps in schedule
        return self.GetSize() - 1    
    def GetSize(self):
        # get total number of items in schedule
        return len(self.schedule)    
    def GetTimeGrid(self):
        # get QuantLib TimeGrid object, constructed by using list of scheduled times
        return TimeGrid(self.GetTimes(), self.GetSize())
    def GetDt(self):
        # get constant time step
        return self.GetMaturity() / self.GetSteps()
    
main()

QuantLib-Python: Path Generator Method for Uncorrelated and Correlated 1-D Stochastic Processes

This Python program presents one compact method for simulating paths for the both uncorrelated and correlated stochastic processes.

Thanks for reading my blog.
-Mike

%config IPCompleter.greedy = True
from QuantLib import *
import numpy as Numpy
import matplotlib.pyplot as Matplotlib

# method for simulating paths for the both uncorrelated and correlated processes
# arguments:
# process = QuantLib 1-dimensional stochastic process object or 
#           StochasticProcessArray (Array of correlated 1-D stochastic processes)
# timeGrid = QuantLib TimeGrid object
# n = number of paths
def GeneratePaths(process, timeGrid, n):
    
    # correlated processes, use GaussianMultiPathGenerator
    if(type(process) == StochasticProcessArray):
        times = []; [times.append(timeGrid[t]) for t in range(len(timeGrid))]        
        nGridSteps = (len(times) - 1) * process.size()
        sequenceGenerator = UniformRandomSequenceGenerator(nGridSteps, UniformRandomGenerator())
        gaussianSequenceGenerator = GaussianRandomSequenceGenerator(sequenceGenerator)
        pathGenerator = GaussianMultiPathGenerator(process, times, gaussianSequenceGenerator, False)        
        paths = Numpy.zeros(shape = (n, process.size(), len(timeGrid)))
        
        # loop through number of paths
        for i in range(n):
            # request multiPath, which contains the list of paths for each process
            multiPath = pathGenerator.next().value()
            # loop through number of processes
            for j in range(multiPath.assetNumber()):
                # request path, which contains the list of simulated prices for a process
                path = multiPath[j]
                # push prices to array
                paths[i, j, :] = Numpy.array([path[k] for k in range(len(path))])
        # resulting array dimension: n, process.size(), len(timeGrid)
        return paths

    # uncorrelated processes, use GaussianPathGenerator
    else:
        sequenceGenerator = UniformRandomSequenceGenerator(len(timeGrid), UniformRandomGenerator())
        gaussianSequenceGenerator = GaussianRandomSequenceGenerator(sequenceGenerator)
        maturity = timeGrid[len(timeGrid) - 1]
        pathGenerator = GaussianPathGenerator(process, maturity, len(timeGrid), gaussianSequenceGenerator, False)
        paths = Numpy.zeros(shape = (n, len(timeGrid)))   
        for i in range(n):
            path = pathGenerator.next().value()
            paths[i, :] = Numpy.array([path[j] for j in range(len(timeGrid))])
        # resulting array dimension: n, len(timeGrid)
        return paths

# create simulation-related parameters
today = Date(30, November, 2018)
maturity = 5.0
nSteps = int(maturity) * 365
# create regularly spaced QuantLib TimeGrid object
timeGrid = TimeGrid(maturity, nSteps)
nPaths = 25

# create HW1F model
reversionSpeed = 0.05
rateVolatility = 0.0099255
curve = RelinkableYieldTermStructureHandle(FlatForward(today, 0.01, Actual360()))
HW1F = HullWhiteProcess(curve, reversionSpeed, rateVolatility)
hw1f_paths = GeneratePaths(HW1F, timeGrid, nPaths)

# create GBM model
initialValue = 0.01
mue = 0.01
sigma = 0.0099255
GBM = GeometricBrownianMotionProcess(initialValue, mue, sigma)
gbm_paths = GeneratePaths(GBM, timeGrid, nPaths)

# plot uncorrelated paths
times = []; [times.append(timeGrid[t]) for t in range(len(timeGrid))]
Matplotlib.rcParams['figure.figsize'] = [12.0, 8.0]
f, subPlots = Matplotlib.subplots(2, sharex = True)
f.suptitle('Uncorrelated paths n=' + str(nPaths))
subPlots[0].set_title('HW1F')
subPlots[1].set_title('GBM')

for i in range(hw1f_paths.shape[0]):
    path = hw1f_paths[i, :] 
    subPlots[0].plot(times, path)

for i in range(gbm_paths.shape[0]):
    path = gbm_paths[i, :] 
    subPlots[1].plot(times, path)

# create correlated paths
rho = 1.0
correlation = [[1.0, rho], [rho, 1.0]]
processArray = StochasticProcessArray([HW1F, GBM], correlation)
correlated_paths = GeneratePaths(processArray, timeGrid, nPaths)

# plot correlated paths
f2, subPlots2 = Matplotlib.subplots(processArray.size(), sharex = True)
f2.suptitle('Correlated paths n=' + str(nPaths) + ', rho=' + str(rho))
subPlots2[0].set_title('HW1F')
subPlots2[1].set_title('GBM')

for i in range(nPaths):
    for j in range(processArray.size()):
        path = correlated_paths[i, j, :]
        subPlots2[j].plot(times, path)

QuantLib-Python: Transaction Builder

Even the fanciest restaurant needs to have dedicated staff to visit fresh market and peel potatoes, in order to have everything ready for head chef to prepare delicious late night dinners for us. Similarly, having amazing analytics library (such as QuantLib) available for calculations is still only "halfway home", since all required data inputs (ex. transactions) need to be constructed beforehand. It is highly preferred for this part of the process to be performed in a way, that maximum re-configurability would be maintained, while manual labour would be completely avoided.

In a nutshell, this post will present, how to go from having several XML configuration files for specific QuantLib transaction in a directory (shown on the left side), to have all these transactions constructed and processed through QuantLib (shown on the right side).

Implement the following Python program. First, it will create QuantLib flat yield term structure and discounting bond pricing engine, request a batch of constructed QuantLib transactions from TransactionManager method (located in a separate QuantLibTransactionBuilder), then assign pricing engine for each transaction and finally, print calculated NPV along with some other transaction-related information.

%config IPCompleter.greedy = True
from QuantLib import *
from QuantLibTransactionBuilder import *

# create valuation curve and pricing engine
tradeDate = Date(28, November, 2018)
settlementDate = TARGET().advance(tradeDate, Period(2, Days))
curveHandle = YieldTermStructureHandle(FlatForward(settlementDate, 0.01, Actual360()))
engine = DiscountingBondEngine(curveHandle)

# create QuantLib transactions from repository XML files
transactionsFolder = '/home/TransactionRepository/'
transactions = TransactionManager(transactionsFolder)

# set pricing engine for all transactions, request PV
for t in range(len(transactions)):
    transactions[t].setPricingEngine(engine)
    print('Maturity: ' + str(transactions[t].maturityDate()))
    print('Notional: ' + str(transactions[t].notional()))
    print('NPV: ' + str(transactions[t].NPV()))
    print()

Next, implement the following new module (QuantLibTransactionBuilder.py), which contains specific transaction builder for QuantLib ZeroCouponBond instrument, static class methods for QuantLib-related type conversions and one method for handling a set of given XML files and their conversions into QuantLib objects. Given comments in this module should give crystal clear picture what is happening here.

import os
import importlib
import re as Regex
from bs4 import BeautifulSoup # sudo apt-get install python3-bs4
from QuantLib import *

# static class for conversions from string to QuantLib objects/enumerators
class QL():
    @staticmethod
    def to_date(s):
        monthDictionary = {
            '01': January, '02': February, '03': March,
            '04': April, '05': May, '06': June,
            '07': July, '08': August, '09': September,
            '1': January, '2': February, '3': March,
            '4': April, '5': May, '6': June,
            '7': July, '8': August, '9': September,
            '10': October, '11': November, '12': December
        }
        arr = Regex.findall(r"[\w']+", s)
        day = int(arr[2])
        month = monthDictionary[arr[1]]
        year = int(arr[0])
        return Date(day, month, year)
    @staticmethod
    def to_businessDayConvention(s):
        if (s.upper() == 'MODIFIEDFOLLOWING'): return ModifiedFollowing
        # add new businessdayconvention here
    @staticmethod
    def to_calendar(s):
        if (s.upper() == 'TARGET'): return TARGET()
        # add new calendar here


# loop through XML files in a given folder
# for each transaction, read 'transactionBuilder' attribute from XML data
# call configured transaction builder, build transaction and add it to list
# finally, return list of all built QuantLib transactions to client 
def TransactionManager(folder):
    transactions = []
    files = os.listdir(folder)
    for f in range(len(files)):
        soup = BeautifulSoup(open(folder + files[f]).read(), 'xml')
        transactionBuilder = soup.find('transactionBuilder').get_text()
        builder = getattr(importlib.import_module('QuantLibTransactionBuilder'), transactionBuilder)(folder + files[f])
        transaction = builder
        transactions.append(transaction)
    return transactions


# method for constructing QuantLib 'ZeroCouponBond' object from a given XML data 
def ZeroCouponBondBuilder(filePathName):
    # assign transaction data from XML to variables using soup
    # use conversion class methods for all QuantLib-related conversion 
    soup = BeautifulSoup(open(filePathName).read(), 'xml')
    tradeDate = QL.to_date(soup.find('tradeDate').get_text())
    settlementDate = QL.to_date(soup.find('settlementDate').get_text())
    calendar = QL.to_calendar(soup.find('calendar').get_text())
    faceAmount = float(soup.find('faceAmount').get_text())
    maturityDate = QL.to_date(soup.find('maturityDate').get_text())
    paymentConvention = QL.to_businessDayConvention(soup.find('paymentConvention').get_text())
    settlementDays = settlementDate - tradeDate 
    # create QuantLib object by calling appropriate constructor
    return ZeroCouponBond(settlementDays, calendar, faceAmount, maturityDate, paymentConvention)

All desired new transaction builders should be implemented into previous module by adding another builder method. Also, corresponding builder method name should be also set into transaction XML file as one input parameter.

<ZeroCouponBond>
  <transactionBuilder>ZeroCouponBondBuilder</transactionBuilder>
  <transactionID>CITI.0207</transactionID>
  <tradeDate>2008-07-02</tradeDate>
  <settlementDate>2008-07-05</settlementDate>
  <calendar>target</calendar>
  <faceAmount>1000000</faceAmount>
  <maturityDate>2032-07-05</maturityDate>
  <paymentConvention>modifiedfollowing</paymentConvention>
</ZeroCouponBond>

Needless to say, one should also add all required new conversion methods from their string presentation to QuantLib objects into previous module. The original C# implementation can be found in here. Thanks for reading my blog.
-Mike

QuantLib-Python: Hull-White one-factor model calibration

This Python program is presenting the process of calibrating Hull-White One-factor interest rate model to a given set of Swaption volatilities. In the example program, I have used exactly the same data as used in the book QuantLib Python Cookbook by G. Balaraman and L. Ballabio in the Chapter 14 (Short Interest Rate Model Calibration) and corresponding results are perfectly replicated here.

Thanks for reading my blog.
-Mike

from QuantLib import *
import numpy as Numpy

# hard-coded data
def CreateSwaptionVolatilityList():
    vol = []
    vol.append(0.1148)
    vol.append(0.1108)
    vol.append(0.1070)
    vol.append(0.1021)
    vol.append(0.1000)
    return vol

class ModelCalibrator:
    def __init__(self, endCriteria):        
        self.endCriteria = endCriteria
        self.helpers = []

    def AddCalibrationHelper(self, helper):
        self.helpers.append(helper)

    def Calibrate(self, model, engine, curve, fixedParameters):
        # assign pricing engine to all calibration helpers
        for i in range(len(self.helpers)):
            self.helpers[i].setPricingEngine(engine)
        method = LevenbergMarquardt()
        if(len(fixedParameters) == 0):
            model.calibrate(self.helpers, method, self.endCriteria)
        else:
            model.calibrate(self.helpers, method, self.endCriteria,
                NoConstraint(), [], fixedParameters)

# general parameters
tradeDate = Date(15, February, 2002)
Settings.instance().evaluationDate = tradeDate
settlementDate = Date(19, February, 2002)
calendar = TARGET()
dayCounter = Actual360()

# create market data: term structure and diagonal volatilities
curveHandle = YieldTermStructureHandle(FlatForward(settlementDate, 0.04875825, Actual365Fixed()))
vol = CreateSwaptionVolatilityList()

# create calibrator object
endCriteria = EndCriteria(10000, 100, 0.000001, 0.00000001, 0.00000001)
calibrator = ModelCalibrator(endCriteria)

# add swaption helpers to calibrator
for i in range(len(vol)):
    t = i + 1; tenor = len(vol) - i    
    helper = SwaptionHelper(
        Period(t, Years), 
        Period(tenor, Years), 
        QuoteHandle(SimpleQuote(vol[i])), 
        USDLibor(Period(3, Months), curveHandle), 
        Period(1, Years), 
        dayCounter, 
        dayCounter, 
        curveHandle)    
    calibrator.AddCalibrationHelper(helper)

# create model and pricing engine, calibrate model and print calibrated parameters
print('case 1 : calibrate all involved parameters (HW1F : reversion, sigma)')
model = HullWhite(curveHandle)
engine = JamshidianSwaptionEngine(model)
fixedParameters = []
calibrator.Calibrate(model, engine, curveHandle, fixedParameters)
print('calibrated reversion: ' + str(round(model.params()[0], 5)))
print('calibrated sigma: ' + str(round(model.params()[1], 5)))
print()

print('case 2 : calibrate sigma and fix reversion to 0.05')
model = HullWhite(curveHandle, 0.05, 0.0001)
engine = JamshidianSwaptionEngine(model)
fixedParameters = [True, False]
calibrator.Calibrate(model, engine, curveHandle, fixedParameters)
print('fixed reversion: ' + str(round(model.params()[0], 5)))
print('calibrated sigma: ' + str(round(model.params()[1], 5)))
print()

print('case 3 : calibrate reversion and fix sigma to 0.01')
model = HullWhite(curveHandle, 0.05, 0.01)
engine = JamshidianSwaptionEngine(model)
fixedParameters = [False, True]
calibrator.Calibrate(model, engine, curveHandle, fixedParameters)
print('calibrated reversion: ' + str(round(model.params()[0], 5)))
print('fixed sigma: ' + str(round(model.params()[1], 5)))
print()

QuantLib-Python: Simulating Paths for Correlated 1-D Stochastic Processes

This program, which is just an extension to my previous post, will create two correlated Geometric Brownian Motion processes, then request simulated paths from dedicated generator function and finally, plots all simulated paths to charts. For the two processes in this example program, correlation has been set to minus one and total of 20 paths has been requested for the both processes. Each path has maturity of one year, which has been divided into 90 time steps. This example can be generalized for higher dimensions by adjusting size of a given correlation matrix and corresponding lists of other parameters (nProcesses, names, spot, mue, sigma).

Thanks for reading my blog.
-Mike 

%config IPCompleter.greedy = True
from QuantLib import *
import numpy as Numpy
import matplotlib.pyplot as Matplotlib

# processArray = StochasticProcessArray (Array of correlated 1-D stochastic processes)
# timeGrid = TimeGrid object
def GenerateCorrelatedPaths(processArray, timeGrid, nPaths):
    times = []; [times.append(timeGrid[t]) for t in range(len(timeGrid))]
    generator = UniformRandomGenerator()
    nProcesses = processArray.size()
    nGridSteps = len(times) - 1 # deduct initial time (0.0)
    nSteps = nGridSteps * nProcesses
    sequenceGenerator = UniformRandomSequenceGenerator(nSteps, generator)
    gaussianSequenceGenerator = GaussianRandomSequenceGenerator(sequenceGenerator)
    multiPathGenerator = GaussianMultiPathGenerator(processArray, times, gaussianSequenceGenerator)
    paths = Numpy.zeros(shape = (nPaths, nProcesses, len(timeGrid)))

    # loop through number of paths
    for i in range(nPaths):
        # request multiPath, which contains the list of paths for each process
        multiPath = multiPathGenerator.next().value()
        # loop through number of processes
        for j in range(multiPath.assetNumber()):
            # request path, which contains the list of simulated prices for a process
            path = multiPath[j]
            # push prices to array
            paths[i, j, :] = Numpy.array([path[k] for k in range(len(path))])
    return paths

# create two 1-D stochastic processes
process = []
nProcesses = 2
correlation = -1.0
names = ['equity_1', 'equity_2']
spot = [100.0, 100.0]
mue = [0.01, 0.01]
sigma = [0.10, 0.10]
[process.append(GeometricBrownianMotionProcess(spot[i], mue[i], sigma[i])) for i in range(nProcesses)]
matrix = [[1.0, correlation], [correlation, 1.0]]

# create timegrid object and define number of paths
maturity = 1.0
nSteps = 90
timeGrid = TimeGrid(maturity, nSteps)
nPaths = 20

# create StochasticProcessArray object
# (array of correlated 1-D stochastic processes)
processArray = StochasticProcessArray(process, matrix)
# request simulated correlated paths for all processes
# result array dimensions: nPaths, nProcesses, len(timeGrid)
paths = GenerateCorrelatedPaths(processArray, timeGrid, nPaths)

# plot paths
f, subPlots = Matplotlib.subplots(nProcesses, sharex = True)
f.suptitle('Path simulations rho=' + str(correlation) + ', n=' + str(nPaths))

for i in range(nPaths):
    for j in range(nProcesses):
        subPlots[j].set_title(names[j])
        path = paths[i, j, :]
        subPlots[j].plot(timeGrid, path)

QuantLib-Python: Simulating Paths for 1-D Stochastic Processes

This simple Python program will create two 1-dimensional stochastic process objects (Hull-White 1-Factor and Geometric Brownian Motion), then request simulated paths from dedicated generator function and finally, plots all simulated paths to charts. The original C++ implementation can be found in here.

Thanks for reading my blog.
-Mike

%config IPCompleter.greedy = True
from QuantLib import *
import numpy as Numpy
import matplotlib.pyplot as Matplotlib

# process = QuantLib 1-dimensional stochastic process object
def GeneratePaths(process, maturity, nPaths, nSteps):
    generator = UniformRandomGenerator()
    sequenceGenerator = UniformRandomSequenceGenerator(nSteps, generator)
    gaussianSequenceGenerator = GaussianRandomSequenceGenerator(sequenceGenerator)
    paths = Numpy.zeros(shape = ((nPaths), nSteps + 1))
    pathGenerator = GaussianPathGenerator(process, maturity, nSteps, gaussianSequenceGenerator, False)
    for i in range(nPaths):
        path = pathGenerator.next().value()
        paths[i, :] = Numpy.array([path[j] for j in range(nSteps + 1)])
    return paths


# general parameters and objects
tradeDate = Date(23, November, 2018)
Settings_instance().evaluationDate = tradeDate
dayCounter = Actual360()
calendar = UnitedStates()
settlementDate = calendar.advance(tradeDate, 2, Days)

# common simulation-related parameters for all processes
maturity = 3.0
nPaths = 50
nSteps = int(maturity * 365)
timeGrid = Numpy.linspace(0.0, maturity, nSteps + 1)

# create HW1F model, request paths from generator
reversionSpeed = 0.05
rateVolatility = 0.0099255
r = QuoteHandle(SimpleQuote(0.01))
curve = RelinkableYieldTermStructureHandle(FlatForward(settlementDate, r, dayCounter))
HW1F = HullWhiteProcess(curve, reversionSpeed, rateVolatility)
hw1f_paths = GeneratePaths(HW1F, maturity, nPaths, nSteps)

# create GBM model, request paths from generator
initialValue = 0.01
mue = 0.01
sigma = 0.0099255
GBM = GeometricBrownianMotionProcess(initialValue, mue, sigma)
gbm_paths = GeneratePaths(GBM, maturity, nPaths, nSteps)

# plot all paths for the both processes
f, subPlots = Matplotlib.subplots(2, sharex = True)
Matplotlib.rcParams['figure.figsize'] = [16.0, 10.0]
f.suptitle('Path simulations n=' + str(nPaths))
subPlots[0].set_title('Hull-White 1-Factor')
subPlots[1].set_title('Geometric Brownian Motion')

for i in range(hw1f_paths.shape[0]):
    path = hw1f_paths[i, :] 
    subPlots[0].plot(timeGrid, path)

for i in range(gbm_paths.shape[0]):
    path = gbm_paths[i, :] 
    subPlots[1].plot(timeGrid, path)

Synthetic Basis Spread Calculation for Short-term Cross-currency Swaps

For cross-currency basis spread curves, there are usually no quotes available below 1Y time point (for 3M and 6M) and there might be a need to implement the appropriate calculation methodology for the missing data. This post will explain one possible calculation methodology based on Covered Interest Parity and presents the most relevant test results for this applied methodology.

Methodology

We will use adjusted version of traditional Covered Interest Rate Parity (CIP) for calculating synthetic quotes for short-term cross-currency basis spreads. First, in the absence of any arbitrage opportunities, CIP is assuming that the following parity will hold:



Assuming EUR/USD currency pair, we may consider the following two separate investment strategies:

1. Converting EUR amount to USD by using FX spot rate, then investing received USD amount by using USD interest rate for a given period and finally, converting resulting USD amount back to EUR by using FX forward.

2. Investing directly into EUR currency by using EUR interest rate for a given period.

As empirical evidence is strongly suggesting, CIP does not hold, since there is always some basis spread for a given currency pair for a given period. In order to take this basis spread properly into account, adjusted CIP is assuming the following parity to hold:



Where S is cross-currency basis spread for a given period. Finally, we can solve this cross-currency basis spread S for a given period by using the following analytical formula:

Test I: snapshot

As a first test for evaluating this methodology, we used formula for calculating short-term basis spread for a few liquid/major currency pairs. We used comparison data for this specific date from Bloomberg and/or ICE. We present the results in the following tables. Note, that even the curves are up to 5Y, only 3M and 6M points have been estimated by using Adjusted CIP.

For these specific liquid/major currency pairs, applied calculation methodology is able to re-produce snapshot market quotes for the short-end relatively accurately. General hypothesis is, that the more liquid/major the currency pair, the more accurate results will be produced by Adjusted CIP.

Test II: time-series

At this point in our analysis, we are interested about the consistency of the proposed methodology. We want to know, whether this methodology produces consistent results as market changes over time. As a second test, we calculated short-term basis spread time-series for EUR/USD pair since the beginning of the year of 2014. We have used Bloomberg market quotes for this period as benchmark. The following table shows time-series data for 3M cross-currency basis spread.

While there are clearly some periods with larger difference between market quote and estimated spread, applied calculation methodology is still able to re-produce market quotes consistently and relatively accurately over time.

Sensitivity

Generally, calculated basis spread estimate is highly sensitive for the interaction of specific variables (∆t, FX spot and FX forward). More specifically, as tenor ∆t will approach to zero, the relative change in spread estimate will generally increase. The following analysis was made for EUR/USD currency pair. All the other parameters were held constant, while FX spot was stressed to both directions in order to see the effect. In practice this means, that basis spread estimate might be heavily biased if, for example, market variables used in parity formula are not captured exactly at the same time.

Practical implementation notes

Assume the following parameters and market data. As we estimate spread for 3M tenor, we can use adjusted CIP formula as follows:

Note, that when estimating spread for 6M tenor, the formula above can still be used, but domestic and foreign interest rate quotes must be adjusted accordingly by using 3v6 tenor basis spreads. Since there is no explicit basis spread quotes available for 3M and 6M, 1Y quote can be used as relatively close proxy.

Finally, the following Excel/VBA function implements the calculation presented above. Thanks for reading my blog.
-Mike

Public Function GetXCCYBasisSpread( _
    ByVal FX_spot As Double, _
    ByVal FX_forward As Double, _
    ByVal BaseDepositRate As Double, _
    ByVal TermDepositRate As Double, _
    ByVal Tenor As String, _
    ByVal BasisSign As Integer, _
    ByVal ScaleRates As Boolean, _
    ByVal UseForwardPoints As Boolean, _
    ByVal ConvertToBasisPoints As Boolean) As Double
    
    ' tenor conversion from string ("3M") to double (0.25)
    ' handling for FX spot and forward, rate scaling
    Dim tenorInYears As Double
    If (VBA.UCase(Tenor) = "3M") Then tenorInYears = (3 / 12)
    If (VBA.UCase(Tenor) = "6M") Then tenorInYears = (6 / 12)
    If (UseForwardPoints) Then FX_forward = FX_spot + FX_forward
    If (ScaleRates) Then
        BaseDepositRate = BaseDepositRate / VBA.CDbl(100)
        TermDepositRate = TermDepositRate / VBA.CDbl(100)
    End If

    ' calculate cross-currency basis spread from given market data
    ' by using modified FX-IR-parity :
    ' (FX_spot / FX_forward) * (1 + R_domestic * dt) = (1 + [R_foreign + spread] * dt)
    
    ' solve the previous parity formula for spread
    Dim spread As Double
    spread = (((FX_spot / FX_forward) * (1 + TermDepositRate * tenorInYears) - 1) * _
            (1 / tenorInYears)) - BaseDepositRate
    
    If (ConvertToBasisPoints) Then spread = spread * 10000
    GetXCCYBasisSpread = spread * VBA.CDbl(BasisSign)
End Function

QuantLib-Python: Builder for Piecewise Term Structure

This post is presenting one possible implementation for Python builder class for constructing QuantLib piecewise yield term structure. The purpose is simple: one can assemble piecewise yield curve by adding arbitrary amount of different quote types and finally request handle for the curve. This curve can then be used in other part of the program. Effectively, this class is wrapping some of the required tedious administrative code work away from a client and also offering a nice compact package for all corresponding purposes. Now, it is not my intention to be object-oriented in Python world just for the sake of being object-oriented, but .. this is just a perfect place for Python class implementation. Corresponding C++ version has been presented in here.

Thanks for reading my blog.
-Mike

from QuantLib import *
import numpy as Numpy

# create piecewise yield term structure
class PiecewiseCurveBuilder:
    def __init__(self, settlementDate, dayCounter):
        self.helpers = []
        self.settlementDate = settlementDate
        self.dayCounter = dayCounter

    # 4th constructor: DepositRateHelper(Rate rate, const shared_ptr<IborIndex> &iborIndex)
    def AddDeposit(self, rate, iborIndex):
        helper = DepositRateHelper(rate, iborIndex)
        self.helpers.append(helper)

    # 4th constructor: FraRateHelper(Rate rate, Natural monthsToStart, const shared_ptr<IborIndex> &iborIndex)
    def AddFRA(self, rate, monthsToStart, iborIndex):
        helper = FraRateHelper(rate, monthsToStart, iborIndex)
        self.helpers.append(helper)
    
    # 6th constructor (Real price, const Date &iborStartDate, const ext::shared_ptr<IborIndex> &iborIndex) 
    def AddFuture(self, price, iborStartDate, iborIndex):
        helper = FuturesRateHelper(price, iborStartDate, iborIndex)
        self.helpers.append(helper)
    
    # 4th constructor: SwapRateHelper(Rate rate, const Period &tenor, const Calendar &calendar, 
    # Frequency fixedFrequency, BusinessDayConvention fixedConvention, const DayCounter &fixedDayCount, 
    # const shared_ptr<IborIndex> &iborIndex)
    def AddSwap(self, rate, periodLength, fixedCalendar, fixedFrequency, fixedConvention, fixedDayCount, floatIndex):
        helper = SwapRateHelper(rate, periodLength, fixedCalendar, fixedFrequency, 
            fixedConvention, fixedDayCount, floatIndex)
        self.helpers.append(helper)
    
    # PiecewiseYieldCurve <ZeroYield, Linear>
    def GetCurveHandle(self):  
        yieldTermStructure = PiecewiseLinearZero(self.settlementDate, self.helpers, self.dayCounter)
        return RelinkableYieldTermStructureHandle(yieldTermStructure)


# general parameters    
tradeDate = Date(4, February, 2008)
calendar = TARGET()
dayCounter = Actual360()
convention = ModifiedFollowing
settlementDate = calendar.advance(tradeDate, Period(2, Days), convention)  
swapIndex = USDLibor(Period(3, Months))
frequency = Annual

# create curve builder object
Settings.instance().evaluationDate = tradeDate
builder = PiecewiseCurveBuilder(settlementDate, dayCounter)

# cash deposit
depos = []
depos.append((0.032175, USDLibor(Period(1, Weeks))))
depos.append((0.0318125, USDLibor(Period(1, Months))))
depos.append((0.03145, USDLibor(Period(3, Months))))
[builder.AddDeposit(d[0], d[1]) for d in depos]

# futures
futures = []
futures.append((97.41, IMM.nextDate(settlementDate + Period(3, Months)), swapIndex))
futures.append((97.52, IMM.nextDate(settlementDate + Period(6, Months)), swapIndex))
futures.append((97.495, IMM.nextDate(settlementDate + Period(9, Months)), swapIndex))
futures.append((97.395, IMM.nextDate(settlementDate + Period(12, Months)), swapIndex))
[builder.AddFuture(f[0], f[1], f[2]) for f in futures]

# swaps
swaps = []
swaps.append((0.02795, Period(2, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.03035, Period(3, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.03275, Period(4, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.03505, Period(5, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.03715, Period(6, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.03885, Period(7, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.04025, Period(8, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.04155, Period(9, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.04265, Period(10, Years), calendar, frequency, convention, dayCounter, swapIndex))
swaps.append((0.04435, Period(12, Years), calendar, frequency, convention, dayCounter, swapIndex))
[builder.AddSwap(s[0], s[1], s[2], s[3], s[4], s[5], s[6]) for s in swaps]

# get relinkable curve handle from builder
curve = builder.GetCurveHandle()
curve.enableExtrapolation()

# create and print array of discount factors for every 3M up to 15Y
times = Numpy.linspace(0.0, 15.0, 61)
dfs = Numpy.array([curve.discount(t) for t in times])
print(dfs)

Wilmott : Software Interoperability in Computational Finance, Part II: Applications to Derivatives Pricing in C++11 and C#

"With an anxiety that almost amounted to agony, I collected the instruments of life around me, that I might infuse a spark of being into the lifeless thing that lay at my feet. It was already one in the morning; the rain pattered dismally against the panes, and my candle was nearly out, when, by the glimmer of the half-extinguished light, I saw the dull yellow eye of the creature open; it breathed hard, and a convulsive motion agitated its limbs."
- Mary Shelley, Frankenstein 

This technical paper, which was published in Wilmott September magazine, is the second in a series of two on the design of software systems in computational finance. We created multi-language application to value an Equity-linked product. The actual pricing of this product was performed by using QuantLib Monte Carlo framework. We used C++/CLI code as a wrapper class for native QuantLib C++ code. Then, we used C# as a front end to C++/CLI wrapper, after having constructed transaction-related parameters and market data. For flexible input data construction, a specific factory mechanism was implemented by using C# Assembly, Reflection API, and Dynamic data types. Finally, we interfaced C# client code to Excel by using Excel-DNA.

The entire source code for this application is presented in this blog post. Since the paper discussed how we implemented this application, we feel that being able to compile and run the actual code is necessary in order to understand the complete design. The original post for Equity-linked note implementation (including the original transaction term sheet) can be found in here.

The actual paper can be found in here. Thanks for reading this blog.
-Mike

C++ project

In Visual Studio, create a new C++/CLR Class Library project (QLWrapper).

At this point, pre-installed and pre-built QuantLib and Boost libraries should be available. In the case one may not have these libraries available, all required procedures for getting this part done correctly is well presented in here and here. Create references to required Boost and QuantLib header files and libraries as follows.

Next, some C++/CLI project settings needs to be modified. Disable the use of pre-compiled headers.

Update properties to suppress some specific warnings.

Optionally, update properties to suppress (almost) all the other warnings.

Next, add required C++ header and source files as presented below. Add new file for containing native C++ header file information (EquityLinkedNote.h).

// EquityLinkedNote.h
#pragma once
#include <ql/quantlib.hpp>
using namespace QuantLib;

namespace QuantLibCppNative {

 // instrument implementation for equity-linked note
 class EquityLinkedNote : public Instrument {
 public:
  // forward class declarations
  class arguments;
  class engine;

  // ctor and implementations for required base class methods
  EquityLinkedNote(Real notional, Real initialFixing, const std::vector<Date>& fixingDates,
   const std::vector<Date>& paymentDates, Real cap, Real floor);
  bool isExpired() const;

 private:
  void setupArguments(PricingEngine::arguments* args) const;
  // term sheet information
  Real notional_;
  Real initialFixing_;
  std::vector<Date> fixingDates_;
  std::vector<Date> paymentDates_;
  Real cap_;
  Real floor_;
 };

 // inner arguments class
 class EquityLinkedNote::arguments : public PricingEngine::arguments {
 public:
  void validate() const;
  Real notional;
  Real initialFixing;
  std::vector<Date> fixingDates;
  std::vector<Date> paymentDates;
  Real cap;
  Real floor;
 };

 // inner engine class
 class EquityLinkedNote::engine
  : public GenericEngine <EquityLinkedNote::arguments, EquityLinkedNote::results> {
  // base class for all further engine implementations
 };

 // path pricer implementation for equity-linked note
 class EquityLinkedNotePathPricer : public PathPricer < Path > {
 public:
  EquityLinkedNotePathPricer(Real notional, Real initialFixing, const std::vector<Date>& fixingDates,
   const std::vector<Date>& paymentDates, Real cap, Real floor, const Handle<YieldTermStructure>& curve);
  Real operator()(const Path& path) const;
 private:
  Real notional_;
  Real initialFixing_;
  std::vector<Date> fixingDates_;
  std::vector<Date> paymentDates_;
  Real cap_;
  Real floor_;
  Handle<YieldTermStructure> curve_;
 };

 // monte carlo framework engine implementation for base engine class
 template <typename RNG = PseudoRandom, typename S = Statistics>
 class EquityLinkedNoteMonteCarloPricer : public EquityLinkedNote::engine, private McSimulation <SingleVariate, RNG, S> {
 public:
  // ctor
  EquityLinkedNoteMonteCarloPricer(const boost::shared_ptr<StochasticProcess>& process,
   const Handle<YieldTermStructure>& curve, bool antitheticVariate, Size requiredSamples,
   Real requiredTolerance, Size maxSamples, BigNatural seed)
   : McSimulation<SingleVariate, RNG, S>(antitheticVariate, false), process_(process), curve_(curve),
   requiredSamples_(requiredSamples), requiredTolerance_(requiredTolerance), maxSamples_(maxSamples), seed_(seed) {
   // register observer (pricer) with observables (stochastic process, curve)
   registerWith(process_);
   registerWith(curve_);
  }

  // implementation for required base engine class method
  void calculate() const {
   // the actual simulation process will be performed within the following method
   McSimulation<SingleVariate, RNG, S>::calculate(requiredTolerance_, requiredSamples_, maxSamples_);
   this->results_.value = this->mcModel_->sampleAccumulator().mean();
   //
   if (RNG::allowsErrorEstimate) {
    this->results_.errorEstimate = this->mcModel_->sampleAccumulator().errorEstimate();
   }
   else {
    this->results_.errorEstimate = Null<Real>();
   }
  }

 private:
  // type definitions
  typedef McSimulation<SingleVariate, RNG, S> simulation;
  typedef typename simulation::path_pricer_type path_pricer_type;
  typedef typename simulation::path_generator_type path_generator_type;

  // implementation for McSimulation class virtual method
  TimeGrid timeGrid() const {
   // create time grid based on a set of given index fixing dates
   Date referenceDate = curve_->referenceDate();
   DayCounter dayCounter = curve_->dayCounter();
   std::vector<Time> fixingTimes(arguments_.fixingDates.size());
   for (Size i = 0; i != fixingTimes.size(); ++i) {
    fixingTimes[i] = dayCounter.yearFraction(referenceDate, arguments_.fixingDates[i]);
   }
   return TimeGrid(fixingTimes.begin(), fixingTimes.end());
  }

  // implementation for McSimulation class virtual method
  boost::shared_ptr<path_generator_type> pathGenerator() const {
   // create time grid and get information concerning number of simulation steps for a path
   TimeGrid grid = timeGrid();
   Size steps = (grid.size() - 1);
   // create random sequence generator and return path generator
   typename RNG::rsg_type generator = RNG::make_sequence_generator(steps, seed_);
   return boost::make_shared<path_generator_type>(process_, grid, generator, false);
  }

  // implementation for McSimulation class virtual method
  boost::shared_ptr<path_pricer_type> pathPricer() const {
   // create path pricer implementation for equity-linked note
   return boost::make_shared<EquityLinkedNotePathPricer>(arguments_.notional, arguments_.initialFixing,
    arguments_.fixingDates, arguments_.paymentDates, arguments_.cap, arguments_.floor, this->curve_);
  }

  // private data members
  boost::shared_ptr<StochasticProcess> process_;
  Handle<YieldTermStructure> curve_;
  Size requiredSamples_;
  Real requiredTolerance_;
  Size maxSamples_;
  BigNatural seed_;
 };
}

Add new file for containing native C++ source file information (EquityLinkedNote.cpp).

// EquityLinkedNote.cpp
#include "EquityLinkedNote.h"
#include <algorithm>

namespace QuantLibCppNative {
 // implementations for equity-linked note methods
 EquityLinkedNote::EquityLinkedNote(Real notional, Real initialFixing, const std::vector<Date>& fixingDates,
  const std::vector<Date>& paymentDates, Real cap, Real floor)
  : notional_(notional), initialFixing_(initialFixing), fixingDates_(fixingDates),
  paymentDates_(paymentDates), cap_(cap), floor_(floor) {
  // ctor
 }

 bool EquityLinkedNote::isExpired() const {
  Date valuationDate = Settings::instance().evaluationDate();
  // note is expired, if valuation date is greater than the last fixing date
  if (valuationDate > fixingDates_.back())
   return true;
  return false;
 }

 void EquityLinkedNote::setupArguments(PricingEngine::arguments* args) const {
  EquityLinkedNote::arguments* args_ = dynamic_cast<EquityLinkedNote::arguments*>(args);
  QL_REQUIRE(args_ != nullptr, "arguments casting error");
  args_->notional = notional_;
  args_->initialFixing = initialFixing_;
  args_->fixingDates = fixingDates_;
  args_->paymentDates = paymentDates_;
  args_->cap = cap_;
  args_->floor = floor_;
 }

 void EquityLinkedNote::arguments::validate() const {
  // checks for some general argument properties
  QL_REQUIRE(notional > 0.0, "notional must be greater than zero");
  QL_REQUIRE(initialFixing > 0.0, "initial fixing must be greater than zero");
  QL_REQUIRE(cap > floor, "cap must be greater than floor");
  // check for date consistency : all payment dates have to be included 
  // within a set of given fixing dates
  for (int i = 0; i != paymentDates.size(); ++i) {
   if (std::find(fixingDates.begin(), fixingDates.end(), paymentDates[i]) == fixingDates.end()) {
    QL_REQUIRE(false, "payment date has to be included within given fixing dates");
   }
  }
 }

 // implementations for equity-linked path pricer methods
 EquityLinkedNotePathPricer::EquityLinkedNotePathPricer(Real notional, Real initialFixing, const std::vector<Date>& fixingDates,
  const std::vector<Date>& paymentDates, Real cap, Real floor, const Handle<YieldTermStructure>& curve)
  : notional_(notional), initialFixing_(initialFixing), fixingDates_(fixingDates),
  paymentDates_(paymentDates), cap_(cap), floor_(floor), curve_(curve) {
  // ctor
 }

 // the actual pricing algorithm for a simulated path is implemented in this method
 Real EquityLinkedNotePathPricer::operator()(const Path& path) const {
  Real coupon = 0.0;
  Real cumulativeCoupon = 0.0;
  Real aggregatePathPayoff = 0.0;
  int paymentDateCounter = 0;

  // loop through fixing dates
  for (int i = 1; i != fixingDates_.size(); ++i) {
   // calculate floating coupon, based on simulated index fixing values
   coupon = std::min(path.at(i) / path.at(i - 1) - 1, cap_);
   // add floating coupon to cumulative coupon
   cumulativeCoupon += coupon;

   // calculate period payoff for each payment date
   if (fixingDates_[i] == paymentDates_[paymentDateCounter]) {
    // calculate discounted payoff for current period, add value to aggregate path payoff
    aggregatePathPayoff += std::max(cumulativeCoupon, floor_) * notional_ * curve_->discount(fixingDates_[i]);
    // re-initialize cumulative coupon to zero, look for the next payment date
    cumulativeCoupon = 0.0;
    paymentDateCounter++;
   }
  }
  return aggregatePathPayoff;
 }
}

Update the existing file to contain managed C++ header file information (QLWrapper.h).

// QLWrapper.h
#pragma once
#include "EquityLinkedNote.h"
using namespace System;
using namespace System::Collections::Generic;

namespace QLWrapper {

 // C++/CLI wrapper class
 public ref class EquityLinkedNote {
 
 public:
  EquityLinkedNote(double notional, double cap, double floor, DateTime transactionDate, 
   int settlementDays, String^ calendar, String^ dayCountConvention, int requiredSamples, 
   int seed, List<DateTime>^ fixingDates, List<DateTime>^ paymentDates, 
   double initialFixing, double volatility, Dictionary<DateTime, double>^ curve);
  !EquityLinkedNote();
  ~EquityLinkedNote();
  double PV();
 
 private:
  // note : class members as native pointers
  QuantLibCppNative::EquityLinkedNote* note;
  QuantLibCppNative::EquityLinkedNoteMonteCarloPricer<PseudoRandom, Statistics>* pricer;
 };
}

namespace QuantLibConversions {
 
 // one static class for all QuantLib-related type conversions
 public ref class Convert abstract sealed {
 public:

  // convert System.String to QuantLib.DayCounter class
  static DayCounter ToDayCounter(String^ dayCounterString);

  // convert System.DateTime to QuantLib.Date class
  static Date ToDate(DateTime dateTime);

  // convert System.String to QuantLib.Calendar class
  static Calendar ToCalendar(String^ calendarString);

  // convert System.String to QuantLib.BusinessDayConvention enumerator
  static BusinessDayConvention ToBusinessDayConvention(String^ businessDayConventionString);

 };
}

Update the existing file to contain managed C++ source file information (QLWrapper.cpp).

// QLWrapper.cpp
#pragma once
#include "QLWrapper.h"

namespace QLWrapper {
 using QL = QuantLibConversions::Convert;

 EquityLinkedNote::EquityLinkedNote(double notional_, double cap_, double floor_,
  DateTime transactionDate_, int settlementDays_, String^ calendar_,
  String^ dayCountConvention_, int requiredSamples_, int seed_,
  List<DateTime>^ fixingDates_, List<DateTime>^ paymentDates_,
  double initialFixing_, double volatility_, Dictionary<DateTime, double>^ curve_) {

  // create QL calendar from a given string
  Calendar calendar = QL::ToCalendar(calendar_);

  // create QL daycounter from a given string
  DayCounter dayCountConvention = QL::ToDayCounter(dayCountConvention_);

  Date transactionDate = QL::ToDate(transactionDate_);
  Date settlementDate = calendar.advance(transactionDate, Period(settlementDays_, Days));
  Settings::instance().evaluationDate() = settlementDate;

  // import fixing dates to std::vector
  std::vector<Date> fixingDates;
  for each (System::DateTime dt in fixingDates_) {
   fixingDates.push_back(QL::ToDate(dt));
  }

  // import payment dates to std::vector
  std::vector<Date> paymentDates;
  for each (System::DateTime dt in paymentDates_) {
   paymentDates.push_back(QL::ToDate(dt));
  }

  // create structured equity-linked note
  note = new QuantLibCppNative::EquityLinkedNote(notional_, initialFixing_, fixingDates, paymentDates, cap_, floor_);

  // create std::vector containers for dates and discount factors
  std::vector<Date> maturityDates;
  std::vector<double> discountFactors;
  for each (KeyValuePair<DateTime, double> kvp in curve_) {
   maturityDates.push_back(QL::ToDate(kvp.Key));
   discountFactors.push_back(kvp.Value);
  }

  // create valuation curve from a set of given discount factors
  auto riskFreeRateTermStructure = boost::make_shared<InterpolatedDiscountCurve<LogLinear>>
   (maturityDates, discountFactors, dayCountConvention, calendar);
  Handle<YieldTermStructure> riskFreeRateTermStructureHandle(riskFreeRateTermStructure);

  // create stochastic process
  Handle<Quote> initialFixingHandle(boost::shared_ptr<Quote>(new SimpleQuote(initialFixing_)));
  auto volatilityQuote = boost::make_shared<SimpleQuote>(volatility_);
  Handle<Quote> volatilityHandle(volatilityQuote);
  Handle<BlackVolTermStructure> volatilityTermStructureHandle(boost::shared_ptr<BlackVolTermStructure>
   (new BlackConstantVol(settlementDays_, calendar, volatilityHandle, dayCountConvention)));
  auto process = boost::make_shared<BlackScholesProcess>(initialFixingHandle, riskFreeRateTermStructureHandle, volatilityTermStructureHandle);

  // create pricer instance
  pricer = new QuantLibCppNative::EquityLinkedNoteMonteCarloPricer<PseudoRandom, Statistics>
   (process, riskFreeRateTermStructureHandle, false, static_cast<Size>(requiredSamples_),
   Null<Real>(), static_cast<Size>(requiredSamples_), static_cast<BigNatural>(seed_));

  // assign pricer to instrument
  // note : cast pricer from native pointer to boost shared pointer
  note->setPricingEngine(static_cast<boost::shared_ptr<
   QuantLibCppNative::EquityLinkedNoteMonteCarloPricer<PseudoRandom, Statistics>>>(pricer));
 }
 EquityLinkedNote::!EquityLinkedNote() {
  delete note;
  delete pricer;
 }
 EquityLinkedNote::~EquityLinkedNote() {
  this->!EquityLinkedNote();
 }
 double EquityLinkedNote::PV() {
  return note->NPV();
 }
}

namespace QuantLibConversions {

 DayCounter Convert::ToDayCounter(String^ dayCounterString) {
  if (dayCounterString->ToUpper() == "ACTUAL360") return Actual360();
  if (dayCounterString->ToUpper() == "THIRTY360") return Thirty360();
  if (dayCounterString->ToUpper() == "ACTUALACTUAL") return ActualActual();
  if (dayCounterString->ToUpper() == "BUSINESS252") return Business252();
  if (dayCounterString->ToUpper() == "ACTUAL365NOLEAP") return Actual365NoLeap();
  if (dayCounterString->ToUpper() == "ACTUAL365FIXED") return Actual365Fixed();
  // requested day counter not found, throw exception
  throw gcnew System::Exception("undefined daycounter");
 }

 Date Convert::ToDate(DateTime dateTime) {
  // Date constructor using Excel dateserial
  return Date(dateTime.ToOADate());
 }

 Calendar Convert::ToCalendar(String^ calendarString) {
  if (calendarString->ToUpper() == "ARGENTINA.MERVAL") return Argentina(Argentina::Market::Merval);
  if (calendarString->ToUpper() == "AUSTRALIA") return Australia();
  if (calendarString->ToUpper() == "BRAZIL.EXCHANGE") return Brazil(Brazil::Market::Exchange);
  if (calendarString->ToUpper() == "BRAZIL.SETTLEMENT") return Brazil(Brazil::Market::Settlement);
  if (calendarString->ToUpper() == "CANADA.SETTLEMENT") return Canada(Canada::Market::Settlement);
  if (calendarString->ToUpper() == "CANADA.TSX") return Canada(Canada::Market::TSX);
  if (calendarString->ToUpper() == "CHINA.IB") return China(China::Market::IB);
  if (calendarString->ToUpper() == "CHINA.SSE") return China(China::Market::SSE);
  if (calendarString->ToUpper() == "CZECHREPUBLIC.PSE") return CzechRepublic(CzechRepublic::Market::PSE);
  if (calendarString->ToUpper() == "DENMARK") return Denmark();
  if (calendarString->ToUpper() == "FINLAND") return Finland();
  if (calendarString->ToUpper() == "GERMANY.SETTLEMENT") return Germany(Germany::Market::Settlement);
  if (calendarString->ToUpper() == "GERMANY.FRANKFURTSTOCKEXCHANGE") return Germany(Germany::Market::FrankfurtStockExchange);
  if (calendarString->ToUpper() == "GERMANY.XETRA") return Germany(Germany::Market::Xetra);
  if (calendarString->ToUpper() == "GERMANY.EUREX") return Germany(Germany::Market::Eurex);
  if (calendarString->ToUpper() == "GERMANY.EUWAX") return Germany(Germany::Market::Euwax);
  if (calendarString->ToUpper() == "HONGKONG.HKEX") return HongKong(HongKong::Market::HKEx);
  if (calendarString->ToUpper() == "INDIA.NSE") return India(India::Market::NSE);
  if (calendarString->ToUpper() == "INDONESIA.BEJ") return Indonesia(Indonesia::Market::BEJ);
  if (calendarString->ToUpper() == "INDONESIA.IDX") return Indonesia(Indonesia::Market::IDX);
  if (calendarString->ToUpper() == "INDONESIA.JSX") return Indonesia(Indonesia::Market::JSX);
  if (calendarString->ToUpper() == "ISRAEL.SETTLEMENT") return Israel(Israel::Market::Settlement);
  if (calendarString->ToUpper() == "ISRAEL.TASE") return Israel(Israel::Market::TASE);
  if (calendarString->ToUpper() == "ITALY.EXCHANGE") return Italy(Italy::Market::Exchange);
  if (calendarString->ToUpper() == "ITALY.SETTLEMENT") return Italy(Italy::Market::Settlement);
  if (calendarString->ToUpper() == "JAPAN") return Japan();
  if (calendarString->ToUpper() == "MEXICO.BMV") return Mexico(Mexico::Market::BMV);
  if (calendarString->ToUpper() == "NEWZEALAND") return NewZealand();
  if (calendarString->ToUpper() == "NORWAY") return Norway();
  if (calendarString->ToUpper() == "POLAND") return Poland();
  if (calendarString->ToUpper() == "ROMANIA") return Romania();
  if (calendarString->ToUpper() == "RUSSIA.MOEX") return Russia(Russia::Market::MOEX);
  if (calendarString->ToUpper() == "RUSSIA.SETTLEMENT") return Russia(Russia::Market::Settlement);
  if (calendarString->ToUpper() == "SAUDIARABIA.TADAWUL") return SaudiArabia(SaudiArabia::Market::Tadawul);
  if (calendarString->ToUpper() == "SINGAPORE.SGX") return Singapore(Singapore::Market::SGX);
  if (calendarString->ToUpper() == "SLOVAKIA.BSSE") return Slovakia(Slovakia::Market::BSSE);
  if (calendarString->ToUpper() == "SOUTHAFRICA") return SouthAfrica();
  if (calendarString->ToUpper() == "SOUTHKOREA.KRX") return SouthKorea(SouthKorea::Market::KRX);
  if (calendarString->ToUpper() == "SOUTHKOREA.SETTLEMENT") return SouthKorea(SouthKorea::Market::Settlement);
  if (calendarString->ToUpper() == "SWEDEN") return Sweden();
  if (calendarString->ToUpper() == "SWITZERLAND") return Switzerland();
  if (calendarString->ToUpper() == "TAIWAN.TSEC") return Taiwan(Taiwan::Market::TSEC);
  if (calendarString->ToUpper() == "TARGET") return TARGET();
  if (calendarString->ToUpper() == "TURKEY") return Turkey();
  if (calendarString->ToUpper() == "UKRAINE.USE") return Ukraine(Ukraine::Market::USE);
  if (calendarString->ToUpper() == "UNITEDKINGDOM.EXCHANGE") return UnitedKingdom(UnitedKingdom::Market::Exchange);
  if (calendarString->ToUpper() == "UNITEDKINGDOM.METALS") return UnitedKingdom(UnitedKingdom::Market::Metals);
  if (calendarString->ToUpper() == "UNITEDKINGDOM.SETTLEMENT") return UnitedKingdom(UnitedKingdom::Market::Settlement);
  if (calendarString->ToUpper() == "UNITEDSTATES.GOVERNMENTBOND") return UnitedStates(UnitedStates::Market::GovernmentBond);
  if (calendarString->ToUpper() == "UNITEDSTATES.LIBORIMPACT") return UnitedStates(UnitedStates::Market::LiborImpact);
  if (calendarString->ToUpper() == "UNITEDSTATES.NERC") return UnitedStates(UnitedStates::Market::NERC);
  if (calendarString->ToUpper() == "UNITEDSTATES.NYSE") return UnitedStates(UnitedStates::Market::NYSE);
  if (calendarString->ToUpper() == "UNITEDSTATES.SETTLEMENT") return UnitedStates(UnitedStates::Market::Settlement);
  // requested calendar not found, throw exception
  throw gcnew System::Exception("undefined calendar");
 }

 BusinessDayConvention Convert::ToBusinessDayConvention(String^ businessDayConventionString) {
  if (businessDayConventionString->ToUpper() == "FOLLOWING") return BusinessDayConvention::Following;
  if (businessDayConventionString->ToUpper() == "HALFMONTHMODIFIEDFOLLOWING") return BusinessDayConvention::HalfMonthModifiedFollowing;
  if (businessDayConventionString->ToUpper() == "MODIFIEDFOLLOWING") return BusinessDayConvention::ModifiedFollowing;
  if (businessDayConventionString->ToUpper() == "MODIFIEDPRECEDING") return BusinessDayConvention::ModifiedPreceding;
  if (businessDayConventionString->ToUpper() == "NEAREST") return BusinessDayConvention::Nearest;
  if (businessDayConventionString->ToUpper() == "PRECEDING") return BusinessDayConvention::Preceding;
  if (businessDayConventionString->ToUpper() == "UNADJUSTED") return BusinessDayConvention::Unadjusted;
  // requested business day convention not found, throw exception
  throw gcnew System::Exception("undefined business day convention");
 }
}

After this, one should be able to build this C++ project successfully.

C# project

In the first stage, a simple console application is implemented, in order to quickly test the core C++ program from C# client program. Add new C# console project (CsClient). Then, add file containing C# tester program.

using System;
using System.Collections.Generic;

namespace CsClient {
    class Program {
        static void Main(string[] args) {
            try {
                double notional = 1000000.0;
                double initialFixing = 3662.18;
                double volatility = 0.16;
                double cap = 0.015;
                double floor = 0.0;
                DateTime transactionDate = new DateTime(2017, 10, 30);
                int settlementDays = 2;
                string calendar = "TARGET";
                string dayCountConvention = "ACTUAL360";
                int requiredSamples = 1000;
                int seed = 0;
                List<DateTime> fixingDates = new List<DateTime>() {
                    new DateTime(2017, 11, 30), new DateTime(2017, 12, 30), new DateTime(2018, 1, 30), 
                    new DateTime(2018, 2, 28), new DateTime(2018, 3, 30), new DateTime(2018, 4, 30), 
                    new DateTime(2018, 5, 30), new DateTime(2018, 6, 30), new DateTime(2018, 7, 30), 
                    new DateTime(2018, 8, 30), new DateTime(2018, 9, 30), new DateTime(2018, 10, 30), 
                    new DateTime(2018, 11, 30), new DateTime(2018, 12, 30), new DateTime(2019, 1, 30), 
                    new DateTime(2019, 2, 28), new DateTime(2019, 3, 30), new DateTime(2019, 4, 30), 
                    new DateTime(2019, 5, 30), new DateTime(2019, 6, 30), new DateTime(2019, 7, 30), 
                    new DateTime(2019, 8, 30), new DateTime(2019, 9, 30), new DateTime(2019, 10, 30), 
                    new DateTime(2019, 11, 30), new DateTime(2019, 12, 30), new DateTime(2020, 1, 30), 
                    new DateTime(2020, 2, 29), new DateTime(2020, 3, 30), new DateTime(2020, 4, 30), 
                    new DateTime(2020, 5, 30), new DateTime(2020, 6, 30), new DateTime(2020, 7, 30), 
                    new DateTime(2020, 8, 30), new DateTime(2020, 9, 30), new DateTime(2020, 10, 30)
                };
                List<DateTime> paymentDates = new List<DateTime>() {
                    new DateTime(2018, 10, 30), new DateTime(2019, 10, 30), new DateTime(2020, 10, 30)
                };
                Dictionary<DateTime, double> curve = new Dictionary<DateTime, double>() {
                    { new DateTime(2017, 10, 30), 1.0 }, { new DateTime(2018, 10, 30), 0.98 },
                    { new DateTime(2019, 10, 30), 0.96 }, { new DateTime(2020, 10, 30), 0.92 },
                    { new DateTime(2021, 10, 30), 0.85 }
                };
                QLWrapper.EquityLinkedNote wrapperNote = new QLWrapper.EquityLinkedNote(notional, cap, floor, transactionDate, settlementDays,
                    calendar, dayCountConvention, requiredSamples, seed, fixingDates, paymentDates, initialFixing, volatility, curve);
                double pv = wrapperNote.PV();
                Console.WriteLine(pv.ToString());
                GC.SuppressFinalize(wrapperNote);
            }
            catch (Exception e) {
                Console.WriteLine(e.Message);
            }
        }
    }
}

Set C# project as StartUp project and add reference to C++ project (QLWrapper).

At the moment, C# console client is using managed C++ code, which is wrapping native C++ code, which uses QuantLib C++ library. At this point, build should be successfully completed and previous C# program should return PV for equity-linked note.

Configurations and Factories

In order to get rid of all hard-coded market data and transaction parameters in previous C# client, configurations and data factories will be implemented. Next, implement the following XML configuration file. Remember to define the correct paths for XML and Access files into this configuration file.

<configurations>
     <XMLFilePathName>..\EquityLinkedNote.xml</XMLFilePathName>
    <connectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\Market.accdb;Jet OLEDB:Database Password=MyDbPassword;</connectionString>
</configurations>

Then, implement the following XML presentation for Equity-linked note transaction.

<EquityLinkedNote>
  <notional>1000000.0</notional>
  <cap>0.015</cap>
  <floor>0.0</floor>
  <transactionDate>2017-10-30T00:00:00</transactionDate>
  <settlementDays>2</settlementDays>
  <calendar>TARGET</calendar>
  <dayCountConvention>ACTUAL360</dayCountConvention>
  <requiredSamples>1000</requiredSamples>
  <seed>0</seed>
  <fixingDates>
    <dateTime>2017-11-30T00:00:00</dateTime>    <dateTime>2017-12-30T00:00:00</dateTime>
    <dateTime>2018-01-30T00:00:00</dateTime>    <dateTime>2018-02-28T00:00:00</dateTime>
    <dateTime>2018-03-30T00:00:00</dateTime>    <dateTime>2018-04-30T00:00:00</dateTime>
    <dateTime>2018-05-30T00:00:00</dateTime>    <dateTime>2018-06-30T00:00:00</dateTime>
    <dateTime>2018-07-30T00:00:00</dateTime>    <dateTime>2018-08-30T00:00:00</dateTime>
    <dateTime>2018-09-30T00:00:00</dateTime>    <dateTime>2018-10-30T00:00:00</dateTime>
    <dateTime>2018-11-30T00:00:00</dateTime>    <dateTime>2018-12-30T00:00:00</dateTime>
    <dateTime>2019-01-30T00:00:00</dateTime>    <dateTime>2019-02-28T00:00:00</dateTime>
    <dateTime>2019-03-30T00:00:00</dateTime>    <dateTime>2019-04-30T00:00:00</dateTime>
    <dateTime>2019-05-30T00:00:00</dateTime>    <dateTime>2019-06-30T00:00:00</dateTime>
    <dateTime>2019-07-30T00:00:00</dateTime>    <dateTime>2019-08-30T00:00:00</dateTime>
    <dateTime>2019-09-30T00:00:00</dateTime>    <dateTime>2019-10-30T00:00:00</dateTime>
    <dateTime>2019-11-30T00:00:00</dateTime>    <dateTime>2019-12-30T00:00:00</dateTime>
    <dateTime>2020-01-30T00:00:00</dateTime>    <dateTime>2020-02-29T00:00:00</dateTime>
    <dateTime>2020-03-30T00:00:00</dateTime>    <dateTime>2020-04-30T00:00:00</dateTime>
    <dateTime>2020-05-30T00:00:00</dateTime>    <dateTime>2020-06-30T00:00:00</dateTime>
    <dateTime>2020-07-30T00:00:00</dateTime>    <dateTime>2020-08-30T00:00:00</dateTime>
    <dateTime>2020-09-30T00:00:00</dateTime>    <dateTime>2020-10-30T00:00:00</dateTime>
  </fixingDates>
  <paymentDates>
    <dateTime>2018-10-30T00:00:00</dateTime>
    <dateTime>2019-10-30T00:00:00</dateTime>
    <dateTime>2020-10-30T00:00:00</dateTime>
  </paymentDates>
</EquityLinkedNote>

Next, prepare MS Access database (name: Market.accdb) for containing all required market information. For the reasons of convenience we use simple MS Access database to host market data. However, extending this code for more realistic settings (for example, SQL Server) should not pose difficulties for an experienced developer.

Add C# file containing abstraction for EquityLinkedNote and required factories for XML transaction and market data.

// EquityLinkedNote.cs
using System;
using System.Collections.Generic;
using System.Xml.Serialization;
using System.IO;
using System.Data;
using System.Data.OleDb;

namespace CsClient
{
    // interface for all factory classes
    public interface IFactory {
        // return any type
        dynamic Create();
    }

    // XML factory : create equity-linked note from XML file
    public class XMLNoteFactory : IFactory {
        public XMLNoteFactory() {
            // default ctor 
        }
        public dynamic Create() {
            
            // de-serialize equity-linked note object from XML file
            XmlSerializer serializer = new XmlSerializer(typeof(EquityLinkedNote));
            string XMLFilePathName = @CsClient.configurations.SelectSingleNode("configurations/XMLFilePathName").InnerText;
            EquityLinkedNote note = null;
            FileStream stream = File.OpenRead(XMLFilePathName);
            note = (EquityLinkedNote)serializer.Deserialize(stream);
            return note;
        }
    }

    // SQL database factory : create all required market data from database tables
    public class SQLMarketFactory : IFactory {
        public SQLMarketFactory() {
            // default ctor 
        }
        public dynamic Create() {

            // create connection string and SQL queries
            string connectionString = @CsClient.configurations.SelectSingleNode("configurations/connectionString").InnerText;

            // create and open connection
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();

            // create adapter, request curve data
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from Curve", connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset, "curve");

            // update command, request volatility
            adapter.SelectCommand.CommandText = "select * from Volatility";
            adapter.Fill(dataset, "volatility");

            // update command, request index fixing
            adapter.SelectCommand.CommandText = "select * from Fixings";
            adapter.Fill(dataset, "initialFixing");

            // extract data to curve dictionary
            Dictionary<DateTime, double> curve = new Dictionary<DateTime, double>();
            for (int i = 0; i < dataset.Tables["Curve"].Rows.Count; i++) {
                curve.Add(
                    dataset.Tables["curve"].Rows[i].Field<DateTime>("MaturityDate"), 
                    dataset.Tables["curve"].Rows[i].Field<double>("DiscountFactor"));
            }

            // extract volatility and initial fixing
            double volatility = dataset.Tables["volatility"].Rows[0].Field<double>("Rate");
            double initialFixing = dataset.Tables["initialFixing"].Rows[0].Field<double>("Rate");

            // pack all market data into tuple
            Tuple<Dictionary<DateTime, double>, double, double> market = 
                new Tuple<Dictionary<DateTime,double>,double,double>(curve, volatility, initialFixing);

            // dispose adapter, close connection, return tuple containing market
            adapter.Dispose();
            connection.Close();
            return market;
        }
    }

    // Equity-linked note class : container class for term sheet parameters
    public class EquityLinkedNote {

        public double notional;
        public double cap;
        public double floor;
        public DateTime transactionDate;
        public int settlementDays;
        public string calendar;
        public string dayCountConvention;
        public int requiredSamples;
        public int seed;
        public List<DateTime> fixingDates = null;
        public List<DateTime> paymentDates = null;

        EquityLinkedNote() {
            // default ctor required for XML de-serialization
        }

        public EquityLinkedNote(double notional_, double cap_, double floor_, DateTime transactionDate_, 
            int settlementDays_, string calendar_, string dayCountConvention_, int requiredSamples_, 
            int seed_, List<DateTime> fixingDates_, List<DateTime> paymentDates_) {
            
            notional = notional_;
            cap = cap_;
            floor = floor_;
            transactionDate = transactionDate_;
            settlementDays = settlementDays_;
            calendar = calendar_;
            dayCountConvention = dayCountConvention_;
            requiredSamples = requiredSamples_;
            seed = seed_;
            fixingDates = fixingDates_;
            paymentDates = paymentDates_;
        }
    }
}

Finally, update C# client for creating transaction parameters and market data from configured XML files. Remember to update path to configuration XML file.

// Program.cs
using System;
using System.Collections.Generic;
using System.Xml;

namespace CsClient {
    public static class CsClient {
        public static XmlDocument configurations = null;
        private static string configurationFilePath =
            @"..\configurations.xml";
        private static double result = 0.0;

        public static void Main() {
            try {
                // create configurations
                configurations = new XmlDocument();
                configurations.Load(configurationFilePath);

                // create and use factory to create equity-linked note object from XML file
                IFactory noteFactory = new XMLNoteFactory();
                EquityLinkedNote note = noteFactory.Create();

                // use factory to create market data from database
                IFactory marketDataFactory = new SQLMarketFactory();
                Tuple<Dictionary<DateTime, double>, double, double> market = marketDataFactory.Create();
                Dictionary<DateTime, double> curveData = market.Item1;
                double volatility = market.Item2;
                double initialFixing = market.Item3;

                // use C++/CLI wrapper : create equity-linked note instance
                QLWrapper.EquityLinkedNote wrapperNote =
                    new QLWrapper.EquityLinkedNote(note.notional, note.cap, note.floor,
                        note.transactionDate, note.settlementDays, note.calendar, note.dayCountConvention,
                        note.requiredSamples, note.seed, note.fixingDates, note.paymentDates, initialFixing, volatility, curveData);

                // use C++/CLI wrapper : value equity-linked note
                result = wrapperNote.PV();
                Console.WriteLine(result.ToString());
                GC.SuppressFinalize(wrapperNote);
            }
            catch (Exception e) {
                Console.WriteLine(e.Message);
            }
        }
    }
}

At this point, build should be successfully completed and our new C# client program should return PV for equity-linked note. All transaction parameters and required market data can now be modified, without touching the actual program.

Excel interfacing and Factory of Factories

We will be using Excel as input-output platform for our C# client program. For interfacing task we will use Excel-DNA. Dedicated blog post on how to implement this, can be found in here. However, for the sake of completeness, the whole procedure is also explained below on a detailed level.

First, prepare Excel GUI carefully, by following detailed instructions (range names) given in the screenshot below. For flexible transaction data construction, a specific factory mechanism is implemented by using C# Assembly, Reflection API, and Dynamic data types. Note, that the actual factory is created inside C# program, based on the string given in cell D2 (namespace.class: CsClient.XMLNoteFactory or CsClient.ExcelNoteFactory).

Add reference to Excel-DNA library (Project - Add reference - Browse - \\ExcelDna.Integration.dll) and click OK. This dll file is inside the distribution folder what has been downloaded from Excel-DNA website. From the properties of this reference, set Copy Local to be False.

Add new file as text file to project (Project - Add new item - Text file) and name it to be CsClient.dna. CopyPaste the following xml code into this file.

<DnaLibrary Name="CsClient" RuntimeVersion="v4.0">
  <ExternalLibrary Path="CsClient.dll" />
</DnaLibrary>

From the properties of this dna file, set Copy to Output Directory to be Copy if newer.

Next, from the downloaded Excel-DNA folder (Distribution), copy ExcelDna.xll file into your project folder and rename it to be CsClient.xll. Then, add this xll file into your current project (Project - Add existing item). At this point, it might be that you do not see anything else, except cs files on this window. From drop down box on the bottom right corner of this window, select All files and you should see ExcelInterface.xll file what we just pasted into this ExcelInterface folder. Select this file and press Add. Finally, from the properties of this xll file, set Copy to Output Directory to be Copy if newer. At this point, we are done with C# and Excel-DNA.

Next, update EquityLinkedNote file for containing Excel factory for Equity-linked note.

// EquityLinkedNote.cs
using System;
using System.Collections.Generic;
using System.Xml.Serialization;
using System.IO;
using System.Data;
using System.Data.OleDb;
using ExcelDna.Integration;

namespace CsClient {
    // interface for all factory classes
    public interface IFactory {
        // return any type
        dynamic Create();
    }

    // Excel factory : create equity-linked note from Excel named ranges
    public class ExcelNoteFactory : IFactory {
        public ExcelNoteFactory() {
            // default ctor 
        }
        public dynamic Create() {

            // create Excel application
            dynamic Excel = ExcelDnaUtil.Application;

            // create all scalar parameters
            double notional = (double)Excel.Range["notional"].Value2;
            double cap = (double)Excel.Range["cap"].Value2;
            double floor = (double)Excel.Range["floor"].Value2;
            DateTime transactionDate = DateTime.FromOADate((double)Excel.Range["transactionDate"].Value2);
            int settlementDays = (int)Excel.Range["settlementDays"].Value2;
            string calendar = (string)Excel.Range["calendar"].Value2;
            string dayCountConvention = (string)Excel.Range["dayCountConvention"].Value2;
            int requiredSamples = (int)Excel.Range["requiredSamples"].Value2;
            int seed = (int)Excel.Range["seed"].Value2;

            // create list of fixing dates
            List<DateTime> fixingDates = new List<DateTime>();
            dynamic fixingDatesArray = Excel.Range["fixingDates"].Value2;
            for (int i = 0; i != fixingDatesArray.GetUpperBound(0); ++i) {
                fixingDates.Add(DateTime.FromOADate(Convert.ToDouble(fixingDatesArray.GetValue(i + 1, 1))));
            }

            // create list of payment dates
            List<DateTime> paymentDates = new List<DateTime>();
            dynamic paymentDatesArray = Excel.Range["paymentDates"].Value2;
            for (int i = 0; i != paymentDatesArray.GetUpperBound(0); ++i) {
                paymentDates.Add(DateTime.FromOADate(Convert.ToDouble(paymentDatesArray.GetValue(i + 1, 1))));
            }

            // create equity-linked note instance
            return new EquityLinkedNote(notional, cap, floor, transactionDate, settlementDays,
                calendar, dayCountConvention, requiredSamples, seed, fixingDates, paymentDates);
        }
    }

    // XML factory : create equity-linked note from XML file
    public class XMLNoteFactory : IFactory {
        public XMLNoteFactory() {
            // default ctor 
        }
        public dynamic Create() {

            // de-serialize equity-linked note object from XML file
            XmlSerializer serializer = new XmlSerializer(typeof(EquityLinkedNote));
            string XMLFilePathName = @CsClient.configurations.SelectSingleNode("configurations/XMLFilePathName").InnerText;
            EquityLinkedNote note = null;
            FileStream stream = File.OpenRead(XMLFilePathName);
            note = (EquityLinkedNote)serializer.Deserialize(stream);
            return note;
        }
    }

    // SQL database factory : create all required market data from database tables
    public class SQLMarketFactory : IFactory {
        public SQLMarketFactory() {
            // default ctor 
        }
        public dynamic Create() {

            // create connection string and SQL queries
            string connectionString = @CsClient.configurations.SelectSingleNode("configurations/connectionString").InnerText;

            // create and open connection
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();

            // create adapter, request curve data
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from Curve", connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset, "curve");

            // update command, request volatility
            adapter.SelectCommand.CommandText = "select * from Volatility";
            adapter.Fill(dataset, "volatility");

            // update command, request index fixing
            adapter.SelectCommand.CommandText = "select * from Fixings";
            adapter.Fill(dataset, "initialFixing");

            // extract data to curve dictionary
            Dictionary<DateTime, double> curve = new Dictionary<DateTime, double>();
            for (int i = 0; i < dataset.Tables["Curve"].Rows.Count; i++) {
                curve.Add(
                    dataset.Tables["curve"].Rows[i].Field<DateTime>("MaturityDate"),
                    dataset.Tables["curve"].Rows[i].Field<double>("DiscountFactor"));
            }

            // extract volatility and initial fixing
            double volatility = dataset.Tables["volatility"].Rows[0].Field<double>("Rate");
            double initialFixing = dataset.Tables["initialFixing"].Rows[0].Field<double>("Rate");

            // pack all market data into tuple
            Tuple<Dictionary<DateTime, double>, double, double> market =
                new Tuple<Dictionary<DateTime, double>, double, double>(curve, volatility, initialFixing);

            // dispose adapter, close connection, return tuple containing market
            adapter.Dispose();
            connection.Close();
            return market;
        }
    }

    // Equity-linked note class : container class for term sheet parameters
    public class EquityLinkedNote {

        public double notional;
        public double cap;
        public double floor;
        public DateTime transactionDate;
        public int settlementDays;
        public string calendar;
        public string dayCountConvention;
        public int requiredSamples;
        public int seed;
        public List<DateTime> fixingDates = null;
        public List<DateTime> paymentDates = null;

        EquityLinkedNote() {
            // default ctor required for XML de-serialization
        }

        public EquityLinkedNote(double notional_, double cap_, double floor_, DateTime transactionDate_,
            int settlementDays_, string calendar_, string dayCountConvention_, int requiredSamples_,
            int seed_, List<DateTime> fixingDates_, List<DateTime> paymentDates_) {

            notional = notional_;
            cap = cap_;
            floor = floor_;
            transactionDate = transactionDate_;
            settlementDays = settlementDays_;
            calendar = calendar_;
            dayCountConvention = dayCountConvention_;
            requiredSamples = requiredSamples_;
            seed = seed_;
            fixingDates = fixingDates_;
            paymentDates = paymentDates_;
        }
    }
}

Then, update C# client program for consisting interface to Excel and factory of factories. Since we might need to use Windows Forms object in our program (MessageBox in Catch block), we need to create reference to System.Windows.Forms library (Project - Add reference - Assemblies - System.Windows.Forms).

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Windows.Forms;
using System.Xml;
using ExcelDna.Integration;

namespace CsClient {
    public static class CsClient {
        public static XmlDocument configurations = null;
        private static string configurationFilePath =
            @"..\configurations.xml";
        private static double result = 0.0;

        public static void Run() {
            try {
                // create Excel
                dynamic Excel = ExcelDnaUtil.Application;

                // create configurations
                configurations = new XmlDocument();
                configurations.Load(configurationFilePath);

                // get factory selection from Excel
                string assemblyFile = @configurations.SelectSingleNode("configurations/assemblyFilePathName").InnerText;
                string factorySelection = Excel.Range["factorySelection"].Value2;

                // create and use factory to create equity-linked note object
                dynamic noteFactory = LoadFactory(assemblyFile, factorySelection);
                dynamic note = noteFactory.Create();

                // use factory to create market data from database
                IFactory marketDataFactory = new SQLMarketFactory();
                Tuple<Dictionary<DateTime, double>, double, double> market = marketDataFactory.Create();
                Dictionary<DateTime, double> curveData = market.Item1;
                double volatility = market.Item2;
                double initialFixing = market.Item3;

                // use C++/CLI wrapper : create equity-linked note instance
                QLWrapper.EquityLinkedNote wrapperNote =
                    new QLWrapper.EquityLinkedNote(note.notional, note.cap, note.floor,
                        note.transactionDate, note.settlementDays, note.calendar,
                        note.dayCountConvention, note.requiredSamples, note.seed,
                        note.fixingDates, note.paymentDates, initialFixing, volatility, curveData);

                // use C++/CLI wrapper : value equity-linked note
                result = wrapperNote.PV();
                Excel.Range["pv"] = result;
                GC.SuppressFinalize(wrapperNote);
            }
            catch (Exception e) {
                MessageBox.Show(e.Message);
            }
        }

        // Load requested factory from a given assembly
        public static dynamic LoadFactory(string assemblyFile, string factoryFullName) {
            dynamic factory = null;
            Assembly assembly = Assembly.LoadFrom(assemblyFile);
            Type[] types = assembly.GetTypes();
            foreach (Type type in types) {
                if (type.FullName == factoryFullName) {
                    factory = assembly.CreateInstance(type.FullName);
                    break;
                }
            }
            return factory;
        }

    }
}

Next, update XML configuration file with path name to dll file, which is containing C# project assembly.

<configurations>
    <assemblyFilePathName>..\CsClient.dll</assemblyFilePathName>
    <XMLFilePathName>..\QLWrapper\EquityLinkedNote.xml</XMLFilePathName>
    <connectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\Market.accdb;Jet OLEDB:Database Password=MyDbPassword;</connectionString>
</configurations>

Finally, change C# project type to Class Library (Project - CsClient Properties - Application - Output Type - Class Library). At this point we are done with the program part. Open the actual Excel interface workbook, which has been created earlier. Open created CsClient.xll file in the open Excel workbook. For existing action button found in the worksheet, assign macro (name: Run) which will trigger C# program execution.

In this final application, Excel is using external data sources (MS Access, XML) and C# class library, which is using managed C++ code, which is wrapping native C++ code, which uses QuantLib C++ library.

Thanks for reading this blog.
-Mike