diff options
Diffstat (limited to 'python/notebooks')
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 120 |
1 files changed, 96 insertions, 24 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb index a1af9465..9ad2a050 100644 --- a/python/notebooks/Allocation Reports.ipynb +++ b/python/notebooks/Allocation Reports.ipynb @@ -11,6 +11,7 @@ "import globeop_reports as go\n", "import pandas as pd\n", "import matplotlib.pyplot as plt\n", + "import numpy as np\n", "\n", "from db import dbengine\n", "engine = dbengine('dawndb')" @@ -33,8 +34,12 @@ "metadata": {}, "outputs": [], "source": [ - "pnl_alloc = go.alloc('pnl')\n", - "alloc = pnl_alloc.xs(report_date)" + "#Find the strategies that are not defined: undefined needs to be mapped in strat_map\n", + "strats = pd.read_csv('/home/serenitas/edwin/Python/strat_map.csv')\n", + "nav = go.get_net_navs()\n", + "m_pnl = go.get_monthly_pnl(['strat', 'custacctname'])\n", + "m_pnl = m_pnl.reset_index().merge(strats, on=['strat', 'custacctname'], how='left')\n", + "undefined = m_pnl[m_pnl.pnl.isna()].groupby(['strat', 'custacctname']).last()" ] }, { @@ -43,8 +48,12 @@ "metadata": {}, "outputs": [], "source": [ - "#Prev monthend PNL Allocation\n", - "go.pnl_alloc_plot(alloc)" + "#Get PNL Allocation\n", + "#Input latest NAVS to: '/home/serenitas/edwin/Python/subscription_fee_data.csv'\n", + "pnl_alloc = m_pnl.groupby(['date', 'pnl']).sum()\n", + "pnl_alloc = pnl_alloc.join(nav.begbooknav)\n", + "pnl_alloc['strat_return'] = pnl_alloc.mtdtotalbookpl / pnl_alloc.begbooknav\n", + "pnl_alloc_last_month = pnl_alloc.xs(report_date)" ] }, { @@ -53,9 +62,14 @@ "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')" + "#Plot this month's PNL\n", + "ax = pnl_alloc_last_month['strat_return'].plot(kind='bar', figsize = (6,6), width = .35)\n", + "ax.set_xlabel('Strategy')\n", + "ax.set_ylabel('Return (%)')\n", + "x_ticks = ax.get_xticks()\n", + "y_ticks = ax.get_yticks()\n", + "ax.set_yticklabels(['{:.2f}%'.format(y*100) for y in y_ticks])\n", + "#plt.tight_layout()" ] }, { @@ -64,10 +78,36 @@ "metadata": {}, "outputs": [], "source": [ - "#Capital Allocation\n", - "cap_alloc = go.alloc('capital')\n", - "alloc1 = cap_alloc.xs(report_date)\n", - "go.cap_alloc_plot_pie(alloc1)" + "#Pnl through time\n", + "#pnl_alloc_sum = pnl_alloc['mtdtotalbookpl']/ pnl_alloc['mtdtotalbookpl'].groupby(['date']).sum()\n", + "#pnl_alloc_sum['strat_return'].unstack().plot(kind='bar')" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#Capital Allocation - Find the strategies that are not defined: undefined needs to be mapped in strat_map\n", + "port = go.get_portfolio().reset_index()\n", + "cap_alloc = port.reset_index().merge(strats, on=['strat', 'custacctname'], how='left')\n", + "undefined = cap_alloc[cap_alloc.pnl.isna()].groupby(['strat', 'custacctname']).last()\n", + "alloc1 = cap_alloc[cap_alloc.periodenddate == report_date].groupby(['capital']).sum()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "# create piechart and add a circle at the center\n", + "alloc1['percentage'] = alloc1['endbooknav']/alloc1['endbooknav'].sum()\n", + "ax = alloc1[alloc1>0]['percentage'].plot(kind='pie', figsize=(8,4), autopct='%1.1f%%', pctdistance=1.25, labeldistance=1.5)\n", + "ax.add_artist(plt.Circle((0,0), 0.7, color='white'))\n", + "ax.axis('equal')\n", + "#plt.tight_layout()" ] }, { @@ -77,7 +117,15 @@ "outputs": [], "source": [ "#Average Portfolio Sales Turnover - as of last monthend from today\n", - "go.avg_turnover()" + "#Total Bond Sales Proceeds/Average starting 12 months NAV\n", + "avg_nav = go.get_net_navs().begbooknav[-12:].mean()\n", + "last_monthend = datetime.date.today() - off.MonthEnd(1)\n", + "sql_string = \"SELECT * FROM bonds where buysell = 'False'\"\n", + "df = pd.read_sql_query(sql_string, dbengine('dawndb'),\n", + " parse_dates={'lastupdate':'utc=True', 'trade_date':'', 'settle_date':''})\n", + "df = df[(df.trade_date > last_monthend - off.MonthEnd(12))\n", + " & (df.trade_date <= last_monthend)]\n", + "(df.principal_payment + df.accrued_payment).sum()/avg_nav" ] }, { @@ -86,8 +134,12 @@ "metadata": {}, "outputs": [], "source": [ - "#Number of bond positions by strategy by month - and copy to clipboard\n", - "#go.num_bond_by_strat()" + "#Number of bond positions by strategy by month\n", + "df = go.get_portfolio()\n", + "df = df[(df.custacctname == 'V0NSCLMAMB') &\n", + " ~(df.invid.isin(['USD', 'CAD', 'EUR'])) & (df.endqty > 0)]\n", + "df = df.groupby(pd.Grouper(freq='M'), group_keys=False).apply(lambda df: df.loc[df.index[-1]])\n", + "num_bond_pos = df.groupby(['periodenddate', 'port']).identifier.nunique().unstack()" ] }, { @@ -96,8 +148,13 @@ "metadata": {}, "outputs": [], "source": [ - "#Number of bond trades by direction by month - and copy to clipboard\n", - "#go.num_bond_trades()" + "#Number of bond trades by direction by month\n", + "sql_string = \"SELECT * FROM bonds\"\n", + "df = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['trade_date'],\n", + " index_col=['trade_date'])\n", + "df = df.groupby([pd.Grouper(freq='M'), 'buysell'], group_keys=False).identifier.count().unstack()\n", + "idx = pd.date_range(df.index[0], df.index[-1], freq = 'M')\n", + "num_bond_trades = df.reindex(idx, fill_value = 0)" ] }, { @@ -106,11 +163,11 @@ "metadata": {}, "outputs": [], "source": [ - "df = cap_alloc.endbooknav.groupby('periodenddate').apply(lambda x: x/x.sum())\n", - "df = df.unstack().groupby(pd.Grouper(freq='M')).apply(lambda df: df.loc[df.index[-1]])\n", - "df = go.shift_cash(datetime.date(2017,11,30), -2096454, df, 'Curve')\n", - "temp = df.iloc[-1].sort_values(ascending=False)\n", - "df = df.reindex(temp.index, axis=1)" + "#capital allocation across time\n", + "cap_alloc_time = cap_alloc.groupby(['periodenddate','capital']).sum()\n", + "cap_alloc_time = cap_alloc_time.reset_index('capital').groupby(pd.Grouper(freq='M'), group_keys=False).apply(lambda df: df.loc[df.index[-1]])\n", + "cap_alloc_time['perc'] = cap_alloc_time['endbooknav'].groupby('periodenddate').apply(lambda x: x/x.sum())\n", + "cap_alloc_time = cap_alloc_time.set_index('capital', append=True)['perc'].unstack()" ] }, { @@ -119,8 +176,21 @@ "metadata": {}, "outputs": [], "source": [ - "ax = go.cap_alloc_plot_bar(df[:-1])\n", - "lgd = ax.legend(loc='lower center', bbox_to_anchor=(0.5, -0.3), ncol=4)\n", + "ax = cap_alloc_time.plot.bar(stacked=True, legend=False, figsize=(10,6))\n", + "\n", + "#Format Y Axis\n", + "vals = ax.get_yticks()\n", + "ax.set_yticklabels(['{:3.0f}%'.format(x*100) for x in vals])\n", + "\n", + "#Format X Axis\n", + "visible = ax.xaxis.get_ticklabels()[::6]\n", + "for label in ax.xaxis.get_ticklabels():\n", + " if label not in visible:\n", + " label.set_visible(False)\n", + "ax.xaxis.set_major_formatter(plt.FixedFormatter(df.index.to_series().dt.strftime(\"%b %Y\")))\n", + "ax.xaxis.set_label_text(\"\")\n", + "lgd = ax.legend(loc='lower center', bbox_to_anchor=(0.50, -0.6), ncol=4)\n", + "plt.tight_layout()\n", "ax.figure.savefig(\"/home/serenitas/edwin/PythonGraphs/cap_alloc_1.png\", bbox_extra_artists=(lgd,), bbox_inches='tight')" ] }, @@ -200,7 +270,9 @@ "execution_count": null, "metadata": {}, "outputs": [], - "source": [] + "source": [ + "engine.dispose()" + ] } ], "metadata": { |