Cephei Sample

Low-code/no-code model development in Excel

Posted by steve on May 23, 2024

Introduction

Cephei was conceived on the premise that Excel is not inherently a bad tool to prototyping a model, but it is a poor tool to operate a business that depends on the model.

The architecture impedance issue is that it is historically difficult to translate the prototype model from a spreadsheet to a reliable application. Cephei addresses the impedance issue by replicating the Cell notification paradigm and designing the implementing quantitative functions with code-generation metadata.

Cephei.XL can be viewed as a Low-code development environment for a Financial Digital Twin

Cephei can be used directly with Quantlib analytics or used as a an Architype when integrated with another financial library

Cephei.XL Solution

The Cephei.XL solution provided a comprehensive Quantitative Finance Library of 15000 functions that can be used like a traditional Excel XLL addin, but without the need to disable automatic calculation because formula are refreshed by RTD only when they change, and always in the background.

At any point the model the corresponding source code (model and Excel addin functions) can be generated from the Cephei menu and compiled to executable code for deployment to pricing server or Financial Digital Twin Cephei models use the Cephei Cell Framework to mirror the automatic dependency tracking of Excel, but with parallel calculation within Excel or a server application like Cephei.Orleans

Sample

The simplest example uses a fixed rate bond, entered interactively in Excel using Excel Formula wizard with standard evaluation and validation as each formula is entered. All Cephei functions are prefixed with _ with Mnemonics prefixed with + for model parameters and - for private formula that are not exported as properties or with Excel addin functions

Spreadsheet formula

Sample.xlsx open with Cephei 32-bit or Cephei 64-bit

Forumula view

Generated Source Code

namespace Cephei.Models

open QLNet
open Cephei.QL
open Cephei.QL.Util
open Cephei.Cell
open Cephei.Cell.Generic
open System
open System.Collections

type FixedBond 
    ( Tenor : ICell<Int32>
    , Maturity : ICell<Date>
    , FixedAmount : ICell<Double>
    ) as this =
    inherit Model ()

