Wednesday, 27 May 2015

FCell Calculation Engine - loading Mortality

Introduction

In this post, we will be loading Mortality rates created using Mortality Manager (see post http://fsharpactuary.blogspot.co.uk/2015/03/fcell-30-and-updates.html).

If you look at the Excel only version, you will see that there are mortality improvement factors stored in sheets MaleMortImprov and FemaleMortImprov. There are then base factors stored in sheets MaleMort and FemaleMort.

For the former, I will use a simple approach of treating these in the same way as the other inputs and just use them as data read from Excel. For the latter, I will read these from files held in JSON format created by Mortality Manager.

A copy of the workbook created at this stage can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/fd38bc66998c224d7e682ec6aaf9f1cda5717a72/SingleProj/Excel/SingleProj_v2.xlsm?at=default

Mortality Improvements

The improvement factors were created by Mortality Manager, but then were simply pasted into the spreadsheet. The simplest option is to just load the rates into a 2D array of floats. It is also necessary to load in the range of calendar years for which the rates apply. The type Imps used is defined as follows:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
//define type for improvements
[<XlInvisible>]
type Imps = 
    { StartYear : int
      EndYear : int
      ImpRates : float [,] }
    //define conversion from Excel data to improvements data type
    static member op_Explicit (v : XlData) : Imps = 
        match v with
        | XlArray2D(arr) -> 
            let lastcol = (arr |> Array2D.length2) - 1
            let rts = arr.[1..121, 1..lastcol] |> Array2D.map (!!)
            { StartYear = (!!arr.[0, 1] : float) |> int
              EndYear = (!!arr.[0, lastcol] : float) |> int
              ImpRates = rts }
        | _ -> raise (new ArgumentException("Improvements data invalid"))

  • Line 2 ensures that this code is not treated as a source for Excel UDFs by FCell
  • Lines 4 to 6 define the fields of the Imps Record type and each is given a suitable type.
  • Lines 8 to 16 handle conversion from the FCell XlData type to the Imps type.
  • Line 8 uses op_Explicit to define casting from XlData.
  • Lines 10 to 15 handle the valid case of being provided with a range of the right shape. The code uses the !! operator provided with FCell, which converts an Excel cell to a float or string automatically. In two of the lines, we then need to convert to the int type.
  • Line 16 handles any errors.
The data is then loaded using this code:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
[<XlInvisible>]
module Model = 
    //Mortality
    let maleMortImprov = Cell<Imps>.Create()
    let femaleMortImprov = Cell<Imps>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Mortality
        ThisWorkbook.SubscribeCell(maleMortImprov, "MaleMortImprov", "A1:EC122") |> ignore
        ThisWorkbook.SubscribeCell(femaleMortImprov, "FemaleMortImprov", "A1:EC122") |> ignore
        //temporary code to confirm imps loads
        maleMortImprov.AsObservable.Add(fun m -> MessageBox.Show("Loaded male improvement rates at 0 " + m.ImpRates.[0,0].ToString())|>ignore)

  • Lines 4 and 5 creates Calculation Engine cells containing Imps Records.
  • Lines 10 and 11 uses the FCell SubscribeCell, which binds the records to ranges A1:EC122 in the relevant sheets. When you enter a new value in these ranges, the cells will update their values as well.
  • Line 13 has some temporary code which exposes maleMortImprov as IObservable<Imps>, so that when a new value has been set or recalculated a temporary function is triggered to show a MessageBox.
When you build this version and make an edit to any cell in the sheet MaleMortImprov, then the MessageBox is shown.

Base Mortality Rates

The base mortality rates were created by Mortality Manager, and we will load these from JSON files. To achieve this we could just reference the Dlls used in Mortality Manager. To simplify the explanation and deployment, instead I have just embedded a copy of some of the code used in the workbook. A reference is however needed to Newtonsoft.Json.Dll, which is added using the References tab loaded using the Doc .NET Addin button.  The mortality code is:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46: 
47: 
//local mort table
type mortTyp = 
    | Sel
    | Ult

type mortInfo = 
    { nation : string
      reference : string
      typ : mortTyp
      description : string
      comments : string
      uri : string }

type mortTable = 
    | Select of int * float [] []
    | Ultimate of float []

type MortTbl = 
    { Name : string
      MortInfo : mortInfo
      MortRates : mortTable }

type MortIndx = 
    { Nation : string
      LocalFile : string
      Uri : string }

type MortInds = System.Collections.Generic.Dictionary<string, MortIndx>

module Mort = 
    /// load morttbl from json
    let load jfil = 
        let jfol = @"I:\vs\CalcEng\SingleProj\Mort"
        let jsonfil = Path.Combine(jfol, jfil)
        let mtab = JsonConvert.DeserializeObject<MortTbl>(File.ReadAllText jsonfil)
        mtab

module Ind = 
    /// load morttbl inds from json
    let load() = 
        let jfol = @"I:\vs\CalcEng\SingleProj\Mort"
        let jsonfil = Path.Combine(jfol, "Index.json")
        
        let mtabs = 
            if File.Exists jsonfil then JsonConvert.DeserializeObject<MortInds>(File.ReadAllText jsonfil)
            else new System.Collections.Generic.Dictionary<string, MortIndx>()
        mtabs

  • Lines 2 to 4 define a Discriminated Union type to define whether a Select or Ultimate table.
  • Lines 6 to 12 define details about the mortality table.
  • Lines 14 to 16 define a Discriminated Union type to define rates for a mortality table, depending on whether a Select or Ultimate table.
  • Lines 18 to 21 define the mortality table itself.
  • Lines 23 to 26 define the index for a mortality table.
  • Line 28 defines a dictionary that holds the mortality indices.
  • Lines 30 to 36 is a module to load a table from a JSON file.
  • Lines 38 to 47 is a module to load the indices from a JSON file.
 Lines 33 and 41 need to be edited to specify the tables used. A copy of these tables can be found here:
https://bitbucket.org/pb_bwfc/calceng/src/308b1e991fbc64b46c4d0e439bfddcbe811b8c2c/SingleProj/Mort/?at=default

The data is then loaded using this code:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
[<XlInvisible>]
module Model = 
    //Mortality
    let maleMort = Cell<mortTable>.Create()
    let femaleMort = Cell<mortTable>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen() = 
        //Mortality
        let LoadMort(nm : string) = 
            try
                let mortinds = Ind.load()
                let ind = mortinds.[nm]
                let tbl = Mort.load ind.LocalFile
                let rts = tbl.MortRates
                rts
             with
             |ex ->  MessageBox.Show("Load mort rates failed with error : " + ex.Message)|>ignore
                     Ultimate([||])
        maleMort.SetFormula(maleMortNm, LoadMort)
        femaleMort.SetFormula(femaleMortNm, LoadMort)
        let ShowMort shnm (rts : mortTable) = 
            match rts with
            | Select(per, rates) -> failwith "TODO"
            | Ultimate(rates) -> 
                let hdr = [ "Age"; "Ult q" ]
                ThisWorkbook.SetValue(hdr, shnm, 1, 1, 1, 2)
                ThisWorkbook.SetValue([ 0..120 ], shnm, 2, 1, 122, 1)
                ThisWorkbook.SetValue(rates, shnm, 2, 2, 122, 2)
        maleMort.AsObservable.Add(ShowMort "MaleMort")
        femaleMort.AsObservable.Add(ShowMort "FemaleMort")

  • Lines 4 and 5 creates Calculation Engine cells containing mortTable Records.
  • Lines 10 to 19 define a function to load a mortality table.
  • Lines 20 and 21 use SetFormula to load the rates using the names of the tables defined in the Settings sheet.
  • Lines 22 to 29 define the ShowMort function that shows the rates in a sheet whose name is defined using the variable shnm. (Note that this fails with Select tables currently - see line 24).
  • Lines 30 and 31 call the ShowMort function when the the mortality cells change.
When you build this version and make an edit to the names of the mortality tables, then the rates get updated when you click the Calculate Now button.

No comments:

Post a Comment