{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import pandas.tseries.offsets as off\n",
    "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')\n",
    "Sengine = dbengine('serenitasdb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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\n",
    "pnl_alloc_last_month = pnl_alloc.xs(report_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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": [
    "#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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "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')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Positions and Risks\n",
    "rmbs_pos = go.get_rmbs_pos_df()\n",
    "clo_pos = go.get_clo_pos_df()"
   ]
  },
  {
   "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": [
    "#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",
    "df = pd.merge_asof(rmbs_pos.sort_index(), duration, left_index=True, right_index=True)\n",
    "rmbs_hy_equiv = df.groupby('timestamp').apply(lambda df: sum(df.delta_yield/df.duration * 100))\n",
    "#hy_equiv.plot()"
   ]
  },
  {
   "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.endbooknav * df.hold)/sum(df.endbooknav))\n",
    "ax = holding_period.plot(legend=False, title='Average Holding Period')\n",
    "ax.set_xlabel('date')\n",
    "ax.set_ylabel('Years')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}