# First steps with ChainLadder: Import triangle from Excel into R

[This article was first published on

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Taking the first step is often the hardest: getting data from Excel into R. **mages' blog**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 # ask you to select your CSV-file myCSVfile <- file.choose() # Read file into R dat <- read.csv(file=myCSVfile) # use read.csv2 if semicolons are used as a separator # likely to be the case if you are in continental Europe dat # to see your data AY X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 1 1981 5012 8269 10907 11805 13539 16181 18009 18608 18662 18834 2 1982 106 4285 5396 10666 13782 15599 15496 16169 16704 NA 3 1983 3410 8992 13873 16141 18735 22214 22863 23466 NA NA 4 1984 5655 11555 15766 21266 23425 26083 27067 NA NA NA 5 1985 1092 9565 15836 22169 25955 26180 NA NA NA NA 6 1986 1513 6445 11702 12935 15852 NA NA NA NA NA 7 1987 557 4020 10946 12314 NA NA NA NA NA NA 8 1988 1351 6947 13112 NA NA NA NA NA NA NA 9 1989 3133 5395 NA NA NA NA NA NA NA NA 10 1990 2063 NA NA NA NA NA NA NA NA NAOk, 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 library(ChainLadder) # Ignore first column which holds accident year information tri <- dat[,-1] # Convert to matrix tri <- as.matrix(tri) # Add dimension names dimnames(tri) <- list(origin=dat[,1], dev=1:ncol(tri)) # Convert into a triangle class tri <- as.triangle(tri) tri dev origin 1 2 3 4 5 6 7 8 9 10 1981 5012 8269 10907 11805 13539 16181 18009 18608 18662 18834 1982 106 4285 5396 10666 13782 15599 15496 16169 16704 NA 1983 3410 8992 13873 16141 18735 22214 22863 23466 NA NA 1984 5655 11555 15766 21266 23425 26083 27067 NA NA NA 1985 1092 9565 15836 22169 25955 26180 NA NA NA NA 1986 1513 6445 11702 12935 15852 NA NA NA NA NA 1987 557 4020 10946 12314 NA NA NA NA NA NA 1988 1351 6947 13112 NA NA NA NA NA NA NA 1989 3133 5395 NA NA NA NA NA NA NA NA 1990 2063 NA NA NA NA NA NA NA NA NAWith those preparations done you can execute the

`MackChainLadder`

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

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

*on your keyboard to paste the data into R.* + V

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

To

**leave a comment**for the author, please follow the link and comment on their blog:**mages' blog**.R-bloggers.com offers

**daily e-mail updates**about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.