Taking the first step is often the hardest: getting data from Excel into R.

Suppose you would like to use the ChainLadder package to forecast future claims payments for a run-off triangle that you have stored in Excel.

How do you get the triangle into R and execute a reserving function, such as MackChainLadder?

Well, there are many ways to do this and the ChainLadder package vignette, as well as the R manual on Data Import/Export have all of the details, but here is a quick and dirty solution using a CSV-file.

Open a new Excel workbook and copy your triangle into cell A1, with the first column being the accident or origin period and the first row describing the development period or age. You find an example CSV-file on GitHub.

Make sure that your triangle has no formatting, such a commas to separate thousands, as Excel will save those cell as characters. Now open R and go through the following commands:

# The first command will open a window and <br /># ask you to select your CSV-file<br />myCSVfile <- file.choose()<br /># Read file into R<br />dat <- read.csv(file=myCSVfile) <br /># use read.csv2 if semicolons are used as a separator<br /># likely to be the case if you are in continental Europe<br /><br />dat # to see your data<br /><br />     AY   X1    X2    X3    X4    X5    X6    X7    X8    X9   X10<br />1  1981 5012  8269 10907 11805 13539 16181 18009 18608 18662 18834<br />2  1982  106  4285  5396 10666 13782 15599 15496 16169 16704    NA<br />3  1983 3410  8992 13873 16141 18735 22214 22863 23466    NA    NA<br />4  1984 5655 11555 15766 21266 23425 26083 27067    NA    NA    NA<br />5  1985 1092  9565 15836 22169 25955 26180    NA    NA    NA    NA<br />6  1986 1513  6445 11702 12935 15852    NA    NA    NA    NA    NA<br />7  1987  557  4020 10946 12314    NA    NA    NA    NA    NA    NA<br />8  1988 1351  6947 13112    NA    NA    NA    NA    NA    NA    NA<br />9  1989 3133  5395    NA    NA    NA    NA    NA    NA    NA    NA<br />10 1990 2063    NA    NA    NA    NA    NA    NA    NA    NA    NA<br />

Ok, the data is in R, but now you have to convert it into a triangle. A triangle is basically a matrix with extra attributes. To do this execute the following steps.

# Load the ChainLadder package<br />library(ChainLadder)<br /># Ignore first column which holds accident year information <br />tri <- dat[,-1]<br /># Convert to matrix<br />tri <- as.matrix(tri)<br /># Add dimension names<br />dimnames(tri) <- list(origin=dat[,1], dev=1:ncol(tri))<br /># Convert into a triangle class<br />tri <- as.triangle(tri)<br />tri<br />      dev<br />origin    1     2     3     4     5     6     7     8     9    10<br />  1981 5012  8269 10907 11805 13539 16181 18009 18608 18662 18834<br />  1982  106  4285  5396 10666 13782 15599 15496 16169 16704    NA<br />  1983 3410  8992 13873 16141 18735 22214 22863 23466    NA    NA<br />  1984 5655 11555 15766 21266 23425 26083 27067    NA    NA    NA<br />  1985 1092  9565 15836 22169 25955 26180    NA    NA    NA    NA<br />  1986 1513  6445 11702 12935 15852    NA    NA    NA    NA    NA<br />  1987  557  4020 10946 12314    NA    NA    NA    NA    NA    NA<br />  1988 1351  6947 13112    NA    NA    NA    NA    NA    NA    NA<br />  1989 3133  5395    NA    NA    NA    NA    NA    NA    NA    NA<br />  1990 2063    NA    NA    NA    NA    NA    NA    NA    NA    NA<br />

With those preparations done you can execute the MackChainLadder function:

M <- MackChainLadder(tri, est.sigma = "Mack")<br />M<br />     Latest Dev.To.Date Ultimate   IBNR Mack.S.E CV(IBNR)<br />1981 18,834       1.000   18,834      0        0      NaN<br />1982 16,704       0.991   16,858    154      206    1.339<br />1983 23,466       0.974   24,083    617      623    1.010<br />1984 27,067       0.943   28,703  1,636      747    0.457<br />1985 26,180       0.905   28,927  2,747    1,469    0.535<br />1986 15,852       0.813   19,501  3,649    2,002    0.549<br />1987 12,314       0.694   17,749  5,435    2,209    0.406<br />1988 13,112       0.546   24,019 10,907    5,358    0.491<br />1989  5,395       0.336   16,045 10,650    6,333    0.595<br />1990  2,063       0.112   18,402 16,339   24,566    1.503<br /><br />               Totals<br />Latest:    160,987.00<br />Dev:             0.76<br />Ultimate:  213,122.23<br />IBNR:       52,135.23<br />Mack S.E.:  26,909.01<br />CV(IBNR):        0.52<br />

To copy the full triangle back into Excel you can use the clipboard:
write.table(M$FullTriangle, file="clipboard", sep="t").
Go back to Excel and hit + V on your keyboard to paste the data into R.

For more details see the package vignette and Dan’s post on pasting triangles from Excel into R via the clipboard.

If you are after a thorough overview of R in insurance take a look at the book Computational Actuarial Science with R.

Finally, join the Special Interest Group on using R in actuarial science and insurance to share your questions and answers.

Session Info

R version 3.1.2 (2014-10-31)<br />Platform: x86_64-apple-darwin13.4.0 (64-bit)<br /><br />locale:<br />[1] en_GB.UTF-8/en_GB.UTF-8/en_GB.UTF-8/C/en_GB.UTF-8/en_GB.UTF-8<br /><br />attached base packages:<br />[1] stats     graphics  grDevices utils     datasets  methods  <br />[7] base     <br /><br />other attached packages:<br />[1] ChainLadder_0.1.9 systemfit_1.1-14  lmtest_0.9-33    <br />[4] zoo_1.7-11        car_2.0-21        Matrix_1.1-4     <br /><br />loaded via a namespace (and not attached):<br /> [1] acepack_1.3-3.3     actuar_1.1-6        cluster_1.15.3     <br /> [4] foreign_0.8-61      Formula_1.1-2       grid_3.1.2         <br /> [7] Hmisc_3.14-5        lattice_0.20-29     latticeExtra_0.6-26<br />[10] MASS_7.3-35         nnet_7.3-8          plyr_1.8.1         <br />[13] RColorBrewer_1.0-5  Rcpp_0.11.3         reshape2_1.4       <br />[16] rpart_4.1-8         sandwich_2.3-2      splines_3.1.2      <br />[19] statmod_1.4.20      stringr_0.6.2       survival_2.37-7    <br />[22] tools_3.1.2         tweedie_2.2.1