diff options
Diffstat (limited to 'python/notebooks/Reto Report.ipynb')
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 216 |
1 files changed, 216 insertions, 0 deletions
diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb new file mode 100644 index 00000000..c91cd918 --- /dev/null +++ b/python/notebooks/Reto Report.ipynb @@ -0,0 +1,216 @@ +{ + "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", + "import exploration.VaR as var\n", + "\n", + "from analytics.curve_trades import curve_pos, on_the_run\n", + "from analytics.index_data import get_index_quotes\n", + "from analytics.scenarios import run_portfolio_scenarios\n", + "from analytics import Swaption, BlackSwaption, CreditIndex, BlackSwaptionVolSurface, Portfolio, ProbSurface, DualCorrTranche\n", + "from db import dbconn, dbengine\n", + "\n", + "conn = dbconn('dawndb')\n", + "dawndb = dbengine('dawndb')\n", + "serenitasdb = dbengine('serenitasdb')" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#PNL Allocation\n", + "date = datetime.date.today() - off.BDay(1)\n", + "report_date = date - 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()\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 = '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": [ + "#Calculate amount of stress for reports\n", + "df = get_index_quotes('HY', list(range(on_the_run('HY') - 10, on_the_run('HY') + 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": [ + "#Current tranche and swaptions positions\n", + "t_sql_string = (\"SELECT id, sum(notional * case when protection='Buyer' then -1 else 1 end) \"\n", + " \"OVER (partition by security_id, attach) AS ntl_agg \"\n", + " \"FROM cds WHERE swap_type='CD_INDEX_TRANCHE' AND termination_cp IS NULL \"\n", + " \"AND trade_date <= %s\")\n", + "swaption_sql_string = (\"select id, security_desc from swaptions where date(expiration_date) \"\n", + " \"> %s and swap_type = 'CD_INDEX_OPTION' \"\n", + " \"AND trade_date <= %s AND termination_date iS NULL\")\n", + "index_sql_string = (\"SELECT id, sum(notional * case when protection='Buyer' then -1 else 1 end) \"\n", + " \"OVER (partition by security_id, attach) AS ntl_agg \"\n", + " \"FROM cds WHERE swap_type='CD_INDEX' AND termination_cp IS null \"\n", + " \"AND folder = 'IGOPTDEL' OR folder = 'HYOPTDEL' \"\n", + " \"AND trade_date <= %s\")\n", + "with conn.cursor() as c:\n", + " #Get Tranche Trade Ids\n", + " c.execute(t_sql_string, (date,))\n", + " t_trade_ids = [dealid for dealid, ntl in c if ntl != 0]\n", + " #Get Swaption Trade Ids\n", + " c.execute(swaption_sql_string, (date, date))\n", + " swaption_trades = c.fetchall()\n", + " #Get Index/deltas Trade Ids\n", + " c.execute(index_sql_string, (date,))\n", + " index_trade_ids = [dealid for dealid, ntl in c if ntl != 0]\n", + " \n", + "portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for dealid in t_trade_ids],\n", + " t_trade_ids)\n", + "for row in swaption_trades:\n", + " option_delta = CreditIndex(row[1].split()[1], row[1].split()[3][1:], '5yr', date)\n", + " option_delta.mark()\n", + " portf.add_trade(BlackSwaption.from_tradeid(row[0], option_delta), 'opt_' + str(row[0]))\n", + "for index_id in index_trade_ids:\n", + " portf.add_trade(CreditIndex.from_tradeid(index_id), 'index_' + str(index_id))\n", + " \n", + "#Update manually - positive notional = long risk\n", + "non_trancheSwap_risk_notional = 49119912 \n", + "\n", + "portf.add_trade(CreditIndex('HY', on_the_run('HY'), '5yr', value_date = date, notional = -non_trancheSwap_risk_notional), 'port')\n", + " \n", + "portf.value_date = date\n", + "portf.mark(interp_method=\"bivariate_linear\")\n", + "portf.reset_pv()\n", + " \n", + "vs = BlackSwaptionVolSurface(portf.swaptions[0].index.index_type, \n", + " portf.swaptions[0].index.series, \n", + " value_date=date, \n", + " interp_method = \"bivariate_linear\")\n", + "vol_surface = vs[vs.list(option_type='payer')[-1]]\n", + "vol_shock = [0]\n", + "corr_shock = [0]\n", + "spread_shock = widen + tighten\n", + "date_range = [pd.Timestamp(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", + "scens.sum(axis=1)" + ] + }, + { + "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.7.1" + } + }, + "nbformat": 4, + "nbformat_minor": 2 +} |