{
 "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 db import dbconn, dbengine\n",
    "\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",
    "dawndb = dbengine('dawndb')\n",
    "serenitasdb = dbengine('serenitasdb')"
   ]
  },
  {
   "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(1)).date()\n",
    "shock_date = (datetime.date.today() - pd.tseries.offsets.BDay(1)).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, 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, (position_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, (position_date, position_date))\n",
    "    swaption_trades = c.fetchall()\n",
    "    #Get Index/deltas Trade Ids\n",
    "    c.execute(index_sql_string, (position_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",
    "                  ['trn_'+ str(a) for a in t_trade_ids])\n",
    "for row in swaption_trades:\n",
    "    option_delta = CreditIndex(row[1].split()[1], row[1].split()[3][1:], '5yr', position_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",
    "#get bond risks:\n",
    "rmbs_pos = go.rmbs_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\n",
    "portf.add_trade(CreditIndex('HY', on_the_run('HY'), '5yr', value_date = shock_date, \n",
    "                            notional = rmbs_pos['hy_equiv'].sum()), '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.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}