{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# better formatting for large floats\n",
    "import pandas as pd\n",
    "pd.options.display.float_format = \"{:,.2f}\".format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from risk.swaptions import get_swaption_portfolio\n",
    "import datetime\n",
    "from utils.db import dbconn\n",
    "from analytics import init_ontr\n",
    "import analytics\n",
    "conn = dbconn('dawndb')\n",
    "conn.autocommit = True\n",
    "value_date = datetime.date.today()\n",
    "init_ontr(value_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "Portfolio 2020-01-22\n",
       "\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Product</th>\n",
       "      <th>Index</th>\n",
       "      <th>Notional</th>\n",
       "      <th>Ref</th>\n",
       "      <th>Strike</th>\n",
       "      <th>Direction</th>\n",
       "      <th>Type</th>\n",
       "      <th>Expiry</th>\n",
       "      <th>Vol</th>\n",
       "      <th>PV</th>\n",
       "      <th>Delta</th>\n",
       "      <th>Gamma</th>\n",
       "      <th>Theta</th>\n",
       "      <th>Vega</th>\n",
       "      <th>HY Equiv</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ids</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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>SWPTN91</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>45.0</td>\n",
       "      <td>Long</td>\n",
       "      <td>receiver</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>40.74%</td>\n",
       "      <td>150,061.67</td>\n",
       "      <td>-37.06%</td>\n",
       "      <td>66.00%</td>\n",
       "      <td>-3,327.79</td>\n",
       "      <td>4,576.20</td>\n",
       "      <td>-21,825,100.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN92</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>65.0</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-02-19</td>\n",
       "      <td>75.62%</td>\n",
       "      <td>-20,633.56</td>\n",
       "      <td>-6.40%</td>\n",
       "      <td>-13.42%</td>\n",
       "      <td>1,809.79</td>\n",
       "      <td>-1,399.72</td>\n",
       "      <td>-3,765,995.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN97</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>42.5</td>\n",
       "      <td>Long</td>\n",
       "      <td>receiver</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>38.45%</td>\n",
       "      <td>96,216.26</td>\n",
       "      <td>-21.68%</td>\n",
       "      <td>43.36%</td>\n",
       "      <td>-1,761.48</td>\n",
       "      <td>5,166.44</td>\n",
       "      <td>-12,768,492.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN107</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>42.5</td>\n",
       "      <td>Long</td>\n",
       "      <td>receiver</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>38.45%</td>\n",
       "      <td>96,216.26</td>\n",
       "      <td>-21.68%</td>\n",
       "      <td>43.36%</td>\n",
       "      <td>-1,761.48</td>\n",
       "      <td>5,166.44</td>\n",
       "      <td>-12,768,492.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN108</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>55.0</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>54.75%</td>\n",
       "      <td>-123,985.48</td>\n",
       "      <td>-26.66%</td>\n",
       "      <td>-32.67%</td>\n",
       "      <td>2,590.27</td>\n",
       "      <td>-5,340.77</td>\n",
       "      <td>-15,699,000.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN98</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>57.5</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>57.96%</td>\n",
       "      <td>-101,557.29</td>\n",
       "      <td>-21.92%</td>\n",
       "      <td>-27.94%</td>\n",
       "      <td>2,453.43</td>\n",
       "      <td>-4,793.54</td>\n",
       "      <td>-12,910,454.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN93</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>300,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>62.5</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-03-18</td>\n",
       "      <td>63.80%</td>\n",
       "      <td>-107,301.85</td>\n",
       "      <td>-15.41%</td>\n",
       "      <td>-20.60%</td>\n",
       "      <td>3,231.38</td>\n",
       "      <td>-5,769.27</td>\n",
       "      <td>-13,613,875.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN103</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>42.5</td>\n",
       "      <td>Long</td>\n",
       "      <td>receiver</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>39.95%</td>\n",
       "      <td>130,102.18</td>\n",
       "      <td>-22.89%</td>\n",
       "      <td>35.40%</td>\n",
       "      <td>-1,518.80</td>\n",
       "      <td>6,419.89</td>\n",
       "      <td>-13,477,124.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN101</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>100,000,000.00</td>\n",
       "      <td>45.13</td>\n",
       "      <td>42.5</td>\n",
       "      <td>Long</td>\n",
       "      <td>receiver</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>39.95%</td>\n",
       "      <td>65,051.09</td>\n",
       "      <td>-22.89%</td>\n",
       "      <td>35.40%</td>\n",
       "      <td>-759.40</td>\n",
       "      <td>3,209.94</td>\n",
       "      <td>-6,738,562.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN99</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.12</td>\n",
       "      <td>42.5</td>\n",
       "      <td>Long</td>\n",
       "      <td>receiver</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>39.96%</td>\n",
       "      <td>130,399.30</td>\n",
       "      <td>-22.93%</td>\n",
       "      <td>35.43%</td>\n",
       "      <td>-1,520.20</td>\n",
       "      <td>6,424.14</td>\n",
       "      <td>-13,500,437.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN102</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>100,000,000.00</td>\n",
       "      <td>45.12</td>\n",
       "      <td>57.5</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>55.99%</td>\n",
       "      <td>-86,043.53</td>\n",
       "      <td>-28.92%</td>\n",
       "      <td>-27.46%</td>\n",
       "      <td>1,137.31</td>\n",
       "      <td>-3,432.55</td>\n",
       "      <td>-8,516,333.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN104</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.12</td>\n",
       "      <td>57.5</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>55.99%</td>\n",
       "      <td>-172,087.07</td>\n",
       "      <td>-28.92%</td>\n",
       "      <td>-27.46%</td>\n",
       "      <td>2,274.63</td>\n",
       "      <td>-6,865.09</td>\n",
       "      <td>-17,032,666.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN100</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.12</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-04-15</td>\n",
       "      <td>58.42%</td>\n",
       "      <td>-147,765.75</td>\n",
       "      <td>-24.99%</td>\n",
       "      <td>-24.50%</td>\n",
       "      <td>2,198.93</td>\n",
       "      <td>-6,373.46</td>\n",
       "      <td>-14,714,847.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN105</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.12</td>\n",
       "      <td>42.5</td>\n",
       "      <td>Long</td>\n",
       "      <td>receiver</td>\n",
       "      <td>2020-05-20</td>\n",
       "      <td>41.20%</td>\n",
       "      <td>161,429.07</td>\n",
       "      <td>-23.06%</td>\n",
       "      <td>29.16%</td>\n",
       "      <td>-1,309.02</td>\n",
       "      <td>7,595.96</td>\n",
       "      <td>-13,577,001.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SWPTN106</th>\n",
       "      <td>Swaption</td>\n",
       "      <td>IG33 5yr</td>\n",
       "      <td>200,000,000.00</td>\n",
       "      <td>45.12</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Short</td>\n",
       "      <td>payer</td>\n",
       "      <td>2020-05-20</td>\n",
       "      <td>56.55%</td>\n",
       "      <td>-229,470.13</td>\n",
       "      <td>-31.54%</td>\n",
       "      <td>-23.91%</td>\n",
       "      <td>2,020.49</td>\n",
       "      <td>-8,542.41</td>\n",
       "      <td>-18,570,543.35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "Portfolio 2020-01-22\n",
       "\n",
       "           Product     Index       Notional   Ref Strike Direction      Type      Expiry    Vol  \\\n",
       "ids                                                                                               \n",
       "SWPTN91   Swaption  IG33 5yr 200,000,000.00 45.13   45.0      Long  receiver  2020-02-19 40.74%   \n",
       "SWPTN92   Swaption  IG33 5yr 200,000,000.00 45.13   65.0     Short     payer  2020-02-19 75.62%   \n",
       "SWPTN97   Swaption  IG33 5yr 200,000,000.00 45.13   42.5      Long  receiver  2020-03-18 38.45%   \n",
       "SWPTN107  Swaption  IG33 5yr 200,000,000.00 45.13   42.5      Long  receiver  2020-03-18 38.45%   \n",
       "SWPTN108  Swaption  IG33 5yr 200,000,000.00 45.13   55.0     Short     payer  2020-03-18 54.75%   \n",
       "SWPTN98   Swaption  IG33 5yr 200,000,000.00 45.13   57.5     Short     payer  2020-03-18 57.96%   \n",
       "SWPTN93   Swaption  IG33 5yr 300,000,000.00 45.13   62.5     Short     payer  2020-03-18 63.80%   \n",
       "SWPTN103  Swaption  IG33 5yr 200,000,000.00 45.13   42.5      Long  receiver  2020-04-15 39.95%   \n",
       "SWPTN101  Swaption  IG33 5yr 100,000,000.00 45.13   42.5      Long  receiver  2020-04-15 39.95%   \n",
       "SWPTN99   Swaption  IG33 5yr 200,000,000.00 45.12   42.5      Long  receiver  2020-04-15 39.96%   \n",
       "SWPTN102  Swaption  IG33 5yr 100,000,000.00 45.12   57.5     Short     payer  2020-04-15 55.99%   \n",
       "SWPTN104  Swaption  IG33 5yr 200,000,000.00 45.12   57.5     Short     payer  2020-04-15 55.99%   \n",
       "SWPTN100  Swaption  IG33 5yr 200,000,000.00 45.12   60.0     Short     payer  2020-04-15 58.42%   \n",
       "SWPTN105  Swaption  IG33 5yr 200,000,000.00 45.12   42.5      Long  receiver  2020-05-20 41.20%   \n",
       "SWPTN106  Swaption  IG33 5yr 200,000,000.00 45.12   60.0     Short     payer  2020-05-20 56.55%   \n",
       "\n",
       "                  PV   Delta   Gamma     Theta      Vega       HY Equiv  \n",
       "ids                                                                      \n",
       "SWPTN91   150,061.67 -37.06%  66.00% -3,327.79  4,576.20 -21,825,100.50  \n",
       "SWPTN92   -20,633.56  -6.40% -13.42%  1,809.79 -1,399.72  -3,765,995.13  \n",
       "SWPTN97    96,216.26 -21.68%  43.36% -1,761.48  5,166.44 -12,768,492.88  \n",
       "SWPTN107   96,216.26 -21.68%  43.36% -1,761.48  5,166.44 -12,768,492.88  \n",
       "SWPTN108 -123,985.48 -26.66% -32.67%  2,590.27 -5,340.77 -15,699,000.27  \n",
       "SWPTN98  -101,557.29 -21.92% -27.94%  2,453.43 -4,793.54 -12,910,454.18  \n",
       "SWPTN93  -107,301.85 -15.41% -20.60%  3,231.38 -5,769.27 -13,613,875.38  \n",
       "SWPTN103  130,102.18 -22.89%  35.40% -1,518.80  6,419.89 -13,477,124.52  \n",
       "SWPTN101   65,051.09 -22.89%  35.40%   -759.40  3,209.94  -6,738,562.26  \n",
       "SWPTN99   130,399.30 -22.93%  35.43% -1,520.20  6,424.14 -13,500,437.56  \n",
       "SWPTN102  -86,043.53 -28.92% -27.46%  1,137.31 -3,432.55  -8,516,333.11  \n",
       "SWPTN104 -172,087.07 -28.92% -27.46%  2,274.63 -6,865.09 -17,032,666.22  \n",
       "SWPTN100 -147,765.75 -24.99% -24.50%  2,198.93 -6,373.46 -14,714,847.97  \n",
       "SWPTN105  161,429.07 -23.06%  29.16% -1,309.02  7,595.96 -13,577,001.50  \n",
       "SWPTN106 -229,470.13 -31.54% -23.91%  2,020.49 -8,542.41 -18,570,543.35  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portf = get_swaption_portfolio(value_date, conn, source_list=[\"GS\"])\n",
    "portf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = portf._todf()\n",
    "positions = df.set_index(\"Index\")[[\"Delta\", \"Notional\"]].prod(axis=1).groupby(level=\"Index\").sum()\n",
    "positions.name = 'current_delta'\n",
    "gamma = df.set_index(\"Index\")[[\"Gamma\", \"Notional\"]].prod(axis=1).groupby(level=\"Index\").sum()\n",
    "gamma.name = 'gamma'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "hedges = pd.read_sql_query(\"SELECT security_desc, notional FROM list_cds_positions_by_strat(%s) \"\n",
    "                           \"WHERE folder in ('IGOPTDEL', 'HYOPTDEL')\",\n",
    "                           conn, params=(value_date,))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>current hedge</th>\n",
       "      <th>current_delta</th>\n",
       "      <th>gamma</th>\n",
       "      <th>net_delta</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>IG33 5yr</th>\n",
       "      <td>681,400,000.00</td>\n",
       "      <td>-677,507,134.07</td>\n",
       "      <td>151,731,367.46</td>\n",
       "      <td>3,892,865.93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          current hedge   current_delta          gamma    net_delta\n",
       "Index                                                              \n",
       "IG33 5yr 681,400,000.00 -677,507,134.07 151,731,367.46 3,892,865.93"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def f(s):\n",
    "    l = s.split(\" \")\n",
    "    return f\"{l[1]}{l[3][1:]} {l[4].lower()}r\"\n",
    "\n",
    "hedges[\"Index\"] = hedges[\"security_desc\"].apply(f)\n",
    "hedges = hedges.rename(columns={\"notional\": \"current hedge\"})\n",
    "hedges = hedges.set_index(\"Index\")[\"current hedge\"]\n",
    "hedges = hedges.reindex(positions.index, fill_value=0.)\n",
    "risk = pd.concat([hedges, positions, gamma], axis=1)\n",
    "risk['net_delta'] = risk[\"current hedge\"] + risk.current_delta\n",
    "risk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5758.050875686662"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portf.theta"
   ]
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}