This is the FAQ for QuantLibAddin and QuantLibXL, which are subprojects of QuantLib. Please see also the general QuantLib FAQ and the QuantLib developer FAQ.

1. General

1.1 What is QuantLibAddin?

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

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.

ObjectHandler

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.

QuantLibAddin

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.

1.2 What is QuantLibXL?

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.

1.3 Where do I go for help?

The mailing list for this project is quantlib-users@lists.sf.net. The list archives may be searched here.

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 ;-)

2. Installation

2.1 How do I compile and/or install QuantLibAddin/QuantLibXL?

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.

3. Compilation

3.1 Why does my compilation fail with an error message about windows.h?

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.

4. Usage

4.1 How do I start QuantLibXL?

See http://www.quantlibxl.org/installation.html.

4.2 How do I call QuantLibXL functions from Excel VBA?

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 QuantLibXL\Workbooks\StandaloneExamples\Options.xls.

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

4.3 What is the maximum number of arguments for a QuantLibXL function?

Twenty.

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.

4.4 What do I do if a QuantLibXL function returns #NUM?

Call ohRangeRetrieveError().

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(), and 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.

4.5 Why don't QuantLibXL functions just return the error message string instead of #NUM?

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.

5. Troubleshooting

5.1 Why does Excel display an error message when I load QuantLibXL?

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 MSVCP80.DLL and MSVCR80.DLL.

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.

5.2 Why does Excel crash when I use the Function Wizard?

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.

6. Debugging

6.1 How do I debug QuantLibXL?

compile QuantLibXL with the Debug configuration
start Excel and load QuantLibXL and a client worksheet
Go into Microsoft Visual Studio
do 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 QuantLibXL\qlxl\Functions\volatilities.cpp
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
etc.

6.2 How do I debug a QuantLibAddin Linux executable at the command line?

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:

./configure CXXFLAGS=-gdwarf-2

Then recompile.

The Executable

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

?)

Debugging

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 main
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

etc.