path: root/python/notebooks/Reto Report.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "from pandas.tseries.offsets import BDay, MonthEnd\n",
    "import globeop_reports as go\n",
    "import pandas as pd\n",
    "import analytics\n",
    "import numpy as np\n",
    "\n",
    "from analytics.index_data import get_index_quotes\n",
    "from analytics.scenarios import run_portfolio_scenarios\n",
    "from analytics import BlackSwaption, CreditIndex, BlackSwaptionVolSurface, Portfolio,DualCorrTranche\n",
    "\n",
    "from utils.db import dbconn, dbengine\n",
    "\n",
    "from risk.tranches import get_tranche_portfolio\n",
    "from risk.bonds import subprime_risk, clo_risk, crt_risk\n",
    "\n",
    "dawn_engine = dbengine('dawndb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#PNL Allocation\n",
    "date = datetime.date.today() - BDay(1)\n",
    "report_date = date - 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()\n",
    "#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",
    "#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": [
    "#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 IS False\"\n",
    "df = pd.read_sql_query(sql_string, dawn_engine,\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",
    "cf = pd.read_sql_query(\"SELECT * FROM cashflow_history\", dawn_engine,\n",
    "                       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": [
    "position_date = (datetime.date.today() - BDay(1)).date()\n",
    "shock_date = (datetime.date.today() - BDay(1)).date()\n",
    "spread_date = shock_date\n",
    "(position_date, spread_date, shock_date)\n",
    "analytics.init_ontr(spread_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Calculate amount of stress for reports\n",
    "from analytics.curve_trades import on_the_run\n",
    "df = get_index_quotes('HY', list(range(on_the_run('HY', spread_date) - 10, on_the_run('HY', spread_date) + 1)),\n",
    "                          tenor=['5yr'], years=5)\n",
    "df = df.xs('5yr', level='tenor')['close_spread'].groupby(['date', 'series']).last()\n",
    "\n",
    "widen, tighten = [], []\n",
    "#approximately 1,3,6 months move (22 each months)\n",
    "for days in [22, 66, 132]: \n",
    "    calc = df.unstack().pct_change(freq= str(days)+'B').stack().groupby('date').last()\n",
    "    widen.append(calc.max())\n",
    "    tighten.append(calc.min())\n",
    "pd.DataFrame([widen, tighten], columns=['1M', '3M', '6M'], index=['widen', 'tighten'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#tranche positions\n",
    "conn = dawn_engine.raw_connection()\n",
    "portf = get_tranche_portfolio(position_date, conn, False, 'SERCGMAST')\n",
    "\n",
    "#swaption positions\n",
    "swaption_sql_string = (\"select id, folder, expiration_date from swaptions where expiration_date > %s \"\n",
    "                       \"AND swap_type = 'CD_INDEX_OPTION' \"\n",
    "                       \"AND trade_date <= %s AND termination_date IS NULL\")\n",
    "\n",
    "with conn.cursor() as c:\n",
    "    c.execute(swaption_sql_string, (position_date, position_date))\n",
    "    for trade_id, strat, expiration_date in c:\n",
    "        if expiration_date > shock_date:\n",
    "            portf.add_trade(BlackSwaption.from_tradeid(trade_id), (strat, trade_id))\n",
    "conn.close()\n",
    "\n",
    "#index positions\n",
    "df = pd.read_sql_query(\"SELECT * from list_cds_positions_by_strat(%s)\",\n",
    "                       dawn_engine, params=(position_date,))\n",
    "df_curve = df[df.folder.str.contains(\"CURVE\")]\n",
    "df_no_curve = df[~df.folder.str.contains(\"CURVE\")]\n",
    "for t in df_no_curve.itertuples(index=False):\n",
    "    portf.add_trade(CreditIndex(redcode=t.security_id, maturity=t.maturity, notional=t.notional),\n",
    "                   (t.folder, t.security_desc))\n",
    "    \n",
    "#separately add in curve delta\n",
    "curve_portf = Portfolio([CreditIndex(redcode=t.security_id, maturity=t.maturity, notional=t.notional)\n",
    "                         for t in df_curve.itertuples(index=False)])\n",
    "curve_portf.value_date = spread_date\n",
    "curve_portf.mark()\n",
    "portf.add_trade(CreditIndex('HY', on_the_run('HY', spread_date), '5yr', \n",
    "                            value_date=spread_date, \n",
    "                            notional=curve_portf.hy_equiv), ('curve_trades', ''))\n",
    "\n",
    "mysql_engine = dbengine('rmbs_model')\n",
    "mysqlcrt_engine = dbengine('crt')\n",
    "\n",
    "#get bond risks:\n",
    "with dbconn('etdb') as etconn, dbconn('dawndb') as dawnconn:\n",
    "    rmbs_pos = subprime_risk(position_date, dawnconn, mysql_engine)\n",
    "    clo_pos = clo_risk(position_date, dawnconn, etconn)\n",
    "    crt_pos = crt_risk(position_date, dawnconn, mysqlcrt_engine)\n",
    "duration = analytics._ontr.risky_annuity\n",
    "rmbs_pos['hy_equiv'] = rmbs_pos['delta_yield']/duration * 100\n",
    "crt_pos['hy_equiv'] = crt_pos['delta_yield']/duration * 100\n",
    "notional = rmbs_pos['hy_equiv'].sum() + clo_pos['hy_equiv'].sum() + crt_pos['hy_equiv'].sum()\n",
    "portf.add_trade(CreditIndex('HY', on_the_run('HY', spread_date), '5yr', \n",
    "                            value_date = spread_date, \n",
    "                            notional = -notional), ('bonds', ''))\n",
    "    \n",
    "portf.value_date = spread_date\n",
    "portf.mark(interp_method=\"bivariate_linear\")\n",
    "portf.reset_pv()\n",
    "\n",
    "vol_surface = {}\n",
    "for trade in portf.swaptions:\n",
    "    vs = BlackSwaptionVolSurface(trade.index.index_type, trade.index.series, \n",
    "                                 value_date=spread_date, interp_method = \"bivariate_linear\")\n",
    "    vol_surface[(trade.index.index_type, trade.index.series)] = vs[vs.list(option_type='payer')[-1]]\n",
    "vol_shock = [0]\n",
    "corr_shock = [0, -.1]\n",
    "spread_shock = tighten + [0] + widen\n",
    "date_range = [pd.Timestamp(shock_date)]\n",
    "\n",
    "scens = run_portfolio_scenarios(portf, date_range, params=[\"pnl\"],\n",
    "                                spread_shock=spread_shock,\n",
    "                                vol_shock=vol_shock,\n",
    "                                corr_shock=corr_shock,\n",
    "                                vol_surface=vol_surface)\n",
    "\n",
    "attrib = (scens.\n",
    "          reset_index(level=['date'], drop=True).\n",
    "          groupby(level=0, axis=1).sum())\n",
    "attrib.columns.name = 'strategy'\n",
    "results = attrib.xs((widen[2], 0.), level=['spread_shock', 'corr_shock']).unstack('strategy')\n",
    "results.name = 'pnl'\n",
    "#results.to_clipboard(header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Run set of scenario\n",
    "spread_shock = np.round(np.arange(-.2, 1, .025), 3)\n",
    "scens = run_portfolio_scenarios(portf, date_range, params=['pnl', 'delta'],\n",
    "                                spread_shock=spread_shock,\n",
    "                                vol_shock=vol_shock,\n",
    "                                corr_shock=[0],\n",
    "                                vol_surface=vol_surface)\n",
    "\n",
    "pnl = scens.xs('pnl', axis=1, level=2)\n",
    "pnl = pnl.xs((0,0), level=['vol_shock', 'corr_shock'])\n",
    "\n",
    "scenarios = (pnl.\n",
    "          reset_index(level=['date'], drop=True).\n",
    "          groupby(level=0, axis=1).sum())\n",
    "\n",
    "options = ['HYOPTDEL', 'HYPAYER', 'HYREC', 'IGOPTDEL', 'IGPAYER', 'IGREC']\n",
    "tranches = ['HYMEZ', 'HYINX', 'HYEQY', 'IGMEZ', 'IGINX', 'IGEQY', 'IGSNR', 'IGINX', 'BSPK']\n",
    "\n",
    "scenarios['options'] = scenarios[set(scenarios.columns).intersection(options)].sum(axis=1)\n",
    "scenarios['tranches'] = scenarios[set(scenarios.columns).intersection(tranches)].sum(axis=1)\n",
    "\n",
    "synthetic = scenarios[['options', 'tranches', 'curve_trades']]\n",
    "synthetic['total'] = synthetic.sum(axis = 1)\n",
    "synthetic.plot()"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}