Painlessly Merge Data into Actuarial Loss Development Triangles with R

Sam Castillo • December 31, 2017

Objective

Create a method which easily combines loss runs, or listings of insurance claims, into triangles. Using only Excel, the common method is to create links between the excel files which must be updated manually at each new evaluation. This is prone to human error and is time consuming. Using a script to merge the files first and then create a triangle saves time and is more consistent.

Methods

I use the packages dplyr, plyr, tidyr, lubridate, readxl, and ChainLadder to manipulate the data once it is loaded into R. All of these packages (except ChainLadder) are included in Hadley Wickham’s tidyverse package. The package ChainLadder has a variety of tools for actuarial reserve analysis.

library(dplyr)
library(readxl)
library(plyr)
library(tidyr)
library(ChainLadder)
library(lubridate)

Step 1: Organize the Excel Files

Copy all of the excel files into the same windows folder, inside the working directory of the R project. It is important to have only the files that are going to be loaded into R in this folder.

On my computer, my file structure is as follows:

C:/Users/Sam/Desktop/[R project working directory]/[folder with excel claims listing files to aggregate]/[file 1, file 2, file 3, etc]

Using the command list.files(), R automatically looks in the working directory and returns a vector of all the file names which it sees.

wd_path = "C:/Users/Sam/Desktop/projects/Excel Aggregate Loop/excel files"
list.files(wd_path)

## [1] "claims listing 2011.xlsx" "claims listing 2012.xlsx"
## [3] "claims listing 2013.xlsx" "claims listing 2014.xlsx"
## [5] "claims listing 2015.xlsx" "claims listing 2016.xlsx"

The loss files have dummy data for a claims listing. Each row is an individual claim. To make this as realistic as possible, they have arbitrary columns in addition to the ones which we need: file year, loss date, and paid.

In order to evaluate the age of the losses, we need to take into account when each loss was evaluated. This is accomplished by going into Excel and adding in a column for “file year”, which specifies the year of evaluation of the file.

Step 2: Load the Data into R

Initialize a data frame which will store the aggregated loss run data from each of the excel files.

merged_data = as_data_frame(matrix(nrow = 0, ncol = 3))
names(merged_data) = c("file year", "loss date", "paid")

The function below takes in the file name of the excel file and returns a data frame with only the columns which are selected.

excel_file_extractor = function(cur_file_name){
  read_excel(cur_file_name[1], sheet = "Sheet1", col_names = T) %>%
    select(`file year`, `loss date`, paid) %>%
    rbind(merged_data)
}

Apply the function to all of the files in the folder using ldply from the plyr package.

loss_run_data = ldply(file_paths, excel_file_extractor)
names(loss_run_data) = c("file_year", "loss_date", "paid losses")

Step 3: Create Development Triangles

Finally, once we have the loss run combined, we just need to create a triangle. This is made easy by the as.triangle function from the ChainLadder package.

loss_run_data = loss_run_data %>%
  mutate(accident_year = as.numeric(year(ymd(loss_date))),
         maturity_in_months = (file_year - accident_year)*12)

merged_triangle = as.triangle(loss_run_data,
                              dev = "maturity_in_months",
                              origin = "accident_year",
                              value = "paid losses")

The resulting triangle:

Within the package ChainLadder is a plot function, which shows the development of losses by accident year.

plot(merged_triangle, main = "Paid Losses vs Maturity by Accident Year",
     xlab = "Maturity in Months", ylab = "Paid Losses")

Conclusion

When it comes to aggregating excel files, R can be faster and more consistent than linking together each of the excel files, and once this script is set in place, making modifications to the data can be done easily by editing the excel_file_extractor function.

Explore the full repository, data, and Rmd file on GitHub →