This document explains how to expose additional QuantLib classes to Excel. As a prerequisite it is assumed that you have already compiled QuantLibXL from source code using the Full build documented in the tutorial Build QuantLibXL From Source Code.
In this example, the QuantLib class Stock is exposed to Excel. The Visual C++ solution for this tuturial is QuantLibXL\QuantLibXL_full_vc?.sln.
1 Wrap the QuantLib Class in a QuantLibAddin Class
2 Create a New Function Category
3 Autogenerate New Source Code Files
4 Build QuantLibXL
5 Test the New Functions
6 Notes
1 Wrap the QuantLib Class in a QuantLibAddin Class
In order to store a reference to a QuantLib::Stock in the ObjectHandler repository, we implement class QuantLibAddin::Stock which derives from ObjectHandler::Object and holds a pointer to QuantLib::Stock. In fact, rather than inherit directly from ObjectHandler::Object, we derive our QuantLibAddin::Stock class from QuantLibAddin::Instrument. This allows us to invoke the Instrument interface on objects of class Stock.
Create the following two new files:
QuantLibAddin\qlo\stock.hpp QuantLibAddin\qlo\stock.cpp
Here is the code for the files:
stock.hpp:
#ifndef qla_stock_hpp
#define qla_stock_hpp
#include <qlo/baseinstruments.hpp>
#include <ql/handle.hpp>
namespace QuantLib {
class Stock;
class Quote;
}
namespace QuantLibAddin {
class Stock : public Instrument {
public:
Stock(
const boost::shared_ptr<ObjectHandler::ValueObject>& properties,
const QuantLib::Handle<QuantLib::Quote>& quote,
bool permanent);
};
}
#endif
stock.cpp:
#include <qlo/qladdindefines.hpp>
#include <qlo/stock.hpp>
#include <ql/instruments/stock.hpp>
#include <ql/quote.hpp>
namespace QuantLibAddin {
Stock::Stock(
const boost::shared_ptr<ObjectHandler::ValueObject>& properties,
const QuantLib::Handle<QuantLib::Quote>& quote,
bool permanent) : Instrument(properties, permanent) {
libraryObject_ = boost::shared_ptr<QuantLib::Stock>(
new QuantLib::Stock(quote));
}
}
Add the files to project QuantLibObjects, under folder Instruments.
2 Create a New Function Category
QuantLibAddin functions are grouped into Categories. Here we create a new category called Stock.
Edit file QuantLibAddin\gensrc\config\categories.xml and add the new category stock:
<root>
<addinCategoryNames>
<categoryName>abcd</categoryName>
<categoryName>accountingengines</categoryName>
<categoryName>assetswap</categoryName>
...
<categoryName>statistics</categoryName>
<categoryName>stock</categoryName>
<categoryName>swap</categoryName>
...
<categoryName>volatilities</categoryName>
<categoryName>volatility</categoryName>
<categoryName>quotes</categoryName>
</addinCategoryNames>
</root>
Create the following new file:
QuantLibAddin\gensrc\metadata\Functions\stock.xml
Here are the contents of the file:
<Category name='stock'>
<description>functions to construct and use Stock objects</description>
<displayName>Stock</displayName>
<xlFunctionWizardCategory>QuantLib - Financial</xlFunctionWizardCategory>
<includes>
<include>qlo/stock.hpp</include>
</includes>
<copyright>
Copyright (C) 2008 Eric Ehlers
</copyright>
<Functions>
<Constructor name='qlStock'>
<libraryFunction>Stock</libraryFunction>
<SupportedPlatforms>
<SupportedPlatform name='Excel'/>
</SupportedPlatforms>
<ParameterList>
<Parameters>
<Parameter name='Quote'>
<type>QuantLib::Quote</type>
<superType>libToHandle</superType>
<tensorRank>scalar</tensorRank>
<description>quote object IDs</description>
</Parameter>
</Parameters>
</ParameterList>
</Constructor>
</Functions>
</Category>
Add the file to project qlgensrc, under folder functions.
Above we have configured a single constructor. There is also support for member and utility functions, refer to the other XML files for examples. The tag superType determines the type of reference which is passed to the underlying QuantLib code, in this case libToHandle reflects the fact that Handle<Quote> is expected. For a summary of supported supertypes refer to the comments in file QuantLibAddin\gensrc\metadata\types\supertypes.xml.
3 Autogenerate New Source Code Files
Now we run gensrc to autogenerate the new source code corresponding to the contents of stock.xml. Build project qlgensrc. In the gensrc output, notice that 9 new files are created (irrelevant output omitted):
1>C:\..\QuantLibXL\qlxl\register\register_stock.cpp: created 1>C:\..\QuantLibXL\qlxl\functions\stock.cpp: created 1>C:\..\QuantLibAddin\qlo\valueobjects\vo_stock.hpp: created 1>C:\..\QuantLibAddin\qlo\valueobjects\vo_stock.cpp: created 1>C:\..\QuantLibAddin\qlo\serialization\create\create_stock.hpp: created 1>C:\..\QuantLibAddin\qlo\serialization\create\create_stock.cpp: created 1>C:\..\QuantLibAddin\qlo\serialization\register\serialization_stock.hpp: created 1>C:\..\QuantLibAddin\qlo\serialization\register\serialization_stock.cpp: created 1>C:\..\QuantLibAddin\Docs\auto.pages\stock.docs: created 1> 1>addin unchanged updated created total 1>=============== =========== =========== =========== =========== 1>Excel 114 2 2 118 1>Calc 23 0 0 23 1>Cpp 111 0 0 111 1>ValueObjects 98 1 2 101 1>Enumerations 3 0 0 3 1>Loop 11 0 0 11 1>Serialization 188 12 4 204 1>Doxygen 67 2 1 70 1>=============== =========== =========== =========== =========== 1>total 615 17 9 641
For purposes of this tutorial we ignore the new file for Doxygen. The other 8 files need to be added to the relevant projects as shown below:
| Add file(s) ... | To project ... | In folder ... |
| QuantLibXL/qlxl/register/register_stock.cpp | QuantLibXLStatic | register |
| QuantLibXL/qlxl/functions/stock.cpp | QuantLibXLStatic | functions |
| QuantLibAddin/qlo/valueobjects/vo_stock.*pp | QuantLibObjects | valueobjects |
| QuantLibAddin/qlo/serialization/create/create_stock.*pp | QuantLibObjects | serialization/create |
| QuantLibAddin/qlo/serialization/register/serialization_stock.*pp | QuantLibObjects | serialization/register |
4 Build QuantLibXL
Build solution QuantLibXL_full_vc?.sln, configuration Release (static runtime). The new source code files in QuantLibAddin and QuantLibXL are compiled and the binaries are linked.
The build process creates the QuantLibAddin XLL file QuantLibXL\xll\QuantLibXL-vc??-mt-s-1_22_0.xll.
5 Test the New Functions
Start Excel and load the XLL. Enter the new constructor into cell A1:
=qlStock("my_stock",1.23)
This should return the ID of the newly created object. Try invoking a member function on the object. In cell A2, enter:
=qlInstrumentNPV(A1)
This should return the value 1.23.
6 Notes
- See also project
ExampleXllStaticin solutionObjectHandler\ObjectHandler_vc?.sln. This is a hello world XLL using ObjectHandler to export an object-oriented interface (constructors and member functions) to Excel. All of the source code is hand-written, as opposed to the QuantLibXL code which is autogenerated by gensrc.ExampleXllStaticrelies on an example library calledExampleObjectswhich could be replaced by QuantLib or some third-party library.
- If you're going to rerun gensrc often, and if you're only interested in the Excel addin, then you can speed gensrc up by opening file
QuantLibAddin/gensrc/Makefile.vc
and editing line 28:#$(GENSRC_DIR)\gensrc.py -a --oh_dir=$(OH_DIR) $(GENSRC_DIR)\gensrc.py -xvels --oh_dir=$(OH_DIR)This restricts the gensrc output from "all" (including C++ and Calc) to only the modules required for QuantLibXL.