Tuesday, 25 March 2014

Mortality Manager with FCell 2.1


This is just a brief post to cover the creation of a new version of Mortality Manager to use the recently released update 2.1 for FCell. Full details of this very significant update can be found at http://fcell.io/.

I will limit this post to looking at three features:
  1. Workbook Customisation
  2. Excel Customisation
  3. UDF support for complex parameters and values
Note that the first two items have allowed me to distribute the mortality manager functionality without the need to have FCell installed and with no need for any local set up effort.

Workbook Customisation

The new version allows simpler creation of macro enabled workbooks that have all the required libraries embedded.

This allows Mortality Manager to be distributed as a standalone workbook. I have created two versions for 32 bit and 64 bit Excel. These are available from bitbucket at:
    Once you download the appropriate version, you can simply open in Excel:

    Simply follow the instructions in the GettingStarted sheet to be able to replicate the examples contained in the workbook.

    Excel Customisation

    A new capability added in this version allows the supporting code to be packaged as an .XLL file. This allows the distribution of the Ribbon, Custom Task Pane and UDF additions included in Mortality Manager using a single file.

    I have created two versions of the XLL for 32 bit and 64 bit Excel. These are also available from bitbucket at:
    Once you have downloaded these they can simply be opened using Excel or to install permanently use File > Options > Add-ins and then use the Go.. button to "Manage the Excel Add-ins" and then Browse to add the downloaded XLL.

    (N.B. If you get a message that the XLL is in an invalid format then either you are trying to use the wrong version or your computer is missing the Visual C++ runtime for Visual Studio 2012, which is required by these XLLs. For the latter issue please use the installer available from Microsoft at http://www.microsoft.com/en-us/download/details.aspx?id=30679.)

    Once loaded, you should see the Mort Ribbon and additions in the function wizard, e.g. a new category MortMgrUdf.Mort.

    To get started, I suggest using the following steps:
    1. On the Mort Ribbon select "Settings". Specify local folders to store local versions of tables and to store Transitory data when importing from the SoA site, e.g. "C:\Mort\Local" and "C:\Mort\SoA". N.B. Create these folders first and then browse to select them using the "..." button.
    2. Restart Excel to get these settings reloaded. Re-open the Settings dialog and confirm that the folders are still correctly set.
    3. Using the SoA toggle button open the SoA Task Pane. Use the "Reload Index from Website" button - this may take some time! Confirm that the file "soaindx.json" has been create in the SoA folder, e.g. in C:\Mort\SoA
    4. Import some sample Select and Ultimate tables. For example, in the SoA Task Pane select a Nation of "United Kingdom". Select the checkboxes for the tables "ELT No. 16 (2000-02) - Female, ANB", "ELT No. 16 (2000-02) - Male, ANB", "AFC00" and "AMC00". Import using the "Import" button. Confirm that new json files appear in the local tables folder, e.g.  in C:\Mort\Local.
    You should then be able to use the Task Panes included to view the tables, compare tables and calculate various factors. You should also be able to get results from the tables using the UDFs provided.

    UDF support for complex parameters and values

    I have limited the changes made to Mortality Manager to a minimum. I will return to review the enhancements and apply them to Mortality Manager in a later post. I will however briefly cover the very significant and powerful facility added for UDFs to be able to use and create complex values.

    I have created one sample to illustrate this functionality. I provide an alternative function to read qx ultimate rates from a table. The original function has this code:

    /// qx for ultimate table
    let qx tblnm x = 
        let tblfil = Ind.tblfil tblnm
        let tbl = Mort.load tblfil
        let rts = tbl.MortRates
        match rts with
        | Ultimate(r) -> r.[x]
        | _ -> failwith "Table is not an Ultimate table"

    This function takes in parameters of a string (tblnm) holding the name of the table and an integer (x) holding the age. It then gets the reference to the table in line 3, loads the table in line 4, gets the rates in line 5, and then reads the rates for the supplied age (or errors) in lines 6 to 8.

    As an illustration, I want instead to separate this code into two functions: the first will load the table and the second will use the table loaded and extract the rate from it. The new first function is:

    /// gets a tbl to be used in other functions
    let get tblnm = 
        let tblfil = Ind.tblfil tblnm
        Mort.load tblfil

    If I use this in Excel with the formula =get("ELT No. 16 (2000-02) – Female, ANB") I get returned MortTbl@0. This cell now holds a handle to the mortality table, which can be referenced in another UDF. The second function is:

    /// qx for ultimate table using 2.1 ability to handle complex parameters
    let qx2_1 tbl x = 
        let rts = tbl.MortRates
        match rts with
        | Ultimate(r) -> r.[x]
        | _ -> failwith "Table is not an Ultimate table"
    Now if we used the first function in cell F61, we can simple call the second function using =qx2_1(F61,30) which returns the mortality rate for age 30, namely 0.00042.

    Although this example is very straightforward, it does illustrate the simplicity of using this enhancement and the much greater flexibility this enhancement provides. For a  better example of using this capability, please see the YouTube video on FCell at http://www.youtube.com/watch?v=jtNyFMp4NAg.

    Friday, 14 March 2014

    Smith Wilson and Deedle

    A Diversion

    In this post, I am taking a brief detour into the asset side of actuarial modelling. This is definitely not my area of expertise, but fortunately I know some experts who are happy to give me a hand.

    For this post I am going to look at the Smith-Wilson method of curve fitting. This was the method preferred under QIS5 for interpolating and extrapolating interest rate curves - see http://eiopa.europa.eu/fileadmin/tx_dam/files/consultations/QIS/QIS5/ceiops-paper-extrapolation-risk-free-rates_en-20100802.pdf.
    (This method was proposed in one of the earlier publications jointly authored by the amazing Andrew Smith - http://www.theactuary.com/features/2012/12/dare-to-be-different/).

    I have had a lot of help from an ex-colleague and expert in this field, Phil Joubert. Phil writes a very interesting blog at http://www.not-normal-consulting.co.uk/. He has written a CRAN R package to carry out this curve fitting, which is documented in two of his blogs:
    In this post, I will initially run this R package using RStudio and then use F# to host the package using Deedle and the R Type Provider.

    The R Package

    The R Package is called "SmithWilsonYieldCurve". Once installed, you have access to a number of functions. The most straightforward to use is fFitSmithWilsonYieldCurveToInstruments. This is described as "A convenience function that takes a dataframe containing market instrument data as type, tenor, frequency and rate. It extracts the required vectors and matrices and then calls fFitSmithWilsonYieldCurve."

    To illustrate usage, I will use code that replicates results from a QIS5 Example - see page 24 of the paper mentioned above.

    It is very straightforward to do this in R Studio, using the R Code shown below:

      library( "SmithWilsonYieldCurve" )
      InstrumentSet1 <- read.csv("InstrumentSet1.csv")
      ufr <- log( 1 + 0.042 )
      alpha <- 0.1
      Curve <- fFitSmithWilsonYieldCurveToInstruments(InstrumentSet1, ufr, alpha )

    This displays this curve:
    You can also verify that this generates the expected example result by inspecting the value of Curve$P(4) which returns 0.8850041. (0.885 is the value in the QIS5 example).

    F# using Deedle

    In this post, I will limit myself to retaining the use of R for the calculations and plotting (I will revisit this in a later post). I will however use F# Interactive to host the code and use F# to call into R using the R Type Provider. I will also use Deedle, which provides a dataframe capability that is directly usable with R.

    To access the supporting libraries it is easiest to use NuGet. You should select the Deedle package with the R Plugin. (Note that there seems an issue with the NuGet package - so you might need to make some manual changes - see this StackOverflow question).

    Once you have this installed, you can reference the relevant libraries and very simply replicate the R calling code:
    #I "../packages/Deedle.0.9.12/"
    #I "../packages/RProvider.1.0.5/"
    #load "RProvider.fsx"
    #load "Deedle.fsx"
    open RProvider
    open RDotNet
    open Deedle
    open RProvider.graphics
    open RProvider.SmithWilsonYieldCurve
    open RProvider.``base``
    let InstrumentSet1 = Frame.ReadCsv("c:/temp/InstrumentSet1.csv")
    let ufr = log( 1.0 + 0.042 )
    let alpha = 0.1
    let Curve = R.fFitSmithWilsonYieldCurveToInstruments(InstrumentSet1, ufr, alpha )
    let Pfn = Curve.AsList().[0].AsFunction()
    let ans  = R.sapply(R.c(4),Pfn)

    (A copy of the InstrumentSet1.csv file can be downloaded from bitbucket.)

    Lines 13 to 17 are virtually identical to the R code and generate exactly the same effect - R is called and displays the identical curve.

    When you run this in F# interactive it also display the following output:

    val InstrumentSet1 : Deedle.Frame<int,string> =
         Type Tenor Rate  Frequency
    0 -> SWAP 1     0.01  1        
    1 -> SWAP 2     0.02  1        
    2 -> SWAP 3     0.026 1        
    3 -> SWAP 5     0.034 1        

    val ufr : float = 0.04114194333
    val alpha : float = 0.1
    val Curve : RDotNet.SymbolicExpression =
    function (t)
        fBase(t) + t(KernelWeights) %*% fCompoundKernel(t)
    <environment: 0x08cde364>

    [1,]  57.790688
    [2,] -33.507208
    [3,]  11.396473
    [4,]  -5.466968

    function (t)
        CashflowMatrix %*% fKernel(t, TimesVector)
    <environment: 0x08cde364>

    [1] "SmithWilsonYieldCurve" "YieldCurve"          

    Notice that the dataframe InstrumentSet1 is displayed in a friendly format followed by the other function parameters: ufr and alpha

    This is followed by a full display of the Curve object. This is a rather complex object, which is documented as "Objects of class SmithWilsonYieldCurve are a list, the first element of which is a function P(t), which returns the zero coupon bond price of the fitted curve at time t."

    To obtain the test value for P in F#, we therefore, in line 18, convert the R SymbolicExpression to a List, take the first element and then convert this to a Function. We can then call this function, in line 19, using the R high level function sapply . In F# Interactive we then get:

    val ans : SymbolicExpression = [1] 0.8850041

    In the next post, I will explore converting the underlying R code to F#.

    Monday, 3 March 2014

    Performance and Progress


    Performance of actuarial software has always been a major challenge. The standard packages have historically all been written with performance as a priority. They have therefore typically generated code in a highly performant language, usually C or C++. They have also offered ways to utilise multiple machines to carry out the computations. More recently they have offered Cloud compute facilities, for example see:


    Even so, there continues to be interest in getting even more performance. There is an interesting conference, which is principally about this topic, next month in Edinburgh - Computation in Finance and Insurance, post Napier. I also noticed an interesting academic paper - An efficient algorithm for the calculation of non-unit linked reserves. This used reasonably complex algorithms and specialised tools such as Fortran and OpenMP.

    One of the benefits of using F# is that it offers the potential of being both easy for an actuary to use and also highly performant. Where necessary, there is easy access to more powerful computational techniques. This includes highly optimised libraries, such as Math.NET Numerics, Extreme Optimization Numerical Libraries for .NET, StatFactory FCore. For relevant links, see the Math page on the F# Software Foundation site. There are straightforward options for parallelising F# code and running it on multiple machines or on GPUs. For  more links, see the options on the Cloud and GPU pages on the F# Software Foundation site.

    Although mortality manager currently does not require high performance, this may become more important when stochastic mortality is added. I therefore decided to have a brief look at the performance of some of the code.

    Simple Performance Test

    In this section I will test options for coding the calculation of Dx. I will consider both the simplicity of the code and its relative performance, as it might be expected that some loss of simplicity will be required to achieve higher performance.

    The test will be to generate 1,000,000 sets of Dx values from age 0 to 120 and for interest rates from  0.00001% to 10.0% in steps of 0.00001%.

    I will assume I already have a list containing lx for each age.I can then define a list (is) or array (isa) of interest rates:
    //list and array of interest rates
    let is = [0.0000001..0.0000001..0.1]
    let isa = [|0.0000001..0.0000001..0.1|]

    The simplest code for this would be:
    //simplest test
    let Ds = is|>List.iter(fun i -> ls|>List.mapi(fun n l -> l/(1.0+i)**float(n))|>ignore)
    This just calculates the Ds values but does not retain them as that would have significant memory implications. It does this by just raising v (i.e. 1/(1+i)) to the required power and multiplying it by the relevant lx.

    In some circumstances using an array can give better performance than using a list. So here is an array version:
    //array test
    let Dsa = isa|>Array.iter(fun i -> ls|>List.mapi(fun n l -> l/(1.0+i)**float(n))|>ignore)
    F# makes it very simple to run this code in Parallel, using multiple cores. Here is a parallel version:
    //array parallel
    let Dsap = isa|>Array.Parallel.iter(fun i -> ls|>List.mapi(fun n l -> l/(1.0+i)**float(n))|>ignore)
    One known slow element will be the use of powers. You can remove this by creating a more complex recursive version:
    //avoid power
    let rec getDs i ll lastv Dl =
        if ll=[] then List.rev Dl
            let newv = lastv/(1.0+i)
            let D = newv*ll.Head
            getDs i ll.Tail newv (D::Dl)
    let Dsrec = isa|>Array.Parallel.iter(fun i -> getDs i ls.Tail 1.0 [ls.[0]]|>ignore)
    This starts at t=0 and then recursively iterates through each t, calculating v^n by dividing the previous one by (1+i).

    We can use F# Interactive and #time to get a quick performance result for the 4 versions:
    1. Real: 00:00:06.416, CPU: 00:00:06.411, GC gen0: 773, gen1: 0, gen2: 0
    2. Real: 00:00:06.366, CPU: 00:00:06.364, GC gen0: 774, gen1: 0, gen2: 0
    3. Real: 00:00:01.932, CPU: 00:00:06.864, GC gen0: 777, gen1: 1, gen2: 0
    4. Real: 00:00:01.208, CPU: 00:00:03.385, GC gen0: 1541, gen1: 0, gen2: 0
    The first thing to notice is the very high performance of all versions.You can see that the first two were virtually identical, as you might expect. On my quad core machine, the parallel version performed very well and efficiently used the cores available. The last version is interesting in demonstrating that in some cases it is worth making the code more complex to get a performance gain. I currently use this more performant recursive approach.

    I may return to this topic in a later post - I intend to run this test on GPUs.


    Since my previous post, it has been very straightforward to add further facilities. These include:
    • Creation of rates based on existing rates
    • Generation of Commutation functions
    • Generation of Present Value functions
    • Support for Joint Life functions
    • Greatly expanded set of UDFs (now 58)
    I have therefore created a new version - V002. It looks like this:


    This can be set up in the same way as V001 - see the instructions in my previous post. The new version is stored in bitbucket at:

    I now think this has a fairly complete set of base functionality. I therefore do not currently intend to add anything further that works with the standard mortality rates. Instead, I will next add modelling of mortality improvements and stochastic mortality modelling.