diff options
| -rw-r--r-- | python/collateral_calc.py | 54 |
1 files changed, 43 insertions, 11 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index ad223e22..42684581 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -1,10 +1,12 @@ +import os +import pandas as pd + +from db import dbengine from exchange import get_msgs, get_account from exchangelib import Mailbox, Message, HTMLBody from ftplib import FTP from pathlib import Path from time import sleep -import os -import pandas as pd from pandas.tseries.offsets import BDay DAILY_DIR = Path(os.environ["DAILY_DIR"]) @@ -57,15 +59,44 @@ def sg_collateral(d): # index=['T2201711010000A3K20000045561220U'])) df = df_activity.join(df_position) df['Collateral'] = df['Mtm Value'] - df['Amount'] - d = {'ITRAXX': 'SER_ITRXCVCSH', - 'IG': 'SER_IGCVECSH', - 'HY': 'MBSCDSCSH'} - strat = [] - r = [] - for k, v in d.items(): - r.append((v, df.loc[df['Reference Entity'].str.contains(k), 'Collateral'].sum(), - "EUR" if k == "ITRAXX" else "USD")) - return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency']) + ref_entity = df['Reference Entity'].str.split("-", expand=True) + del ref_entity[0] + ref_entity.columns = ['to_split', 'tenor'] + ref_entity = ref_entity.join(ref_entity['to_split'].str. + extract("(IG|HY|EUROPE)S(\d+)V(\d+)$", + expand=True)) + del ref_entity['to_split'] + ref_entity.columns = ['tenor', 'index_type', 'series', 'version'] + ref_entity.index_type[ref_entity.index_type == "EUROPE"] = "EU" + df = df.join(ref_entity) + df = df.groupby(['index_type', 'series', 'tenor'])['Collateral'].sum() + positions = pd.read_sql_query("SELECT security_desc, folder, notional, currency " \ + "FROM list_cds_positions_by_strat(%s)", + dbengine("dawndb"), params=(d.date(),)) + instruments = positions.security_desc.str.split(expand=True)[[1, 3, 4]] + instruments.columns = ['index_type', 'series', 'tenor'] + instruments.series = instruments.series.str.extract("S(\d+)") + instruments.index_type[instruments.index_type == "EUR"] = "EU" + positions = positions.join(instruments) + del positions['security_desc'] + positions = positions.set_index(['index_type', 'series', 'tenor']) + df = positions.join(df) + d = {} + def f(g): + g.Collateral = g.Collateral * g.notional / g.notional.sum() + return g + df = (df.groupby(level=['index_type', 'series', 'tenor']). + apply(f)) + df = df.groupby(['folder']).agg({'Collateral': 'sum', 'currency': 'first'}) + df = df.reset_index('folder') + df = df.rename(columns= + {'folder': 'Strategy', + 'currency': 'Currency', + 'Collateral': 'Amount'}) + df.Strategy = df.Strategy.map({'HEDGE_MBS': 'MBSCDSCSH', + 'SER_ITRXCURVE': 'SER_ITRXCVCSH', + 'SER_IGCURVE': 'SER_IGCVECSH'}) + return df def ms_collateral(d): df = pd.read_excel(DAILY_DIR / "MS_reports" / f"{d:%Y%m%d}.xls" ) @@ -81,6 +112,7 @@ def ms_collateral(d): return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency']) def send_email(account, df_ms, df_sg): + pd.set_option('display.float_format', '{:.2f}'.format) content = HTMLBody('<html><body>' \ '<h3>At Morgan Stanley:</h3>' \ '{}' \ |