Tuesday, 19 May 2015

FCell Calculation Engine - Loading the Inputs

Introduction

In this post, we will just be loading inputs stored in sheets of the Excel workbook so that they are available for use by the Calculation Engine. In the sections below, we will cover loading:
  1. Policy Data
  2. General Settings
  3. Investment Rates and Illiquidity Premiums
  4. Lapse Rates
The model at the end of the post will therefore not be doing anything, but processing of these inputs will be added in subsequent posts.

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

 

Policy Data

The policy data is loaded from sheet Pol. It seems sensible to load this into a F# Record. We therefore use this code to define the type Pol:

 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: 
//define type for policies
[<XlInvisible>]
type Pol = 
    { EntryYear : int
      EntryMonth : int
      EntryAge1 : float
      Sex1 : string
      EntryAge2 : float
      Sex2 : string
      DeathBen : float
      MaturityBen : float
      Term : int
      PremTerm : int
      AnnPrem : float
      Freq : int
      EscRate : float
      PolNum : string }
    //define conversion from Excel data to policy data type
    static member op_Explicit (v : XlData) : Pol = 
        match v with
        | XlArray2D(arr) when arr.GetLength(0) = 1 && arr.GetLength(1) = 14 -> 
            { EntryYear = (!!arr.[0, 0] : float) |> int
              EntryMonth = (!!arr.[0, 1] : float) |> int
              EntryAge1 = !!arr.[0, 2]
              Sex1 = !!arr.[0, 3]
              EntryAge2 = !!arr.[0, 4]
              Sex2 = !!arr.[0, 5]
              DeathBen = !!arr.[0, 6]
              MaturityBen = !!arr.[0, 7]
              Term = (!!arr.[0, 8] : float) |> int
              PremTerm = (!!arr.[0, 9] : float) |> int
              AnnPrem = !!arr.[0, 10]
              Freq = (!!arr.[0, 11] : float) |> int
              EscRate = !!arr.[0, 12]
              PolNum = !!arr.[0, 13] }
        | XlArray2D(arr) -> raise (new ArgumentException("wrong array size"))
        | _ -> raise (new ArgumentException("Model Point invalid"))
  • Line 2 ensures that this code is not treated as a source for Excel UDFs by FCell
  • Lines 4 to 17 define the fields of the Pol Record type and each is given a suitable type.
  • Lines 19 to 37 handle conversion from the FCell XlData type to the Pol type.
  • Line 19 uses op_Explicit to define casting from XlData.
  • Lines 21 to 35 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 some lines (e.g. line 22), we then need to convert to the type used in the Record.
  • Line 36 handles the case where the range is not the right shape.
  • Line 37 handles any other errors.

The data is then loaded using this code:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
[<XlInvisible>]
module Model = 
    //Pol
    let pol = Cell<Pol>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Pol
        ThisWorkbook.SubscribeCell(pol, "Pol", "A2:N2") |> ignore
        //temporary code to confirm pol loads
        pol.AsObservable.Add(fun p -> MessageBox.Show("Loaded policy number " + p.PolNum)|>ignore)

  • Line 4 creates a Calculation Engine cell pol containing a Pol Record.
  • Line 9 uses the FCell SubscribeCell, which binds pol to the range A2:N2 in the sheet Pol. When you enter a new value in that range, the cell will update its value as well.
  • Line 11 has some temporary code which exposes pol as IObservable<Pol>, 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 specified range, then the MessageBox is shown.

 

General Settings

The settings are loaded from sheet Settings. These are loaded individually using this code (only relevant parts shown):

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
[<XlInvisible>]
module Model = 
    //Settings
    let startDate = Cell<DateTime>.Create()
    let futYears = Cell<int>.Create()
    let inflatRate = Cell<float>.Create()
    let renExp = Cell<float>.Create()
    let maleMortNm = Cell<string>.Create()
    let femaleMortNm = Cell<string>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Settings
        ThisWorkbook.SubscribeCell(startDate, "Settings", "B2") |> ignore
        ThisWorkbook.SubscribeCell(futYears, "Settings", "B3") |> ignore
        ThisWorkbook.SubscribeCell(inflatRate, "Settings", "B4") |> ignore
        ThisWorkbook.SubscribeCell(renExp, "Settings", "B5") |> ignore
        ThisWorkbook.SubscribeCell(maleMortNm, "Settings", "B6") |> ignore
        ThisWorkbook.SubscribeCell(femaleMortNm, "Settings", "B7") |> ignore
        //temporary code to confirm renExp loads
        renExp.AsObservable.Add(fun r -> MessageBox.Show("Loaded renExp " + r.ToString())|>ignore)

  • Lines 4 to 9 creates Calculation Engine cells for each item of appropriate types. FCell can automatically handle these types.
  • Lines 14 to 19 uses the FCell SubscribeCell, which binds each item in the sheet Settings. When you enter a new value in that range, the relevant cell will update its value as well.
  • Line 21 has some temporary code which exposes renExp as IObservable<float>, 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 cell B5, then the MessageBox is shown.

Investment Rates and Illiquidity Premiums

The rates are loaded from sheets InvRates and IllPrem. These are loaded using this code (only relevant parts shown):

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
[<XlInvisible>]
module Model = 
    //Investment
    let invRates = Cell<float []>.Create()
    let illPrem = Cell<float []>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Investments
        ThisWorkbook.SubscribeCell(invRates, "InvRates", "B2:B136") |> ignore
        ThisWorkbook.SubscribeCell(illPrem, "IllPrem", "B2:B136") |> ignore
        //temporary code to confirm invRates loads
        invRates.AsObservable.Add(fun i -> MessageBox.Show("Loaded invRates at 0 " + i.[0].ToString())|>ignore) 
  • Lines 4 and 5 creates Calculation Engine cells invRates and illPrem of type float[] . FCell can automatically handle this type.
  • Lines 10 and 11 uses the FCell SubscribeCell, which binds the ranges in sheets InvRates and IllPrem. When you enter a new value in these ranges, the relevant cell will update its value as well.
  • Line 13 has some temporary code which exposes invRates as IObservable<float[]>, 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 in sheet invRates, then the MessageBox is shown.

Lapses

The rates are loaded from the sheet Lapses. These are loaded using this code (only relevant parts shown):

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
[<XlInvisible>]
module Model = 
    //Lapses 
    let lapses = Cell<float []>.Create()

    [<XlWorkbookOpen>]
    let workbookOpen () = 
        //Lapses
        ThisWorkbook.SubscribeCell(lapses, "Lapses", "B2:B136") |> ignore
        //temporary code to confirm invRates loads
        lapses.AsObservable.Add(fun l -> MessageBox.Show("Loaded lapses at 0 " + l.[0].ToString())|>ignore)
  • Line 4 creates Calculation Engine cell lapses of type float[].
  • Line 9 uses the FCell SubscribeCell, which binds the ranges in sheet Lapses. When you enter a new value in that range, the cell will update its value as well.
  • Line 11 has some temporary code which exposes lapses as IObservable<float[]>, 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 in sheet Lapses, then the MessageBox is shown.

No comments:

Post a Comment