1 files changed, 33 insertions, 18 deletions
diff --git a/python/notebooks/Allocation Reports.ipynb b/python/notebooks/Allocation Reports.ipynb
index 1ce2a13e..b8399305 100644
--- a/python/notebooks/Allocation Reports.ipynb
+++ b/python/notebooks/Allocation Reports.ipynb
@@ -14,7 +14,8 @@
     "import numpy as np\n",
     "\n",
     "from db import dbengine\n",
-    "engine  = dbengine('dawndb')"
+    "engine  = dbengine('dawndb')\n",
+    "Sengine = dbengine('serenitasdb')"
    ]
   },
   {
@@ -219,8 +220,8 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "#This takes a while\n",
-    "df = go.get_rmbs_pos_df()"
+    "#RMBS Positions and Risks\n",
+    "rmbs_pos = go.get_rmbs_pos_df()"
    ]
   },
   {
@@ -233,9 +234,9 @@
     "#Filtering out RMBS Bonds:\n",
     "#df = df[df.strat != 'MTG_FP']\n",
     "bond_dur, bond_yield = {}, {}\n",
-    "for d, g in df.groupby(pd.Grouper(freq='M')):\n",
-    "        bond_dur[d] = sum(g.curr_ntl * g[('moddur', 3)])/sum(g.curr_ntl)\n",
-    "        bond_yield[d] = sum(g.endlocalmv * g[('moddur', 3)] * g.b_yield) /sum(g.endlocalmv * g[('moddur', 3)])\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",
     "a = pd.Series(bond_dur)\n",
     "b = pd.Series(bond_yield)\n",
     "a.name = 'Duration'\n",
@@ -263,22 +264,29 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "#Calculate Average Holding Period of RMBS portfolio\n",
-    "sql_string = \"SELECT * FROM bonds where buysell= True\"\n",
-    "df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates={'lastupdate': {'utc': True}, 'trade_date': {}, 'settle_date': {}})\n",
+    "#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",
+    "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))"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "#Calculate Average Holding Period of RMBS portfolio - Need RMBS Positions and Risks\n",
+    "sql_string = \"SELECT cusip, trade_date FROM bonds where buysell= True\"\n",
+    "df_trades = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['trade_date'])\n",
     "df_trades['trade_date2'] = df_trades['trade_date']\n",
-    "#df_trades = df_trades.groupby(['identifier']).last()\n",
-    "#df_with_trades = df.reset_index().merge(df_trades.reset_index(), on='identifier')\n",
-    "df_with_trades = pd.merge_asof(df.sort_index(), df_trades.set_index('trade_date').sort_index(), \n",
+    "df_with_trades = pd.merge_asof(rmbs_pos.sort_index(), df_trades.set_index('trade_date').sort_index(), \n",
     "                               left_index=True,\n",
     "                               right_index=True,\n",
-    "                               left_by='identifier',\n",
-    "                               right_by='cusip')\n",
+    "                               by='cusip')\n",
     "df_with_trades['hold'] = (df_with_trades.index - df_with_trades.trade_date2).dt.days/365\n",
-    "sp = {}\n",
-    "for i, g in df_with_trades.groupby('periodenddate'):\n",
-    "    sp[i] = sum(g.endbooknav * g.hold)/sum(g.endbooknav)\n",
-    "holding_period = pd.DataFrame.from_dict(sp, orient='index')\n",
+    "holding_period = df_with_trades.groupby('timestamp').apply(lambda df: sum(df.endbooknav * df.hold)/sum(df.endbooknav))\n",
     "ax = holding_period.plot(legend=False, title='Average Holding Period')\n",
     "ax.set_xlabel('date')\n",
     "ax.set_ylabel('Years')"
@@ -292,6 +300,13 @@
    "source": [
     "engine.dispose()"
    ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "metadata": {},
+   "outputs": [],
+   "source": []
   }
  ],
  "metadata": {