Friday, 27 January 2012

Using Event Loss Tables and Year Loss Tables

This leads on from the previous blog entry on probabilistic models. The platform I am working on incorporates catastrophe models that produce Event Loss Tables or ELTs. These are tables containing Events, Ground Up Loss, Ground Up Loss standard deviation, loss frequency and various other loss perspectives. These loss tables are sorted in order of increasing ground up loss giving a cumulative loss distribution. A function that fits this curve is called a cumulative distribution function or cdf. Pricing a Cat XL treaty is done by integrating the CDF. This can be made in a number of ways:

The simplest way is by burning cost. This means just summing up the events taht trigger the treaty. This works very well in low layers because there are naturally more low cost events than high cost events. For high layers there may be only a few events and the accuracy is not sufficient. In these cases we can try to fit a points to a function such as Paratoo or log polynomial and then integrate the resulting function over the Cat XL layer. There are a number of problems with this approach, for example the numerical regression may give unreasonable coefficients that give unexpected results. The method that we use the most is to to sampling each loss assuming a distribution like log normal and the standard deviation and applying a burning cost.

Event loss tables can become quite large but are still manageable compared to Year Loss Tables. For example it is possible to make a loss estimation of a current event by making a query based on event or combination of events in the event loss table. It is not possible to combine different ELTs of different event sets

Some cat models incorporate some information over when an event occurred. This makes it possible to model clustering of events much more accurately. This has quite an impact on pricing. For example inuring treaties can be priced much more accurately. Imagine with an ELT you need to assume an average number of events in a year. There is a big difference between having an average of 2 events per year and a year loss table that says on the first year there are 0 events on the second year there are 4 events. Both have a mean of 2 events but in the first year the treaty is not touched and in the second year it can depend on the number of reinstatements.

Year loss tables are event based which makes them easy to combine with other YLTs.

Here are the steps needed to create a YLT from an ELT

1. Decide on how long the YLT is to be based
2. Determine an average number of events that happen in a year
3. Use a Poison distribution with a random sample to determine how many events happen in a particular year
(The poison distribution provides a sequence of integer numbers )
4. Sample from the ELT randomly these events
5. Based on the Mean Loss and STD Loss make a sample with a distribution such as a Beta or log normal to determine an actual loss using another random variable

The random variables are created from seeds. For a given seed the same sequence of random numbers are generated each time the application is run. Mathematically it turns out that this is an acceptable way of sampling and it means that the resulting YLTs are not needed to be stored. This is advantageous because in order to get events in the high layers a long simulation period is needed which means the storage requirements of a YLT is much bigger than an ELT.

Now let's drill into the details. Step 3 sounds very easy, make a sample random sample of a Poisson distribution. The thing is that Poisson is expressed in a formula like frequency = Function( Mean, Standard Deviation). For example in Excel it looks like POISSON(A3,$B$1,TRUE). The problem is that we need to make random samples of the number of events with a given mean and sd. To do this we need to use something called Inversion theory. There are some statistical packages that have this function built in. For example in r it looks like

pois <- rpois(100, lambda=3)


[1] 1 2 3 2 2 2 3 3 6 2

In C# you need to find a library to do this. I found 2 that look interesting open source projects that have these functions

Monday, 16 January 2012

Introducing probablistic models

Recently I have been revisiting some mathematics. Often maths becomes very complex very quickly so here I enjoy keeping it simple.

Probabilistic models consist of 3 parts
1. A model that produces a random variable(s) X
2. A model that creates probabilities for the random variables
3. The sum of frequencies must add up to 1

If we take the example of flipping a coin: The coin has 2 faces and these correspond to the random variable X. The values of X can be give a value such as X=1 for heads and X=2 for tails. The model would be the probability of X=1 is 0.5 and the probability of X=2 is 0.5 and the sum of these probabilities sum to 1. Then you have a coin that may not be completely round or evenly balanced meaning it may behave differently to the model.

There are other types of models for example the number of people in a queue can be modeled with Poison. Mr Poison was a lawyer that studied the lengths of queues Research on the "Probability of Judgments in Criminal and Civil Matters" and build a model around this. This is a very interesting model when modeling the length of queues, It's also very useful when converting an Event loss table to a Year loss table. Here the problem is to sample the number of events that happen in a year given a mean and a standard deviation

Catastrophe probabilistic models can also be broken down into these 3 components.
1. A modeling that combines the Hazard, Vulnerability and Exposure to produce the random variable ground up loss
2. A model that assigns a frequency for each event
3. An event catalogue where the sum of frequencies adds up to the total length of time the catalogue is constructed for

