{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "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",
    "from analytics.index_data import get_index_quotes\n",
    "\n",
    "plt.interactive(False)\n",
    "plt.style.use('ggplot')\n",
    "\n",
    "engine = dbengine('serenitasdb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "sql_string = '''SELECT a.*, b.trancheupfrontmid, b.indexrefspread, b.tranchedelta, b.trancherunningmid\n",
    "                FROM risk_numbers 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}})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "#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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 0,
        "height": 11,
        "hidden": false,
        "row": 64,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "otr03.plot(x='index_pnl', y='tranche_pnl', kind='scatter')\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 4,
        "height": 19,
        "hidden": false,
        "row": 64,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "model = smf.ols('tranche_pnl~0+index_pnl', data=otr03)\n",
    "results = model.fit()\n",
    "results.summary()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "def rolling_ols(df, formula, window=20):\n",
    "    r = []\n",
    "    for i in range(len(df)-window):\n",
    "        model = smf.ols(formula, data=df.iloc[i:(20+i),])\n",
    "        results = model.fit()\n",
    "        r.append(results.params)\n",
    "    r = pd.concat(r, axis=1).T\n",
    "    return r.set_index(df.index[20:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 8,
        "height": 11,
        "hidden": false,
        "row": 64,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "#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(otr03, rsuffix='_ols')[['delta_ols', 'delta', 'tranchedelta']]\n",
    "test.rename(columns={\"tranchedelta\": \"dealerdelta\"})\n",
    "test.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 8,
        "height": 11,
        "hidden": false,
        "row": 75,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "#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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 4,
        "height": 32,
        "hidden": false,
        "row": 86,
        "width": 5
       },
       "report_default": {}
      }
     }
    }
   },
   "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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 0,
        "height": 12,
        "hidden": false,
        "row": 90,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "res.plot(annualize='D')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 0,
        "height": 11,
        "hidden": false,
        "row": 102,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "import math\n",
    "annualized_vol  = res.conditional_volatility * math.sqrt(252)\n",
    "annualized_vol.plot()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "col": 0,
        "height": 9,
        "hidden": false,
        "row": 113,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "returns[returns.abs()>0.04].groupby(pd.TimeGrouper('M')).count()"
   ]
  },
  {
   "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": {}
      }
     }
    }
   },
   "outputs": [],
   "source": [
    "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": {},
   "outputs": [],
   "source": [
    "#Back test tranche pair trades  \n",
    "sql_str = \"select * from markit_tranche_quotes a left join \" \\\n",
    "        \"(select index, series, version, basketid from index_version) b \" \\\n",
    "        \"using (basketid) order by quotedate asc\"\n",
    "index_columns=['index', 'series', 'version', 'tenor', 'attach', 'detach']\n",
    "df = pd.read_sql_query(sql_str, engine, parse_dates=['quotedate'], index_col=index_columns)\n",
    "df['day_frac'] = (df.groupby(index_columns)['quotedate'].\n",
    "                transform(lambda s: s.\n",
    "                          diff().astype('timedelta64[D]') / 360))\n",
    "df['close_price'] = 1-df['upfront_mid']\n",
    "df = df.set_index('quotedate', append=True)\n",
    "df['price_return'] = df['close_price'].groupby(level=index_columns).diff()\n",
    "df['price_return'] += df.day_frac * df.tranche_spread/10000\n",
    "df = df.drop(['basketid', 'upfront_bid', 'upfront_ask', 'upfront_mid', 'index_price', 'day_frac', 'tranche_spread', 'close_price'], axis=1)\n",
    "df = df.dropna()\n",
    "#Focus on IG\n",
    "ig_tranches = df.xs(('IG', '5yr', 23), level = ['index', 'tenor', 'series'])\n",
    "ig_tranches = ig_tranches.reset_index(['version', 'detach'], drop=True)\n",
    "ig_tranches = ig_tranches.unstack(level='attach')\n",
    "ig_tranches.columns = ig_tranches.columns.droplevel()\n",
    "\n",
    "#carry strat = long 15-100 and short 7-15: 4.6 by 1, 50bps IA\n",
    "carrystrat = (4.6 * ig_tranches[15] - ig_tranches[7])/.05\n",
    "cum_return = (carrystrat+1).cumprod()\n",
    "cum_return.plot()\n",
    "#equity gamma strat = long 0-3 and short 7-15: 1 by 6, 12% IA\n",
    "equitygammastrat = (1 * ig_tranches[0] - 6 * ig_tranches[7])/.12\n",
    "cum_return = (equitygammastrat+1).cumprod()\n",
    "cum_return.plot()\n",
    "#mezz gamma strat = long 3-7 and short 7-15: 1 by 2.75, 2.5% IA\n",
    "mezzgammastrat =(1 * ig_tranches[3] - 2.75 * ig_tranches[7])/.025\n",
    "cum_return = (mezzgammastrat+1).cumprod()\n",
    "cum_return.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "r=[]\n",
    "r.append(carrystrat.mean()/carrystrat.std() * math.sqrt(252))\n",
    "r.append(equitygammastrat.mean()/equitygammastrat.std() * math.sqrt(252))\n",
    "r.append(mezzgammastrat.mean()/mezzgammastrat.std() * math.sqrt(252))\n",
    "r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "extensions": {
   "jupyter_dashboards": {
    "activeView": "grid_default",
    "version": 1,
    "views": {
     "grid_default": {
      "cellMargin": 10,
      "defaultCellHeight": 20,
      "maxColumns": 12,
      "name": "grid",
      "type": "grid"
     },
     "report_default": {
      "name": "report",
      "type": "report"
     }
    }
   }
  },
  "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": 1
}