GREATools
Actuarial Shareware
| Home | Help |
|
GREAToolsActuarial Shareware |
|
Contents |
User Manual |
GREATools, Generalizable, Re-usable, Extensible Actuarial Tools is a very useful Excel-based collection of actuarial templates, functions, and tools, and a task/project management framework, which together provide support for various actuarial tasks, including loss distributions, aggregate distributions, loss development, simulation, histograms, statistics and graphics.
The package consists of five main pieces:
All five components are automatically installed when you download the software.
GREAT has been evolving since 1997. It began as a collection of routines which I used over-and-over again, and realized that I should code once and be done. This lead to many of the templates, such as the distributions group (lognormal parameters from mean and CV, and negative binomial r and p from mean and contagion, for example) and the loss development group, including development factors and averages from a triangle of losses, and the ever-helpful "delete below the diagonal" function to convert a square into a triangle.
At the same time I realized that all actuarial computations fall into a three step process, which I called a Task:
Thinking of tasks like this was prompted in part by the VTK notion of a filter, see VTKBook.
Basic task units can be chained together in complicated ways, with outputs from one becoming inputs into another. I call a group of tasks run in this way a project, but it is important to realize that a project is really just a particular type of task.
The Add In templates are a simple type of task: the inputs, transformation and output are all held in the same spreadsheet and may be implemented entirely in the spreadsheet, i.e. involve no code. Other tasks are more complicated. For example, I built a Variability Analysis Model (VAM) for CNA Re, which produced aggregate loss distributions from a very detailed breakdown of the book of business by line, layer, and attachment. VAM had a complicated input template format and used MATLAB to actually run the mathematics. VAM is proprietary to CNA Re and so is not included in this package.
My experience as an occasional user of VAM made me realize that users often need some help in setting up templates. On the other hand, you have to be able to mass produce templates. This led to my two-point design philosophy:
A dialog based interface to help set up a loss development blank is great if you only use it once in a while and can't remember all the options. It doesn't work when you have to work by state by line by business unit and have 400 triangles to produce.
I do not like dialog boxes. They are not that easy to code and are typically not worth the effort. They look cool and demo well, and support Point 1 of the philosophy, but are typically a hindrance to Point2. To the extent that dialog boxes are needed, I like to use an HTML brower control and make the dialog in HTML, on the fly if necessary. However, I try to avoid them as much as possible. Since Excel 97's wonderful "Data Validation" feature, which will turn any cell into a drop down box, the need for dialog boxes to control data input has decreased. That said, there are plenty of old fashioned dialog boxes in GREATools4.0.
To implement the philosophy I needed a task-management tool. I first thought about this in 1999, and came up with some initial ideas, but did not develop a finished framework. Then, in 2002, I started coding more "tasks" with the LinAlg object, to implement linear regression and other statistical methods. I began by building custom interfaces to these methods, and then suddenly realized what I was doing exactly fat into my earlier task/project framework. I dusted off my old notes and reimplemented them in GREATools4.00, with some simplifications from the original design. (In particular, I realized a project was a special kind of task.)
There are several ideas behind the task/project manager:
For example, to create numerous triangle tasks by state and line, the user could create a single new blank using the CreateNew method, getting a lot of help. Then he or she could dump that task out of the workspace, copy it 100 times with appropriate parameter modifications using all available Excel functions, and finally load all 100 new tasks back to the workspace. Obviously, a simple task could be written to handle "create tasks by state/line"! The user can also create a project to automatically run all 100 tasks sequentially. The tasks currently available in GREAT4.0 are described in the help.
The current release of the software can be downloaded as a Windows Installer (MSI) file. This means the software will install and configure itself, and that it can be managed and completely removed using Control Panel->Add/Remove Programs.
When you load GREATools, the following code runs:
Private Sub Workbook_Open()
'' check you have the menu on open
utilitiesMenu
End Sub
Utilities menu then sets up the user menu. It begins:
Sub utilitiesMenu()
Dim myBar As CommandBar
Dim myButton As CommandBarButton
Dim myButton2 As CommandBarPopup, myButton3 As CommandBarPopup
Dim myTopButton As CommandBarPopup
Dim myComboButton As CommandBarComboBox
Dim windowsButton As CommandBarPopup
' avoids errors
On Error Resume Next
Set myBar = CommandBars(BARNAME)
If Not (myBar Is Nothing) Then
'' ok bar already there
#If DebugMode Then
myBar.Delete
#Else
Exit Sub
#End If
End If
On Error GoTo 0
This code checks to see if the meny bar is already installed. If it is (so myBar is not nothing), then in non-debug mode, the routine just exits: there is nothing to do. In debug mode, you want to force the buttons to be re-assigned, so the bar is deleted and rebuilt afresh.
Set myBar = CommandBars.Add(Name:=BARNAME, Position:=msoBarTop, _
MenuBar:=False, Temporary:=False)
'' Utilities
Set myTopButton = myBar.Controls.Add(msoControlPopup)
myTopButton.Caption = "&Utilities"
myTopButton.BeginGroup = True
myTopButton.TooltipText = "Create a new task from existing Ranges"
'==========================================================================
'' Utilities::Triangles
Set myButton2 = myTopButton.Controls.Add(Type:=msoControlPopup)
myButton2.Caption = "&Triangles"
Set myButton = myButton2.Controls.Add(Type:=msoControlButton)
myButton.Caption = "&Development"
myButton.OnAction = "makeTrgFormula"
myButton.FaceId = 966
Set myButton = myButton2.Controls.Add(Type:=msoControlButton)
myButton.Caption = "&Averages Below Triangle"
myButton.OnAction = "trgAvgsOnly"
myButton.FaceId = 448
Set myButton = myButton2.Controls.Add(Type:=msoControlButton)
myButton.Caption = "Delete &Lower Half of Trg"
myButton.OnAction = "deleteLowerHalf"
Set myButton = myButton2.Controls.Add(Type:=msoControlButton)
myButton.Caption = "AS to &Std Format"
myButton.OnAction = "AStoStd"
Set myButton = myButton2.Controls.Add(Type:=msoControlButton)
myButton.Caption = "Diagonal to &Row"
myButton.OnAction = "DiagToRow"
Code similar to this is used in many of my spreadsheets. Using a menu bar and buttons is preferable to embedding buttons in the workbook. Menu bar buttons are always at the top of the application, they don't print, they don't cause focus problems, and they are where people expect command buttons to be. You also get tool tips and can combine graphics and text.
GREAT relies on several freely available software packages. This page gives a list and web addresses for each such package.
Blitz
FFTW
Intel MKL
LAPACK
VTK
(c) 2002 Stephen Mildenhall