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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
import pandas as pd
from . import mysql_engine
def latest_sim(date):
sql_string = (
"SELECT model_id_sub FROM model_versions "
"JOIN model_versions_nonagency USING (model_id_sub) "
"JOIN simulations_nonagency USING (simulation_id) "
"WHERE (date(start_time) <= %s) AND (description = 'normal') "
"ORDER BY start_time DESC")
conn = mysql_engine.raw_connection()
c = conn.cursor()
c.execute(sql_string, (date,))
model_id_sub, = next(c)
c.close()
return model_id_sub
def get_df(date):
model_id_sub = latest_sim(date)
df_prices = pd.read_sql_query(
"SELECT cusip, model_version, pv, modDur, delta_yield, "
"wal, pv_io, pv_po, pv_RnW, delta_ir_io, delta_ir_po, "
"delta_hpi, delta_RnW, delta_mult, delta_ir, pv_FB "
"FROM priced WHERE "
"timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) "
"AND model_id_sub=%s "
"AND normalization='current_notional'",
mysql_engine,
["cusip", "model_version"],
params=(date, date, model_id_sub))
df_percentiles = pd.read_sql_query("SELECT cusip, PV, percentile "
"FROM priced_percentiles WHERE "
"timestamp BETWEEN %s AND date_add(%s, INTERVAL 1 DAY) "
"AND model_version=3 "
"AND model_id_sub=%s "
"AND percentile IN (5, 25, 50, 75, 95) "
"AND normalization='current_notional'",
mysql_engine,
["cusip", "percentile"],
params=(date, date, model_id_sub))
df_prices = df_prices.unstack("model_version")
df_percentiles = df_percentiles.unstack("percentile")
return df_prices.join(df_percentiles, how="left")
def subprime_risk(date):
df = get_df(date)
df_pv = df.xs('pv', axis=1, level=0)
df_pv.columns = ['pv1', 'pv2', 'pv3']
df_pv_perct = df.xs('PV', axis=1, level=0)
df_pv_perct.columns = ['pv5', 'pv25', 'pv50', 'pv75', 'pv95']
df_modDur = df[('modDur', 1)]
df_modDur.name = 'modDur'
df_v1 = df.xs(1, axis=1, level='model_version')[
['pv_RnW', 'delta_mult', 'delta_hpi', 'delta_ir']]
df_v1.columns = ['v1pv_RnW', 'v1_lsdel', 'v1_hpidel', 'v1_irdel']
df_pv_FB = df[('pv_FB', 3)]
df_pv_FB.name = 'pv_FB'
return pd.concat([df_pv, df_modDur, df_pv_perct,
df.xs(3, axis=1, level='model_version')[
['delta_yield', 'wal', 'pv_io', 'pv_po', 'pv_RnW', 'delta_ir_io',
'delta_ir_po', 'delta_hpi', 'delta_RnW', 'delta_mult']],
df_v1, df_pv_FB],
axis=1)
def get_rmbs_portfolio(date, conn, fund="SERCGMAST",
include_unsettled=True):
df = pd.read_sql_query("SELECT * FROM list_positions(%s, 'Subprime', %s, %s)",
conn, params=(date, include_unsettled, fund))
df["cusip"] = df.identifier.str.slice(0, 9)
df = df.set_index("cusip")
return df
|
