{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "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 utils.db import dbconn, dbengine\n",
    "\n",
    "import analytics\n",
    "import datetime\n",
    "import exploration.VaR as var\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import globeop_reports as go\n",
    "\n",
    "conn = dbconn('dawndb')\n",
    "conn.autocommit = True\n",
    "dawndb = dbengine('dawndb')\n",
    "serenitasdb = dbengine('serenitasdb')\n",
    "analytics.init_ontr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date = (datetime.date.today() - pd.tseries.offsets.BDay(3)).date()\n",
    "report_date = (date + pd.tseries.offsets.BMonthEnd(-1)).date()\n",
    "index_type = \"IG\"\n",
    "quantile = .025"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#IG Curve VaR\n",
    "portf = curve_pos(date, index_type)\n",
    "ig_curve_var = abs(var.hist_var(portf, quantile=quantile, years=5))\n",
    "ig_curve_var"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#EU Curve VaR\n",
    "index_type = \"EU\"\n",
    "portf = curve_pos(date, index_type)\n",
    "eu_curve_var = abs(var.hist_var(portf, quantile=quantile, years=5))\n",
    "eu_curve_var"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Mortgage Hedge VaR - use IG spread relative move for VaR\n",
    "df = pd.read_sql_query(\"SELECT * from list_cds_marks_by_strat(%s) where strategy ='HEDGE_MBS'\",\n",
    "                                 dawndb, params=(date,))\n",
    "portf = Portfolio([CreditIndex(row.p_index, row.p_series, row.tenor,\n",
    "                                       report_date, -row.notional)\n",
    "                      for row in df[['p_index', 'tenor', 'p_series', 'notional']].\n",
    "                       itertuples(index=False)])\n",
    "portf.mark()\n",
    "mort_hedge_var = abs(var.hist_var(portf, index_type = \"IG\", quantile=quantile, years=3))\n",
    "mort_hedge_var"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import the IM at the FCM account: calculate the IM share of different strategies as a share of VaR\n",
    "#filename = date.strftime('%Y%m%d') + \"_OTC_MARGIN.csv\"\n",
    "#margin_df = pd.read_csv(\"/home/serenitas/Daily/SG_reports/\" + filename, index_col='System Currency')\n",
    "#mortg_hedge_im = mort_hedge_var + mort_hedge_var/(mort_hedge_var + ig_curve_var) * margin_df.loc[('USD', 'SG Settlement Margin')]\n",
    "#mortg_hedge_im"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Oct ME Bond HY Equiv\n",
    "bond_HY_equiv = -.12088\n",
    "percentile = .95"
   ]
  },
  {
   "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": [
    "position_date = (datetime.date.today() - pd.tseries.offsets.BDay(3)).date()\n",
    "shock_date = (datetime.date.today() - pd.tseries.offsets.BDay(3)).date()\n",
    "(position_date, shock_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Current tranche and swaptions positions\n",
    "t_sql_string = (\"SELECT id, folder, 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, folder 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, folder, 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 trade_date <= %s\")\n",
    "with conn.cursor() as c:\n",
    "    c.execute(t_sql_string, (position_date,))\n",
    "    t_trades = [[dealid, f\"{folder}_{dealid}\"] for dealid, folder, ntl in c if ntl != 0]\n",
    "    c.execute(swaption_sql_string, (position_date, position_date))\n",
    "    swaption_trades = c.fetchall()\n",
    "    c.execute(index_sql_string, (position_date,))\n",
    "    index_trades = [[dealid, f\"{folder}_{dealid}\"] for dealid, folder, ntl in c if ntl != 0]\n",
    "    \n",
    "portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for dealid, _ in t_trades],\n",
    "                  [trade_id for _, trade_id in t_trades])\n",
    "for trade_id, desc, strat in swaption_trades:\n",
    "    portf.add_trade(BlackSwaption.from_tradeid(trade_id), str(strat) + \"_\" + str(trade_id))\n",
    "for trade_id, name in index_trades:\n",
    "    portf.add_trade(CreditIndex.from_tradeid(trade_id), name)\n",
    "    \n",
    "#get bond risks:\n",
    "rmbs_pos = go.rmbs_pos(position_date)\n",
    "clo_pos = go.clo_pos(position_date)\n",
    "r = serenitasdb.execute(\"select duration from on_the_run where index = 'HY' and date = %s\",\n",
    "                               shock_date)\n",
    "duration, = next(r)\n",
    "rmbs_pos['hy_equiv'] = rmbs_pos['delta_yield']/duration * 100\n",
    "notional = rmbs_pos['hy_equiv'].sum() + clo_pos['hy_equiv'].sum()\n",
    "temp = CreditIndex('HY', on_the_run('HY'), '5yr', value_date = shock_date, notional = notional)\n",
    "temp.direction = 'Seller'\n",
    "portf.add_trade(temp, 'rmbs_bond')\n",
    "    \n",
    "portf.value_date = shock_date\n",
    "portf.mark(interp_method=\"bivariate_linear\")\n",
    "portf.reset_pv()\n",
    "\n",
    "vol_surface = {}\n",
    "for trade in portf.swaptions:\n",
    "    vs = BlackSwaptionVolSurface(trade.index.index_type, trade.index.series, \n",
    "                                 value_date=shock_date, interp_method = \"bivariate_linear\")\n",
    "    vol_surface[(trade.index.index_type, trade.index.series)] = vs[vs.list(option_type='payer')[-1]]\n",
    "vol_shock = [0]\n",
    "corr_shock = [0]\n",
    "spread_shock = tighten + [0] + widen\n",
    "date_range = [pd.Timestamp(shock_date)]\n",
    "\n",
    "scens = run_portfolio_scenarios(portf, date_range, params=[\"pnl\", \"hy_equiv\"],\n",
    "                                spread_shock=spread_shock,\n",
    "                                vol_shock=vol_shock,\n",
    "                                corr_shock=corr_shock,\n",
    "                                vol_surface=vol_surface)\n",
    "\n",
    "scens.xs('pnl', level=1, axis=1).sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spread_shock = np.arange(-.4, 2.2, .2)\n",
    "\n",
    "scens = run_portfolio_scenarios(portf, date_range, params=[\"pnl\", \"hy_equiv\"],\n",
    "                                spread_shock=spread_shock,\n",
    "                                vol_shock=vol_shock,\n",
    "                                corr_shock=corr_shock,\n",
    "                                vol_surface=vol_surface)\n",
    "results = {}\n",
    "for x in ['pnl', 'hy_equiv']:\n",
    "    df = scens.xs(x, level=1, axis=1)\n",
    "    for y in ['trn', 'opt', 'index']:\n",
    "        columns = [col for col in df.columns if 'trn' in col]\n",
    "        results[(x,y)] = df[columns].sum(axis=1)\n",
    "    \n",
    "hy_equiv = scens.xs('hy_equiv', level=1, axis=1).sum(axis=1)\n",
    "\n",
    "#risk_notional = [t.notional * t._index.duration for t in portf.indices]\n",
    "#portf.trades[0]._index.duration()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Calculate the margins for cleared CDS required for each strategy\n",
    "df = pd.read_sql_query(\"SELECT * from list_cds_marks_by_strat(%s)\",\n",
    "                                 dawndb, params=(date,))\n",
    "percentile = .95 #monthly 90%tile case.\n",
    "shocks, widen, tighten, onTR_dur, onTR_spread = {}, {}, {}, {}, {}\n",
    "for ind in ['IG', 'HY', 'EU']:\n",
    "    shocks[ind], onTR_spread[ind], onTR_dur[ind] = rel_spread_diff(date, index=ind)\n",
    "    widen[ind] = shocks[ind].quantile(percentile)\n",
    "    tighten[ind] = shocks[ind].quantile(1-percentile)\n",
    "\n",
    "df['onTR_notional'] = df.apply(lambda df:\n",
    "                               df.notional * df.factor * df.duration / onTR_dur[df.p_index], axis=1)\n",
    "df['widen'] = df.apply(lambda df:\n",
    "                       df.onTR_notional * onTR_spread[df.p_index] * onTR_dur[df.p_index] * widen[df.p_index]/10000, axis=1)\n",
    "df['tighten'] = df.apply(lambda df:\n",
    "                         df.onTR_notional * onTR_spread[df.p_index] * onTR_dur[df.p_index] * tighten[df.p_index]/10000, axis=1)\n",
    "delta_alloc = df.groupby('strategy').sum()\n",
    "delta_alloc['total'] = delta_alloc.apply(lambda df: max(abs(df.widen), abs(df.tighten)), 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.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}