In a previous we blog we reproduced example 1 of the 2014 paper John Hull and Alan White “Generalized Procedure for Building Short Rate Trees”. In the paper the authors derive a 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. Continue reading
Excel/VBA
Wrapping C++ DLL for use in Excel / VBA using some pretty awesome open source projects
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 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
Automation of FTP process
For a large Dutch bank we created a detailed plan plus working prototype to replace the manual daily funds transfer prices (FTP) for the mortgages domain process with an automate system.
The resulting prototype achieved automation using the windows scheduler, Visual Basic scripts and Excel (with macro code). This prototype was the first step to automate the process and greatly reduced the workload for the business team charged with producing the daily prices. From an IT perspective this first round of automation was not enough as the prototype model didn’t fulfill the straight through processing required by IT as it still requires manual steps. In the second phase of the project we investigated which steps can be taken to achieve straight through processing. We suggested to migrate the solution to a more technically robust environment using C#, SISS package and SQL-server.
Square Law of Spreadsheets
Ella places two printouts on John’s desk and points at the total columns. “Why don’t these numbers match?” she asks. John looks for a second, hoping Ella has made a simple mistake. But she’s right, they should match. John spends the rest of the day crawling through dozens of spreadsheets, attempting to find the source of the discrepancy. Eventually the problem is found, but only after turning up five or six similar issues. The team collectively pauses for breath when they consider the number of decisions made on faulty data. It is another week before John has fixed the problems and can return to his original task.
This is a particularly familiar story to many growing financial companies, which often prompts the question “how did things get like this?”. To find the answer, we need to investigate a misunderstood dynamic in spreadsheet and tool development. Consider the following diagram:
Figure 1 – The Square Law of Spreadsheets Effects Diagram. Continue reading
Matlab Validation at Kempen & Co.
In collaboration with Valu8, we acted for Kempen & Co in validating the valuation and hedging of the Inflation Breaker, a portfolio of ground lease contracts.
The contracts in the portfolio generate monthly cash flows based on the rent. Rents increase annually, with the Dutch pricing inflation index. At maturity of the contract, the tenant has the option, but not the obligation, to buy back the land.
This was not the first time Kempen & Co decided to entrust this validation to Valu8 and Ugly Duckling. This shows that Ugly Duckling is a key player in software, management and finance with an integrated approach that delivers. The work done was a continuation of previous validation work where we checked the cash flow model, the interest rate curve construction methodology and the inflation model. The previous implementation was improved and migrated to Matlab (previously Excel/VBA), which required a new validation.
Modeling Pension Rights at Cardano
We supported Cardano in validating and optimizing the Excel/VBA code used to model pension rights. Our team successfully completed a first analysis of the raw data.
In the quest for a new sustainable pension contract, two criteria play a crucial role: a minimum level of security and sufficient indexation potential to avoid erosion of pensions by inflation. These two criteria intuitively collide. To gain more insight in these matters, Cardano started an interesting research project which we supported by validating and optimizing the Excel/VBA code used to model pension rights.
We completed our involvement in the project by providing a first analysis of the raw data. Based on the model, Cardano has published a paper ‘Getting Real about Nominal Guarantees’ by Theo Kocken, Bart Oldenkamp and Joeri Potters.
Operational risks in code libraries
Valuation Tool for Dutch Bank
We helped to build a Microsoft Excel Application for a Dutch bank that assisted in the valuation of a portfolio with loans.
The client required tooling that would generate valuation reports of their holdings for accounting purposes. The portfolio had various embedded option features within the foreign loan contracts.
The resulting tool was built using Microsoft Excel extended with Visual Basic for Application. It gathers the required market data from Bloomberg and uses our financial algorithms to produce portfolio valuation reports.