Frequently Asked Questions
- 1. General
- 2. Installation
- 3. Compilation
- 4. Usage
- 5. Troubleshooting
- 6. Debugging
QuantLibAddin is an interface allowing QuantLib (an open source analytics library for quantitative finance) to be deployed to end user environments such as spreadsheets.
QuantLib is a static library of classes. Before it can be deployed to an end user, it must be wrapped in an application layer - a command line executable, a spreadsheet addin, a webserver application.
A function of the application layer is to provide a mechanism for storing QuantLib objects once they are created. Spreadsheets do not provide such a mechanism so one must be implemented by the addin. QuantLibAddin uses ObjectHandler as its object repository. The core of QuantLibAddin is a library called QuantLibObjects, which wraps QuantLib objects in a form that allows them to be stored in the ObjectHandler repository.
Additionally QuantLibAddin implements an interface, a library of functions, which the end user invokes to access the underlying QuantLib objects. For example on a spreadsheet when the user enters formula qlPiecewiseYieldCurve() into a cell, a QuantLib yield curve is instantiated in the ObjectHandler repository and a reference to that object is returned to the calling cell. QuantLibAddin supports this interface on a variety of end user platforms such as Microsoft Excel and OpenOffice.org Calc.
QuantLibXL is the Excel implementation of the QuantLibAddin interface. QuantLibXL is an Excel Addin, a C++ binary in the XLL format, allowing QuantLib functions to be accessed from cell formulas.
QuantLibAddin is the product of collaboration from many volunteers. Not all of them follow the mailing list and some questions go unanswered, sorry about that. Try writing in Italian ;-)
- If you want an installer delivering a compiled QuantLibXL Addin and documentation for use with Microsoft Excel, visit www.quantlibxl.org.
- If you want to compile QuantLibAddin/QuantLibXL from source code for Excel or other platforms, visit http://www.quantlibaddin.org/tutorials.html.
fatal error C1083: Cannot open include file: 'windows.h': No such file or directory
You need to install the Platform SDK as explained in the documentation for Visual Studio Express.
QuantLibXL can be invoked from VBA using Application.Run(). For example, below is a subroutine which calculates the price of an option. This code mimics the behavior of example spreadsheet
Sub priceEuropeanOption() On Error GoTo Catch Dim blackVolId As String Dim blackScholesId As String Dim exerciseId As String Dim payoffId As String Dim engineId As String Dim optionId As String Dim npv As Double Call Application.Run("qlSettingsSetEvaluationDate", 35930) blackVolId = Application.Run("qlBlackConstantVol", _ "blackConstantVol", 35932, "Target", 0.2, "Actual/365 (Fixed)") blackScholesId = Application.Run( _ "qlGeneralizedBlackScholesProcess", "blackScholes", _ blackVolId, 36, "Actual/365 (Fixed)", 35932, 0.06, 0) exerciseId = Application.Run("qlEuropeanExercise", _ "europeanExercise", 36297) payoffId = Application.Run("qlStrikedTypePayoff", _ "strikedTypePayoff", "Vanilla", "Put", 40) engineId = Application.Run("qlPricingEngine", _ "pricingEngine", "AE", blackScholesId) optionId = Application.Run("qlVanillaOption", _ "vanillaOption", payoffId, exerciseId) Call Application.Run("qlInstrumentSetPricingEngine", _ optionId, engineId) npv = Application.Run("qlInstrumentNPV", optionId) Debug.Print "NPV = " & npv Exit Sub Catch: Debug.Print "QuantLibXL Error: " + Err.Description MsgBox Err.Description, vbCritical, "QuantLibXL Error" End Sub
Excel versions up to and including 2003 have a limit of 30 function arguments. But there is an effective limit of 20 arguments for user defined functions. This is because the function is registered with a call to xlfRegister, which is itself limited to 30 arguments. xlfRegister uses 10 of its arguments to register the function, leaving 20 slots for the descriptions of the arguments to your addin function.
It is in fact possible to register a user defined function with up to 30 arguments, by omitting descriptions for arguments beyond the 20th. The 21st and later arguments are valid but appear in the Function Wizard with no descriptions. An attempt was made to support this trick in QuantLibXL but at present the implementation is not functional because of a bug.
Excel 2007 introduces support for user defined functions with up to 255 arguments but QuantLibXL has not yet been upgraded to take advantage of this.
When a QuantLibXL function returns #NUM, this indicates an application-level error within QuantLib (as opposed to, say, an input of the wrong type, which is captured by Excel itself and indicated with #VALUE). Enter
ohRangeRetrieveError() into another cell, take the range containing the QuantLibXL function that returned #NUM and pass that as input into
ohRangeRetrieveError() returns the actual error message string generated by QuantLib.
The QuantLibXL Framework includes VBA code which allows you to right-click on a cell containing #NUM in order to see the error. There is also a feature allowing you to select the cell containing #NUM and hit Ctrl-Shift-E to see the error message. These routines call
ohRangeRetrieveError() behind the scenes, saving you the hassle of manually entering that function into another cell.
If you enable logging and call
ohRepositoryLogAllObjects() you will get a list of all QuantLib error messages associated with all of the ranges containing #NUM in the active Excel session.
Because such a design would subvert Excel's error handling mechanism.
The design of Excel assumes that worksheet functions indicate errors with #NUM, #REF, etc. The absence of those codes is interpreted by Excel to mean that the function completed successfully. If the return value of one function is passed as input to another, and if the first function returns an error, Excel doesn't call the second function, instead it sets the second function to #REF.
Suppose function 1 returns the ID of an object, e.g. "swap1", and this is passed as input to function 2, which retrieves "swap1" and does something with it. Suppose function 1 fails, and instead of returning #NUM, it returns the actual error message, e.g. "Could not create object!". Function 2 does not know that function 1 has failed, and attempts to retrieve an object called "Could not create object!", with the failure of that operation leading to further confusion. Returning #NUM in case of error respects Excel's native error handling mechanism.
When you try to load an XLL into Excel, you may get a warning message such as "This file is not in a recognizable format" or "XYZ.XLL is not a valid add-in". Excel may attempt to load the XLL as a text file. These errors indicate that the XLL has a runtime dependency which is not satisfied.
If you compiled your XLL on one machine, and are trying to use it on another, ensure that the runtime library is present on the target machine. For example an XLL compiled in VC8 using configuration
Release has a runtime dependency on files
If you used
QuantLibAllDynamic_vc?.sln to compile ObjectHandler and QuantLibXL as separate, dynamically linked XLLs, note that the ObjectHandler XLL must be loaded into Excel before QuantLibXL.
Microsoft Visual C++ includes the utility
DEPENDS.EXE which can be used to identify the runtime dependencies of a binary.
The Function Wizard (launched by clicking the
fx button next to the Formula Bar) allows the user to specify function inputs by clicking in selected cell ranges. The Function Wizard calls the underlying cell formula repeatedly every time the inputs change - so the formula will get invoked repeatedly with incomplete or nonsensical inputs. Any bug in the validation of the inputs may cause the formula to malfunction or crash. Try calling the function without using the Function Wizard - for example, format the call to the function in Notepad, and paste the formula directly into a cell.
It is possible for an Excel Addin function to detect whether it has been called from the Function Wizard. Certain QuantLibXL functions perform this test, exiting immediately with a return value of null if the Function Wizard is detected. Such functions are configured with tag
calcInWizard='false' in the XML metadata (
QuantLibAddin\gensrc\metadata\functions). The test is expensive and its use is discouraged. Unfortunately QuantLibXL may contain functions which do not perform the test and which crash when invoked from the Function Wizard.
- compile QuantLibXL with the Debug configuration
- start Excel and load QuantLibXL and a client worksheet
- Go into Microsoft Visual Studio
Build | Debug | Attach to Process
- Select Excel from the list of processes.
- From VC, open up a file containing the source code you'd like to debug - for example file
- Identify a function you'd like to debug, for example
qlBlackConstantVol(). Put the cursor on a line in the function and hit F9 to set a breakpoint on the line
- Back in Excel, trigger the breakpoint - select the cell containing the relevant formula, e.g.
qlBlackConstantVol(), hit F2 to force the cell to recalc
- Back in VC, execution should now be stopped on the breakpoint and you can debug from there, e.g.
F10 - advance a line
F11 - step into a function
F5 - run until the next breakpoint or until completion
This section describes how to debug a standalone C++ QuantLibAddin client linux program with the
gdb command line debugger, using program
QuantLibAddin/Clients/C++/QLADemo as an example.
Compiling for Debug
By default QuantLibAddin compiles with flags
-g -O2 which is good for general purpose use but does not give ideal results in gdb:
- certain variables get optimized away - so you're looking at a line in the source code, but it's invisible to the debugger
- the debugger may step into the source code of the Standard Template Library, even if you try to tell it not to
gdb performs better when run against programs which are compiled with flag
-gdwarf-2. So before debugging the QuantLibAddin executable, it's best to first reconfigure and recompile all relevant binaries - QuantLib, log4cxx, ObjectHandler, and QuantLibAddin. cd to the top level directory for each project and run configure with the appropriate flag:
The program we want to debug is QLADemo. File
QuantLibAddin/Clients/C++/QLADemo is a shell script wrapper for the underlying executable
QuantLibAddin/Clients/C++/.libs/lt-QLADemo which is created the first time the wrapper is invoked. So run the wrapper once to create the executable, then cd into the .libs directory.
(Or maybe you can do
libtool --mode=execute gdb QLADemo
Now you can debug the program in the usual way e.g.
gdb lt-QLADemo- to start the debugger
b(reak) main- to set a breakpoint in function
r(un)- to start the program
l(ist)- to list lines of code surrounding the breakpoint
n(ext)- to advance to the next line
s(tep)- to step into a function
c(ontinue)- to continue to another breakpoint
p(rint)- to display a variable's value