diff options
Diffstat (limited to 'python/notebooks/Reto Report.ipynb')
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 23 |
1 files changed, 11 insertions, 12 deletions
diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index 4ced9d4d..cf823db0 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -19,11 +19,8 @@ "from analytics.index_data import get_index_quotes\n", "from analytics.scenarios import run_portfolio_scenarios, run_swaption_scenarios\n", "from analytics import Swaption, BlackSwaption, CreditIndex, BlackSwaptionVolSurface, Portfolio, ProbSurface, DualCorrTranche\n", - "from db import dbconn, dbengine\n", + "from db import serenitas_engine, dawn_engine\n", "\n", - "conn = dbconn('dawndb')\n", - "dawndb = dbengine('dawndb')\n", - "serenitasdb = dbengine('serenitasdb')\n", "analytics.init_ontr()" ] }, @@ -71,8 +68,8 @@ "#Average Portfolio Sales Turnover - as of last monthend from today\n", "#(total Bond Sales Proceeds + paydown)/average starting 12 months NAV\n", "nav = go.get_net_navs()\n", - "sql_string = \"SELECT * FROM bonds where buysell = 'False'\"\n", - "df = pd.read_sql_query(sql_string, dbengine('dawndb'),\n", + "sql_string = \"SELECT * FROM bonds WHERE buysell IS False\"\n", + "df = pd.read_sql_query(sql_string, dawn_engine,\n", " parse_dates={'lastupdate':'utc=True', 'trade_date':'', 'settle_date':''},\n", " index_col = 'trade_date')\n", "df = df.groupby(pd.Grouper(freq='M')).sum()\n", @@ -84,10 +81,10 @@ " (portfolio.endqty != 0)]\n", "portfolio = portfolio.set_index('identifier', append=True)\n", "portfolio = portfolio['endqty'].groupby(['identifier', 'periodenddate']).sum()\n", - "portfolio = portfolio.reset_index('identifier') \n", - "sql_string = \"SELECT * from cashflow_history\"\n", - "cf = pd.read_sql_query(sql_string, dbengine('dawndb'), parse_dates=['date'],\n", - " index_col=['date']).sort_index()\n", + "portfolio = portfolio.reset_index('identifier')\n", + "cf = pd.read_sql_query(\"SELECT * FROM cashflow_history\", dawn_engine,\n", + " parse_dates=['date'],\n", + " index_col=['date']).sort_index()\n", "df_1 = pd.merge_asof(cf, portfolio.sort_index(), left_index=True, right_index=True, by='identifier')\n", "df_1 = df_1.dropna(subset=['endqty'])\n", "df_1 = df_1[(df_1.principal_bal != 0) & (df_1.principal != 0)]\n", @@ -149,6 +146,7 @@ " \"OVER (partition by security_id, attach) AS ntl_agg \"\n", " \"FROM cds WHERE swap_type='CD_INDEX' AND termination_cp IS null \"\n", " \"AND trade_date <= %s AND maturity > %s\")\n", + "conn = dawn_engine.raw_connection()\n", "with conn.cursor() as c:\n", " c.execute(t_sql_string, (position_date,))\n", " t_trades = [[dealid, f\"{folder}_{dealid}\"] for dealid, folder, ntl in c if ntl != 0]\n", @@ -156,6 +154,7 @@ " swaption_trades = [[dealid, f\"{folder}_{dealid}\", expiration_date] for dealid, folder, expiration_date in c]\n", " c.execute(index_sql_string, (position_date, position_date))\n", " index_trades = [[dealid, f\"{folder}_{dealid}\"] for dealid, folder, ntl in c if ntl != 0]\n", + "conn.close()\n", " \n", "portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for dealid, _ in t_trades],\n", " [trade_id for _, trade_id in t_trades])\n", @@ -168,7 +167,7 @@ "#get bond risks:\n", "rmbs_pos = go.rmbs_pos(position_date)\n", "clo_pos = go.clo_pos(position_date)\n", - "r = serenitasdb.execute(\"select duration from on_the_run where index = 'HY' and date = %s\",\n", + "r = serenitas_engine.execute(\"select duration from on_the_run where index = 'HY' and date = %s\",\n", " spread_date)\n", "duration, = next(r)\n", "rmbs_pos['hy_equiv'] = rmbs_pos['delta_yield']/duration * 100\n", @@ -229,7 +228,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.7.1" + "version": "3.7.2" } }, "nbformat": 4, |