Storing data in database

First generate a fresh database built using latest source code:

import logging
import sys
logging.basicConfig(format='%(asctime)s | %(levelname)s : %(message)s',
                    level=logging.INFO, stream=sys.stdout)

# Check your environment
print(sys.path)

Create a database with:

from solvency2_data.eiopa_data import get_workspace
from solvency2_data.sqlite_handler import EiopaDB
database = get_workspace()['database']
db = EiopaDB(database)

Reset the database with:

# Hard reset of DB - deletes the file and all stored data and rebuilds empty DB
db.reset()

Now populate it for every month:

import solvency2_data
solvency2_data.refresh()

Now this can be indirectly queried using the API

import solvency2_data
from datetime import date
ref_date = date(2020, 12, 31)
rfr = solvency2_data.get(ref_date)
meta = solvency2_data.get(ref_date, 'meta')
spr = solvency2_data.get(ref_date, 'spreads')
gov = solvency2_data.get(ref_date, 'govies')
sym_adj =  solvency2_data.get(ref_date, 'sym_adj')
rfr.head()

Or directly queried via a SQL expression:

import pandas as pd
sql = "SELECT * FROM rfr"
df = pd.read_sql(sql, con=db.conn)
df = df.loc[df.scenario=='base',['currency_code','ref_date', 'duration', 'spot']]
df.head()

month_list = df.ref_date.drop_duplicates().to_list()
month_list[:5]

df['ref_date'] = df.ref_date.apply(lambda x: month_list.index(x))
df.head()

eurs = df.loc[df.currency_code=='EUR', ['ref_date', 'duration', 'spot']].set_index('ref_date')
gbps = df.loc[df.currency_code=='GBP', ['ref_date', 'duration', 'spot']].set_index('ref_date')
chfs = df.loc[df.currency_code=='CHF', ['ref_date', 'duration', 'spot']].set_index('ref_date')
usds = df.loc[df.currency_code=='USD', ['ref_date', 'duration', 'spot']].set_index('ref_date')
eurs.head()

list(eurs.loc[66, 'spot'].values)[:5]

Now have some fun :p

import numpy as np
import matplotlib.pyplot as plt
from matplotlib.animation import FuncAnimation
from IPython.display import HTML

# plt.style.use('ggplot')
plt.xkcd()

fig, ax = plt.subplots(figsize=(7, 4))
ax.set(xlim=(0, 100), ylim=(-0.01, 0.04))

date_text = ax.text(0.02, 0.9, '', transform=ax.transAxes)
#plt.tight_layout()
plt.gcf().subplots_adjust(bottom=0.15, left=0.15)
plt.title('EIOPA Spots')

x = list(range(1,151))
#plt.xticks(ticks = x)
plt.xlabel('duration (years)')
plt.locator_params(axis='x', nbins=10)

percentage = np.array(list(map("{:.1%}".format, 0.005 * np.arange(-2, 10))))
plt.yticks(ticks=0.005 * np.arange(-2, 10), labels=percentage)
plt.ylabel('risk free spots')

date_text.set_text(month_list[0])

eur_start = ax.plot(x, eurs.loc[0,'spot'].values, color='b', ls='dashed', lw=2)[0]
eur_line = ax.plot(x, eurs.loc[0,'spot'].values, color='b', lw=2)[0]

gbp_start = ax.plot(x, gbps.loc[0, 'spot'].values, color='y', ls='dashed', lw=2)[0]
gbp_line = ax.plot(x, gbps.loc[0, 'spot'].values, color='y', lw=2)[0]

chf_start = ax.plot(x, chfs.loc[0, 'spot'].values, color='m', ls='dashed', lw=2)[0]
chf_line = ax.plot(x, chfs.loc[0, 'spot'].values, color='m', lw=2)[0]

ax.legend((eur_line, chf_line, gbp_line), ('eur', 'chf', 'gbp'), loc='lower right')
def animate(i):
    key = min(i, 66)
    date_text.set_text(month_list[key])
    eur_line.set_ydata(list(eurs.loc[key, 'spot'].values))
    gbp_line.set_ydata(list(gbps.loc[key, 'spot'].values))
    chf_line.set_ydata(list(chfs.loc[key, 'spot'].values))

anim = FuncAnimation(
    fig, animate, interval=100, frames=67 + 15)

plt.draw()
#plt.show()
HTML(anim.to_html5_video())