diff options
| -rw-r--r-- | python/globeop_reports.py | 57 | ||||
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 51 | ||||
| -rw-r--r-- | sql/serenitasdb.sql | 13 |
3 files changed, 83 insertions, 38 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index 4562c605..9ac2d013 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -125,18 +125,20 @@ def calc_trade_performance_stats(): def get_rmbs_pos_df(date = None): engine = dbengine('dawndb') - calc_df = pd.DataFrame() end_date = pd.datetime.today() - MonthEnd(1) if date is not None: date = date + MonthEnd(0) df = get_portfolio(date) df = df.sort_index().loc[:end_date] - mask = (df.port == 'MORTGAGES') & (df.endbookmv > 0) & (df['invid'].str.len() == 9) - df = df[mask] - sql_string = "SELECT distinct timestamp FROM priced" + df = df[(df.port == 'MORTGAGES') & + (df.endbookmv > 0) & + (df['invid'].str.len() == 9)] + sql_string = "SELECT distinct timestamp FROM priced where normalization = 'current_notional'" timestamps = pd.read_sql_query(sql_string, engine) + df = df[['endbooknav', 'endlocalmarketprice', 'identifier']] + calc_df = pd.DataFrame() for d, g in df.groupby(pd.Grouper(freq='M')): model_date = pd.to_datetime(timestamps[timestamps.timestamp <= d+off.DateOffset(days=1)].max()[0]).date() yc = YC(evaluation_date=model_date) @@ -148,21 +150,36 @@ def get_rmbs_pos_df(date = None): #special case if model_date == pd.datetime(2017, 10, 27).date(): model_id = 4 - sql_string = "SELECT * FROM priced where date(timestamp) = %s and model_id_sub = %s" - model = pd.read_sql_query(sql_string, engine, params=[model_date, model_id]) + sql_string = """ + SELECT date(timestamp) as timestamp, cusip, model_version, pv, moddur, delta_yield, delta_ir + FROM priced where date(timestamp) = %s + and normalization ='current_notional' + and model_version <> 2 + and model_id_sub = %s""" + params_list = [model_date, model_id] else: - sql_string = "SELECT * FROM priced where date(timestamp) = %s" - model = pd.read_sql_query(sql_string, engine, params=[model_date]) - model['timestamp'] = model['timestamp'].dt.date - model = model[model.normalization == 'current_notional'] - model = model.set_index(['cusip', 'model_version']).unstack(1) - temp = pd.merge(g.loc[d], model, left_on='identifier', right_index=True) - temp['curr_ntl'] = temp.endbooknav/temp.endlocalmarketprice *100 - temp['b_yield'] = np.minimum((temp[('pv', 1)]/temp.endlocalmarketprice*100) ** (1/temp[('moddur', 1)]) - 1, 10) - temp = temp.dropna(subset=['b_yield']) - temp['b_yield'] = temp.apply(lambda df: df['b_yield'] + float(yc.zero_rate(df[('moddur', 3)])) - libor, axis=1) - temp = temp[(temp[('pv', 3)] != 0)] - temp['percent_model'] = temp.apply(lambda df: df.endlocalmarketprice/100/df[('pv', 3)], axis=1) - calc_df = calc_df.append(temp) + sql_string = """ + SELECT date(timestamp) as timestamp, cusip, model_version, pv, moddur, delta_yield, delta_ir + FROM priced where date(timestamp) = %s + and model_version <> 2 + and normalization ='current_notional'""" + params_list = [model_date] + model = pd.read_sql_query(sql_string, engine, parse_dates=['timestamp'], + params=params_list) + comb_g = g.loc[d].groupby('identifier').agg({'endbooknav': np.sum, + 'endlocalmarketprice': np.mean}) + model = pd.merge(comb_g, model, left_on = 'identifier', right_on='cusip') + positions = model.set_index(['cusip', 'model_version']).unstack(1).dropna() + positions = positions[positions.pv.iloc[:,0] != 0] + v1 = positions.xs(1, level='model_version', axis=1) + v3 = positions.xs(3, level='model_version', axis=1) + v3 = v3.assign(curr_ntl = v3.endbooknav/v3.endlocalmarketprice *100) + v3 = v3.assign(b_yield = v3.moddur.apply(lambda x: + float(yc.zero_rate(x)) - libor)) + v3.b_yield += np.minimum((v1.pv / v1.endlocalmarketprice * 100) + ** (1/v1.moddur) - 1, 10).dropna() + v3.delta_yield = v3.delta_yield * (v3.endlocalmarketprice/100)/ v3.pv * v3.curr_ntl + v3.delta_ir = v3.delta_ir * np.minimum(1, 1/v3.moddur) * (v3.endlocalmarketprice/100)/ v3.pv * v3.curr_ntl + calc_df = calc_df.append(v3) - return calc_df + return calc_df.reset_index().set_index('timestamp').sort_index() diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb index 1ce2a13e..b8399305 100644 --- a/python/notebooks/Allocation Reports.ipynb +++ b/python/notebooks/Allocation Reports.ipynb @@ -14,7 +14,8 @@ "import numpy as np\n", "\n", "from db import dbengine\n", - "engine = dbengine('dawndb')" + "engine = dbengine('dawndb')\n", + "Sengine = dbengine('serenitasdb')" ] }, { @@ -219,8 +220,8 @@ "metadata": {}, "outputs": [], "source": [ - "#This takes a while\n", - "df = go.get_rmbs_pos_df()" + "#RMBS Positions and Risks\n", + "rmbs_pos = go.get_rmbs_pos_df()" ] }, { @@ -233,9 +234,9 @@ "#Filtering out RMBS Bonds:\n", "#df = df[df.strat != 'MTG_FP']\n", "bond_dur, bond_yield = {}, {}\n", - "for d, g in df.groupby(pd.Grouper(freq='M')):\n", - " bond_dur[d] = sum(g.curr_ntl * g[('moddur', 3)])/sum(g.curr_ntl)\n", - " bond_yield[d] = sum(g.endlocalmv * g[('moddur', 3)] * g.b_yield) /sum(g.endlocalmv * g[('moddur', 3)])\n", + "for d, g in rmbs_pos.groupby(pd.Grouper(freq='M')):\n", + " bond_dur[d] = sum(g.curr_ntl * g.moddur)/sum(g.curr_ntl)\n", + " bond_yield[d] = sum(g.endbooknav * g.moddur * g.b_yield) /sum(g.endbooknav * g.moddur)\n", "a = pd.Series(bond_dur)\n", "b = pd.Series(bond_yield)\n", "a.name = 'Duration'\n", @@ -263,22 +264,29 @@ "metadata": {}, "outputs": [], "source": [ - "#Calculate Average Holding Period of RMBS portfolio\n", - "sql_string = \"SELECT * FROM bonds where buysell= True\"\n", - "df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates={'lastupdate': {'utc': True}, 'trade_date': {}, 'settle_date': {}})\n", + "#RMBS Risk - need RMBS Positions and Risks\n", + "sql_string = \"select date, duration, series from on_the_run where index = 'HY'\"\n", + "duration = pd.read_sql_query(sql_string, Sengine, parse_dates=['date'])\n", + "df = pd.merge_asof(rmbs_pos.sort_index(), duration, left_index=True, right_index=True)\n", + "df.groupby('timestamp').apply(lambda df: sum(df.delta_yield/df.duration * 100))" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#Calculate Average Holding Period of RMBS portfolio - Need RMBS Positions and Risks\n", + "sql_string = \"SELECT cusip, trade_date FROM bonds where buysell= True\"\n", + "df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['trade_date'])\n", "df_trades['trade_date2'] = df_trades['trade_date']\n", - "#df_trades = df_trades.groupby(['identifier']).last()\n", - "#df_with_trades = df.reset_index().merge(df_trades.reset_index(), on='identifier')\n", - "df_with_trades = pd.merge_asof(df.sort_index(), df_trades.set_index('trade_date').sort_index(), \n", + "df_with_trades = pd.merge_asof(rmbs_pos.sort_index(), df_trades.set_index('trade_date').sort_index(), \n", " left_index=True,\n", " right_index=True,\n", - " left_by='identifier',\n", - " right_by='cusip')\n", + " by='cusip')\n", "df_with_trades['hold'] = (df_with_trades.index - df_with_trades.trade_date2).dt.days/365\n", - "sp = {}\n", - "for i, g in df_with_trades.groupby('periodenddate'):\n", - " sp[i] = sum(g.endbooknav * g.hold)/sum(g.endbooknav)\n", - "holding_period = pd.DataFrame.from_dict(sp, orient='index')\n", + "holding_period = df_with_trades.groupby('timestamp').apply(lambda df: sum(df.endbooknav * df.hold)/sum(df.endbooknav))\n", "ax = holding_period.plot(legend=False, title='Average Holding Period')\n", "ax.set_xlabel('date')\n", "ax.set_ylabel('Years')" @@ -292,6 +300,13 @@ "source": [ "engine.dispose()" ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [] } ], "metadata": { diff --git a/sql/serenitasdb.sql b/sql/serenitasdb.sql index 4566d61e..8c073703 100644 --- a/sql/serenitasdb.sql +++ b/sql/serenitasdb.sql @@ -801,3 +801,16 @@ CREATE TABLE swaption_vol_cube( cube bytea NOT NULL,
source vol_source,
UNIQUE (date, vol_source))
+
+CREATE OR REPLACE VIEW public.on_the_run AS
+ SELECT DISTINCT ON (index_quotes.date, index_quotes.index) index_quotes.date,
+ index_quotes.index,
+ index_quotes.series,
+ index_quotes.version,
+ index_quotes.duration,
+ index_quotes.theta,
+ index_quotes.closeprice,
+ index_quotes.closespread
+ FROM index_quotes
+ WHERE index_quotes.tenor = '5yr'::tenor
+ ORDER BY index_quotes.date, index_quotes.index, index_quotes.series DESC, index_quotes.version;
|