1 files changed, 20 insertions, 28 deletions
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,