Saturday, 26 April 2014

Mortality Manager v003

Overview

I have now completed the development of support for Mortality Projections. I have decided to produce an updated version which includes this additional functionality in a similar way to that made available in the post Mortality Manager with FCell 2.1.
This post describes what is provided and instructions for use and also covers the changes made to the UDFs.

Workbook Customisation

One option for obtaining Mortality Manager v003 is 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

The other option for obtaining Mortality Manager v003 is as an .XLL file. This provides 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.
  5. In the CMI 2012 Task Pane, select Get Sample Results and then Save Rates to create a default set of Projected Rates.
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.

UDFs with complex table parameters and results

I have converted all the supplied UDFs to use a facility included in FCell 2.1 to use complex parameters and return complex results. The UDFs that return mortality results now all work with the mortality table as one of the parameters. It is assumed that another UDF has been used to pull in the mortality table reference to an Excel cell.

This is best illustrated by an example provided with the customised workbook:



Please note the get UDF references in cell F3, with this formula:
=get("ELT No. 16 (2000-02) – Female, ANB")
This simple gets this mortality table and holds a reference to it in the cell. You can then use this in another UDF, as illustrated by the function wizard for cell F7:


The code for this UDF is simply:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
/// qx for ultimate table
[<XlFunctionHelp("Get q from Ultimate table")>]
let qx tbl x = 
    let rts = tbl.MortRates
    match rts with
    | Ultimate(r) -> r.[x]
    | _ -> failwith "Table is not an Ultimate table"

Note the use of the XlFunctionHelp attribute to add documentation in the function wizard in Excel.

No comments:

Post a Comment