A catastrophe model is more elaborate than the coin example and comprises of the Hazard (the earthquake or the windstorm), the vulnerability (how fragile it is) and en exposure (what buildings are at risk)

Thursday, 5 January 2012

Experimenting with Powershell

Command line scripts have been around for a long time. Power shell adds a powerful fluent syntax that enables a richer and more expressive way of coding our batch files. Powershell comes with Windows 7 and above as well as Windows Server 2008 R2 and above

Here is how to start using it... The first time you start PowerShell (Under Administrative tools I selected Windows PowerShell Modules) you will get the following warning

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.
WARNING: File C:\Windows\system32\WindowsPowerShell\v1.0\Modules\PSDiagnostics\PSDiagnostics.psm1 cannot be loaded

because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.

PS C:\Users\nfindlater>

To fix this do the following steps

1. Under Administrative tools select Server Manager
2. In the tree view select Server Manager/Features
3. Add Feature
4. Select Windows PowerShell
5. Next, Install
6. under Accessories in a directory caller PowerShell

PoweShell basics

Powershell respects admin rights. Certain commandlet information require elevated privileges. Meaning that if you need access to information that requires elevated privileges you need to start powershell as administrator.

Here is a simple example how powershell can be used. Start notepad then execute

get -process notepad

This gets a list of processes named “notepad” and pipes them to a Stop-Process commandlet which has the effect of closing the notepad that you just opened.

There are other ways of getting to the same information, for example

PS C:\Users\nfindlater> get-process | where { $_.ProcessName -eq 'notepad' }
Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
------- ------ ----- ----- ----- ------ -- -----------
47 2 1108 3732 61 0.20 3112 notepad
Here’s an example using the event log
PS C:\Users\nfindlater> get-eventlog -LogName application -Newest 3

Index Time EntryType Source InstanceID Message
----- ---- --------- ------ ---------- -------
77896 Oct 04 15:14 Information Microsoft-Windows... 1 The description for Event
77895 Oct 04 15:14 Information Desktop Window Ma... 1073750827 The Desktop Window Manager
77894 Oct 04 15:14 Information Winlogon 1073745925 Windows license validated.

The output from a commandlet can be formatted in various ways, here are some examples:

get-process notepad
Format-List *

Format-Table name, starttime

where-object { $_.starttime }
Format-Table name, starttime
For WMI calls the command “get-wmiobject win32_service” does not give much detail. But the following provides plenty of detail:

get-wmiobject win32_service
Format-list *

Here are some examples of how to start applications

Start-process -FilePath notepad
Start-process -FilePath notepad -WindowStyle minimized

PS C:\Users\nfindlater> Get-EventLog application | where-object { $_.message -match 'Desk' }
Index Time EntryType Source InstanceID Message
----- ---- --------- ------ ---------- -------
77895 Oct 04 15:14 Information Desktop Window Ma... 1073750827 The Desktop Window Manager was unable to start ...
This is faster
C:\Users\nfindlater> Get-EventLog application -InstanceId 1001

PS C:\Users\nfindlater> Get-EventLog application -newest 1
Index Time EntryType Source InstanceID Message
----- ---- --------- ------ ---------- -------
77897 Oct 04 16:40 Information MSSQLSERVER 1073760088 Database backed up. Database: ICW_20081001_tRI_...
Here are some more examples using the eventlog

PS C:\Users\nfindlater> Get-EventLog application -newest 1
format-list *

EventID : 18264
MachineName :
Data : {88, 71, 0, 0...}
Index : 77897
Category : Backup

Here are some examples with dir.

Give me all files containing the string ground (Very usefull since in Windows 7 the text search function is missing)

dir *.* -recurs
select-string "ground"
Format-List FileName

Here’s a more elaborate version looking for files with “pro” in the name

dir *.* -recurs
Where-Object {$_.Name -like "*pro*"}
select-string "ground"
Format-List FileName

dir *.* -recurs
Where-Object {$_.Name -like "*"}
select-string "ground"
Format-List FileName

Filename : myfile.vcxproj

dir *.* -recurs
Where-Object {$_.Name -like "*vcx*"}
select-string "ground"
Format-List LineNumber, Path

LineNumber : 64
Path : D:\path\myfile.vcxproj

Here’s an example of how to replace a string in a file

