This time, I wanted to present kind of "industry standard" way to accomplish this task using "easy-to-use" Excel/C++ interfacing tool, which has been there already since 2002. XLW is an application which wraps Excel/C API into a simple C++ interface, which can then be used to customize Excel with user-defined worksheet functions. For any newcomer into this issue, it is highly recommended first to watch instructional "how-to-use" clips from project homepage. The complete workflow of using XLW wrapper is presented there from downloading to debugging. Also, AdaptiveRisk blog is presenting extremely useful stuff, well enough to get started.
cppinterface.h
For this project, I have extracted a new XLW xll template and opened corresponding solution in my Visual Studio Express 2013. The content of this header file in my current project is presented below. I have method declarations for two functions, which are both returning matrix object (XLW data structure). It is my intention to use my previous PathGenerator project, in order to fill these matrix objects with paths using desired one-factor processes.
#ifndef TEST_H #define TEST_H // #include "xlw/MyContainers.h" #include <xlw/CellMatrix.h> #include <xlw/DoubleOrNothing.h> #include <xlw/ArgList.h> #include <xlw/XlfServices.h> // using namespace xlw; // //<xlw:libraryname=XLWPathGenerator // // method for requesting vasicek paths MyMatrix // return matrix of random paths following Vasicek SDE //<xlw:volatile GetPaths_Vasicek(double t, // time to maturity double r, // current short rate double longTermRate, // long-term average rate double meanReversion, // mean reversion speed double rateVolatility // rate volatility ); // // method for requesting GBM paths MyMatrix // return matrix of random paths following Geometric Brownian Motion SDE //<xlw:volatile GetPaths_BrownianMotion(double t, // time to maturity double s, // current spot rate double rate, // risk-free rate double volatility // volatility ); // #endif
Commenting may seem a bit strange first, but the following screenshot containing Excel function argument input box may help to catch the point.
source.cpp
Implementations for two methods declared in header file are presented below. Information concerning number of time steps (for a path) and number of paths to be created are extracted from matrix dimensions using XlfServices object. After this, desired OneFactorProcess and PathGenerator objects are created. Finally, PathGenerator object is used to process a path, which will be imported into resulting matrix object (paths) and returned for the client (Excel).
#include <cppinterface.h> #include "PathGenerator.h" #pragma warning (disable : 4996) // MyMatrix GetPaths_Vasicek(double t, double r, double longTermRate, double meanReversion, double rateVolatility) { // request dimensions for calling matrix const unsigned int nPaths = XlfServices.Information.GetCallingCell().columns(); const unsigned int nSteps = XlfServices.Information.GetCallingCell().rows(); // create container for all processed paths MyMatrix paths(nSteps, nPaths); // create container for a single path to be processed MyArray path(nSteps); // // create vasicek process and path generator std::shared_ptr<MJProcess::OneFactorProcess> vasicek = std::shared_ptr<MJProcess::Vasicek>(new MJProcess::Vasicek(meanReversion, longTermRate, rateVolatility)); PathGenerator<> shortRateProcess(r, t, vasicek); // // process paths using path generator for (unsigned int i = 0; i != nPaths; ++i) { shortRateProcess(path); // import processed path into paths container for (unsigned j = 0; j != nSteps; ++j) { paths[j][i] = path[j]; } } return paths; } // MyMatrix GetPaths_BrownianMotion(double t, double s, double rate, double volatility) { // request dimensions for calling matrix const unsigned int nPaths = XlfServices.Information.GetCallingCell().columns(); const unsigned int nSteps = XlfServices.Information.GetCallingCell().rows(); // create container for all processed paths MyMatrix paths(nSteps, nPaths); // create container for a single path to be processed MyArray path(nSteps); // // create geometric brownian motion process and path generator std::shared_ptr<MJProcess::OneFactorProcess> brownianMotion = std::shared_ptr<MJProcess::GBM>(new MJProcess::GBM(rate, volatility)); PathGenerator<> equityPriceProcess(s, t, brownianMotion); // // process paths using path generator for (unsigned int i = 0; i != nPaths; ++i) { equityPriceProcess(path); // import processed path into paths container for (unsigned j = 0; j != nSteps; ++j) { paths[j][i] = path[j]; } } return paths; } //
In order to get this thing up and running, header file for PathGenerator has to be included. I have set the current XLL project as startup project. As a side, I have opened my PathGenerator project (containing header files for RandomGenerator, OneFactorProcess and PathGenerator). Since this side project is still unaccessible, it has to be linked to my current XLL project : Project - Properties - Configuration Properties - C/C++ - General - Additional Include Directories (Browse folder containing source files for the project to be linked). After completing these steps and building this project succesfully, I am finally ready to test the provided functionality in Excel.
Excel
After opening a new Excel, I need to drag-and-drop (or open from Excel) newly created xll template from my \\Projects\XLWTester\Debug folder to Excel. In my Excel (screenshot below), I have two boxes for input parameters (Vasicek, Brownian Motion) and two ranges for resulting one-factor process paths (36 steps, 15 paths). As soon as I hit F9 button, my one-factor paths will be re-created. Finally, it should be noted that the functions are both array formulas.
Finally, thanks for reading this blog. Pleasant waiting for a new year for everybody.
-Mike
No comments:
Post a Comment