Cephei was designed following deep experience of Excel being used for Bond Pricing in an Investment Bank front office. Traditionally it is necessary to disable recalculation while models are being developed to prevent Excel from appearing to hang as market data triggers re-calculation of prices.
It is especially important to be careful when indicative prices are being contributed to the markets because stale prices can be contributed whilst the UI appears to hang. Handle (text or number) references are normally used to reference an object cache in the Quant Library, and these Handles need to be changed to trigger dependancies while redundant Handles must be carefully removed to prevent memory leaks or crashes.
Cephei does not update handles, but uses them for code-generation. Background threads perform all calculation, adressing the problem of:
How to feed background value changes to the foreground spreadsheet
How to trigger calculation with the result of market-data changes
How to release old calculation objects when they are no longer referenced
Cephei solves these problems by:
Using “Real Time Data” RTD component for references to background objects – Excel treats them the same as RTD subscriptions to market data.
Using RTD to push updates to Excel when background values change.
Using a Cell Framework to build a calculation graph independent of Excel. It is this independent calculation-graph that allows code-generation to reproduce the model as source-code for compiled assemblies.
Cephei can enable traders to build pricing servers on a desktop, but there is minimal risk of “live RAD solutions” because it is simple to move a spreadsheet model to an application code. It is far cheaper to generate appication services than for a trader or RAD-developer monitoring a spreadsheet for problems.
Cephei solves the spreadsheet performance problem, but also to governance problem that spreadsheets have historically lived on for months or years. Requirement governance is focused on the model rather than the implimentation and testing of developments.
The two samples BondBlotter.xlsx and Bond_Fixed_Portfolio.xlsx demonstrate spreadsheets with real-time ticking changes, with all work performed in the background.
Setup
Please register and then download either the Cephei 32-bit or Cephei 64-bit addin from cepheis.com. The download contains a single XLL (Excel Link Library) file that can be opened directly in Excel without admin installation (to avoid prompting when opened, it should be saved to an Excel trusted addin folder).
Bond Blotter
The Bond Blotter is a simple blotter of 120 fixed rate bond annually and semi-annually with tenors between 3 and 15 years and yields of 3% to 6% (sample Quantlib test data from the past).
For the purpose of demonstration, the coupon rates are multiplied by random numbers to simulate the effect of live ticking data, with a =_Delay_Double(.. ,10) function to provide Red/Amber/Green conditional formatting for price movement over a period of time, together with a bar graph of the clean price for each bond. Continuous background calculation updates Excel as prices change with no locking or appaent hanging.
Selecting Generate from the Cephei menu within Excel generates the source code for the model. When the name of the cell begins with + the generated cel is a parameter, and prefix of - does not include Excel functions.
Bond_Fixed_Portfolio
This example uses the generared and compiled Cephei.Model.dll file in the zip file (which needs to be copied to the same folder as Cephei.xll to be loaded at startup).
Bond_Fixed_Portfolio used the Fixed Rate Model created sample to create a portfolio of 100 bonds, calculating {NPV, Clean Price, Dirty Price, Yield} for each the bond. Excel shows a graph and trend condition formatting.
The sample performs hundreds of calculations per second, but does not stop interactive editing in the same way that market-data subscription does not stop editing. The sample demonstrates that Cephei & Excel can be used as a model development environment |
 |