Session 15 OF, Unpacking the Actuary's Technical Toolkit Moderator:
Albert Jeffrey Moore, ASA, MAAA
Presenters:
Melissa Boudreau, FCAS
Albert Jeffrey Moore, ASA, MAAA Christopher Kenneth Peek Yonasan Schwartz, FSA, MAAA
Session 15: Unpacking the Actuary’s
Technical Toolkit
Presenters: Chris Peek
Yonasan Schwartz, FSA, MAAA
Moderator: Albert J. Moore, ASA, MAAA
Actuarial Uses for Microsoft Access
Yonasan Schwartz
TIAA-CREF
Agenda
•
What is Access?
•
Access vs. Excel – pros and cons
•
Access basics: Tables
•
Access basics: Queries
•
Access basics: SQL
Access vs. Excel – pros and cons
•
Excel is better for:
– Less data
– Complex operations – Exceptions
– Processes that may
change
•
Access is better for:
– More data
– Simple operations – Consistency
Access basics: Tables
•
Similar to Excel spreadsheets
•
“Records” and “Fields” instead of rows and columns
•
Fields must have a data type
Access basics: Queries
•
Pull data from
tables (or other
queries)
•
Join tables
together for
powerful analysis
•
Based on SQL
•
Input and output
always in the
form of a table
Case studies
•
Valuation system
Session 15: Open Forum
Unpacking the Actuary’s Toolkit Presenter: Chris Peek
Advanced Excel Features
•
Power Pivot
– Cost effective approach for Business Intelligence – Data accumulation into Excel from multiple outside
sources
– Extends Excel features you already know
•
Power View
– Feature of Excel 2013 and add-in for SQL SSRS for
SharePoint Servers
– Data visualization and reporting tool – Integrates with Power Pivot workbooks
PowerPivot Demonstration
Why use programming to solve problems?
•
Actuarial problems complex many involve:
– Stochastic calculations – Large data sets
– Iterative techniques
– Require advanced statistical measures
•
Can use both Excel and programming languages to
prototype calculations for later inclusion in company
production processes
•
Useful to test 3
rdparty bespoke applications
Common Actuarial Programming Options
•
What’s available
Language Cost C#/C++ $$$ VB.NET $$$ VBA Free VB Script Free R Free MATLAB $$$ Mathematica $$$ APL $$$ SAS $$$ 14Insights from an Actuarial Software Vendor
•
Actuaries from my firm have used all of the
applications discussed. Their key comments include:
– All programming requires the development of “Best
Practices” for coding, testing and version control
– Don’t reinvent the wheel. If there is code out there, use it!
• This is particularly true with the open source languages (e.g. “R”)
– Be careful that your “ad-hoc” program doesn’t become the
“standard process”
– As an organization, commit to a specific programming
platform
What is “R”?
•
R is a free open source programming platform
– Data analysis software suited for:
• Statistical analysis • Predictive modeling • Data visualization
– R community is large and active with more than 2000
add-in packages available for free download
– Lots of free resources to learn R and jumpstart a project
– Python is another open source language gaining traction as
an easier to learn, intuitive application.
R Console
• Direct execution of code from the console, no compilation
• Use of the R command line console allows for interactive exploration of a data set as well as on the fly graphics and plotting
• Extensive number of ready to use packages. CRAN (Comprehensive R Archive Network) is a network of ftp sites storing up-to-date versions of code and documentation
Key R Capabilities
•
Statistical Modeling
•
Simulation
•
Big Data Analytics
•
Machine Learning
•
Dynamic Graphics
R Visualizations
•
Ready made
packages in R
– Example: ggvis for graphics – Can integrate with other R packages – Diverse set of graphing charts and plots 19Getting started in R
•
Clear use of R with your IT professionals
•
Free online courses
•
Tons of YouTube tutorials
•
The R Project for Statistical Computing
• www.r-project.org
Useful Utilities for the Actuary
•
Beyond Compare
– Comparison tool for files and folders
• Can do byte-by-byte comparisons
– Synchronize folders
• Data reconciliation
• Command line scripting for automating tasks
– Can be used for version control activities
• Compare log files of meta data
• Comparisons for different types of files
– EXE files – Binary
Useful Utilities for the Actuary
•
CSTEP
– Developed by Central Washington University
– Cluster Sampling Technique for Tail Estimation of
Probability (CSTEP)
– Supports four sampling methods:
• Euclidean Distance Method • Significance Method
• Present Value Distance Method • Economic Value Distance Method
– CSTEP is a desktop application with a reasonable amount
of documentation
Useful Utilities for the Actuary
•
MAXIMA
– Computer Algebra System
– Open Source
– Specializes in solving
symbolic mathematical equations
• Integration
• Partial and total
differentiation
• Automatic simplification
• Large number factorization
• Manipulation of large
polynomials
Useful Utilities for the Actuary
•
AutoIt
– Freeware with BASIC-style scripting to allow for
automation
– Large user community, good help files
– Simulates key-strokes and mouse movements – Interacts with all standard Windows controls
– Used to help automate manual, repetitive processes in 3rd
party applications:
• Example: Update tables in other Windows applications
Useful Utilities for the Actuary
•
M
ortality and
O
ther
R
ate
T
ables
mort.soa.org
•
XtbML format
•
The value of standard formats
– Facilitates Automated processing – Portability
– Independent of Platform – Simple Text Files
– A persistent International Standard
Useful References
• http://www.mathworks.com/products/ (MATLAB)
• https://msdn.microsoft.com/en-us/library/dn594433.aspx (Business Intelligence and Analysis Tools) • https://www.coursera.org/course/rprog (Free online R Programming Course)
• http://www.inside-r.org/packages/ (Free packages for use with R) • http://scootersoftware.com/ (Beyond Compare)
• http://maxima.sourceforge.net/ (MAXIMA)
• https://prezi.com/fqkfu4jk0ott/cstep-scenario-reduction-for-model-efficiency/(CSTEP Presentation) • http://www.cwu.edu/~chueh/cstep_1.0.3.zip(CSTEP Application)
• https://www.autoitscript.com/site/ (AutoIt Site)
