diff options
Diffstat (limited to 'python/notebooks/Allocation Reports.ipynb')
| -rw-r--r-- | python/notebooks/Allocation Reports.ipynb | 37 |
1 files changed, 28 insertions, 9 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb index 9ad2a050..1ce2a13e 100644 --- a/python/notebooks/Allocation Reports.ipynb +++ b/python/notebooks/Allocation Reports.ipynb @@ -69,7 +69,7 @@ "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()" + "plt.tight_layout()" ] }, { @@ -107,7 +107,7 @@ "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()" + "plt.tight_layout()" ] }, { @@ -117,15 +117,34 @@ "outputs": [], "source": [ "#Average Portfolio Sales Turnover - as of last monthend from today\n", - "#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", + "#(total Bond Sales Proceeds + paydown)/average starting 12 months NAV\n", + "nav = go.get_net_navs()\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" + " parse_dates={'lastupdate':'utc=True', 'trade_date':'', 'settle_date':''},\n", + " index_col = 'trade_date')\n", + "df = df.groupby(pd.Grouper(freq='M')).sum()\n", + "#Now get portfolio paydown per month\n", + "portfolio = go.get_portfolio()\n", + "portfolio = portfolio[(portfolio.custacctname == 'V0NSCLMAMB') &\n", + " (portfolio.port == 'MORTGAGES') &\n", + " (portfolio.identifier != 'USD') &\n", + " (portfolio.endqty != 0)]\n", + "portfolio = portfolio.set_index('identifier', append=True)\n", + "portfolio = portfolio['endqty'].groupby(['identifier', 'periodenddate']).sum()\n", + "portfolio = portfolio.reset_index('identifier') \n", + "sql_string = \"SELECT * from cashflow_history\"\n", + "cf = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['date'],\n", + " index_col=['date']).sort_index()\n", + "df_1 = pd.merge_asof(cf, portfolio.sort_index(), left_index=True, right_index=True, by='identifier')\n", + "df_1 = df_1.dropna(subset=['endqty'])\n", + "df_1 = df_1[(df_1.principal_bal != 0) & (df_1.principal != 0)]\n", + "df_1['paydown'] = df_1.apply(lambda df: df.endqty/df.principal_bal * df.principal, axis=1)\n", + "paydowns = df_1.paydown.groupby(pd.Grouper(freq='M')).sum()\n", + "temp = pd.concat([paydowns, df.principal_payment, df.accrued_payment], axis=1).fillna(0)\n", + "turnover = temp.rolling(12).sum().sum(axis=1)/ nav.begbooknav.rolling(12).mean()\n", + "turnover[12:].plot()\n", + "turnover[-1]" ] }, { |