{
 "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",
    "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",
    "\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",
    "\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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "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']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "version": 1,
      "views": {
       "grid_default": {
        "hidden": true
       },
       "report_default": {}
      }
     }
    }
   },
   "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"
   ]
  },
  {
   "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": [
    "ig25_data.xs(3, level='attach').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": [
    "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",
    "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": [
    "rols = rolling_ols(ig25_data.xs(15, level='attach'), '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.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "extensions": {
     "jupyter_dashboards": {
      "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,
        "row": 75,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "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()"
   ]
  },
  {
   "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",
    "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": 4,
        "height": 15,
        "hidden": false,
        "row": 118,
        "width": 4
       },
       "report_default": {}
      }
     }
    }
   },
   "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()"
   ]
  },
  {
   "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": [
    "ig25.index"
   ]
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}