Tuesday, 25 March 2014

Mortality Manager with FCell 2.1


Overview

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:

    1: 
    2: 
    3: 
    4: 
    5: 
    6: 
    7: 
    8: 
    
    /// 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:

    1: 
    2: 
    3: 
    4: 
    
    /// 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:

    1: 
    2: 
    3: 
    4: 
    5: 
    6: 
    
    /// 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.

    No comments:

    Post a Comment