The traditional approach

The one constant in financial modelling is that they always start with a Spreadsheet because there is nothing to match it for access to ticking market data combined with flexible access to internal and external quantitative libraries of functions and with the agility to test and change as an idea evolves.Traditionally this meant that libraries needed to be packaged in a way that suited Excel and avoided complex data-types that spreadsheets could not support.

The work around to complex structures was to place the objects in a memory “cache” and only include a reference in the actual spreadsheet.The cache allowed us to build complex models, but at a the cost of maintaining a cache and related code, and the fiddly problem that Excel does not understand the references and multiple Ctrl-F9 recalculations need to be done before all dependencies are resolved.

Excel function libraries are normally coded to minimise the number of objects that needs to be cached (and consequently recalculated), and do not have a one-to-one mapping with the underlying Quantitative class functions.

The chasm

Whilst financial models can be built relatively quickly using Excel there is a chasm to cross converting those models into IT systems in two respects:

• Re-implementing an Excel model in an IT imperative language like C++, Java or C# doesn’t add business value for the front office (by design), and doesn’t initially contribute to regulatory risk management.
• Spreadsheet models are generally not organised to separate market-data, static-data and reference-data from the trade-data the sheet was designed for leading to misunderstandings about what would normally be changed (e.g. a Yield curve reference for Bond/Swap changing after a credit downgrade).

No silver bullet

Since the financial crisis the weakness of Excel models have been highlighted, especially with Collateralised Debt Obligation (CDO) and Credit Default Swaps (CDS) where spreadsheets obfuscated systemic risks.The knee jerk response was to try to replace all the spreadsheets with regular IT applications, but this ignores the lessons of history and stages of business maturity.

Spreadsheet Applications first came to prominence as an IT desktop tool to solve financial accounting problems, consolidation and reporting. Over time Accountants have switched from importing and processing mainframe reports to using OLAP and other reporting tools to the extent that only adhoc analysis and final presentation packs are completed using Spreadsheets.

The lesson from Accounting, is that Spreadsheets are not the problem, the problem is poor integration between IT systems and business processes, and poor tooling to allow knowledge workers to prepare models that can be used with automated IT systems for pricing, booking and risk.

The Cephei approach

It is entirely possible to follow the mammoth approach typified by huge SAP or Oracle ERP programmes and even integrate proprietary libraries with commercial booking systems, but it is our contention that:

• Like Financial Accounting, you will still need Spreadsheets for ad-hoc or complex structured products and for back-testing hypothesis, whether you design for it, or add it latter with reporting tools.
• Excel integration can be done better avoiding the chasm by structuring a model at the outset with a view to using it for pricing and risk, and allowing for the model to be shared and stored as structured data without all the foibles of Excel.
• Excel integration done right should allow you to save an Excel model as a code model without the lengthy process of re-implementation.

The Cephei delivery

Today the Cephei.XL addin for Microsoft Excel uses every function from the Quantlib open source financial function library to provide a fast (parallel processing) modelling environment that can be saved as XML (to file, database or compute grid); human readable F# source code or a compiled .NET assembly

Cephei.XL is available for download for 32-bit Excel and 64-bit Excel