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:
Programming in finance
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
Generalized Procedure for Building Short Rate Trees in Excel / VBA
In their 2014 paper John Hull and Alan White derive generalized method for the construction of short rate trees. This generalization is interesting as it allows for one tree (or lattice) construction algorithm for all one factor short rate models. The only difference between the various models is the function 
, which is explained briefly here and in detail in the paper. 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.
Reproduction Example 1 of Generalized Procedure for Building Trees
In a recent (2014) paper John Hull and Alan White demonstrate a generalized method for the construction of short rate trees. Keen to understand the model we tried to reproduce the results of the first example mentioned in the paper on page 10. The example considers the short rate model:
which is transformed using
Curiosities on the Monotone Preserving Cubic Spline
In this paper we describe some new features of the monotone-preserving cubic splines and the Hyman’s monotonicity constraint, that is implemented into various spline interpolation methods to ensure monotonicity. We find that, while the Hyman constraint is in general useful to enforce monotonicity, it can be safely omitted when the monotone-preserving cubic spline is considered. We also find that, when computing sensitivities, consistency requires making some specific assumptions about how to deal with non-differentiable locations, that become relevant for special values of the parameter space.
Keywords: Yield curve, fixed-income, interpolation, Hyman, monotone preserving cubic splines.
Click here for the full paper.
On the non-differentiability of Hyman Monotonicity constraint
In this paper we describe some new features of Hyman’s monotonicity constraint, which is implemented into various cubic spline interpolation methods. We consider the problem of understanding how sensitive such methods are to small changes of the input y-values and, in particular, how relevant Hyman’s constraint is with respect to such changes. We find that many things cancel out and that eventually Hyman’s constraint can be safely omitted when the monotone-preserving cubic spline is used. We also find that consistency requires including some specific boundary conditions that become relevant for special values of the parameter space.
Keywords: Yield curve, interpolation, monotone preserving cubic splines.
Click here for the full paper.
Finance: Between Testing and Regulations
Last July 8th we hosted our second meet up. The announced topics were Testing and Regulations, however the event was mostly testing. In a way, it has been very exciting to organise it, mainly because of some last-minute changes that affected the whole program. In fact, one of the speaker was sick and could not attend, and on top of that a few unexpected technical problems arose at the MixTup venue as well. Eventually everything worked fine and all the efforts made during the few days and hours before the event contributed to the success of the evening.
The chairman of the event was my colleague Jorrit-Jaap de Jong (Ugly Duckling), who entertained the audience, introduced the speakers, and reminded everybody of the purposes of the FEN evenings (which you can read in the ad of our introductory meeting here). The two talks were essentially on testing. The first speaker was our financial engineer Michele Maio (Ugly Duckling) who introduced various testing techniques and good practices such as test driven development and automated testing (mostly focusing on Excel and FitNesse). Michele used the programming language C#. The second speaker was Mert Aybat (Connectis). Mert had an extremely nice "live" and interactive presentation on how to use Mocks when testing complicated projects with many dependencies, and he definitely showed his competence in testing. Mert used Java and Mockito as tools of choice.
The crowd present to the meeting also deserves some comment. Besides the people who attended our first meeting, we were happy to see many new faces who joined this second event, coming both from the software and the finance industries. They all stimulated the discussions that arose during the presentations: thanks to them the flow of information was not in one direction only.
What will happen in the future?
It is important for us to keep track of what we want to achieve and what the FEN events are all about, namely: sharing knowledge, creating an intellectual stimulating environment, meeting like-minded people, discussing trends and hot topics in finance, and networking. In order to move forward, events are planned every couple of month, and the next one will be in autumn when everybody is back from summer vacations. We will keep the current format, which has been successful so far: hosting two talks within an informal environment with topics related to software, finance, and financial engineering.
Requests
To make the FEN community larger, it is crucial that everybody is active. That's why we would like to reminder everybody that anybody is welcome to join. Moreover, as Jorrit mentioned during this event:
- we are always looking for speakers, so if anybody is interested to talk at next event please let us know;
- we are also looking for locations, so if anybody would like to organise the next event with use by booking a seminar room in his/her bank or institution please let us know;
- we are always looking forward to growing our community, so please share it with your network.