diff options
Diffstat (limited to 'python/notebooks')
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 51 |
1 files changed, 33 insertions, 18 deletions
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": { |