{
 "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",
    "\n",
    "from db import dbengine\n",
    "engine  = dbengine('dawndb')"
   ]
  },
  {
   "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": [
    "pnl_alloc = go.alloc('pnl')\n",
    "alloc = pnl_alloc.xs(report_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Prev monthend PNL Allocation\n",
    "go.pnl_alloc_plot(alloc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Average Portfolio Sales Turnover - as of last monthend from today\n",
    "go.avg_turnover()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Number of bond positions by strategy by month - and copy to clipboard\n",
    "#go.num_bond_by_strat()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Number of bond trades by direction by month - and copy to clipboard\n",
    "#go.num_bond_trades()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.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": [
    "#This takes a while\n",
    "df = go.get_rmbs_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 df.groupby(pd.Grouper(freq='M')):\n",
    "        bond_dur[d] = sum(g.curr_ntl * g[('moddur', 3)])/sum(g.curr_ntl)\n",
    "        bond_yield[d] = sum(g.endlocalmv * g[('moddur', 3)] * g.b_yield) /sum(g.endlocalmv * g[('moddur', 3)])\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": [
    "#Calculate Average Holding Period of RMBS portfolio\n",
    "sql_string = \"SELECT * FROM bonds where buysell= True\"\n",
    "df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates={'lastupdate': {'utc': True}, 'trade_date': {}, 'settle_date': {}})\n",
    "df_trades['trade_date2'] = df_trades['trade_date']\n",
    "#df_trades = df_trades.groupby(['identifier']).last()\n",
    "#df_with_trades = df.reset_index().merge(df_trades.reset_index(), on='identifier')\n",
    "df_with_trades = pd.merge_asof(df.sort_index(), df_trades.set_index('trade_date').sort_index(), \n",
    "                               left_index=True,\n",
    "                               right_index=True,\n",
    "                               left_by='identifier',\n",
    "                               right_by='cusip')\n",
    "df_with_trades['hold'] = (df_with_trades.index - df_with_trades.trade_date2).dt.days/365\n",
    "sp = {}\n",
    "for i, g in df_with_trades.groupby('periodenddate'):\n",
    "    sp[i] = sum(g.endbooknav * g.hold)/sum(g.endbooknav)\n",
    "holding_period = pd.DataFrame.from_dict(sp, orient='index')\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": []
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}