{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import globeop_reports as go\n",
    "import pandas as pd\n",
    "import serenitas.analytics\n",
    "import numpy as np\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",
    "dawn_engine = dbengine('dawndb')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "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",
    "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",
    "brinker_nav = brinker_nav.groupby(pd.Grouper(freq='M')).last()\n",
    "turnover = df.rolling(min(13, len(df))-1).sum()/brinker_nav.total_net_assets\n",
    "turnover"
   ]
  },
  {
   "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','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",
    "monthly= cum_ret.groupby(pd.Grouper(freq='M')).nth(-1)"
   ]
  },
  {
   "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'] = '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()"
   ]
  },
  {
   "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)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}