Make Your Actuarial Spreadsheets Fly Designing and Programming Spreadsheets for Risk Measurement William C. Scheel dfa technologies, llc pete Wick Milliman usa
Make Your Actuarial Spreadsheets Fly Designing and Programming Spreadsheets for Risk Measurement William C. Scheel DFA Technologies, LLC Pete Wick Milliman USA
2002 Risk & Capital Management Seminar
Spreadsheet-Centric Model Design
Advantages
Flexible model design; open platform
Visual Basic for Applications
Accessible to both non-programmers and programmers
Rapid application development and deployment
Disadvantages
Inadequate to task of complex models
Seemingly slow computation
The Fast and Slow if It
When does VBA code run faster than cell-based logic? An example using the Burville simulation method
Some cell-based arrays with formulas such as lookups run very well
John Burville’s Simulation Method
Intervalize a probability distribution; create a 1,000 element vector with x values loaded in proportion to interval probabilities, f(x)
Staged Simulation involves:
Generate a uniform value, u, between 1 and 1,000
Dereference element u of the array to obtain the inverse value
Use a placeholder for any interval where finer granularity is needed. Load another array of length 100 with x values in proportion to the sub-interval probabilities.
If the placeholder is observed on a draw, use another uniform selection to sample from the sub-array
Use as many levels as needed to achieve granularity. E.g., tail areas of the distribution
Excel VBA Programming
The VBA Editor
Viewing the object model
Dynamic range specification and memory-to-cell data transfer
When a spreadsheet isn’t fast enough, you always can use multiple computers.
An introduction to Microsoft MSMQ and its use with Excel.
Pass handles to objects using ByRef arguments. But, always store them in variables of type “Object.” Do NOT use early bound variable types.
My Favorite Excel Tricks…
CurrentRegion (for cell block identification)
Resize (for ranges)
Variant array assignments for transferring content between cells and memory
Collections and “super” collections (collections of collections)
Public methods in worksheets, workbooks, and forms
My Favorite Excel Tricks…
VBA calculation of specific ranges or worksheets is NOT workbook calculate=F9….Calculate or .Calculate often provide needed control over calculation order
.copy
With
With Application.WorksheetFunction … .AVERAGE() ... .PERCENTILE … . … End With
Region item specification using .CurrentRegion
My Favorite Excel Tricks…
Use worksheets as property bags for persisting information
Microsoft Scripting Runtime class for file system objects (Reference=Microsoft Scripting Runtime)
DFATech methods for persisting ranges to XML files
My Favorite Excel Tricks…
Data binding between worksheet data and controls (e.g., lists) is easy programmatically when you remember this assignment: .RowSource = ws.name & “!” & r.address
My Favorite Excel Tricks…
Use worksheets as property bags for persisting information
DFATech methods Get/PutRegionItem used for persisting in-memory arrays to region items in worksheets
DFATech methods for persisting ranges to XML files
Microsoft Scripting Runtime class for file system objects (Reference=Microsoft Scripting Runtime)
Spreadsheet-Centric Programming
Worksheet cells and functions serve as a rich enhancement to a programmers tools
Intrinsic Excel functions work well in programs
User-designed methods in sheets, forms can be called from modules, classes and elsewhere
“Program behind” workbook and worksheet events
Objects
The structure of an object is a class.
Private variables for properties.
Methods for program actions
Events for doing something in response to something happening affecting the object
An instantiation of an object from a class is like a copy of the structure
A worksheet can be a class! Copies of worksheet templates are objects. LOBs are easily cloned.
Must know IDE (integrated development environment)
Must have exceptionally high frustration tolerance
How To Learn Model RAD
Build a reinsurance convolution model…it is a good place to identify the hurdles. Do it in VBA code as a learning exercise.
Range referencing is the hard part
What We Learned...
Value-based spreadsheets load and can be managed quickly
Do not parse individual cells; assign a range of them to in-memory variant arrays and do the parsing there. Do this in reverse when loading cells with data.
In-memory data can be persisted to spreadsheets or ADO recordsets very fast
What We Learned…
You can rapidly sample from pre-generated claims information using ranges
VLOOKUP can be used on intervalized cumulative probability distributions in array formulas with astonishing speed
Slice’n’dice with VBA code on in-memory data
What We Learned…
Proxy variables in strings can be easily converted programmatically to formulas; a type of Excel-scripting without using explicit cell references
Advanced Techniques…
Use the Windows messaging paradigm in VBA code to communicate among Excel objects
Consider the use of Worksheet templates (not just Workbook templates)
Use multiple computers strung together with MSMQ (Microsoft Message Queue)…it works on all MS operating systems.
Advanced Techniques…
Constantly remind yourself that an Excel worksheet is just an event-savvy object
Collections (and dictionaries, which are like keyed or indexed objects) are among the best programming tools. Collections of collections (super collections) also are useful.
Advanced Techniques…
Excel classes are always useful. They are essential for WithEvents programming.
Forms+Modules; Forms+Classes+Collections
Single computer, cross-process communication (say, Visio-to-Excel) can be done with object references passed ByRef between them! This cannot be done across machine boundaries.
Microsoft Office and .NET?
Office products such as Excel will increasingly consume web services. Model building becomes even easier and more RAD-oriented.
.NET web service
DFA will change systematically as new components come on-line
Vended web services such as scenario generators, specialized risk measurement techniques, data (e.g., payment patterns)
Microsoft Office and .NET?
“Data” web services (web queries) that plug-into-your-model will come on-line. E.g., investment and claims scenarios, payment patterns
June 17, 2002, EWeek, “Microsoft to Give XML Bigger Role in Office” By Peter Galli
Microsoft Corp., which faces mounting competition and price pressure focused on its Office desktop productivity suite, is set to release the first beta of an Office upgrade….Due later this year, the beta will feature far greater use of XML and Web services for reporting, analyzing, importing and exporting information—particularly in Outlook and Excel, Steven Sinofsky, senior vice president for Office, in Redmond, Wash., told eWeek.
Q&A: DFA Modeling: Should You Be a “Programmer” too?
When turn-key modeling isn’t enough!
Knee-jerk acceptance of “do it all” in Excel cell-based formulas
Not just a spreadsheet-centric modeling issue…”being a programmer” and architect/builder is generic issue for all computer-based models
References
Excel VBA Programming: M. C. Martin, et al, Mastering Excel 2000; J. Green, Excel 2000 VBA
Bootstrapping: B. Efron and R. J. Tibshirani, An Introduction to the Bootstrap
