Outputting to Excel with Templates

Getting Started

Exercises requires chainladder v0.6.1 and later



In [1]:

    
import chainladder as cl
cl.__version__









    Out[1]:





'0.6.1'

.. note:: Excel templating is a very new feature so the template library is sparse. This tutorial introduces the concept of templating. Expect more templates soon.

Excel Templates

chainladder relies on the xlcompose library to produce rich spreadsheet outputs. These are accessed by calling on cl.load_template which is similar to how we load sample data with cl.load_dataset. A template is a YAML file that specifies the layout of an Excel file. Anyone can write a custom template and pass it to the cl.load_template function. You can view the template files included with chainladder here. To make your own custom templates it is worth reading the xlcompose templating docs.



In [2]:

    
triangle=cl.load_dataset('usauto')['incurred']
triangle

load_template will generate an Excel template object to which you can pass data. We will be using the 'triangle' template which requires (at a minimum) a Triangle object that is a single index/column representation.

.. note:: Excel files do not render as notebooks. The Excel files generated by these examples have been included as images for the purposes of the tutorial only. You will have to jump into Excel to view your own output.

In this example, our template is given one object, out Triangle. From the one Triangle, we can infer a lot of information that we can represent in our exhibit.



In [3]:

    
cl.load_template('triangle', triangle=triangle).to_excel('example.xlsx')

Note also that loading the template creates an xlcompose object, but it does not actually create an Excel file. To do that, you must call the to_excel method on the object.

If we transform our triangle by a Development estimator, let's see how the same template acknowledges this additional information.



In [4]:

    
cl.load_template(
    template='triangle',
    triangle=cl.Development().fit_transform(triangle)
).to_excel('example.xlsx')

Anyone can examine the YAML template to see what options are available. Let's explore all available options in out 'triangle' template.



In [5]:

    
cl.load_template(
    template='triangle',
    triangle=cl.Development().fit_transform(triangle),
    scenarios={'Simple (All)': cl.Development(average='simple'),
               'Volume (All)': cl.Development(),
               'Volume (5Yr)': cl.Development(n_periods=5),
               'Volume (3Yr)': cl.Development(n_periods=3)},
    triangle_name = 'US Auto Industry',
    sheet_name='US Auto',
    set_header=['&RExhbit 1', 'Sheet 1'],
    index_label='Accident Year'
).to_excel('example.xlsx')

By using xlcompose for rendering spreadsheets, you do not need to concern yourself with the shapes of your triangles. The Excel template dynamically resizes to smaller or larger triangles. Let's try the same template on a smaller triangle.



In [6]:

    
triangle = cl.load_dataset('ukmotor')



In [7]:

    
cl.load_template(
    template='triangle',
    triangle=cl.Development(average=['volume']*3+['simple']*3).fit_transform(triangle),
    scenarios={'Simple (All)': cl.Development(average='simple'),
               'Volume (All)': cl.Development(),
               'Volume (5Yr)': cl.Development(n_periods=5),
               'Volume (3Yr)': cl.Development(n_periods=3)},
    triangle_name = 'UK Motor',
    sheet_name='Motor',
    set_header=['&RExhbit 1', 'Sheet 1'],
    index_label='Accident Year'
).to_excel('example.xlsx')



In [ ]:

Origin	12	24	36	48	60	72	84	96	108	120
1998	37,017,487	43,169,009	45,568,919	46,784,558	47,337,318	47,533,264	47,634,419	47,689,655	47,724,678	47,742,304
1999	38,954,484	46,045,718	48,882,924	50,219,672	50,729,292	50,926,779	51,069,285	51,163,540	51,185,767
2000	41,155,776	49,371,478	52,358,476	53,780,322	54,303,086	54,582,950	54,742,188	54,837,929
2001	42,394,069	50,584,112	53,704,296	55,150,118	55,895,583	56,156,727	56,299,562
2002	44,755,243	52,971,643	56,102,312	57,703,851	58,363,564	58,592,712
2003	45,163,102	52,497,731	55,468,551	57,015,411	57,565,344
2004	45,417,309	52,640,322	55,553,673	56,976,657
2005	46,360,869	53,790,061	56,786,410
2006	46,582,684	54,641,339
2007	48,853,563