Spreadsheets Fly Designing and Programming Spreadsheets for Risk

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 cellbased 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 Simple. Convolution. 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 <bootstrap range>. Calculate, do the <bootstrap statistic range>. 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 By. Ref arguments. But, always store them in variables of type “Object. ” Do NOT use early bound variable types.

My Favorite Excel Tricks… • Current. Region (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=F 9…<ws>. Calculate or <r>. Calculate often provide needed control over calculation order <range>. copy <range destination cell> With <object>…. End With Application. Worksheet. Function …. AVERAGE(<range/array>). . PERCENTILE …. <Whatever> … End With Region item specification using <single cell range>. Current. Region

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/Put. Region. Item 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: . Row. Source = ws. name & “!” & r. address

My Favorite Excel Tricks… • Use worksheets as property bags for persisting • • • information DFATech methods Get/Put. Region. Item 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 With. Events programming. Forms+Modules; Forms+Classes+Collections Single computer, cross-process communication (say, Visio-to-Excel) can be done with object references passed By. Ref 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 e. Week.

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 • Got an Excel question? scheel@mindspring. com