diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/globeop_reports.py | 117 | ||||
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 120 |
2 files changed, 102 insertions, 135 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index b92eabfa..4562c605 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -18,9 +18,10 @@ def get_monthly_pnl(group_by = ['identifier']): index_col=['date']) df_pnl['identifier'] = df_pnl.invid.str.replace("_A$", "") pnl_cols = ['bookunrealmtm', 'bookrealmtm', 'bookrealincome', 'bookunrealincome', 'totalbookpl'] - monthend_pnl = df_pnl.groupby(pd.Grouper(freq='M')).apply(lambda df: df.loc[df.index[-1]]) + monthend_pnl = df_pnl.groupby(pd.Grouper(freq='M'), group_keys=False).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 = None): if report_date is not None: sql_string = "SELECT * FROM valuation_reports where periodenddate = %s" @@ -33,6 +34,7 @@ def get_portfolio(report_date = None): df['identifier'] = df.invid.str.replace("_A$", "") return df + def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'): date = (date - off.MonthEnd(1)).date() sql_string = "SELECT * FROM risk_positions(%s, %s) WHERE notional > 0" @@ -41,6 +43,7 @@ def curr_port_PNL(date = datetime.date.today(), asset_class='Subprime'): df_all = df_positions.merge(df_pnl.groupby('identifier').sum().reset_index(), on=['identifier']) return df_all + def trade_performance(): sql_string = "SELECT * FROM bonds" df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates={'lastupdate': {'utc': True}, 'trade_date': {}, 'settle_date': {}}) @@ -74,11 +77,9 @@ def trade_performance(): df_all = df_all.sort_values('trade_date', ascending=False) - table = pd.DataFrame() - #table['average_days_held'] = df_all.days_held.mean() - return df_all + def get_net_navs(): sql_string = "SELECT * FROM valuation_reports" df_val = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['periodenddate']) @@ -91,94 +92,6 @@ def get_net_navs(): df.at[('2013-01-31', 'begbooknav')] = 12500000 return df -#def alloc(report_date, alloc = 'pnl'): -def alloc(alloc = 'pnl'): - - """ Takes strategy grouping """ - "Alloc: pnl or capital" - - if alloc == 'pnl': - #nav = go.get_net_navs() - #df = go.get_monthly_pnl(['strat', 'custacctname']) - nav = get_net_navs() - df = get_monthly_pnl(['strat', 'custacctname']) - df = df.join(nav.begbooknav) - df['strat_return'] = df.mtdtotalbookpl / df.begbooknav - df = df.reset_index().dropna(subset = ['custacctname']) - df.set_index(['strat', 'custacctname'], inplace=True) - df = df.rename(columns={"date": "periodenddate"}) - #df = df.loc[report_date.date()] - elif alloc == 'capital': - df = get_portfolio().reset_index() - df.dropna(subset = ['custacctname'], inplace=True) - df.set_index(['strat', 'custacctname'], inplace=True) - - #get strategy lookup table: group-by a merged DF to spot unmapped strategies - strats = pd.read_csv('/home/serenitas/edwin/Python/strat_map.csv', index_col=['strat', 'custacctname']) - #Check for empty sets: df.set_index(['strat','custacctname']).groupby(['strat','custacctname']) - df = df.merge(strats, left_index=True, right_index=True) - df = df.fillna(-1) - return df.groupby(['periodenddate', alloc]).sum() - -def pnl_alloc_plot(df): - - """ Takes the alloc('pnl') dataframe """ - y = df.strat_return - x = df.index - x_loc = np.arange(len(df.index)) - - width = .35 #width of the bar - fig, ax = plt.subplots(figsize = (6,6)) - ax.bar(x_loc, y, width) - - ax.set_xlabel('Strategy') - ax.set_xticks(x_loc + width /2) - ax.set_xticklabels(x, rotation='45') - - #set y-axis as percentage - ax.set_ylabel('Return (%)') - y_ticks = ax.get_yticks() - ax.set_yticklabels(['{:.2f}%'.format(y*100) for y in y_ticks]) - plt.tight_layout() - -def cap_alloc_plot_pie(df): - - """ Takes the alloc('capital') dataframe""" - # create piechart and add a circle at the center - - df['alloc'] = df['endbooknav']/df['endbooknav'].sum() - fig, ax = plt.subplots(figsize=(8,4)) - ax.pie(df.alloc, labels=df.index, autopct='%1.1f%%', - pctdistance=1.25, labeldistance=1.5) - ax.add_artist(plt.Circle((0,0), 0.7, color='white')) - ax.axis('equal') - plt.tight_layout() - -def avg_turnover(): - #Total Bond Sales Proceeds/Average starting 12 months NAV - avg_nav = get_net_navs().begbooknav[-12:].mean() - last_monthend = datetime.date.today() - off.MonthEnd(1) - sql_string = "SELECT * FROM bonds where buysell = 'False'" - df = pd.read_sql_query(sql_string, dbengine('dawndb'), - parse_dates={'lastupdate':'utc=True', 'trade_date':'', 'settle_date':''}) - df = df[(df.trade_date > last_monthend - off.MonthEnd(12)) - & (df.trade_date <= last_monthend)] - return (df.principal_payment + df.accrued_payment).sum()/avg_nav - -def num_bond_by_strat(): - df = get_portfolio() - df = df[(df.custacctname == 'V0NSCLMAMB') & - ~(df.invid.isin(['USD', 'CAD', 'EUR'])) & (df.endqty > 0)] - df = df.groupby(pd.Grouper(freq='M')).apply(lambda df: df.loc[df.index[-1]]) - return df.groupby(['periodenddate', 'port']).identifier.nunique().unstack() - -def num_bond_trades(): - sql_string = "SELECT * FROM bonds" - df = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['trade_date'], - index_col=['trade_date']) - df = df.groupby([pd.Grouper(freq='M'), 'buysell']).identifier.count().unstack() - idx = pd.date_range(df.index[0], df.index[-1], freq = 'M') - return df.reindex(idx, fill_value = 0) def shift_cash(date, amount, df, strat): nav = get_net_navs() @@ -186,23 +99,6 @@ def shift_cash(date, amount, df, strat): df.loc[date,'Cash'] = df.loc[date, 'Cash'] + amount/nav.loc[date].endbooknav return df -def cap_alloc_plot_bar(df): - #ax = df.plot.bar(stacked=True) - ax = df[:-1].plot.bar(stacked=True, legend=False, figsize=(10,4)) - - #Format Y Axis - vals = ax.get_yticks() - ax.set_yticklabels(['{:3.0f}%'.format(x*100) for x in vals]) - - #Format X Axis - visible = ax.xaxis.get_ticklabels()[::6] - for label in ax.xaxis.get_ticklabels(): - if label not in visible: - label.set_visible(False) - ax.xaxis.set_major_formatter(plt.FixedFormatter(df.index.to_series().dt.strftime("%b %Y"))) - ax.xaxis.set_label_text("") - - return ax def calc_trade_performance_stats(): df = trade_performance().set_index('trade_date') @@ -222,11 +118,10 @@ def calc_trade_performance_stats(): import pdb; pdb.set_trace() y = y.date().year results.loc[y] = df2[df2.days_held.notnull()].mean()[['curr_face','initialinvestment', 'days_held']] - #results.loc[] = len(df2[df2.winners == x].index)/len(df) - df[df.days_held.notnull()]['days_held'].groupby(pd.Grouper(freq='A')).mean() + def get_rmbs_pos_df(date = None): engine = dbengine('dawndb') 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": { |