aboutsummaryrefslogtreecommitdiffstats
path: root/python/notebooks/Allocation Reports.ipynb
diff options
context:
space:
mode:
Diffstat (limited to 'python/notebooks/Allocation Reports.ipynb')
-rw-r--r--python/notebooks/Allocation Reports.ipynb182
1 files changed, 4 insertions, 178 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb
index e64b84b0..69e6e620 100644
--- a/python/notebooks/Allocation Reports.ipynb
+++ b/python/notebooks/Allocation Reports.ipynb
@@ -27,56 +27,6 @@
"metadata": {},
"outputs": [],
"source": [
- "#PNL Allocation\n",
- "report_date = datetime.date.today() - off.MonthEnd(1)\n",
- "report_date"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
- "#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()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
- "#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"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
- "#rolling 12 months PNL per strategy - copy to RiskMonitor\n",
- "start_date = report_date - pd.tseries.offsets.MonthEnd(11)\n",
- "rolling_return = pnl_alloc[start_date:report_date].groupby('pnl').sum()['strat_return']\n",
- "rolling_return.to_clipboard()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
"#Plot this month's PNL\n",
"pnl_alloc_last_month = pnl_alloc.xs(report_date)\n",
"ax = pnl_alloc_last_month['strat_return'].plot(kind='bar', figsize = (6,6), width = .35)\n",
@@ -132,52 +82,6 @@
"metadata": {},
"outputs": [],
"source": [
- "#Average Portfolio Sales Turnover - as of last monthend from today\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",
- " 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]"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
- "turnover.to_clipboard(sep='\\t')"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
"#Number of bond positions by strategy by month\n",
"df = go.get_portfolio()\n",
"df = df[(df.custacctname == 'V0NSCLMAMB') &\n",
@@ -243,92 +147,14 @@
"execution_count": null,
"metadata": {},
"outputs": [],
- "source": [
- "#Positions and Risks\n",
- "rmbs_pos = go.hist_pos(asset_class = 'rmbs')\n",
- "clo_pos = go.hist_pos(asset_class = 'clo')"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "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 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.endbookmv * g.moddur * g.b_yield) /sum(g.endbookmv * g.moddur)\n",
- "a = pd.Series(bond_dur)\n",
- "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.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.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",
- "\n",
- "ax0.legend(loc=2)\n",
- "fig.tight_layout()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
- "a.to_clipboard()\n",
- "#b.to_clipboard()"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {},
- "outputs": [],
- "source": [
- "#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'], index_col=['date'])\n",
- "rmbs_pos = pd.merge_asof(rmbs_pos.sort_index(), duration, left_index=True, right_index=True)\n",
- "rmbs_pos['hy_equiv'] = rmbs_pos.delta_yield/rmbs_pos.duration * 100\n",
- "rmbs_pos.groupby('timestamp').sum()\n",
- "#hy_equiv.plot()"
- ]
+ "source": []
},
{
"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_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",
- " by='cusip')\n",
- "df_with_trades['hold'] = (df_with_trades.index - df_with_trades.trade_date2).dt.days/365\n",
- "holding_period = df_with_trades.groupby('timestamp').apply(lambda df: sum(df.endbookmv * df.hold)/sum(df.endbookmv))\n",
- "ax = holding_period.plot(legend=False, title='Average Holding Period')\n",
- "ax.set_xlabel('date')\n",
- "ax.set_ylabel('Years')\n",
- "holding_period[-1]"
- ]
+ "source": []
},
{
"cell_type": "code",
@@ -387,9 +213,9 @@
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
- "version": "3.7.1"
+ "version": "3.7.3"
}
},
"nbformat": 4,
- "nbformat_minor": 2
+ "nbformat_minor": 4
}
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-22 20:43:34 +0000