diff options
| -rw-r--r-- | python/notebooks/brinker_reports.ipynb | 52 |
1 files changed, 51 insertions, 1 deletions
diff --git a/python/notebooks/brinker_reports.ipynb b/python/notebooks/brinker_reports.ipynb index 9ea8f5ef..58b8dde7 100644 --- a/python/notebooks/brinker_reports.ipynb +++ b/python/notebooks/brinker_reports.ipynb @@ -70,6 +70,56 @@ "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()" ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#PNL over different time frames\n", + "sql_string = \"SELECT * from bbh_val\"\n", + "df = pd.read_sql_query(sql_string, dawn_engine,\n", + " parse_dates=['accounting_date'],\n", + " index_col = 'accounting_date')\n", + "sql_string = \"SELECT * from subscription_and_fee where fund = 'BRINKER'\"\n", + "flow = pd.read_sql_query(sql_string, dawn_engine,\n", + " parse_dates=['date'],\n", + " index_col = 'date')\n", + "df = df.groupby('accounting_date').nth(-1)\n", + "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['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()" + ] + }, + { + "cell_type": "code", + "execution_count": null, + "metadata": {}, + "outputs": [], + "source": [ + "#PNL breakdown\n", + "sql_string = \"SELECT * from bbh_pnl\"\n", + "pnl = pd.read_sql_query(sql_string, dawn_engine,\n", + " parse_dates=['accounting_date'])\n", + "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 == '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" + ] } ], "metadata": { @@ -88,7 +138,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.7.3" + "version": "3.8.1" } }, "nbformat": 4, |