diff options
Diffstat (limited to 'python/notebooks/Reto Report.ipynb')
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 54 |
1 files changed, 21 insertions, 33 deletions
diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index 81210010..1454b51b 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -11,10 +11,11 @@ "import pandas as pd\n", "import numpy as np\n", "\n", - "from pandas.tseries.offsets import BDay, MonthEnd, CustomBusinessMonthEnd\n", + "from pandas.tseries.offsets import BDay, MonthEnd, BMonthEnd, CustomBusinessMonthEnd\n", "\n", "from risk.bonds import subprime_risk, crt_risk, clo_risk\n", "from risk.portfolio import build_portfolio, generate_vol_surface\n", + "from pnl_explain import get_bond_pv\n", "\n", "import serenitas.analytics as ana\n", "from serenitas.analytics.index_data import load_all_curves\n", @@ -33,11 +34,11 @@ "outputs": [], "source": [ "#Set dates\n", - "position_date = (datetime.date.today() - BDay(4)).date()\n", - "spread_date = (datetime.date.today() - BDay(4)).date()\n", + "position_date = (datetime.date.today() - BDay(1)).date()\n", + "spread_date = (datetime.date.today() - BDay(1)).date()\n", "ana._local = False\n", "Trade.init_ontr(spread_date)\n", - "fund ='ISOSEL'" + "fund ='SERCGMAST'" ] }, { @@ -52,7 +53,7 @@ "spread_shock = np.array([-100., -25., 1., +25. , 100., 200., 500, 1000])\n", "spread_shock /= Trade._ontr['HY'].spread\n", "portf, _ = build_portfolio(position_date, spread_date, fund)\n", - "vol_surface = generate_vol_surface(portf, 10, 'MS')\n", + "vol_surface = generate_vol_surface(portf, 10, 'BAML')\n", "portf.reset_pv()\n", "scens = run_portfolio_scenarios(portf, date_range=[pd.Timestamp(spread_date)], params=['pnl', 'hy_equiv'],\n", " spread_shock=spread_shock,\n", @@ -182,34 +183,21 @@ "#Rolling min(month from inception, 12 months) sum of (total bond sales proceeds + paydown)/monthly NAV\n", "nav = go.get_net_navs()\n", "fund='SERCGMAST'\n", - "sql_string = \"SELECT * FROM bond_trades WHERE NOT buysell and fund = %s\"\n", - "df = pd.read_sql_query(sql_string, dawn_engine,\n", - " parse_dates={'lastupdate':{'utc':True}, 'trade_date': {}, 'settle_date':{}},\n", - " params=[fund,],\n", - " index_col = 'trade_date')\n", - "df = df.groupby(pd.Grouper(freq='M')).sum()\n", - "#Average traded volume (Bonds only)\n", - "\n", - "#Now get portfolio paydown per month\n", - "portfolio = go.get_portfolio()\n", - "portfolio = portfolio[(portfolio.custacctname == 'V0NSCLMAMB') &\n", - " (portfolio.identifier != 'USD') &\n", - " (portfolio.endqty != 0)]\n", - "cf = pd.read_sql_query(\"select date, principal_bal, principal, cusip as identifier \"\n", - " \"from cashflow_history a left join \"\n", - " \"(select figi, cusip from securities) b on a.identifier = b.figi\", dawn_engine,\n", - " parse_dates=['date'],\n", - " index_col=['date']).sort_index()\n", - "portfolio = portfolio.set_index('identifier', append=True)\n", - "portfolio = portfolio['endqty'].groupby(['identifier', 'periodenddate']).sum()\n", - "portfolio = portfolio.reset_index('identifier')\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", - "df_1['paydown'] = df_1.apply(lambda df: df.endqty/df.principal_bal * df.principal, axis=1)\n", - "paydowns = df_1.paydown.groupby(pd.Grouper(freq='M')).sum()\n", - "temp = pd.concat([paydowns, df.principal_payment, df.accrued_payment], axis=1).fillna(0)\n", - "turnover = (temp.sum(axis=1)/nav.begbooknav).rolling(12, min_periods=1).sum()\n", + "df_inst={}\n", + "for ac in ['CRT', 'Subprime', 'CLO']:\n", + " df_inst[ac] = get_bond_pv(\n", + " datetime.date.today() - BMonthEnd(24),\n", + " datetime.date.today() - BMonthEnd(),\n", + " fund=fund,\n", + " conn=dbconn(\"dawndb\"),\n", + " asset_class =ac)\n", + "df_inst = pd.concat(df_inst)\n", + "df = df_inst[(df_inst.principal_payment < 0) | \n", + " (df_inst.principal > 0)]\n", + "df['principal_payment'] = df['principal_payment'].abs()\n", + "df = df.reset_index(level=[0,2], drop=True)\n", + "df = df[['principal','principal_payment']].groupby(pd.Grouper(freq='M')).sum().sum(axis=1)\n", + "turnover = (df/nav.begbooknav).rolling(12).sum()\n", "turnover" ] }, |