diff options
| -rw-r--r-- | python/collateral/__main__.py | 11 | ||||
| -rw-r--r-- | python/collateral/bnp.py | 57 | ||||
| -rw-r--r-- | python/collateral/common.py | 14 |
3 files changed, 72 insertions, 10 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py index c927535f..50244580 100644 --- a/python/collateral/__main__.py +++ b/python/collateral/__main__.py @@ -9,6 +9,7 @@ from .common import get_dawn_trades, send_email from pandas.tseries.offsets import BDay import argparse +import datetime import logging fh = SerenitasFileHandler("collateral_calc.log") @@ -20,8 +21,8 @@ parser = argparse.ArgumentParser() parser.add_argument( "workdate", nargs="?", - type=lambda s: pd.datetime.strptime(s, "%Y-%m-%d").date(), - default=pd.Timestamp.today().normalize(), + type=datetime.datetime.fromisoformat, + default=datetime.date.today(), ) parser.add_argument( "-d", "--download", action="store_true", help="download counterparty reports" @@ -30,8 +31,8 @@ parser.add_argument( "-s", "--send-email", action="store_true", help="send email to Globeop" ) args = parser.parse_args() -counterparties = ["citi", "ms", "gs", "baml_fcm", "baml_isda", "wells"] - +# counterparties = ["citi", "ms", "gs", "bnp", "baml_fcm", "baml_isda", "wells"] +counterparties = ["citi", "baml_isda"] if args.download: for cp in counterparties: cp_mod = import_module(f".{cp}", "collateral") @@ -63,7 +64,7 @@ for cp in counterparties: ) except ValueError as e: logger.error(e) - if cp == "citi": + if cp == "citi": # all cp except CITI operate on previous business day args.workdate = args.workdate - BDay() df = pd.concat(df, names=["broker", "strategy"]).reset_index() diff --git a/python/collateral/bnp.py b/python/collateral/bnp.py new file mode 100644 index 00000000..edd4eb85 --- /dev/null +++ b/python/collateral/bnp.py @@ -0,0 +1,57 @@ +import datetime +import pandas as pd +from . import DAILY_DIR + + +def download_files(count: int = 20): + from exchange import ExchangeMessage + + em = ExchangeMessage() + emails = em.get_msgs( + path=["NYops", "Margin Calls BNP"], + count=count, + sender="bnppnycollateralmgmt@us.bnpparibas.com", + ) + DATA_DIR = DAILY_DIR / "BNP_reports" + for msg in emails: + for attach in msg.attachments: + p = DATA_DIR / attach.name + if not p.exists(): + p.write_bytes(attach.content) + + +def load_file(d: datetime.date, report_type: str): + fname = ( + f"{report_type} - BNP PARIBAS - SERENITAS CREDIT GAMMA " + f"MASTER FUND, LP - COB {d:%Y%m%d}.XLS" + ) + return pd.read_excel(DAILY_DIR / "BNP_reports" / fname, skiprows=7) + + +def collateral(d: datetime.date, dawn_trades: pd.DataFrame, *args): + df = load_file(d, "Collateral Positions") + collateral = float(df["Mkt Val (Agmt Ccy)"]) + df = load_file(d, "Exposure Statement") + df = df[["Trade Ref", "Exposure Amount (Agmt Ccy)", "Lock Up (Agmt Ccy)"]] + df["Trade Ref"] = df["Trade Ref"].str.replace("MBO-", "") + df = df.merge(dawn_trades, how="left", left_on="Trade Ref", right_on="cpty_id") + missing_ids = df.loc[df.cpty_id.isnull(), "Trade Ref"] + if not missing_ids.empty: + raise ValueError(f"{missing_ids.tolist()} not in the database") + df = df[["folder", "Exposure Amount (Agmt Ccy)", "Lock Up (Agmt Ccy)"]] + df = df.groupby("folder").sum() + df = df.sum(axis=1).to_frame(name="Amount") + df["Currency"] = "USD" + df = df.reset_index() + df.columns = ["Strategy", "Amount", "Currency"] + df.Amount *= -1 + df = df.append( + { + "Strategy": "M_CSH_CASH", + "Amount": -collateral - df.Amount.sum(), + "Currency": "USD", + }, + ignore_index=True, + ) + df["date"] = d + return df.set_index("Strategy") diff --git a/python/collateral/common.py b/python/collateral/common.py index 6dac36bc..464f55a3 100644 --- a/python/collateral/common.py +++ b/python/collateral/common.py @@ -1,11 +1,13 @@ +import datetime import logging import pandas as pd from exchangelib import HTMLBody +from sqlalchemy.engine import Engine logger = logging.getLogger(__name__) -def compare_notionals(df, positions, fcm: str): +def compare_notionals(df: pd.DataFrame, positions: pd.DataFrame, fcm: str) -> None: check_notionals = ( positions.groupby(level=["security_id", "maturity"])[["notional"]] .sum() @@ -22,11 +24,11 @@ def compare_notionals(df, positions, fcm: str): ) -def get_dawn_trades(d, engine): +def get_dawn_trades(d: datetime.date, engine: Engine) -> pd.DataFrame: df_cds = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * notional / 100 as IA " - "FROM cds " - "WHERE cpty_id IS NOT NULL AND trade_date <= %s", + "FROM list_cds(%s::date) " + "WHERE cpty_id IS NOT NULL", engine, params=(d,), ) @@ -63,13 +65,14 @@ def get_dawn_trades(d, engine): "HYMEZ": "TCSH", "HYEQY": "TCSH", "BSPK": "TCSH", + "XOMEZ": "TCSH", } } ) return df -def send_email(d, df): +def send_email(d: datetime.date, df: pd.DataFrame) -> None: from exchange import ExchangeMessage pd.set_option("display.float_format", "{:.2f}".format) @@ -81,6 +84,7 @@ def send_email(d, df): "BAML_FCM": "Baml FCM", "BAML_ISDA": "Baml OTC", "WELLS": "Wells Fargo", + "BNP": "BNP Paribas", } html = "<html><body>" for cp, name in cp_mapping.items(): |