Actuarial Shareware

Home Help

GREATools Open Source Software Project

COTOR Presentation


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:

  1. GREATools.xla, and Excel Add In which provides a consistent interface to all the components, and contains the code for all the Excel-specific routines. It is written in VBA, and uses the ADO database interface.
  2. A Task/Project Manager (TPM) which is implemented as part of the Add In, again in VBA/ADO.
  3. SADCo2.dll: a COM object which handles aggregate distributions, parameter management and so forth.
  4. LinAlg.dll: a COM object which provides support for linear models, generalized linear models, Choleski decomposition etc.
  5. VISCo(2).dll: an ActiveX object which provides 3-d graphics capabilities.

All five components are automatically installed when you download the software.

Software Design Philosophy

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:

Data gathered either as a direct input, a range in a spreadsheet, or the results of a query against a database
Some kind of manipulation of the data, including the ability to make a selection or the result of an extensive simulation.
Write the results of the transformation to a spreadsheet file or back to a database.

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:

  1. Software should be easy for the occasional or novice user
  2. Software should be powerful for the experienced user

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:

  1. TPM provides a way to save task and project definitions independently of any particular spreadsheet. It does this by using a central database called a workspace. The database can be shared between different users, to facilitate working in teams. The Add In contains code to create a workspace database in any SQL compliant database, including Oracle, Access, SQL Server, or DB2. The user just has to supply a connection string to logon to the database.
  2. The TPM workspace contains a list of tasks, and for each task, a list of input parameters and output values. The framework can use these base entries to instantiate user tasks, and to create "blanks" in a workbook for the user to complete which can then be loaded back to the workspace.
  3. TPM provides a software framework which allows the programmer to add new tasks with minimal effort. The framework handles updating the user interface (Add In menu bar), saving and loading task defintions to the workspace, executing a task, displaying a task and editing it, either in a dialog, or en masse in a worksheet.
  4. To add a new task to the framework, the implementor adds the appropriate input and output arguments to the workspace database, and then adds a class to the Add In with four methods which handle
    1. what to do when the user wants a lot of guidance to create a new task (Create New)
    2. what to do when the user wants to create a new task instance in the workspace from ranges which have already been set up (Create from Existing Range)
    3. There is no number 3?
    4. Execute: when the user wants to run the task
  5. In addition the TPM handles generically (independently of task type) dumping the contents of the workspace to a worksheet, where they can be manipulated and extended, and loading back to the workspace from such a workspace dump.

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.


Download the Software

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.

Software Requirements


Coding Techniques

Startup and Menu Creation

When you load GREATools, the following code runs:

Private Sub Workbook_Open()
    '' check you have the menu on open
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
        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.

Open Source Code Used in GREAT

GREAT relies on several freely available software packages. This page gives a list and web addresses for each such package.






Intel MKL








(c) 2002 Stephen Mildenhall