diff options
Diffstat (limited to 'python/notebooks/brinker_reports.ipynb')
| -rw-r--r-- | python/notebooks/brinker_reports.ipynb | 73 |
1 files changed, 30 insertions, 43 deletions
diff --git a/python/notebooks/brinker_reports.ipynb b/python/notebooks/brinker_reports.ipynb index 03b0a2b4..6f1757dc 100644 --- a/python/notebooks/brinker_reports.ipynb +++ b/python/notebooks/brinker_reports.ipynb @@ -7,22 +7,15 @@ "outputs": [], "source": [ "import datetime\n", - "from pandas.tseries.offsets import BDay, MonthEnd\n", "import globeop_reports as go\n", "import pandas as pd\n", "import serenitas.analytics\n", "import numpy as np\n", "\n", - "from serenitas.analytics.index_data import get_index_quotes\n", - "from serenitas.analytics.scenarios import run_portfolio_scenarios\n", - "from serenitas.analytics import BlackSwaption, CreditIndex, BlackSwaptionVolSurface, Portfolio,DualCorrTranche\n", - "\n", + "from pandas.tseries.offsets import BDay, MonthEnd, BMonthEnd\n", + "from pnl_explain import get_bond_pv\n", "from serenitas.utils.db import dbconn, dbengine\n", "\n", - "from risk.tranches import get_tranche_portfolio\n", - "from risk.swaptions import get_swaption_portfolio\n", - "from risk.bonds import subprime_risk, clo_risk, crt_risk\n", - "\n", "dawn_engine = dbengine('dawndb')" ] }, @@ -34,41 +27,31 @@ "source": [ "################################### Average Portfolio Sales Turnover - as of last monthend from today\n", "#Actually: Rolling months sum of (total bond sales proceeds + paydown)/monthly NAV\n", + "import warnings\n", + "warnings.filterwarnings('ignore')\n", "fund='BRINKER'\n", - "sql_string = \"SELECT * FROM bond_trades WHERE buysell IS False and fund = %s order by trade_date desc\"\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", - "\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", "brinker_nav = pd.read_sql_query(\"select distinct accounting_date, total_net_assets from bbh_val order by accounting_date desc\",\n", " dawn_engine,\n", " parse_dates=[\"accounting_date\"],\n", " index_col=[\"accounting_date\"])\n", "\n", - "start_date = datetime.date(2019,3,18)\n", - "end_date = datetime.date.today()\n", - "cf = pd.read_sql_query(\"SELECT * FROM cashflow_history where date > %s and date <= %s\", dawn_engine,\n", - " params=[start_date, end_date],\n", - " parse_dates=['date'],\n", - " index_col=['date']).sort_index()\n", - "sql_string = \"SELECT description, identifier, notional, price, factor FROM risk_positions(%s, %s, 'BRINKER')\"\n", - "pos = {}\n", - "for d in cf.index.unique():\n", - " for ac in ['Subprime', 'CRT']:\n", - " pos[d, ac] = pd.read_sql_query(sql_string, dawn_engine, params=[d.date(), ac])\n", - "pos = pd.concat(pos, names=['date', 'asset_class'])\n", - "pos = pos.reset_index(level=[1,2])\n", - "\n", - "cf_1 = pd.merge_asof(cf, pos.sort_index(), left_index=True, right_index=True, by='identifier')\n", - "cf_1 = cf_1.dropna(subset=['notional'])\n", - "cf_1 = cf_1[(cf_1.principal_bal != 0) & (cf_1.principal != 0)]\n", - "cf_1['paydown'] = cf_1.apply(lambda df: df.notional * df.factor/df.principal_bal * df.principal, axis=1)\n", - "paydowns = cf_1.paydown.groupby(pd.Grouper(freq='M')).sum()\n", - "turnover = pd.concat([paydowns, df.principal_payment, df.accrued_payment], axis=1).fillna(0)\n", "brinker_nav = brinker_nav.groupby(pd.Grouper(freq='M')).last()\n", - "turnover = (turnover.sum(axis=1)/brinker_nav.total_net_assets).rolling(min(13, len(turnover))-1).sum()" + "turnover = df.rolling(min(13, len(df))-1).sum()/brinker_nav.total_net_assets\n", + "turnover" ] }, { @@ -93,10 +76,7 @@ "df.loc['2019-3-19','total_net_asset'] = 110000000\n", "df['ret'] = (df.total_net_assets - df.beg_nav)/df.beg_nav\n", "cum_ret = (df.ret+1).cumprod()\n", - "\n", - "monthly= cum_ret.groupby(pd.Grouper(freq='M')).nth(-1)\n", - "\n", - "#monthly.pct_change().plot()" + "monthly= cum_ret.groupby(pd.Grouper(freq='M')).nth(-1)" ] }, { @@ -152,11 +132,18 @@ "load_date = datetime.date(2020,9,7)\n", "load.load_reports(load_date)" ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [] } ], "metadata": { "kernelspec": { - "display_name": "Python 3", + "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, @@ -170,7 +157,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.9.1-final" + "version": "3.10.9" } }, "nbformat": 4, |