2 files changed, 28 insertions, 34 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb
index b8399305..1a30f348 100644
--- a/python/notebooks/Allocation Reports.ipynb
+++ b/python/notebooks/Allocation Reports.ipynb
@@ -220,8 +220,9 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "#RMBS Positions and Risks\n",
-    "rmbs_pos = go.get_rmbs_pos_df()"
+    "#Positions and Risks\n",
+    "rmbs_pos = go.get_rmbs_pos_df()\n",
+    "clo_pos = go.get_clo_pos_df()"
    ]
   },
   {
@@ -236,7 +237,7 @@
     "bond_dur, bond_yield = {}, {}\n",
     "for d, g in rmbs_pos.groupby(pd.Grouper(freq='M')):\n",
     "        bond_dur[d] = sum(g.curr_ntl * g.moddur)/sum(g.curr_ntl)\n",
-    "        bond_yield[d] = sum(g.endbooknav * g.moddur * g.b_yield) /sum(g.endbooknav * g.moddur)\n",
+    "        bond_yield[d] = sum(g.endbookmv * g.moddur * g.b_yield) /sum(g.endbookmv * g.moddur)\n",
     "a = pd.Series(bond_dur)\n",
     "b = pd.Series(bond_yield)\n",
     "a.name = 'Duration'\n",
@@ -266,9 +267,10 @@
    "source": [
     "#RMBS Risk - need RMBS Positions and Risks\n",
     "sql_string = \"select date, duration, series from on_the_run where index = 'HY'\"\n",
-    "duration = pd.read_sql_query(sql_string, Sengine, parse_dates=['date'])\n",
+    "duration = pd.read_sql_query(sql_string, Sengine, parse_dates=['date'], index_col=['date'])\n",
     "df = pd.merge_asof(rmbs_pos.sort_index(), duration, left_index=True, right_index=True)\n",
-    "df.groupby('timestamp').apply(lambda df: sum(df.delta_yield/df.duration * 100))"
+    "rmbs_hy_equiv = df.groupby('timestamp').apply(lambda df: sum(df.delta_yield/df.duration * 100))\n",
+    "#hy_equiv.plot()"
    ]
   },
   {
@@ -325,7 +327,7 @@
    "name": "python",
    "nbconvert_exporter": "python",
    "pygments_lexer": "ipython3",
-   "version": "3.6.5"
+   "version": "3.6.6"
   }
  },
  "nbformat": 4,
diff --git a/python/notebooks/Valuation Backtest.ipynb b/python/notebooks/Valuation Backtest.ipynb
index 698a25f1..870fb33d 100644
--- a/python/notebooks/Valuation Backtest.ipynb
+++ b/python/notebooks/Valuation Backtest.ipynb
@@ -6,13 +6,15 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "import matplotlib.pyplot as plt\n",
-    "from matplotlib.ticker import FuncFormatter \n",
     "from datetime import datetime\n",
-    "import pandas as pd\n",
+    "from db import dbengine\n",
     "\n",
     "import mark_backtest_underpar as mark\n",
-    "import globeop_reports as ops"
+    "import globeop_reports as ops\n",
+    "import pandas as pd\n",
+    "import matplotlib.pyplot as plt\n",
+    "\n",
+    "engine = dbengine('dawndb')"
    ]
   },
   {
@@ -205,29 +207,19 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "pnl_breakdown = ops.curr_port_PNL()"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "#Unrealized MTM Gains/Loss\n",
-    "breakdown_summary = pd.DataFrame(index = {'unreal mark-to-market'}, columns={'Gains','Loss','Net'})\n",
-    "breakdown_summary['Gains'] = pnl_breakdown[pnl_breakdown['mtdbookunrealmtm']>=0].sum()['mtdbookunrealmtm']\n",
-    "breakdown_summary['Loss'] = pnl_breakdown[pnl_breakdown['mtdbookunrealmtm']<0].sum()['mtdbookunrealmtm']\n",
-    "breakdown_summary['Net'] = pnl_breakdown.sum()['mtdbookunrealmtm']"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": null,
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "breakdown_summary / nav[-1]"
+    "#Portfolio MTM Gains/Loss/Net\n",
+    "df_pnl = ops.get_monthly_pnl()[:date]\n",
+    "r=[]\n",
+    "for d, g in df_pnl.reset_index('identifier').groupby(pd.Grouper(freq='M')):\n",
+    "    sql_string = \"SELECT * FROM risk_positions(%s, 'Subprime') WHERE notional > 0\"\n",
+    "    pos = pd.read_sql_query(sql_string, engine, params=[g.index[-1].date()])\n",
+    "    pos.identifier = pos.identifier.str[:9]\n",
+    "    pos = pos.merge(df_pnl.groupby('identifier').cumsum().loc[g.index[-1]],\n",
+    "                    on='identifier')['mtdtotalbookpl'] / nav.loc[d]\n",
+    "    r.append([g.index[-1], pos[pos>=0].sum(), pos[pos<0].sum()])\n",
+    "summary = pd.DataFrame.from_records(r, index='date', columns=['date','gains','loss'])\n",
+    "summary['Net'] = summary.gains + summary.loss\n",
+    "summary.plot()"
    ]
   },
   {
@@ -267,7 +259,7 @@
    "name": "python",
    "nbconvert_exporter": "python",
    "pygments_lexer": "ipython3",
-   "version": "3.6.4"
+   "version": "3.6.6"
   }
  },
  "nbformat": 4,