(* functions *)
    let _Calendar = Fun.TARGET()
    let _Today = (value DateTime.Today)
    let _clock = Fun.Date1 (triv (fun () -> int (_Today.Value.ToOADate())))
    let _PriceDay = _Calendar.Adjust _clock (value BusinessDayConvention.Following)
    let _DayCount = Fun.ActualActual1 (value ActualActual.Convention.ISMA) (value (null :> Schedule))
    let _Quote = Fun.SimpleQuote1 (triv (fun () -> toNullable (0.03)))
    let _Tenor = Tenor
    let _Frequency = Fun.Period2 (value Frequency.Annual)
    let _FlatForward = Fun.FlatForward _PriceDay (triv (fun () -> _Quote.Value :> Quote)) (triv (fun () -> _DayCount.Value :> DayCounter))
    let _Maturity = Maturity
    let _Coupon = cell new Generic.List<double>([| Convert.ToDouble(0.02); Convert.ToDouble(0.05); Convert.ToDouble(0.08)|]
    let _ExCoupon = Fun.Period1()
    let _Settlement = (value (Convert.ToInt32(0)))
    let _FixedAmount = FixedAmount
    let _Engine = Fun.DiscountingBondEngine (triv (fun () -> toHandle<YieldTermStructure> (_-FlatForward.Value))) (triv (fun () -> toNullable (True)))
    let _Schedule = Fun.Schedule _PriceDay _Maturity _Frequency (triv (fun () -> _Calendar.Value :> Calendar)) (value BusinessDayConvention.Unadjusted) (value BusinessDayConvention.Unadjusted) (value DateGeneration.Rule.Backward) (value false) (value (null :> Date)) (value (null :> Date))
    let _Bond = Fun.FixedRateBond _Settlement _FixedAmount _Schedule _Coupon (triv (fun () -> _DayCount.Value :> DayCounter)) (value BusinessDayConvention.ModifiedFollowing) _FixedAmount _PriceDay (triv (fun () -> _Calendar.Value :> Calendar)) _ExCoupon (triv (fun () -> _Calendar.Value :> Calendar)) (value BusinessDayConvention.Following) (value False) (triv (fun () -> _Engine.Value :> IPricingEngine)) _PriceDay
    let _clock = Fun.Date1 (triv (fun () -> int (_Today.Value.ToOADate())))
    let _CleanPrice = _Bond.CleanPrice()
    let _DirtyPrice = _Bond.DirtyPrice()
    let _NPV = _Bond.NPV()
    let _Cash = _Bond.CASH()

    do this.Bind ()

(* Externally visible/bindable properties *)
    member this.Today = _Today
    member this.Quote = _Quote
    member this.Tenor = _Tenor
    member this.Frequency = _Frequency
    member this.Maturity = _Maturity
    member this.FixedAmount = _FixedAmount
    member this.clock = _clock
    member this.CleanPrice = _CleanPrice
    member this.Clock = _Clock
    member this.DirtyPrice = _DirtyPrice
    member this.NPV = _NPV
    member this.Cash = _Cash

#if EXCEL
module FixedBondFunction =

    [<ExcelFunction(Name="__FixedBond", Description="Create a FixedBond",Category="Cephei Models", IsThreadSafe = false, IsExceptionSafe=true)>]
    let FixedBond_create
        ([<ExcelArgument(Name="Mnemonic",Description = "Identifer for the value")>] 
         mnemonic : string)
        ([<ExcelArgument(Name="__Tenor",Description = "reference to Int32")>]
        Tenor : obj)
        ([<ExcelArgument(Name="__Maturity",Description = "reference to Date")>]
        Maturity : obj)
        ([<ExcelArgument(Name="__FixedAmount",Description = "reference to Double")>]
        FixedAmount : obj)

        = 
        if not (Model.IsInFunctionWizard()) then

            try
                let _Tenor = Helper.toCell<Int32> Tenor "Tenor"
                let _Maturity = Helper.toCell<Date> Maturity "Maturity"
                let _FixedAmount = Helper.toCell<Double> FixedAmount "FixedAmount"

                let builder (current : ICell) = withMnemonic mnemonic (new FixedBond
                                                            _Tenor.cell
                                                            _Maturity.cell
                                                            _FixedAmount.cell

                                                       ) :> ICell
                let format (i : ICell) (l:string) = Helper.Range.fromModel (i :?> FixedBond) l
                let source () = Helper.sourceFold "new FixedBond"
                                               [| _Tenor.source
                                               ;  _Maturity.source
                                               ;  _FixedAmount.source
                                               |]

                let hash = Helper.hashFold
                                [| _Tenor.cell
                                ;  _Maturity.cell
                                ;  _FixedAmount.cell
                                |]
                Model.specify 
                    { mnemonic = Model.formatMnemonic mnemonic
                    ; creator = builder
                    ; subscriber = Helper.subscriberModel<FixedBond> format
                    ; source = source 
                    ; hash = hash
                    } :?> string
                        with
                        | _ as e ->  "#" + e.Message
        else
            "<WIZ>"

Download

The Excel addin can be downloaded from Cepheis

  • Excel (32-bit)
  • Excel (64-bit)
  • Source code The public version of the addin includes basic telemetry to track errors and usage of functions by user (but not data) in order to assist with support during evaluation. A release version without telemetry is available upon request

Summary

Whilst Cephei can be used directly as a Quantitative Finance library for structuring, pricing and as a summary blotter, it is designed to be used:

  • As a model editor for the prototyping with ticking market data, for saving directly to code that can be deployed without additional development
  • As a recipe editor for model parts that are generated an compiled for use within more complex models (the sample model encapsulates the additional objects (schedule, termsheet, pricing engine) needed for Quantilib
  • As a foundation for risk simulation models (the Cell framework is designed for massively parallel Monte Carlo simulation of Exposure for Realtime Risk (Cephei is named after Delta Cephei )

Further information is available at Cephei.QL , Cephei.XL and Cephei Cell

Contact feedback@cepheis.com

Solution