OverviewThis 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:
- Workbook Customisation
- Excel Customisation
- UDF support for complex parameters and values
Workbook CustomisationThe 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:
Simply follow the instructions in the GettingStarted sheet to be able to replicate the examples contained in the workbook.
Excel CustomisationA 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:
(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:
- 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.
- Restart Excel to get these settings reloaded. Re-open the Settings dialog and confirm that the folders are still correctly set.
- 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
- 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.
UDF support for complex parameters and valuesI 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"
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.