Sunday, 18 May 2014

Charting and Stochastic Mortality (Delayed)

Stochastic Mortality - Challenges

I am planning to add some Stochastic Mortality functionality to Mortality Manager, but this is proving more of a challenge than expected.

The material publicly available seems quite limited. For example, if you go to the Wikipedia page for the Lee Carter model at http://en.wikipedia.org/wiki/Lee_Carter_Model you will find this statement: "There are surprisingly few software packages for forecasting with the Lee-Carter Model.".

I will be continuing to research this, but if anyone has any good references, please let me know.

In the interim, I have decided to add some charting to the application, which is a necessary capability, as and when, I add some stochastic functionality. 

F# Charting Options 

The very useful website F# for Fun and Profit gives a list of options on this page:
This includes the most commonly used option with F# interactive - FSharp.Charting, which I did use in an earlier post.

For Mortality Manager, I need a facility that works with Excel. Two links were provided:
  1. MSDN Excel Charting Tutorial
  2. Blog post on plotting functions in Excel
The former contains extracts from a very good book - Real-World Functional Programming. However, this is quite a specific use rather than a generic facility.

The latter example is more generic and includes some impressive examples, but it is limited to charting functions rather than sets of data.

I therefore decided to create a generic facility, which I could use for mortality manager, but may be more widely useful.

FSharp.XlInt

To progress this, I decided to expand the code that I had to "interop" with Excel. I also wanted to use this more widely. I therefore moved the code into a separate solution called "FSharp.XlInt"and created a new repository on bitbucket at:
 In progressing this development I had some issues with NetOffice (principally when debugging). I therefore moved over to using the standard Microsoft Interop Assemblies.

The code was fairly straightforward to create. For example, here is code to create a separate chart sheet with a simple line graph:
 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: 
/// create plot
static member create (xy : seq<'a * 'b>, ?main : string, ?xlab : string, ?ylab : string, ?legend : string) = 
    let chart = 
        if main.IsSome then Cht.add (main.Value)
        else Cht.add()
        
    let plt = 
        { xys = [ xy |> Seq.map (fun (a, b) -> (box a, box b)) ]
            main = main
            xlab = xlab
            ylab = ylab
            chart = Some(chart) }
        
    chart.ChartType <- XlChartType.xlLine
    chart.HasLegend <- false
    if main.IsSome then 
        chart.Name <- main.Value
        chart.HasTitle <- true
        chart.ChartTitle.Text <- main.Value
    if xlab.IsSome then 
        let x = chart.Axes(1) :?> Axis
        x.HasTitle <- true
        x.AxisTitle.Text <- xlab.Value
    if ylab.IsSome then 
        let y = chart.Axes(2) :?> Axis
        y.HasTitle <- true
        y.AxisTitle.Text <- ylab.Value
    if legend.IsSome then 
        chart.HasLegend <- true
        Plt.draw (0, plt, legend.Value)
    else Plt.draw (0, plt)
    plt

The code uses a method rather than a function as this allows us to use optional parameters.The code simply creates a Chart object (lines 3 to 6), it stores properties in a Record type (lines 7 to 12), adds properties as specified to the Chart object (lines 14 to 29) and then calls the draw method:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
/// draw xy on plot
static member draw (i, plt, ?legend) = 
    match plt.chart with
    | None -> ignore()
    | Some(chart) -> 
        let seriesCollection = chart.SeriesCollection() :?> SeriesCollection
        let series = seriesCollection.NewSeries()
        if legend.IsSome then series.Name <- legend.Value
        series.XValues <- plt.xys.[i]
                            |> Seq.map (fun (x, _) -> x)
                            |> Seq.toArray
        series.Values <- plt.xys.[i]
                            |> Seq.map (fun (_, y) -> y)
                            |> Seq.toArray

This method is passed in an integer, which is the index into the "xy" values, the chart as an option type and the legend if required. It then simply tests whether the option type has a value. If it has, then in lines 6 to 8 it gets the charts series collection then adds in a new series and sets the name to the provided legend. In lines 9 to 11, the xys field is used to set the values on the x axis. This files is a sequence of tuples holding the values for x and y. In lines 12 to 14 the values on the y axis are similarly set.

F# Interactive

This library is written so that it can easily be used from F# interactive. Below is the simplest of scripts:
 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
#r @"..\FSharp.XlIntLib\bin\debug\Microsoft.Office.Interop.Excel.dll"
#r @"..\FSharp.XlIntLib\bin\debug\FSharp.XlIntLib.dll"

open FSharp.XlInt

//simple series function
let xy = 
    [| (0.0, 0.0)
       (1.0, 1.0)
       (2.0, 2.0) |]

let plt = Plt.create (xy, main = "lines", xlab = "x values", ylab = "y values", legend = "First")

let xy1 = 
    [| (0.0, 0.0)
       (1.0, 2.0)
       (2.0, 3.0) |]

Plt.addline(xy1,plt,"Second")

We first set up the required references (lines1 to 4). We then define a simple set of xy data and create a chart using this data (lines 7 to 12). We can then add a second set of data to the chart (lines 14 to 19). This is displayed in Excel:

Chart in Mortality Manager

I have used this facility to add a chart as part of the compare facility for mortality tables. I added this code in Cmp.fs:
 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
// do chart
let rtst1 = Mort.getrtst seltbl1
let rtst2 = Mort.getrtst seltbl2
let p = Plt.create (rtst1.[rtst1.Length - 1].[20..85], "Comparison Chart", "Age", "Rate", seltbl1.Name)
Plt.addline (rtst2.[rtst2.Length - 1].[20..85], p, seltbl2.Name)
if rtst1.Length > 1 then 
    for i = 0 to rtst1.Length - 2 do
        Plt.addline (rtst1.[i].[20..85], p, 
                        if i = 0 then seltbl1.Name + "_Sel"
                        else seltbl1.Name + "_Sel+" + i.ToString())
if rtst2.Length > 1 then 
    for i = 0 to rtst2.Length - 2 do
        Plt.addline (rtst2.[i].[20..85], p, 
                        if i = 0 then seltbl2.Name + "_Sel"
                        else seltbl2.Name + "_Sel+" + i.ToString())

The mortality rates are converted to an array of tuple arrays (lines 2 to 3). There are multiple tuple arrays if the rates include select rates. A chart is then created for the ultimate rates for the first set of mortality rates (line 4). The ultimate rates for the second set of mortality rates are then added (line 5). If the first set has select rates then these are also added (lines 6 to 10). Similarly, select rates are added for the second set (lines 11 to 15).

As an example, this is the chart for AMC00 compared to AFC00:



No comments:

Post a Comment