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
Conclusions from Burville.xls example:
Complex cell logic should be in code
Data sheets with values work fast
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
Traversing objects in code
IDE Tools.Options Editor Tab
Auto Syntax Check OFF
Require Variable Declaration ON
Auto Indent; Tabs Width 2
Intellisense code completion
Why Generate a Worksheet of Claims?
Excel is a good database mechanism with value-based workbooks
Random numbers can be samples of random numbers
Convolutions, event-driven logic can be done with claims worksheets
Conclusions from Generate.xls
Range-based sampling; a programming trick
Programmatic Lookup in F(x) significantly faster then inverse function in array formula
Create bootstrap samples from ranges of values
Rapid Convolutions…
Yes, Virginia, Santa Claus may not be able to always use Fast Fourier Transforms (FFT)
Event monitoring.
Messaging and computational design
Rapid Convolutions…
Conclusions from SimpleConvolution.xls.
Construct worksheets with sample values (claims) that are generated prior to the convolution
Use randomly constructed range references to quickly obtain blocks of random variables
Use worksheet functions, if possible, to do convolution math
Rapid Bootstrapping
More conclusions from Generate.xls
Abstract data to be abstracted to an array of randomly selected ranges
Use VBA code to calculate bootstrap samples with range calculate applied to the array
Use cell formulas that reference the bootstrap sample range to calculate statistics being bootstrapped
After a .Calculate, do the .Calculate
This only can be done using VBA programmatic techniques
Designing Spreadsheet-Centric Models
Do what Excel+VBA does best…Rapid Application Development (RAD)
Always think components
No model is beyond the scope of Excel!
User interface…80-90 percent of the effort
Think virtual general ledger
Statistical and graphical renderings may be done with “object” worksheets and VBA
Rapid Application Development
RAD modeling is a VBA programmer with Excel
Actuary is an interpreter of information
Actuary is a forecaster using models
Actuary should do RAD in Excel
Excel as a Data Engine
Value-base worksheets
Data Sort/Autofilter vs ADO (Active Data Objects class). Both can be done programmatically
Conclusions from Vgl.xls
ADO recordsets made from ranges
Virtual general ledgers from range objects
Excel as a Component Engine
Oh, by-the-way. A worksheet and just about everything else in Excel can be a component.
Demonstration of drag-and-drop modeling to illustrate:
Worksheet components
Multiple process interaction (Visio and Excel)
Excel on Multiple Computers
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 Get/PutRegionItem used for persisting in-memory arrays to region items in worksheets
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.
Microsoft Visio Drag-and-drop example
How Do You Learn the Tricks?
Must know Excel’s Class Model. The object browser
Must know elements of object programming
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
