In this blog it’s explained how you can wrap up C++ managed or unmanaged code and make the functionality available in Excel / VBA. The blog uses levmar as specific example, but most steps are generic. The levmar specific example is due to the fact that we are currently working on some short rate lattice models that need to be calibrated to the interests market. The calibration requires a robust solver for a nonlinear least squares problem. To share our models and their implementation with the wider finance community we choose Excel / VBA for the implementation. This allows us to easily share our ideas and algorithms. For most simple cases this worked wonderfully using a solver algorithm that was originally part of MINPACK developed by Jorge More, Burt Garbow, and Ken Hillstrom at Argonne National Laboratory. The algorithm we used was translated from Fortran to VBA by Vanna and shared on quantcode.com. This algorithm proved not to be stable enough for more complex problems. So we started looking for more stable implementations and found levmar by Lourakis. This package was written in ANSI C. Here we explain how we took this code and build xll add-in for Excel using:
levmar
Levmar Excel version 1.1 released
We are happy to announce that a new version of Levmar Excel has been released. This version exposes more functionalities from the original levmar package to Excel.
New in this version is the ability to pass a VBA class (i.e. object) as extra parameter to LevmarExcel. This object can contain extra information needed to evaluate the error function. This addition offers a lot of extra flexibility using the package. Complex object models can now be passed as arguments to be used for the evaluation of the error function (the error function can be only a thin wrapper for the “real” error function in the object).
The latest version of Excel Levmar is available here. The package contains the example “demo_vba_object.xlsm” which shows how to use this new functionality. The example contains a module with some code to build the LevmarExcelSolver object. Continue reading
Levmar Excel An Introduction
Levmar Excel is a wrapper around levmar, one of the best Levenberg-Marquardt algorithms out there. The wrapper allows you to use levmar in Excel via VBA. This is handy if you want to solve a nonlinear least squares problem. Alternatively you could also try to use the build-in Excel solver. While this solver is excellent, it isn’t too easy to integrate in VBA code. Levmar Excel fills the gap. It gives you an easy to integrate least squares solver for VBA code. In the remainder of the post it will be explained how to set things up to get the demo spreadsheet working. (If you are a developer wanting more details on the source code of this project look here.) Continue reading
Compiling LevmarSharp (Visual Studio 2010)
Prerequisites:
-Visual Studio 2010
-levmar 2.6 (http://users.ics.forth.gr/~lourakis/levmar/)
-levmarsharp (https://github.com/AvengerDr/LevmarSharp)
For a recent research project we needed to solve an optimization problem. In specific we were trying to reproduce the results in the paper “A Generalized Procedure for Building Trees for the Short Rate and its Application to Determining Market Implied Volatility Functions” by Hull and White. In the paper it is described how a lattice can be constructed and calibrated to market. The calibration is essentially an optimization problem where the difference between the discount factors (or interest rates) observed in the market and the discounts generated in the model is made as small as possible by varying the model parameters.
Compiling Levmar using NMake (Visual Studio 2010)
Prerequisites:
-Visual Studio 2010 which comes with NMake
-levmar 2.6 (http://users.ics.forth.gr/~lourakis/levmar/)
For a recent research project we needed to solve an optimization problem. We considered using levmar by Lourakis. Not having touched C or build code using Make for a while it took a little while to get everything setup and building. In this blog the steps needed will be described. Should you run into trouble please consider the troubleshooting section at the end of this post. If you are interested in using levmar in C# check out this blog post.