diff options
| -rw-r--r-- | python/exploration/curve_trades.py | 26 | ||||
| -rw-r--r-- | python/exploration/portfolio_var.py | 124 | ||||
| -rw-r--r-- | python/notebooks/Risk Management.ipynb | 286 |
3 files changed, 315 insertions, 121 deletions
diff --git a/python/exploration/curve_trades.py b/python/exploration/curve_trades.py index 963aac27..a8c791a4 100644 --- a/python/exploration/curve_trades.py +++ b/python/exploration/curve_trades.py @@ -145,13 +145,6 @@ def forward_loss(index='IG'): # annual change, to take out some noise df['fwd_loss_rate'] = df.indexel.diff(2)/df.duration.diff(2) -def read_IG_curve_pos(): - trade_1 = Index.from_tradeid(886) - trade_2 = Index.from_tradeid(885) - trade_1.notional = 132_900_000 - trade_2.notional = 75_000_000 - return Portfolio([trade_1, trade_2]) - def curve_model(tenor_1='5yr', tenor_2='10yr'): #OLS model df = ratio_within_series(param='closespread') @@ -180,25 +173,10 @@ def curve_model_results(df, model): df['dr_dspread'] = np.exp(model.params[0]) * model.params[2] * df.duration1 ** model.params[1] * df.closespread ** (model.params[2] - 1) return df -def curve_var(): - portf = read_IG_curve_pos() - df = curve_model_results(curve_model()[0], curve_model()[1]) - portf.trade_date = pd.datetime.today() - pd.tseries.offsets.BDay(1) - portf.mark() - portf_orig_pv = portf.pv - portf.indices[1].spread = portf.indices[0].spread * df.ratio.quantile(.05) - stress_1 = portf_orig_pv - portf.pv - portf.indices[1].spread = portf.indices[0].spread * df.predicted[-1] - stress_2 = portf_orig_pv - portf.pv - portf.indices[1].spread = portf.indices[0].spread * df.down_2_stdev[-1] - stress_3 = portf_orig_pv - portf.pv - - return stress_1, stress_2, stress_3 - def spread_fin_crisis(index='IG'): - on_the_run = on_the_run(index) + otr = on_the_run(index) ## look at spreads - df = get_index_quotes(index, list(range(8, on_the_run + 1)), + df = get_index_quotes(index, list(range(8, otr + 1)), tenor=['3yr', '5yr', '7yr', '10yr'], years=20) spreads = df.groupby(level=['date', 'tenor']).nth(-1)['closespread'].unstack(-1) spreads_diff = spreads.diff(axis=1) diff --git a/python/exploration/portfolio_var.py b/python/exploration/portfolio_var.py index 5b0006f9..7fba2ba2 100644 --- a/python/exploration/portfolio_var.py +++ b/python/exploration/portfolio_var.py @@ -1,81 +1,97 @@ from index_data import get_index_quotes, index_returns, _serenitas_engine from analytics import Swaption, BlackSwaption, Index, VolatilitySurface, Portfolio from db import dbengine, dbconn -from pandas.tseries.offsets import BDay -import numpy as np +from pandas.tseries.offsets import BDay, BMonthEnd +from copy import deepcopy +import numpy as np import pandas as pd import math import datetime +import exploration.curve_trades as cv + dawndb = dbengine('dawndb') serenitasdb = dbengine('serenitasdb') -def get_spreads(): - ## look at returns - index, df, df1, returns = {}, {}, {}, {} - shock = [] - shock_values = [.95,.05] - - for ind in ['HY','IG']: - index[ind] = Index.from_name(ind, 28, '5yr') - df[ind] = index_returns(index=ind, series=[23, 24, 25, 26, 27, 28], tenor=['3yr', '5yr', '7yr', '10yr'], per = 22) - #df1[ind] = get_index_quotes(index=ind, series=[23, 24, 25, 26, 27, 28], tenor=['3yr', '5yr', '7yr', '10yr']) - returns[ind] = df[ind].price_return.unstack(-1).dropna().groupby(level='date').nth(-1) - for percent in shock_values: #.95 = ~2 st dev tightening, .05 = widening - shock.append([ind, percent, returns[ind].quantile(percent)['5yr']]) - - shock = pd.DataFrame.from_records(shock, columns=['index', 'percent', 'shock'], index=['index', 'percent']) - - index['IG'].spread = 62 - index['HY'].price = 107.5 - -def get_pos(): - #get position by strategy folder - trade_date = datetime.date.today() - BDay(1) - df = pd.read_sql_query("SELECT security_id, notional * case when protection = 'Buyer' then -1 else 1 end as notional, " \ - "maturity, folder FROM cds where trade_date < %s and termination_amount is NULL", - dawndb, params = (trade_date,), parse_dates=['maturity']) - df = df.groupby(['security_id','maturity','folder']).agg(sum) - df = df[df.notional!=0].reset_index() - - df1 = pd.read_sql_query("SELECT redindexcode as security_id, index, series, indexfactor from index_version where lastdate = 'infinity'", serenitasdb) - df2 = pd.read_sql_query("SELECT * from index_maturity", serenitasdb, parse_dates=['maturity']) - df3 = pd.read_sql_query("SELECT index, series, tenor, duration " \ - " FROM index_quotes where date = %s", - dawndb, params = (trade_date,)) - - df = df.merge(df1, on='security_id') - df = df.merge(df2, how='inner', on=['index','maturity', 'series']) - df = df.merge(df3, on=['index','series','tenor']) - - df['ontr_notional'] = df.apply(lambda df: df.notional * df.indexfactor/100 * df.duration/(index[df['index']].DV01/index[df['index']].notional*10000), axis = 1) - for percent in shock_values: - df[percent] = df.apply(lambda df: df['ontr_notional'] * shock.loc[df['index'],percent], axis = 1) - - df1 = df.groupby(['folder']).agg(sum)[[.95,.05]] - df1 = df1.min(axis = 1) - def on_the_run(index): r = _serenitas_engine.execute("SELECT max(series) FROM index_version WHERE index=%s", (index,)) series, = r.fetchone() return series -def rel_spread_diff(index='HY', rolling=10): +def rel_spread_diff(report_date = datetime.date.today(), index='HY', rolling=10): otr = on_the_run(index) ## look at spreads df = get_index_quotes(index, list(range(otr - rolling, otr + 1)), tenor=['3yr', '5yr', '7yr', '10yr'], years=5) - duration = df.duration.xs(('5yr',28), level = ['tenor','series'])[-1] + duration = df.duration.xs((report_date,'5yr', otr), level = ['date','tenor', 'series'])[-1] - df = df.xs('5yr', level = 'tenor')['closespread'].groupby(['date','series']).last() - df = df.loc['2013-01-15':] + df = df.xs('5yr', level='tenor')['closespread'].groupby(['date', 'series']).last() + df = df.loc['2013-01-15':report_date] curr_spread = df.iloc[-1] - df = df.unstack(-1).pct_change(freq = '22B').stack() + df = df.unstack(-1).pct_change(freq='22B').stack() return df.groupby('date').last(), curr_spread, duration -for x, y in stress.iteritems(): - portf.ref = +def get_pos(report_date): + #report_date = datetime.date(2017,10,31) + strats = ["HEDGE_CSO", "HEDGE_MBS", "HEDGE_CLO", "HEDGE_MAC", + "SER_IGINX", "SER_HYINX", "SER_IGCURVE", "SER_ITRXCURVE", + "IGOPTDEL", "HYOPTDEL"] + r = {} + for st in strats: + r[st] = pd.read_sql_query("SELECT * from list_cds_marks(%s, %s)", + dawndb, params=(report_date, st)) + return pd.concat(r, names=['strategy']).reset_index() + +def cleared_cds_margins(report_date=datetime.date.today()): + + df = get_pos(report_date) + + #Cap Allocation for Deltas + percentile = .95 #monthly 90%tile case... + shocks, widen, tighten, onTR_dur, onTR_spread = {}, {}, {}, {}, {} + for ind in ['IG', 'HY', 'EU']: + shocks[ind], onTR_spread[ind], onTR_dur[ind] = rel_spread_diff(report_date, index=ind) + widen[ind] = shocks[ind].quantile(percentile) + tighten[ind] = shocks[ind].quantile(1-percentile) + + df['onTR_notional'] = df.apply(lambda df: + df.notional * df.factor * df.duration / onTR_dur[df.p_index], axis=1) + df['widen'] = df.apply(lambda df: + df.onTR_notional * onTR_spread[df.p_index] * onTR_dur[df.p_index] * widen[df.p_index]/10000, axis=1) + df['tighten'] = df.apply(lambda df: + df.onTR_notional * onTR_spread[df.p_index] * onTR_dur[df.p_index] * tighten[df.p_index]/10000, axis=1) + delta_alloc = df.groupby('strategy').sum() + delta_alloc['total'] = delta_alloc.apply(lambda df: max(abs(df.widen), abs(df.tighten)), axis=1) + return delta_alloc + +def index_curve_margins(report_date=datetime.date.today()): + + #Curve - whole curve flattening and steepening + df = get_pos(report_date) + diff = cv.curve_spread_diff() + diff = diff.pct_change(freq='22B').dropna() * diff.iloc[-1] + + ctrades = df[df.strategy.isin(['SER_IGCURVE','SER_ITRXCURVE'])] + trades = [] + for ind, t in ctrades.iterrows(): + trades.append(Index.from_name(t.p_index, t.p_series, t.tenor, report_date, t.notional * (-1 if t.notional < 0 else 1))) + trades[-1].spread = t.closespread + trades[-1].direction = 'Seller' if t.notional > 0 else 'Buyer' + portf = Portfolio(trades) + + #uses on-the-run to apply to any-run but let's start there + pv_diff = [] + for stress in [diff.quantile(.1), diff.quantile(.9)]: + portf1 = deepcopy(portf) + for trade in portf1.indices: + tenor = trade.name.split()[4] + if tenor == '3Y': + trade.spread -= stress.loc['3-5'] + elif tenor == '10Y': + trade.spread += stress.loc['5-10'] + pv_diff.append(portf1.pv - portf.pv) + return -min(pv_diff) diff --git a/python/notebooks/Risk Management.ipynb b/python/notebooks/Risk Management.ipynb index a650b1c3..fe0457f3 100644 --- a/python/notebooks/Risk Management.ipynb +++ b/python/notebooks/Risk Management.ipynb @@ -2,8 +2,10 @@ "cells": [ { "cell_type": "code", - "execution_count": null, - "metadata": {}, + "execution_count": 1, + "metadata": { + "collapsed": true + }, "outputs": [], "source": [ "import portfolio_var as port\n", @@ -11,6 +13,7 @@ "from analytics.scenarios import run_swaption_scenarios, run_index_scenarios, run_portfolio_scenarios\n", "import datetime\n", "import pandas as pd\n", + "from pandas.tseries.offsets import BDay, BMonthEnd\n", "\n", "#import exploration.swaption_calendar_spread as spread\n", "import exploration.swaption_calendar_spread as spread" @@ -18,79 +21,276 @@ }, { "cell_type": "code", - "execution_count": null, - "metadata": {}, + "execution_count": 2, + "metadata": { + "collapsed": true + }, "outputs": [], "source": [ - "df, spread, dur = port.rel_spread_diff()" + "#Oct ME Bond HY Equiv\n", + "report_date = (datetime.date.today() + BMonthEnd(-1)).date()\n", + "bond_HY_equiv = -.12088\n", + "percentile = .95" ] }, { "cell_type": "code", - "execution_count": null, - "metadata": {}, + "execution_count": 3, + "metadata": { + "collapsed": true + }, "outputs": [], "source": [ "#The 95%tile \n", - "stress = pd.DataFrame(index = ['widen', 'tighten'], columns=['pts'])\n", - "stress.loc['widen'] = df.quantile(.975) \n", - "stress.loc['tighten'] = df.quantile(.025)\n", - "stress = -stress * spread * dur/100" + "df, spread, dur = port.rel_spread_diff(report_date)\n", + "stress = pd.DataFrame()\n", + "stress.at[('2SD_widen', 'spread')] = df.quantile(.975) \n", + "stress.at[('2SD_tighten', 'spread')] = df.quantile(.025) \n", + "stress.at[('worst_widen', 'spread')] = df.max()\n", + "stress['pts'] = -stress * spread * dur/100\n", + "stress['nav_impact'] = bond_HY_equiv * stress['pts']" ] }, { "cell_type": "code", - "execution_count": null, + "execution_count": 6, "metadata": {}, - "outputs": [], + "outputs": [ + { + "data": { + "text/html": [ + "<div>\n", + "<style>\n", + " .dataframe thead tr:only-child th {\n", + " text-align: right;\n", + " }\n", + "\n", + " .dataframe thead th {\n", + " text-align: left;\n", + " }\n", + "\n", + " .dataframe tbody tr th {\n", + " vertical-align: top;\n", + " }\n", + "</style>\n", + "<table border=\"1\" class=\"dataframe\">\n", + " <thead>\n", + " <tr style=\"text-align: right;\">\n", + " <th></th>\n", + " <th>spread</th>\n", + " <th>pts</th>\n", + " <th>nav_impact</th>\n", + " </tr>\n", + " </thead>\n", + " <tbody>\n", + " <tr>\n", + " <th>2SD_widen</th>\n", + " <td>0.157884</td>\n", + " <td>-2.208036</td>\n", + " <td>0.266907</td>\n", + " </tr>\n", + " <tr>\n", + " <th>2SD_tighten</th>\n", + " <td>-0.163480</td>\n", + " <td>2.286301</td>\n", + " <td>-0.276368</td>\n", + " </tr>\n", + " <tr>\n", + " <th>worst_widen</th>\n", + " <td>0.359386</td>\n", + " <td>-5.026082</td>\n", + " <td>0.607553</td>\n", + " </tr>\n", + " </tbody>\n", + "</table>\n", + "</div>" + ], + "text/plain": [ + " spread pts nav_impact\n", + "2SD_widen 0.157884 -2.208036 0.266907\n", + "2SD_tighten -0.163480 2.286301 -0.276368\n", + "worst_widen 0.359386 -5.026082 0.607553" + ] + }, + "execution_count": 6, + "metadata": {}, + "output_type": "execute_result" + } + ], "source": [ - "#August ME Bond HY Equiv\n", - "bond_HY_equiv = .1652\n", - "stress['nav_impact'] = bond_HY_equiv * stress" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": {}, - "outputs": [], - "source": [ - "#Swaptions\n", - "#Aug 2018: Buy Sept HY payer spread\n", - "option_delta = Index.from_tradeid(891)\n", - "option1 = BlackSwaption.from_tradeid(10, option_delta)\n", - "option2 = BlackSwaption.from_tradeid(11, option_delta)\n", - "portf = Portfolio([option1, option2, option_delta])\n", - "portf.trade_date = datetime.date(2017, 8, 31)\n", - "portf.mark()\n", - "orig_pv = portf.pv\n", - "orig_ref = portf.ref" + "stress" ] }, { "cell_type": "code", - "execution_count": null, + "execution_count": 8, "metadata": {}, - "outputs": [], + "outputs": [ + { + "data": { + "text/html": [ + "<div>\n", + "<style>\n", + " .dataframe thead tr:only-child th {\n", + " text-align: right;\n", + " }\n", + "\n", + " .dataframe thead th {\n", + " text-align: left;\n", + " }\n", + "\n", + " .dataframe tbody tr th {\n", + " vertical-align: top;\n", + " }\n", + "</style>\n", + "<table border=\"1\" class=\"dataframe\">\n", + " <thead>\n", + " <tr style=\"text-align: right;\">\n", + " <th></th>\n", + " <th>level_1</th>\n", + " <th>notional</th>\n", + " <th>factor</th>\n", + " <th>coupon</th>\n", + " <th>duration</th>\n", + " <th>theta</th>\n", + " <th>price</th>\n", + " <th>closespread</th>\n", + " <th>clean_nav</th>\n", + " <th>accrued</th>\n", + " <th>onTR_notional</th>\n", + " <th>widen</th>\n", + " <th>tighten</th>\n", + " <th>total</th>\n", + " </tr>\n", + " <tr>\n", + " <th>strategy</th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " <th></th>\n", + " </tr>\n", + " </thead>\n", + " <tbody>\n", + " <tr>\n", + " <th>HEDGE_MBS</th>\n", + " <td>3</td>\n", + " <td>-30500000.0</td>\n", + " <td>2.92</td>\n", + " <td>0.15</td>\n", + " <td>9.579586</td>\n", + " <td>0.091530</td>\n", + " <td>323.911577</td>\n", + " <td>728.775311</td>\n", + " <td>-2.454496e+06</td>\n", + " <td>-173891.666667</td>\n", + " <td>-2.271120e+07</td>\n", + " <td>-392951.746260</td>\n", + " <td>439165.979817</td>\n", + " <td>439165.979817</td>\n", + " </tr>\n", + " <tr>\n", + " <th>SER_IGCURVE</th>\n", + " <td>1</td>\n", + " <td>-11000000.0</td>\n", + " <td>2.00</td>\n", + " <td>0.02</td>\n", + " <td>4.202955</td>\n", + " <td>0.005495</td>\n", + " <td>203.166063</td>\n", + " <td>38.112262</td>\n", + " <td>-2.220374e+04</td>\n", + " <td>-12833.333333</td>\n", + " <td>1.006705e+06</td>\n", + " <td>3787.727425</td>\n", + " <td>-3681.218467</td>\n", + " <td>3787.727425</td>\n", + " </tr>\n", + " <tr>\n", + " <th>SER_ITRXCURVE</th>\n", + " <td>1</td>\n", + " <td>39300000.0</td>\n", + " <td>2.00</td>\n", + " <td>0.02</td>\n", + " <td>14.638235</td>\n", + " <td>0.021951</td>\n", + " <td>203.615045</td>\n", + " <td>138.590158</td>\n", + " <td>2.027082e+06</td>\n", + " <td>53396.910000</td>\n", + " <td>-3.481913e+06</td>\n", + " <td>-15658.882722</td>\n", + " <td>16533.674519</td>\n", + " <td>16533.674519</td>\n", + " </tr>\n", + " </tbody>\n", + "</table>\n", + "</div>" + ], + "text/plain": [ + " level_1 notional factor coupon duration theta \\\n", + "strategy \n", + "HEDGE_MBS 3 -30500000.0 2.92 0.15 9.579586 0.091530 \n", + "SER_IGCURVE 1 -11000000.0 2.00 0.02 4.202955 0.005495 \n", + "SER_ITRXCURVE 1 39300000.0 2.00 0.02 14.638235 0.021951 \n", + "\n", + " price closespread clean_nav accrued \\\n", + "strategy \n", + "HEDGE_MBS 323.911577 728.775311 -2.454496e+06 -173891.666667 \n", + "SER_IGCURVE 203.166063 38.112262 -2.220374e+04 -12833.333333 \n", + "SER_ITRXCURVE 203.615045 138.590158 2.027082e+06 53396.910000 \n", + "\n", + " onTR_notional widen tighten total \n", + "strategy \n", + "HEDGE_MBS -2.271120e+07 -392951.746260 439165.979817 439165.979817 \n", + "SER_IGCURVE 1.006705e+06 3787.727425 -3681.218467 3787.727425 \n", + "SER_ITRXCURVE -3.481913e+06 -15658.882722 16533.674519 16533.674519 " + ] + }, + "execution_count": 8, + "metadata": {}, + "output_type": "execute_result" + } + ], "source": [ - "for x, y in stress.pts.iteritems():\n", - " portf.ref = orig_ref + y\n", - " stress[x] = portf.pv - orig_pv" + "port.cleared_cds_margins(report_date, percentile)" ] }, { "cell_type": "code", - "execution_count": null, + "execution_count": 9, "metadata": {}, - "outputs": [], + "outputs": [ + { + "data": { + "text/plain": [ + "120816.55576340854" + ] + }, + "execution_count": 9, + "metadata": {}, + "output_type": "execute_result" + } + ], "source": [ - "stress" + "port.index_curve_margins(report_date)" ] }, { "cell_type": "code", "execution_count": null, - "metadata": {}, + "metadata": { + "collapsed": true + }, "outputs": [], "source": [] } |