diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 101 |
1 files changed, 61 insertions, 40 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb index 2b35e2bb..a1af9465 100644 --- a/python/notebooks/Allocation Reports.ipynb +++ b/python/notebooks/Allocation Reports.ipynb @@ -10,7 +10,10 @@ "import pandas.tseries.offsets as off\n", "import globeop_reports as go\n", "import pandas as pd\n", - "import matplotlib.pyplot as plt" + "import matplotlib.pyplot as plt\n", + "\n", + "from db import dbengine\n", + "engine = dbengine('dawndb')" ] }, { @@ -20,9 +23,7 @@ "outputs": [], "source": [ "#PNL Allocation\n", - "#report_date = datetime.date(2017,10,31)\n", - "report_date = datetime.date.today()\n", - "report_date = report_date - off.MonthEnd(1)\n", + "report_date = datetime.date.today() - off.MonthEnd(1)\n", "report_date" ] }, @@ -33,7 +34,16 @@ "outputs": [], "source": [ "pnl_alloc = go.alloc('pnl')\n", - "alloc = pnl_alloc.xs(report_date)\n", + "alloc = pnl_alloc.xs(report_date)" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#Prev monthend PNL Allocation\n", "go.pnl_alloc_plot(alloc)" ] }, @@ -43,6 +53,17 @@ "metadata": {}, "outputs": [], "source": [ + "#Pnl Alloc through time\n", + "pnl_alloc_sum = pnl_alloc['mtdtotalbookpl']/ pnl_alloc['mtdtotalbookpl'].groupby(['periodenddate']).sum()\n", + "pnl_alloc_sum.unstack().plot(kind='bar')" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ "#Capital Allocation\n", "cap_alloc = go.alloc('capital')\n", "alloc1 = cap_alloc.xs(report_date)\n", @@ -109,6 +130,7 @@ "metadata": {}, "outputs": [], "source": [ + "#This takes a while\n", "df = go.get_rmbs_pos_df()" ] }, @@ -118,36 +140,31 @@ "metadata": {}, "outputs": [], "source": [ + "#Plot Duration and Yield to Maturity of RMBS Portfolio\n", + "#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", "a = pd.Series(bond_dur)\n", - "b = pd.Series(bond_yield)" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ + "b = pd.Series(bond_yield)\n", + "a.name = 'Duration'\n", + "b.name = 'Yield-to-maturity'\n", + "\n", "fig = plt.figure()\n", "ax0 = fig.add_subplot(111)\n", + "ax1 = ax0.twinx()\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", + "a.plot(kind='line', color = 'r', ax=ax0, label = a.name, legend=True)\n", + "b.plot(kind='line', secondary_y=True, ax=ax1, label = b.name, legend=True)\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_xlabel('date')\n", "ax1.set_xlim([a.index.min(), a.index.max()])\n", + "ax0.set_ylabel('Duration')\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", + "\n", + "ax0.legend(loc=2)\n", "fig.tight_layout()" ] }, @@ -157,14 +174,25 @@ "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)" + "#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", + "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", + " left_index=True,\n", + " right_index=True,\n", + " left_by='identifier',\n", + " right_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", + "ax = holding_period.plot(legend=False, title='Average Holding Period')\n", + "ax.set_xlabel('date')\n", + "ax.set_ylabel('Years')" ] }, { @@ -173,13 +201,6 @@ "metadata": {}, "outputs": [], "source": [] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [] } ], "metadata": { @@ -198,7 +219,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.6.4" + "version": "3.6.5" } }, "nbformat": 4, |