UPDATE: The code mentioned in this blog has been moved to https://github.com/wjwillemse/solvency2-data
If you want to make insurance calculations you often need an recent interest rate term structure. In this example we show how to import the Solvency 2 Risk Free Rate from the EIOPA website in a convenient Pandas Data Frame, ready to be used for future calculations.
The code can be found on https://github.com/wjwillemse/insurlib.
First we import insurlib.solvency2, the Python package that contains functions to generate the names of the files, import the zip-file from the EIOPA website, extract it to an Excel file (both stored on disk) and read the Excel file in a proper Pandas Data Frame.
In [1]: import pandas as pd
from datetime import datetime
from insurlib import solvency2
We have now all the functions we need.
The function that does all this is solvency2.rfr.read, it returns a Python dictionary with all information about the RFR of a certain reference date.
If you do not add a input datetime, i.e. solvency2.rfr.read(), then the function with use today() and you will receive the most recent published RFR.
In [2]: d = solvency2.rfr.read(datetime(2018,1,1))
What information is stored in the dictionary?
In [3]: d.keys()
Out[3]: dict_keys(['input_date',
'reference_date',
'url',
'path_zipfile',
'name_zipfile',
'path_excelfile',
'name_excelfile',
'metadata',
'RFR_spot_no_VA',
'RFR_spot_with_VA'])
Let’s take a look at the individual elements of the dictionary.
The original date by which the function was called is stored in the dictionary as input_date
In [4]: d['input_date']
Out[4]: datetime.datetime(2018, 1, 1, 0, 0)
You can call the function with any date and the function will generate a proper reference date from it. The reference date is the most recent end of the month prior to the input date. So if for example the input is datetime(2018, 1, 1) then the reference date is '20171231', because this the most recent end of the month prior to the input date. The reference date is a string because it is used in the name of the files to be downloaded from the EIOPA-website.
In [5]: d['reference_date']
Out[5]: '20171231'
Furthermore the url, location and filenames are stored in the dictionary.
In [6]: print(d['url'])
print(d['name_zipfile'])
print(d['name_excelfile'])
Out[6]: https://eiopa.europa.eu/Publications/Standards/
EIOPA_RFR_20171231.zip
EIOPA_RFR_20171231_Term_Structures.xlsx
Now, let’s take a look at the available RFR’s.
In [7]: d['metadata'].columns
Out[7]: Index(['Euro', 'Austria', 'Belgium', 'Bulgaria', 'Croatia', 'Cyprus', 'Czech Republic', 'Denmark', 'Estonia', 'Finland', 'France', 'Germany', 'Greece', 'Hungary', 'Iceland', 'Ireland', 'Italy', 'Latvia', 'Liechtenstein', 'Lithuania', 'Luxembourg', 'Malta', 'Netherlands', 'Norway', 'Poland', 'Portugal', 'Romania', 'Russia', 'Slovakia', 'Slovenia', 'Spain', 'Sweden', 'Switzerland', 'United Kingdom', 'Australia', 'Brazil', 'Canada', 'Chile', 'China', 'Colombia', 'Hong Kong', 'India', 'Japan', 'Malaysia', 'Mexico', 'New Zealand', 'Singapore', 'South Africa', 'South Korea', 'Taiwan', 'Thailand', 'Turkey', 'United States'], dtype='object')
To get all the metadata of the French RFR we select metadata from the dictionary.
In [8]: d['metadata'].loc[:,'France']
Out[8]: Info FR_31_12_2017_SWP_LLP_20_EXT_40_UFR_4.2
Coupon_freq 1
LLP 20
Convergence 40
UFR 4.2
alpha 0.126759
CRA 10
VA 4
reference date 20171231
Name: France, dtype: object
That is: the Coupon frequency is one year, the Last Liquid Point is 20 years, the Convergence period is 40 years, the Ultimate Forward Rate is 4.2 (this rate changed at the beginning of 2018 to 4.05), the alpha parameter of the Smith-Wilson algorithm is 0.126759, the Credit Rate Adjustment is 10 basis points and because we have the curve without the Volatility adjustment the VA is 4 basis points. It is identical to the Euro curve.
To get one single item from the metadata we can use the following line (note that this is the UFR at the end of 2017).
In [9]: d['metadata'].loc["UFR",'Germany']
Out[9]: 4.2
To get the euro RFR without Volatility Adjustment (the first ten durations) we use
In [10]: d['RFR_spot_no_VA']['Euro'].head(10)
Out[10]: Duration
1 -0.00358
2 -0.0025
3 -0.00088
4 0.00069
5 0.00209
6 0.00347
7 0.00469
8 0.00585
9 0.00695
10 0.00802
Name: Euro, dtype: object
Now suppose that we want to store the RFR of six consecutive months into one Data Frame. This is how we can do that.
First we define ref_dates with the reference dates we want to acquire.
In [11]: ref_dates = pd.date_range(start='2018-01-01', periods = 6,freq = 'MS')
ref_dates
Out[11]: DatetimeIndex(['2018-01-01', '2018-02-01', '2018-03-01', '2018-04-01', '2018-05-01', '2018-06-01'], dtype='datetime64[ns]', freq='MS')
Then we use a Python list comprehension to obtain the RFR’s of the reference dates and we show the Data Frame with the first ten durations.
In [12]: rfr = [solvency2.rfr.read(ref_date)['RFR_spot_no_VA']['Euro'] for ref_date in ref_dates]
df_euro = pd.DataFrame(data = rfr, index = ref_dates).T
print(df_euro.head(10))
A list comprehension can also be used for the metadata. The following code obtains the metadata of the UK RFR.
In [13]: rfr = [solvency2.rfr.read(ref_date)['metadata']['United Kingdom'] for ref_date in ref_dates]
print(pd.DataFrame(data = rfr, index = ref_dates))
Out[13]: 2018-01-01 2018-02-01 2018-03-01 2018-04-01 2018-05-01 \
Duration
1 -0.00358 -0.00363 -0.00352 -0.00362 -0.00358
2 -0.00250 -0.00225 -0.00220 -0.00258 -0.00244
3 -0.00088 -0.00020 -0.00022 -0.00083 -0.00065
4 0.00069 0.00190 0.00178 0.00104 0.00120
5 0.00209 0.00380 0.00361 0.00285 0.00286
6 0.00347 0.00537 0.00521 0.00418 0.00441
7 0.00469 0.00670 0.00666 0.00556 0.00577
8 0.00585 0.00791 0.00793 0.00672 0.00698
9 0.00695 0.00899 0.00906 0.00783 0.00809
10 0.00802 0.00987 0.01007 0.00884 0.00911
2018-06-01
Duration
1 -0.00331
2 -0.00236
3 -0.00098
4 0.00057
5 0.00213
6 0.00356
7 0.00490
8 0.00613
9 0.00725
10 0.00824