Software Package Training at APR

The outstanding training provided to each intake of APR’s Graduate Actuarial Associate scheme is an important selling point for new staff. It allows us to hit the ground running on client projects and add value as a result of the knowledge and skills gained. Having completed the induction training offered, I am pleased to report that the training delivered was excellent. I improved my knowledge of many topics during the training. A wide range of areas were covered for example Actuarial Modelling, the UK Regulatory Landscape, and Solvency II, to name just a few. This article focuses on the training delivered on using a range of software packages. This training included sessions on Excel, VBA, Access, SQL and R.

Excel

Microsoft Excel is a spreadsheet software program used widely across the globe. Despite the rise in popularity of programming languages such as R, Excel remains a tool utilized daily by many actuaries. APR’s induction training covered the use of Excel widely, starting with an introduction to Excel fundamentals, functions, formula, and data. This gave us the opportunity to really get to grips with how Excel works.

• Starting right from the nature of Excel as a flat-file database we learned about the structure of workbooks, worksheets, ranges, and cells.
• We then focused on becoming familiar with commonly used Excel formula and with managing and analyzing data

This initial training was utilized throughout many of the exercises we completed during our induction training. These exercises included analysing Excel models to assess their functionality and building cashflow models in Excel for a range of products. One key takeaway from the Excel training for me was the importance of good spreadsheet layout and design, a skill I personally felt I improved a lot.

Visual Basic for Applications (VBA)

VBA is the programming language contained in all Microsoft Office Applications. It is used to develop programs that control the Office application and is most commonly used with Excel. The training brought us on a journey from relative VBA beginners to being able to use VBA for many useful, practical tasks. The course was very hands-on giving lots of realistic examples of how VBA could be utilized in an actuarial environment. The course covered:

• Getting data from the user and from Excel into VBA.
• Exploring the Excel object model and using variables and arrays in VBA.
• Using functions within VBA.
• Learning how to control programming flow. Including learning how to use If statements and loops effectively.

Access

Microsoft Access is a relational database management system. Many real-life databases can contain thousands or millions of records, with data that is very intricately related. Hence using a relational database becomes essential for many of the databases actuaries commonly work with. The Access training introduced us to the concept of a relational database. We discussed the benefits of storing data in a relational database rather than a flat-file database.

• A flat-file database is simply a single table and is often maintained in Excel.
• Whereas a relational database places related data in distinct tables and defines relationships between these tables.

Creating a separate table for each set of related data and then assigning a primary key to each table of data allows a user to create relationships between the tables of data. The benefits of this include:

• Making the data much easier to update
• Allowing incomplete data to be stored
• Ensuring all data can be related to a unique identifier
• Preventing unnecessary data entry

The course worked through creating tables, relationships, queries, and macros in Access. Hands-on exercises were completed throughout the course to help illustrate how Access could be used to sort, filter, and conduct some calculations on data. One key takeaway from this session for me was understanding when using Access is beneficial. Using Access as a database program to manage, sort, filter and interrogate data. With the results from Access, usually a subset of the main data, often copied into Excel for further analysis where seeing the intermediate steps in calculations will be important.

Structured Query Language (SQL)

SQL is a programming language used for querying databases. SQL is a declarative language meaning that users specify the desired result, and it is the computer’s responsibility to figure out how to achieve that result. Using SQL has all the advantages over Excel mentioned in the Access section above. In addition to this, Access is based on the same principles as an SQL database server but can manage a more limited amount of data in comparison. The SQL training built on relational database concepts and focused on good database design and using Microsoft SQL Server Express. SQL is used by many database tools but the skills we learnt are highly transferable to any suitable database server. The areas covered included:

• Schema, Tables and Views
• Writing Queries
• How WHERE and HAVING clauses operate
• Using different types of joins
• How to use conditional expressions
• How to utilize subqueries

One key takeaway from this training for me was the flexibility and versatility of managing a database using SQL. This was my first experience using SQL. The training allowed me to progress from being a complete beginner to being able to understand how and when to use an SQL database server in an actuarial context.

R

R is a programming language primarily used in data science and statistical analysis that can also be utilized across a wide range of general-purpose programming. R has been increasingly used for actuarial applications in recent years. The training focused on giving us a working knowledge of this dynamically typed, vector-based language. The course covered:

• vectors
• arrays
• matrices
• data frames
• lists

The focus being on how R treats each of these and how to utilize this to analyze data effectively and accurately. We completed a number of hands-on exercises to help us get to grips with how to write scripts and use various functions in R. We also covered the availability of a large archive of packages available for use to make certain tasks easier. Noting that good packages can help make code quicker to write and easier to read. But that care should be taken to ensure any package used is working as expected and continues to do so with any new versions of R or RStudio. One key takeaway for me was that due to R’s vector-based nature there is often no need to write code to loop through arrays to perform simple algebra. Thus, allowing arrays to be manipulated in a more simple and efficient way than in other languages (such as VBA).

Uses in Industry and Further Learning

All of the software packages discussed above are used widely in industries where actuaries commonly work. There has been an increased focus on R and Python use in recent years both in traditional actuarial industries and some less traditional areas. Part of this increased focus can be explained by more actuaries working in data science and analytics. The actuarial skillset is very transferrable to these types of roles that have a history of utilizing R and Python. Just as the actuarial skillset is very transferable, the software package training at APR is also extremely transferrable. Although there was no formal Python training during my induction training, the good coding practices and skills learnt using the software packages that were covered could be easily transferred to any other coding language. The Code Clinic at APR also represents a great opportunity for APR staff to further their knowledge and skills using a wide range of coding languages. An interesting article on some recent projects worked on by the code clinic can be found here.

Conclusions

Overall, the training on software packages has improved my knowledge of each of the programs discussed above. I have gained new skills, which will be easy to apply to work on client projects due to the hands-on, practical nature of all the courses covered during the training. The delivery of the training allowed me to learn to use programs that I had no previous experience of and improve my skills using programs I had used before. I found this very impressive as it allowed all participants to absorb new information and skills regardless of their previous level of experience with each of the packages. We also learnt about good coding practice throughout the sessions. For example:

• Structuring code in an easy-to-understand way
• Commenting code to make it more understandable for others
• Debugging code to spot and determine the causes of errors

These skills are highly transferable to all coding languages, giving a good basis of understanding that can be built on and developed for specific languages used when on client projects. Overall, the best way to develop skills with software programs is to gain experience using the software. The induction training gives new APR staff a great foundation of a wide variety of programs. This allows them to hit the ground running on client projects and further develop their skills in an efficient way.

Aidan O’Boyle

February 2022