diff options
| -rw-r--r-- | python/globeop_reports.py | 27 | ||||
| -rw-r--r-- | python/notebooks/Reto Report.ipynb | 42 |
2 files changed, 21 insertions, 48 deletions
diff --git a/python/globeop_reports.py b/python/globeop_reports.py index 4175a501..8385b61f 100644 --- a/python/globeop_reports.py +++ b/python/globeop_reports.py @@ -31,24 +31,17 @@ def get_monthly_pnl(group_by=["identifier"], fund="SERCGMAST"): ].sum() -def get_portfolio(report_date=None): +def get_portfolio(report_date=None, fund="SERCGMAST"): + sql_string = "SELECT * FROM valuation_reports" + df = pd.read_sql_query( + sql_string, + dawn_engine, + parse_dates=["periodenddate"], + index_col=["periodenddate"], + ) + df = df[df.fund == fund] if report_date is not None: - sql_string = "SELECT * FROM valuation_reports where periodenddate = %s" - df = pd.read_sql_query( - sql_string, - dawn_engine, - parse_dates=["periodenddate"], - index_col=["periodenddate"], - params=[report_date], - ) - else: - sql_string = "SELECT * FROM valuation_reports" - df = pd.read_sql_query( - sql_string, - dawn_engine, - parse_dates=["periodenddate"], - index_col=["periodenddate"], - ) + df = df[df.date == report_date] df["identifier"] = df.invid.str.replace("_A$", "", regex=True) return df diff --git a/python/notebooks/Reto Report.ipynb b/python/notebooks/Reto Report.ipynb index 5a95d1e0..d431f970 100644 --- a/python/notebooks/Reto Report.ipynb +++ b/python/notebooks/Reto Report.ipynb @@ -360,39 +360,19 @@ "metadata": {}, "outputs": [], "source": [ - "################################## Leverage Ratio - Positive and negative PV trades. The only thing missing in the calc are the USD/EUR Loans\n", + "################################## Leverage Ratio - Asset/Liability Ratios\n", + "#Only look at trades by taking out cash (USD/EUR) and (USD/EURLoan). \n", + "#Positive_nav_ratio = Cash + Positive PV Trades\n", "nav = go.get_net_navs()\n", "portf = go.get_portfolio()\n", - "df = portf.groupby(pd.Grouper(freq=\"M\"), group_keys=False).apply(\n", - " lambda df: df.loc[df.index[-1]]\n", - " )\n", - "df = df[~df['invid'].isin(['USDLOAN', 'EURLOAN'])]\n", - "df = df[~df['port'].isin(['SER_TEST__SER_TEST', 'GFS_HELPER_BUSINESS_UNIT'])]\n", - "pvs = df.groupby(['periodenddate', df['endbooknav'] >0])['endbooknav'].sum().unstack().rename(\n", + "portf = portf.groupby(pd.Grouper(freq=\"M\"), group_keys=False).apply(lambda df: df.loc[df.index[-1]])\n", + "trades = portf[~portf['invid'].isin(['USDLOAN', 'EURLOAN', 'USD', 'EUR', 'CAD'])]\n", + "trades = trades.groupby(['periodenddate', trades['endbooknav'] >0]).sum(numeric_only=True).unstack(level=1).rename(\n", " columns={True:'endbooknav_pos', False:'endbooknav_neg'})\n", - "nav = nav.merge(pvs, left_index=True, right_index=True)\n", - "nav['long_leverage'] = nav['endbooknav_pos']/nav.endbooknav\n", - "nav['gross_leverage'] = (nav['endbooknav_pos']-nav['endbooknav_neg'])/nav.endbooknav\n", - "print (\"positive pv/nav: \" + str(nav['long_leverage'].mean()), \"gross pv/nav: \" + str(nav['gross_leverage'].mean()))\n", - "################################### Broken out by stratey too\n", - "pvs = df.groupby(['periodenddate', 'port', df['endbooknav'] >0])['endbooknav'].sum().unstack().rename(\n", - " columns={True:'endbooknav_pos', False:'endbooknav_neg'}).unstack(level=1)\n", - "data={}\n", - "for name in ['endbooknav_pos', 'endbooknav_neg']:\n", - " pv_gross = pvs.xs(name, level = 'endbooknav', axis=1)\n", - " pv_gross.loc[:, 'TRANCHE'] = pv_gross[['IG', 'HY', 'LQD_TRANCH','TRANCHE']].fillna(0).sum(axis=1)\n", - " pv_gross.rename({'CURVE': 'CREDIT CURVES',\n", - " 'HEDGE_MAC': 'MACRO HEDGE',\n", - " 'IR': 'INTEREST RATE DERIVATIVES',\n", - " 'MORTGAGES': 'MORTGAGE BONDS',\n", - " 'OPTIONS': 'CREDIT OPTIONS',\n", - " 'STRUCTURED': 'CSO BONDS',\n", - " 'TRANCHE': 'CREDIT TRANCHES'}, axis=1, inplace=True)\n", - " pv_gross.drop(['LQD_TRANCH', 'IG', 'HY'], inplace=True, axis=1)\n", - " pv_gross = pv_gross.merge(nav['endbooknav'], left_index=True, right_index=True)\n", - " data[name] = pv_gross.iloc[:,:-1].div(pv_gross['endbooknav'], axis=0)\n", - "data['endbooknav_pos'].to_clipboard()\n", - "data['endbooknav_neg'].to_clipboard()" + "trades = trades.xs('endbooknav', axis=1)\n", + "trades = trades.merge(nav['endbooknav'], left_index=True, right_index=True)\n", + "trades['cash'] = trades['endbooknav'] - (trades['endbooknav_pos'] + trades['endbooknav_neg'])\n", + "trades['positive_nav_ratio'] = (trades['cash'] + trades['endbooknav_pos'])/trades['endbooknav']" ] }, { @@ -559,7 +539,7 @@ "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", - "version": "3.10.9" + "version": "3.11.3" } }, "nbformat": 4, |