diff options
Diffstat (limited to 'python/notebooks')
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 210 |
1 files changed, 91 insertions, 119 deletions
diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index 03e5c06f..f05d63b2 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -7,21 +7,21 @@ "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 pandas.tseries.offsets import BDay, MonthEnd\n", "from analytics.index_data import get_index_quotes\n", "from analytics.scenarios import run_portfolio_scenarios\n", + "from analytics.utils import run_local\n", "from analytics import BlackSwaption, CreditIndex, BlackSwaptionVolSurface, Portfolio,DualCorrTranche\n", "from copy import deepcopy\n", - "\n", + "from analytics.curve_trades import on_the_run\n", "from risk.tranches import get_tranche_portfolio\n", "from risk.swaptions import get_swaption_portfolio\n", "from risk.bonds import subprime_risk, clo_risk, crt_risk\n", - "\n", "from utils.db import dbconn, dbengine, serenitas_engine, dawn_engine" ] }, @@ -31,7 +31,11 @@ "metadata": {}, "outputs": [], "source": [ - "#PNL Allocation\n", + "#Set dates\n", + "analytics._local = False\n", + "position_date = (datetime.date.today() - BDay(1)).date()\n", + "spread_date = position_date\n", + "analytics.init_ontr(spread_date)\n", "date = datetime.date.today() - BDay(1)\n", "report_date = date - MonthEnd(1)\n", "report_date" @@ -66,15 +70,6 @@ "metadata": {}, "outputs": [], "source": [ - "rolling_return" - ] - }, - { - "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", "#Actually: Rolling 12 months sum of (total bond sales proceeds + paydown)/monthly NAV\n", @@ -105,40 +100,7 @@ "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.sum(axis=1)/nav.begbooknav).rolling(12).sum()\n", - "turnover[12:].plot()\n", - "turnover.min(), turnover.max(), turnover.mean()\n", - "turnover[-1]" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "################################### BRINKER: Average Portfolio Sales Turnover - as of last monthend from today\n", - "#(total Bond Sales Proceeds + paydown)/average starting 12 months NAV\n", - "#Actually: Rolling 12 months sum of (total bond sales proceeds + paydown)/monthly NAV\n", - "nav = go.get_net_navs()\n", - "fund='BRINKER'\n", - "sql_string = \"SELECT * FROM bonds WHERE buysell IS False and fund = %s\"\n", - "df = pd.read_sql_query(sql_string, dawn_engine,\n", - " parse_dates={'lastupdate':{'utc':True}, 'trade_date': {}, 'settle_date':{}},\n", - " params=[fund,],\n", - " index_col = 'trade_date')\n", - "df = df.groupby(pd.Grouper(freq='M')).sum()\n", - "\n", - "cf = pd.read_sql_query(\"SELECT * FROM cashflow_history\", dawn_engine,\n", - " parse_dates=['date'],\n", - " index_col=['date']).sort_index()\n", - "sql_string = \"SELECT description, identifier, notional, price, factor FROM risk_positions(%s, %s, 'BRINKER')\"\n", - "pos = {}\n", - "for d in cf.index.unique():\n", - " for ac in ['Subprime', 'CRT']:\n", - " pos[d, ac] = pd.read_sql_query(sql_string, dawn_engine, params=[d.date(), ac])\n", - "pos = pd.concat(pos, names=['date', 'asset_class'])\n", - "pos = pos.reset_index(level=[1,2])" + "turnover = (temp.sum(axis=1)/nav.begbooknav).rolling(12).sum()" ] }, { @@ -289,8 +251,21 @@ "monthend_portfolio = portfolio.groupby(pd.Grouper(freq=\"M\"), group_keys=False).apply(\n", " lambda df: df.loc[df.index[-1]]\n", " )\n", - "monthend_byinvid = monthend_portfolio.groupby(['periodenddate','invid']).sum()\n", - "positive = monthend_byinvid['endbooknav'].groupby(['periodenddate']).agg(lambda x: x[x>0].sum())\n", + "options_list = ['IGPAYER', 'HYPAYER', 'IGREC', 'HYREC']\n", + "syn_list = ['CSO_TRANCH', 'LQD_TRANCH', 'HEDGE_CLO', 'HEDGE_CSO', 'HEDGE_MAC', 'HEDGE_MBS',\n", + " 'IGMEZ','HYMEZ', 'IGINX', 'MBSCDS', 'IGCURVE', 'IGOPTDEL', 'HYOPTDEL',\n", + " 'ITRXCURVE', 'IGEQY', 'IGSNR', 'BSPK', 'HYINX', 'HYEQY', 'XCURVE', 'XOMEZ', 'XOINX', 'EUMEZ']\n", + "\n", + "monthend_syn = monthend_portfolio[monthend_portfolio.strat.isin(syn_list)]\n", + "monthend_syn = monthend_syn[monthend_syn['endqty'] < 0]\n", + "monthend_syn = monthend_syn.groupby(['periodenddate','invid']).sum()\n", + "\n", + "monthend_cash = monthend_portfolio[~monthend_portfolio.strat.isin(syn_list)]\n", + "monthend_cash = monthend_cash.groupby(['periodenddate','invid']).sum()\n", + "\n", + "positive = pd.concat([monthend_syn, monthend_cash])\n", + "positive = positive['endbooknav'].groupby(['periodenddate']).agg(lambda x: x[x>0].sum())\n", + "\n", "nav = nav.merge(positive, left_index=True, right_index=True)\n", "nav['leverage'] = nav.endbooknav_y/nav.endbooknav_x\n", "nav['leverage'].plot()" @@ -344,6 +319,28 @@ "metadata": {}, "outputs": [], "source": [ + "#Historical max widening\n", + "analytics.init_ontr()\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", + "df=df.loc[:'2020-2-28']\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": [ "################################## Historical Notioinals and HY Equiv\n", "dates = pd.date_range(datetime.date(2013, 1, 30), pd.datetime.today() - MonthEnd(1), freq=\"BM\")\n", "#look for a day with HY quotes... we need that to construct HY Equiv\n", @@ -371,10 +368,13 @@ " if spread_date is None:\n", " spread_date=position_date\n", " conn = dawn_engine.raw_connection()\n", + " conn.autocommit = True\n", + " \n", " mysql_engine = dbengine('rmbs_model')\n", " mysqlcrt_engine = dbengine('crt')\n", " \n", - " on_the_run_index = CreditIndex('HY', on_the_run('HY', position_date), '5yr', value_date=position_date)\n", + " on_the_run_index = analytics._ontr['HY']\n", + " on_the_run_index.value_date = position_date\n", "\n", " #tranche positions\n", " portf = get_tranche_portfolio(position_date, conn, False, 'SERCGMAST')\n", @@ -437,41 +437,10 @@ "metadata": {}, "outputs": [], "source": [ - "################################### Calculate stress scenario \n", - "position_date = (datetime.date.today() - BDay(1)).date()\n", - "spread_date = position_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", - "df=df.loc[:'2020-2-28']\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/swaption positions\n", + "curr_spread = analytics._ontr['HY'].spread\n", + "spread_shock = [100, 200]\n", + "spread_shock = [x / curr_spread for x in spread_shock]\n", "portf = build_portf(position_date, spread_date)\n", "\n", "vol_surface = {}\n", @@ -480,38 +449,21 @@ " vs = BlackSwaptionVolSurface(trade.index.index_type, trade.index.series, \n", " value_date=spread_date, interp_method = \"bivariate_linear\")\n", " except:\n", - " vs = BlackSwaptionVolSurface(trade.index.index_type, trade.index.series + 1, \n", - " value_date=spread_date, interp_method = \"bivariate_linear\")\n", + " vs = BlackSwaptionVolSurface(trade.index.index_type, trade.index.series, \n", + " value_date=spread_date- BDay(4), interp_method = \"bivariate_linear\")\n", " vol_surface[(trade.index.index_type, trade.index.series, trade.option_type)] = vs[vs.list(source='MS', option_type=trade.option_type)[-1]]\n", "\n", + "portf.reset_pv()\n", "scens = run_portfolio_scenarios(portf, date_range=[pd.Timestamp(spread_date)], params=[\"pnl\"],\n", - " spread_shock=widen,\n", - " vol_shock=[0],\n", + " spread_shock=spread_shock,\n", + " vol_shock=[0, .3],\n", " corr_shock = [0],\n", " vol_surface=vol_surface)\n", "\n", "attrib = (scens.\n", " reset_index(level=['date'], drop=True).\n", " groupby(level=0, axis=1).sum())\n", - "results = attrib.xs((widen[2], 0., 0.), level=['spread_shock', 'corr_shock', 'vol_shock']).T" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "results" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "results.to_csv('/home/serenitas/edwin/Python/reto_results.csv')" + "results = attrib.xs((spread_shock[1], 0., 0), level=['spread_shock', 'corr_shock', 'vol_shock']).T" ] }, { @@ -521,30 +473,50 @@ "outputs": [], "source": [ "################################### Run set of scenario\n", - "spread_shock = np.round(np.arange(-.2, 1, .05), 3)\n", - "scens = run_portfolio_scenarios(portf, date_range=[pd.Timestamp(spread_date)], params=['pnl', 'delta'],\n", + "curr_spread = analytics._ontr['HY'].spread\n", + "spread_shock = [-100, -25, 1, +25 , 100]\n", + "spread_shock = [x / curr_spread for x in spread_shock]\n", + "vol_shock = 0.0\n", + "corr_shock = 0\n", + "portf = build_portf(position_date, spread_date)\n", + "\n", + "vol_surface = {}\n", + "for trade in portf.swaptions:\n", + " try:\n", + " vs = BlackSwaptionVolSurface(trade.index.index_type, trade.index.series, \n", + " value_date=spread_date, interp_method = \"bivariate_linear\")\n", + " except:\n", + " vs = BlackSwaptionVolSurface(trade.index.index_type, trade.index.series, \n", + " value_date=spread_date- BDay(4), interp_method = \"bivariate_linear\")\n", + " vol_surface[(trade.index.index_type, trade.index.series, trade.option_type)] = vs[vs.list(source='MS', option_type=trade.option_type)[-1]]\n", + "\n", + "portf.reset_pv()\n", + "scens = run_portfolio_scenarios(portf, date_range=[pd.Timestamp(spread_date)], params=['pnl'],\n", " spread_shock=spread_shock,\n", - " vol_shock=[.5],\n", - " corr_shock=[0],\n", + " vol_shock=[vol_shock],\n", + " corr_shock=[corr_shock],\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", + "pnl = pnl.xs((vol_shock, corr_shock), 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', 'XOMEZ', 'XOINX']\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", + "tranches = ['HYMEZ', 'HYINX', 'HYEQY', 'IGMEZ', 'IGINX', 'IGEQY', 'IGSNR', 'IGINX', 'BSPK', 'XOMEZ', 'XOINX', 'EUMEZ']\n", + "hedges = ['HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS']\n", "\n", - "synthetic = scenarios[['options', 'tranches', 'curve_trades']]\n", + "synthetic =pd.DataFrame()\n", + "synthetic['options'] = scenarios[set(scenarios.columns).intersection(options)].sum(axis=1)\n", + "synthetic['tranches'] = scenarios[set(scenarios.columns).intersection(tranches)].sum(axis=1)\n", + "synthetic['curve_trades'] = scenarios['curve_trades']\n", "synthetic['total'] = synthetic.sum(axis = 1)\n", "nav = go.get_net_navs()\n", - "(synthetic/nav.endbooknav[-1]).plot()" + "#(synthetic/nav.endbooknav[-1])\n", + "scenarios.sum(axis=1)\n", + "scenarios.sum(axis=1).to_clipboard()" ] }, { @@ -578,7 +550,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.8.1" + "version": "3.8.5" } }, "nbformat": 4, |