diff options
Diffstat (limited to 'python/notebooks/brinker_reports.ipynb')
| -rw-r--r-- | python/notebooks/brinker_reports.ipynb | 68 |
1 files changed, 50 insertions, 18 deletions
diff --git a/python/notebooks/brinker_reports.ipynb b/python/notebooks/brinker_reports.ipynb index 58b8dde7..03b0a2b4 100644 --- a/python/notebooks/brinker_reports.ipynb +++ b/python/notebooks/brinker_reports.ipynb @@ -10,14 +10,14 @@ "from pandas.tseries.offsets import BDay, MonthEnd\n", "import globeop_reports as go\n", "import pandas as pd\n", - "import analytics\n", + "import serenitas.analytics\n", "import numpy as np\n", "\n", - "from analytics.index_data import get_index_quotes\n", - "from analytics.scenarios import run_portfolio_scenarios\n", - "from analytics import BlackSwaption, CreditIndex, BlackSwaptionVolSurface, Portfolio,DualCorrTranche\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 utils.db import dbconn, dbengine\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", @@ -35,17 +35,17 @@ "################################### Average Portfolio Sales Turnover - as of last monthend from today\n", "#Actually: Rolling months sum of (total bond sales proceeds + paydown)/monthly NAV\n", "fund='BRINKER'\n", - "sql_string = \"SELECT * FROM bonds WHERE buysell IS False and fund = %s order by trade_date desc\"\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", - "brinker_nav = pd.read_csv(\n", - " \"/home/serenitas/edwin/Python/brinker_nav.csv\",\n", - " parse_dates=[\"date\"],\n", - " index_col=[\"date\"])\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", @@ -68,7 +68,7 @@ "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.nav).rolling(min(13, len(turnover))-1).sum()" + "turnover = (turnover.sum(axis=1)/brinker_nav.total_net_assets).rolling(min(13, len(turnover))-1).sum()" ] }, { @@ -90,13 +90,13 @@ "df = df.merge(flow, how='left', left_index=True, right_index=True)\n", "df.fillna(0, inplace=True)\n", "df['beg_nav'] = df.total_net_assets.shift(1) + df.subscription.shift(1) - df.redemption\n", - "df.loc['2019-3-19','beg_nav'] = 110000000\n", + "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", - "quarterly = cum_ret.groupby(pd.Grouper(freq='Q')).nth(-1)\n", - "monthly.pct_change(), quarterly.pct_change()" + "\n", + "#monthly.pct_change().plot()" ] }, { @@ -112,13 +112,45 @@ "sql_string = \"SELECT * from securities\"\n", "bonds = pd.read_sql_query(sql_string, dawn_engine, index_col = 'cusip')\n", "pnl = pnl.merge(bonds, how='left', left_on='security_id', right_on='cusip')\n", - "pnl.loc[(pnl.sub_security_type_code == 'CXT'),'asset_class'] = 'Tranches'\n", - "pnl.loc[(pnl.sub_security_type_code == 'CDX'),'asset_class'] = 'Tranches'\n", + "pnl.loc[(pnl.sub_security_type_code == 'CXT'),'asset_class'] = 'Corporate Tranches'\n", + "pnl.loc[(pnl.sub_security_type_code == 'CDX'),'asset_class'] = 'Corporate Tranches'\n", "pnl.loc[(pnl.sub_security_type_code == 'SWP'),'asset_class'] = 'IR-Hedges'\n", "pnl.asset_class.fillna('Others', inplace=True)\n", "pnl.set_index(['accounting_date', 'asset_class'], inplace=True)\n", "base_change = pnl['base_change_total'].groupby(['accounting_date', 'asset_class']).sum()\n", - "base_change.unstack()\n" + "base_change.unstack()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#Export to spreadsheet\n", + "df.sort_index(ascending=False)[['total_net_assets', 'subscription', 'redemption']].to_clipboard()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#Export to spreadsheet 2\n", + "base_change.unstack().sort_index(ascending=False).to_clipboard()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#load bbh val reports\n", + "import load_bbh_reports as load\n", + "load_date = datetime.date(2020,9,7)\n", + "load.load_reports(load_date)" ] } ], @@ -138,7 +170,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.8.1" + "version": "3.9.1-final" } }, "nbformat": 4, |