diff options
| -rw-r--r-- | python/notebooks/tranches numbers.ipynb | 380 |
1 files changed, 90 insertions, 290 deletions
diff --git a/python/notebooks/tranches numbers.ipynb b/python/notebooks/tranches numbers.ipynb index cb45f6e2..fc936b7a 100644 --- a/python/notebooks/tranches numbers.ipynb +++ b/python/notebooks/tranches numbers.ipynb @@ -19,127 +19,20 @@ "outputs": [], "source": [ "import pandas as pd\n", + "import statsmodels.api as sm\n", + "import statsmodels.formula.api as smf\n", + "import numpy as np\n", + "\n", + "from db import dbengine\n", "from ipywidgets import interact\n", "from collections import OrderedDict\n", "from matplotlib import pyplot as plt\n", - "plt.interactive(False)\n", - "plt.style.use('ggplot')" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "hidden": true - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "from db import dbengine\n", - "runs = (pd.read_sql_query(\"SELECT DISTINCT index, series, tenor from risk_numbers ORDER BY index, series\",\n", - " dbengine('serenitasdb')).\n", - " itertuples(index=False, name='run'))\n", - "runs = OrderedDict([(\"%s %s %s\" % (r.index, r.series, r.tenor), (r.index, r.series, r.tenor)) for r in runs])" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "hidden": true - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "df = pd.read_sql(\"SELECT * FROM risk_numbers\", SQL_CON, index_col=['date', 'index', 'series', 'tenor'])" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "col": 0, - "height": 57, - "hidden": false, - "row": 0, - "width": 10 - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "risk_numbers = ['skew', 'Dealer Deltas', 'Model Deltas', 'Forward Deltas', 'gammas', 'durations', 'thetas']\n", + "from analytics.index_data import get_index_quotes\n", "\n", - "def corrplot(index, what):\n", - " plt.close('all')\n", - " selection = df.xs(index, level=[1,2,3], drop_level=True)\n", - " cols = selection.attach.iloc[0]\n", - " cols = [\"{}-{}\".format(a,d) for a, d in zip(cols[:-2], cols[1:-1])]\n", - " selection = selection[what].apply(pd.Series)\n", - " selection.drop(selection.columns[-1], axis=1, inplace=True)\n", - " selection.columns = cols\n", - " selection.plot()\n", - " plt.show()\n", - " \n", - "interact(corrplot, index=runs, what=risk_numbers)" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "hidden": true - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "def get_tranche_quotes(index, series, tenor):\n", - " df = pd.read_sql(\"SELECT * FROM tranche_quotes WHERE index=%s and series=%s and tenor =%s\",\n", - " SQL_CON, params=(index, series, tenor), index_col=['quotedate'],\n", - " parse_dates=['quotedate'])\n", - " df.sort_index(inplace=True)\n", - " return df\n", + "plt.interactive(False)\n", + "plt.style.use('ggplot')\n", "\n", - "def get_index_quotes(index, series, tenor):\n", - " df = pd.read_sql(\"SELECT * FROM index_quotes WHERE index=%s and series=%s and tenor=%s\",\n", - " SQL_CON, params = (index, series, tenor), index_col=['date'], parse_dates=['date'])\n", - " df.sort_index(inplace=True)\n", - " return df\n" + "engine = dbengine('serenitasdb')" ] }, { @@ -160,9 +53,17 @@ }, "outputs": [], "source": [ - "ig25_tranches = get_tranche_quotes('IG', 25, '5yr')\n", - "ig25 = get_index_quotes('IG', 25, '5yr')\n", - "ig25_tranches = ig25_tranches[['attach', 'trancheupfrontmid','indexrefspread','tranchedelta']]" + "sql_string = '''SELECT a.*, b.trancheupfrontmid, b.indexrefspread, b.tranchedelta, b.trancherunningmid\n", + " FROM risk_numbers_new a \n", + " join tranche_quotes b on a.tranche_id = b.id\n", + " where a.index <> 'EU'\n", + " '''\n", + "tranche_quotes = pd.read_sql_query(sql_string, engine,\n", + " index_col=['date', 'index', 'series', 'tenor', 'attach'], \n", + " parse_dates={'date': {'utc': True}})\n", + "index_quotes = df = pd.read_sql(\"SELECT * FROM index_quotes\", engine,\n", + " index_col=['date', 'index', 'series', 'tenor'], \n", + " parse_dates={'date': {'utc': True}})" ] }, { @@ -183,39 +84,30 @@ }, "outputs": [], "source": [ - "ig25_tranches = ig25_tranches.groupby([pd.TimeGrouper('D', level=0), 'attach']).last().dropna()\n", - "ig25_tranches = ig25_tranches.reset_index('attach', drop=False)\n", - "ig25 = ig25[['closeprice','duration','closespread']]\n", - "ig25_data = ig25_tranches.join(ig25)" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "col": 0, - "height": 7, - "hidden": false, - "row": 57, - "width": 9 - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "ig25_data = ig25_data.assign(tranche_adjusted =\n", - " lambda x: x.trancheupfrontmid - \\\n", - " x.tranchedelta * (x.indexrefspread-x.closespread) * x.duration/100)\n", - "ig25_data = ig25_data.set_index('attach', append=True)\n", - "ig25_data.head()" + "tranche_quotes = tranche_quotes.groupby([pd.Grouper(freq='D', level=0), \n", + " 'index', 'series', 'tenor', 'attach']).last().dropna()\n", + "tranche_quotes = tranche_quotes.reset_index('attach').join(index_quotes, rsuffix='_index')\n", + "tranche_quotes = tranche_quotes.set_index('attach', append=True)\n", + "#adjusting upfronts with ref and compute the accrued \n", + "#Use the first for that tranche, tranche <> index for older IG index, need to change\n", + "tranche_data = []\n", + "for i, g in tranche_quotes.groupby(level=['index', 'series', 'tenor', 'attach']):\n", + " accrued = g.index.levels[0].to_series().diff().astype('timedelta64[D]') * g.trancherunningmid[0]/360/100\n", + " accrued.name = 'accrued'\n", + " g = g.join(accrued)\n", + " g['index_pnl'] = g['closeprice'].diff() + g.accrued\n", + " if i[0] == 'HY':\n", + " g = g.assign(tranche_adjusted = lambda x: x.trancheupfrontmid - \n", + " x.tranchedelta * (x.index_price *100-x.closeprice))\n", + " g['tranche_risk_price'] = g['tranche_adjusted']\n", + " else:\n", + " g = g.assign(tranche_adjusted = lambda x: x.trancheupfrontmid - \n", + " x.tranchedelta * (x.indexrefspread-x.closespread) * x.duration/100)\n", + " g['tranche_risk_price'] = 100 - g['tranche_adjusted']\n", + " g['tranche_pnl'] = g['tranche_risk_price'].diff() + g.accrued\n", + " g['tranche_ret'] = g.tranche_pnl/g.tranche_risk_price\n", + " tranche_data.append(g)\n", + "tranche_data = pd.concat(tranche_data)" ] }, { @@ -236,35 +128,16 @@ }, "outputs": [], "source": [ - "#compute the accrued\n", - "ig25_data['accrued'] = ig25_data.groupby(level='attach')['tranche_adjusted'].transform(lambda x: x.index.levels[0].to_series().diff().astype('timedelta64[D]')*1/360)" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "hidden": true - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "#compute the daily pnl\n", - "ig25_data['index_pnl'] = (ig25_data.groupby(level='attach')['closeprice'].\n", - " apply(lambda x:x.diff()))\n", - "ig25_data['tranche_pnl'] = (ig25_data.groupby(level='attach')['tranche_adjusted'].\n", - " apply(lambda x:-x.diff()))\n", - "for col in ['index_pnl', 'tranche_pnl']:\n", - " ig25_data[col] += ig25_data.accrued" + "#Now just look at the return of the on-the-run\n", + "otr03 = tranche_data.xs(['IG', '5yr', 0], level=['index', 'tenor', 'attach']).groupby('date').last()\n", + "otr715 = tranche_data.xs(['IG', '5yr', 7], level=['index', 'tenor', 'attach']).groupby('date').last()\n", + "not03 = 1e7\n", + "im = 2e6\n", + "strat_ret = (otr03['tranche_pnl'] * not03 - otr715['tranche_pnl'] * not03* otr03.delta/otr715.delta)/100\n", + "returns_by_year = strat_ret.groupby(pd.Grouper(freq='A')).sum()\n", + "strat_cum_ret = (strat_ret/im+1).cumprod()\n", + "returns_by_year.plot(kind='bar')\n", + "plt.show()" ] }, { @@ -289,7 +162,7 @@ }, "outputs": [], "source": [ - "ig25_data.xs(3, level='attach').plot(x='index_pnl', y='tranche_pnl', kind='scatter')\n", + "otr03.plot(x='index_pnl', y='tranche_pnl', kind='scatter')\n", "plt.show()" ] }, @@ -315,10 +188,7 @@ }, "outputs": [], "source": [ - "import statsmodels.api as sm\n", - "import statsmodels.formula.api as smf\n", - "import numpy as np\n", - "model = smf.ols('tranche_pnl~0+index_pnl', data=ig25_data.xs(15, level='attach'))\n", + "model = smf.ols('tranche_pnl~0+index_pnl', data=otr03)\n", "results = model.fit()\n", "results.summary()" ] @@ -373,9 +243,11 @@ }, "outputs": [], "source": [ - "rols = rolling_ols(ig25_data.xs(15, level='attach'), 'tranche_pnl~0+index_pnl+np.square(index_pnl)')\n", + "#plot actual/model/quoted deltas\n", + "rols = rolling_ols(otr03, 'tranche_pnl~0+index_pnl+np.square(index_pnl)')\n", "rols.columns = ['delta', 'gamma']\n", - "test = rols.join(ig25_data.xs(15, level='attach'))[['delta', 'tranchedelta']]\n", + "test = rols.join(otr03, rsuffix='_ols')[['delta_ols', 'delta', 'tranchedelta']]\n", + "test.rename(columns={\"tranchedelta\": \"dealerdelta\"})\n", "test.plot()" ] }, @@ -388,31 +260,6 @@ "version": 1, "views": { "grid_default": { - "col": 0, - "height": 4, - "hidden": false, - "row": 75, - "width": 4 - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "ig25_data.closespread.apply(np.log).diff().std()*np.sqrt(250)" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { "col": 8, "height": 11, "hidden": false, @@ -426,34 +273,10 @@ }, "outputs": [], "source": [ - "ig25 = get_index_quotes('IG', 25, '5yr')\n", - "ig25['closespread'].rolling(20).apply(lambda x: np.std(np.diff(np.log(x)))*np.sqrt(250)).plot()" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "col": 0, - "height": 11, - "hidden": false, - "row": 79, - "width": 4 - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "ig25 = get_index_quotes('IG', 25, '5yr')\n", - "returns = ig25.closespread.pct_change().dropna()" + "#index spread vol\n", + "index_quotes = get_index_quotes()\n", + "index_quotes = index_quotes.xs(('IG','5yr'), level=['index', 'tenor']).groupby(['date']).last()\n", + "index_quotes['close_spread'].rolling(20).apply(lambda x: np.std(np.diff(np.log(x)))*np.sqrt(250)).plot()" ] }, { @@ -479,6 +302,7 @@ "outputs": [], "source": [ "from arch import arch_model\n", + "returns = index_quotes.close_spread.pct_change().dropna()\n", "am = arch_model(returns, mean='ARX', lags=1, vol='GARCH', o=1)\n", "res = am.fit(update_freq=5)\n", "res.summary()" @@ -571,8 +395,8 @@ "version": 1, "views": { "grid_default": { - "col": 4, - "height": 15, + "col": 8, + "height": 7, "hidden": false, "row": 118, "width": 4 @@ -584,58 +408,34 @@ }, "outputs": [], "source": [ - "returns['2016-03']" - ] - }, - { - "cell_type": "code", - "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "col": 0, - "height": 7, - "hidden": false, - "row": 133, - "width": 10 - }, - "report_default": {} - } - } - } - }, - "outputs": [], - "source": [ - "ig25.head()" + "runs = (pd.read_sql_query(\"SELECT DISTINCT index, series, tenor from risk_numbers ORDER BY index, series\",\n", + " engine).\n", + " itertuples(index=False, name='run'))\n", + "runs = OrderedDict([(\"%s %s %s\" % (r.index, r.series, r.tenor), (r.index, r.series, r.tenor)) for r in runs])\n", + "df = pd.read_sql(\"SELECT * FROM risk_numbers\", engine, index_col=['date', 'index', 'series', 'tenor'])\n", + "\n", + "risk_numbers = ['skew', 'Dealer Deltas', 'Model Deltas', 'Forward Deltas', 'gammas', 'durations', 'thetas']\n", + "\n", + "def corrplot(index, what):\n", + " plt.close('all')\n", + " selection = df.xs(index, level=[1,2,3], drop_level=True)\n", + " cols = selection.attach.iloc[0]\n", + " cols = [\"{}-{}\".format(a,d) for a, d in zip(cols[:-2], cols[1:-1])]\n", + " selection = selection[what].apply(pd.Series)\n", + " selection.drop(selection.columns[-1], axis=1, inplace=True)\n", + " selection.columns = cols\n", + " selection.plot()\n", + " plt.show()\n", + " \n", + "interact(corrplot, index=runs, what=risk_numbers)" ] }, { "cell_type": "code", "execution_count": null, - "metadata": { - "extensions": { - "jupyter_dashboards": { - "version": 1, - "views": { - "grid_default": { - "col": 8, - "height": 7, - "hidden": false, - "row": 118, - "width": 4 - }, - "report_default": {} - } - } - } - }, + "metadata": {}, "outputs": [], - "source": [ - "ig25.index" - ] + "source": [] } ], "metadata": { @@ -673,7 +473,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.6.1" + "version": "3.6.6" } }, "nbformat": 4, |