1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
import pandas as pd
from analytics import Index
from db import dbengine
serenitas_engine = dbengine('serenitasdb')
tenors = {"IG": ("3yr", "5yr", "7yr", "10yr"),
"HY": ("3yr", "5yr", "7yr"),
"EU": ("3yr", "5yr", "7yr", "10yr"),
"XO": ("3yr", "5yr", "7yr", "10yr")}
sql_str = "INSERT INTO index_risk VALUES(%s, %s, %s)"
conn = serenitas_engine.raw_connection()
for index in ["IG", "HY", "EU", "XO"]:
for series in range(18, 31):
if index in ["EU", "XO"] and series == 30:
continue
indices = {t: Index.from_name(index, series, t) for t in tenors[index]}
index_quotes = pd.read_sql_query(
"SELECT id, date, tenor, close_price FROM index_quotes_pre "
"LEFT JOIN index_risk USING (id) "
"WHERE index=%s AND series=%s "
"AND source='MKIT' AND duration is NULL AND tenor IN %s",
serenitas_engine,
params=(index, series, tenors[index]),
parse_dates=['date'],
index_col='id')
if index_quotes.empty:
continue
with conn.cursor() as c:
for k, v in index_quotes.groupby('date'):
for id, tenor, close_price in v[['tenor', 'close_price']].itertuples():
indices[tenor].value_date = k
indices[tenor].price = close_price
duration = indices[tenor].risky_annuity
theta = - indices[tenor].theta / indices[tenor].notional
c.execute(sql_str, (id, theta, duration))
conn.commit()
conn.close()
|
