diff options
| -rw-r--r-- | python/globeop_reports.py | 52 | ||||
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 71 |
2 files changed, 116 insertions, 7 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index f624971c..b92eabfa 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -1,6 +1,7 @@ from glob import iglob from db import dbengine from pandas.tseries.offsets import MonthEnd +from yieldcurve import YC import os import pandas as pd @@ -10,6 +11,7 @@ import matplotlib.pyplot as plt import matplotlib.dates as mdates import pandas.tseries.offsets as off + def get_monthly_pnl(group_by = ['identifier']): sql_string = "SELECT * FROM pnl_reports" df_pnl = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['date'], @@ -19,8 +21,8 @@ def get_monthly_pnl(group_by = ['identifier']): monthend_pnl = df_pnl.groupby(pd.Grouper(freq='M')).apply(lambda df: df.loc[df.index[-1]]) return monthend_pnl.groupby(['date'] + group_by)[['mtd' + col for col in pnl_cols]].sum() -def get_portfolio(report_date = False): - if report_date != False: +def get_portfolio(report_date = None): + if report_date is not None: sql_string = "SELECT * FROM valuation_reports where periodenddate = %s" df = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate'], index_col=['periodenddate'], params=[report_date,]) @@ -208,7 +210,7 @@ def calc_trade_performance_stats(): df['winners'] = df.apply(lambda df: True if df.percent_gain > 0 else False, axis = 1) df['curr_face'] = df.principal_payment/(df.price/100) - index = ['All','2017','2016','2015','2014','2013'] + index = ['All', '2017', '2016', '2015', '2014', '2013'] results = pd.DataFrame(index = index) win_per = len(df[df.winners].index)/len(df) @@ -225,3 +227,47 @@ def calc_trade_performance_stats(): df[df.days_held.notnull()]['days_held'].groupby(pd.Grouper(freq='A')).mean() +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" + timestamps = pd.read_sql_query(sql_string, engine) + + 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) + libor = float(yc.zero_rate(.125)) + if d > pd.datetime(2017, 9, 30): + model_id_sql_string = "SELECT * FROM latest_sim(%s)" + model_id = pd.read_sql_query(model_id_sql_string, engine, params=[model_date]) + model_id = model_id.loc[0][0] + #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]) + 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) + + return calc_df diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb index 00ad7972..2b35e2bb 100644 --- a/python/notebooks/Allocation Reports.ipynb +++ b/python/notebooks/Allocation Reports.ipynb @@ -9,7 +9,8 @@ "import datetime\n", "import pandas.tseries.offsets as off\n", "import globeop_reports as go\n", - "import pandas as pd" + "import pandas as pd\n", + "import matplotlib.pyplot as plt" ] }, { @@ -65,7 +66,7 @@ "outputs": [], "source": [ "#Number of bond positions by strategy by month - and copy to clipboard\n", - "go.num_bond_by_strat()" + "#go.num_bond_by_strat()" ] }, { @@ -75,7 +76,7 @@ "outputs": [], "source": [ "#Number of bond trades by direction by month - and copy to clipboard\n", - "go.num_bond_trades()" + "#go.num_bond_trades()" ] }, { @@ -108,7 +109,7 @@ "metadata": {}, "outputs": [], "source": [ - "df" + "df = go.get_rmbs_pos_df()" ] }, { @@ -116,6 +117,68 @@ "execution_count": null, "metadata": {}, "outputs": [], + "source": [ + "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", + "a = pd.Series(bond_dur)\n", + "b = pd.Series(bond_yield)" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "fig = plt.figure()\n", + "ax0 = fig.add_subplot(111)\n", + "\n", + "a.name = 'Yield-to-maturity'\n", + "ax1.set_xlabel('date')\n", + "a.plot(kind='line', color = 'r', ax=ax0, label = a.name)\n", + "ax0.set_ylabel('Duration')\n", + "ax0.legend(loc=0)\n", + "\n", + "ax1 = ax0.twinx()\n", + "b.name = 'Duration'\n", + "b.plot(kind='line', secondary_y=True, ax=ax1, label = b.name)\n", + "ax1.set_xlim([a.index.min(), a.index.max()])\n", + "ax1.set_ylabel('Yield-to-Maturity')\n", + "ax1.legend(loc=2)\n", + "#plt.legend(bbox_to_anchor=(0, -.1), loc=0, borderaxespad=0.)\n", + "fig.tight_layout()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#filter out First-Pay bonds. \n", + "df_1 = c[c.strat != 'MTG_FP']\n", + "bond_dur, bond_yield = {}, {}\n", + "for d, g in df_1.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", + "a_1 = pd.Series(bond_dur)\n", + "b_1 = pd.Series(bond_yield)" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], "source": [] } ], |