1 files changed, 112 insertions, 0 deletions
diff --git a/python/notebooks/clo_loan_markets.ipynb b/python/notebooks/clo_loan_markets.ipynb
new file mode 100644
index 00000000..9785d612
--- /dev/null
+++ b/python/notebooks/clo_loan_markets.ipynb
@@ -0,0 +1,112 @@
+{
+ "cells": [
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "from utils.db import dbconn, dbengine\n",
+    "\n",
+    "from matplotlib.pyplot import hist\n",
+    "import matplotlib.pyplot as plt\n",
+    "\n",
+    "import pandas as pd\n",
+    "import numpy as np\n",
+    "\n",
+    "etengine = dbengine('etdb')\n",
+    "\n",
+    "%matplotlib inline"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "value_date = pd.datetime.today().date()\n",
+    "date_range = pd.bdate_range(end=value_date, freq='3BM',periods=12)\n",
+    "sql_string = \"SELECT c.loanxid, c.issuername, c.dealname, c.facility_type, c.loanx_facility_type, \" \\\n",
+    "             \"c.initial_amount, c.initial_spread, c.maturity, c.industry, b.bid, b.offer, b.depth, a.latestdate \" \\\n",
+    "             \"FROM ( SELECT markit_prices.pricingdate AS latestdate, \" \\\n",
+    "             \"markit_prices.loanxid as loanxid_a FROM markit_prices \" \\\n",
+    "             \"where pricingdate = %s GROUP BY markit_prices.loanxid, latestdate) a \" \\\n",
+    "             \"JOIN markit_prices b ON loanxid_a = b.loanxid::text AND a.latestdate = b.pricingdate \" \\\n",
+    "             \"JOIN latest_markit_facility c ON loanxid_a = c.loanxid::text;\"\n",
+    "df = pd.DataFrame()\n",
+    "for d in date_range:\n",
+    "    df = df.append(pd.read_sql_query(sql_string, etengine, params=[d,]))\n",
+    "df.sort_values(by='latestdate', inplace=True)\n",
+    "df['mid'] = (df['bid'] + df['offer'])/2\n",
+    "df = df[df['facility_type']!='Equity']\n",
+    "df['mv'] = df['initial_amount'] *1e6 * df['mid']/100"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "hist_bins = np.linspace(50, 110, 13)\n",
+    "hist_bins = np.insert(hist_bins, 0, 0)\n",
+    "df['price_bucket'] = pd.cut(df['mid'], hist_bins)\n",
+    "    \n",
+    "hist_per = df.groupby(['latestdate', 'price_bucket']).agg({'mv': 'sum'})\n",
+    "hist_per = hist_per.groupby(level=0).apply(lambda x: x / float(x.sum()))\n",
+    "hist_per.unstack().plot(kind = 'bar', stacked=True)\n",
+    "plt.legend(loc='center left', bbox_to_anchor=(1.0, 0.5))"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "#Distressed - Industry breakdown\n",
+    "industry_hist = df[df['mid']<80].groupby(['latestdate', 'industry']).agg({'mv': 'sum'})\n",
+    "industry_hist = industry_hist.groupby(level=0).apply(lambda x: x / float(x.sum()))\n",
+    "top = industry_hist.groupby('latestdate').head(20)\n",
+    "top.unstack().plot(kind = 'bar', stacked=True)\n",
+    "plt.legend(loc='center left', bbox_to_anchor=(1.0, 0.5))"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {},
+   "outputs": [],
+   "source": []
+  },
+  {
+   "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
+}