(Get-Content .\File.TXT)
ForEach-Object {$_ -REPLACE "'", """" }
Set-Content TEST2.TXT

Here are some examples of how to read parts of text files

Get-Content ".\file_test.txt"
select -First 10

Get-Content ".\file_test.txt"
select -Last 10

Get-Content ".\file_test.txt"
select -First 3
select -Last 1

Get-Content ".\file_test.txt"
select -Skip 10

Power Shell with SQL Server

I found the following link that describes how to get the SQL features of powershell working

Basically you need to run the following script to enable sql commands to work
# Add the SQL Server Provider.
$ErrorActionPreference = "Stop"
if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
throw "SQL Server Provider for Windows PowerShell is not installed."
$item = Get-ItemProperty $sqlpsreg
$sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)

# Set mandatory variables for the SQL Server provider
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000
# Load the snapins, type data, format data
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml
update-FormatData -prependpath SQLProvider.Format.ps1xml

After executing this scripts you can run commands like:

Invoke-Sqlcmd -Query "SELECT @@VERSION;"

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) ...

Here’s a more entertaining script that executes an arbitrary command on a list of SQL servers

$servers=get-content "D:\Nigel\PowerShell\SqlServers.txt"
foreach($server in $servers)
# go to each server and execute the command in D:\Nigel\PowerShell\SQLCommand.txt
# Invoke-sqlcmd -ServerInstance $server -Database master -InputFile “D:\Nigel\PowerShell\SQLCommand.txt”;
Invoke-Sqlcmd -ServerInstance $server -Query "SELECT @@VERSION;"
Extending this to include input from a file:
$servers=get-content "D:\Nigel\PowerShell\SqlServers.txt"
foreach($server in $servers)
# go to each server and execute the command in D:\Nigel\PowerShell\SQLCommand.txt
# Invoke-sqlcmd -ServerInstance $server -Database master
Invoke-Sqlcmd -ServerInstance $server -InputFile “D:\Nigel\PowerShell\SQLCommand.txt”;



Here’s a URL that describes how to make your own powershell commandlets
Building PowerShell Cmdlets with Visual Studio 2008

1.Download Windows PowerShell Cmdlet templates from CodePlex
2.Run the VSI installer to install the project and item templates
3.Open Visual Studio 2008 and select File->New->Project
4.From the New Project dialog select the ‘PowerShellCmdlet’ template
5.Now right click on the project and select ‘Add New Item’. This will show the ‘Add New Item’ dialog. You can see that there are class templates for PSCmdlets, Cmdlets, SnapIns and XML helper files. For now select the Cmdlet template.
6.The ProcessRecord method performs the actual processing for the Cmdlet, in this example we will just call the WriteObject method to display the ‘Hello World’ message.

using System.Management.Automation;
namespace HelloPowerShell
[Cmdlet(VerbsCommon.Get, "HelloCmdlet")]
public class HelloCmdlet : Cmdlet
protected override void ProcessRecord()
WriteObject("Hello World!");

7.Again right click on the project and select add new item, this time select the ‘PowerShellCmdlet Help XML’. The template will automatically prefix ‘.dll-help.xml’ therefore you should just type ‘Get-Hello’ as the name.

using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
namespace HelloPowerShell
public class HelloSnapIn : CustomPSSnapIn
private Collection _cmdlets;

/// Gets description of powershell snap-in.

public override string Description
get { return "A Description of HelloCmdlet"; }
/// Gets name of power shell snap-in

public override string Name
get { return "HelloCmdlet"; }
/// Gets name of the vendor

public override string Vendor
get { return ""; }
public override Collection Cmdlets
if (null == _cmdlets)
_cmdlets = new Collection();
_cmdlets.Add(new CmdletConfigurationEntry
("Get-HelloCmdlet", typeof(HelloCmdlet), "Get-HelloCmdlet.dll-Help.xml"));
return _cmdlets;

8.Now add another new item and select the ‘PowerShellCmdlet SnapIn’ template. Call this ‘HelloSnapIn.cs’

9.By default the SnapIn template fills out some sample information using MyCmdlet as the name simply change this to refer to Get-HelloCmdlet.

10.You can build the project and get a dll

11.Now start Windows PowerShell and run with administrator privileges as you will be installing a Cmdlet.

12.If you are using a 64-bit version of windows issue the command:
Set-Alias installutil C:\Windows\Microsoft.NET\Framework64\v2.0.50727\InstallUtil.exe

If you have a 32-bit version of windows issues the command:
Set-Alias installutil C:\Windows\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe

13.Now install your Cmdlet with the command:
installutil HelloPowerShell.dll

14.You can verify that the Cmdlet has been installed with the command:
Get-PSSnapin -Registered

15.Add the snap-in to your shell with:
Add-PSSnapin HelloCmdlet

16.You can run the Cmdlet with the command Get-HelloCmdlet and you should see the message “Hello World!”

By the way in VS2010 the PowerShell templates are there by default