diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/collateral/__main__.py | 22 | ||||
| -rw-r--r-- | python/collateral/baml_fcm.py | 50 | ||||
| -rw-r--r-- | python/collateral/common.py | 17 |
3 files changed, 81 insertions, 8 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py index dcf4e18a..01d2a283 100644 --- a/python/collateral/__main__.py +++ b/python/collateral/__main__.py @@ -66,12 +66,18 @@ cp_dict = { } -def run_collateral(cp, fund, positions, workdate, engine): +def run_collateral(cp, fund, positions, positions_rates, workdate, engine): cp_mod = import_module("." + cp, "collateral") lookback = 0 while lookback < 2: try: - return cp_mod.collateral(workdate, positions, engine=engine, fund=fund) + return cp_mod.collateral( + workdate, + positions, + engine=engine, + fund=fund, + positions_irs=positions_rates, + ) except FileNotFoundError as e: logger.warning(e) lookback += 1 @@ -99,12 +105,20 @@ for fund in ("Serenitas", "Brinker", "BowdSt"): index_col=["security_id", "maturity"], parse_dates=["maturity"], ) + positions_rates = pd.read_sql_query( + "SELECT cleared_trade_id, notional, effective_date, maturity_date, payreceive, fixed_rate, folder, currency " + "FROM irs", + dawn_engine, + index_col=["cleared_trade_id"], + parse_dates=["effective_date", "maturity_date"], + ) df[(fund, fcm.upper())] = run_collateral( - fcm, fund, positions, workdate, dawn_engine + fcm, fund, positions, positions_rates, workdate, dawn_engine ) + for cp in cp_dict[fund]["isda_cps"]: df[(fund, cp.upper())] = run_collateral( - cp, fund, bilat_positions, workdate, dawn_engine + cp, fund, bilat_positions, None, workdate, dawn_engine ) df = pd.concat(df, names=["fund", "broker", "strategy"]).reset_index() diff --git a/python/collateral/baml_fcm.py b/python/collateral/baml_fcm.py index c7c892a4..6847eb93 100644 --- a/python/collateral/baml_fcm.py +++ b/python/collateral/baml_fcm.py @@ -1,5 +1,5 @@ from . import DAILY_DIR, SftpClient -from .common import compare_notionals, STRATEGY_CASH_MAPPING +from .common import compare_notionals, compare_notionals_rates, STRATEGY_CASH_MAPPING import pandas as pd from sqlalchemy.exc import IntegrityError @@ -11,12 +11,21 @@ def download_files(*args, fund="Serenitas", **kwargs): sftp.download_files("outgoing", DAILY_DIR / fund / "BAML_reports") -def collateral(d, positions, *, engine, fund="Serenitas", **kwargs): - df = pd.read_csv( +def load_csv(file_type, fund, d, **kwargs): + return pd.read_csv( DAILY_DIR / fund / "BAML_reports" - / f"OTC_Open_Positions_-_Credit_-_LMCG_{d:%Y%m%d}.CSV", + / f"OTC_Open_Positions_-_{file_type}_-_LMCG_{d:%Y%m%d}.CSV", + **kwargs, + ) + + +def collateral(d, positions, *, engine, fund="Serenitas", positions_irs, **kwargs): + df = load_csv( + "Credit", + fund, + d, usecols=[ "MTM", "ACCRUEDCPN", @@ -60,6 +69,33 @@ def collateral(d, positions, *, engine, fund="Serenitas", **kwargs): .reset_index(["folder", "currency"]) ) df = df.rename(columns={"folder": "Strategy", "currency": "Currency"}) + df_rates = load_csv( + "Rates", + fund, + d, + usecols=[ + "COBDATE", + "CLEAREDTRADEID", + "FIXEDRATE", + "NPV", + "DIRECTION", + "NOTIONAL", + "EFFDATE", + "MATDATE", + ], + index_col=["CLEAREDTRADEID"], + parse_dates=["MATDATE", "EFFDATE"], + ) + compare_notionals_rates(df_rates, positions_irs, "BAML") + positions_irs["Amount"] = df_rates.reindex(positions_irs.index)["NPV"] + positions_irs.folder.replace(STRATEGY_CASH_MAPPING, inplace=True) + df_rates = ( + positions_irs.groupby(["folder", "currency"]) + .agg({"Amount": "sum"}) + .reset_index(["folder", "currency"]) + ) + df_rates = df_rates.rename(columns={"folder": "Strategy", "currency": "Currency"}) + col_names = [ "Statement Date", "AT", @@ -105,5 +141,11 @@ def collateral(d, positions, *, engine, fund="Serenitas", **kwargs): ) except IntegrityError: pass + df = pd.concat([df, df_rates]) + df = ( + df.groupby(["Strategy", "Currency"]) + .agg({"Amount": "sum"}) + .reset_index(["Strategy", "Currency"]) + ) df["date"] = d return df.set_index("Strategy") diff --git a/python/collateral/common.py b/python/collateral/common.py index 00b8c8e2..747ffb43 100644 --- a/python/collateral/common.py +++ b/python/collateral/common.py @@ -57,6 +57,23 @@ def compare_notionals(df: pd.DataFrame, positions: pd.DataFrame, fcm: str) -> No ) +def compare_notionals_rates( + df: pd.DataFrame, positions: pd.DataFrame, fcm: str +) -> None: + check_notionals = positions.join(df["NOTIONAL"], how="left") + diff_notionals = check_notionals[ + (check_notionals.notional != check_notionals.NOTIONAL) + & (check_notionals.notional != 0.0) + ] + if not diff_notionals.empty: + + logger.error(f"Database and {fcm} FCM know different notionals") + for t in diff_notionals.itertuples(): + logger.error( + f"{t.Index[0]}\t{t.Index[1]:%Y-%m-%d}\t{t.notional}\t{t.NOTIONAL}" + ) + + def get_bilateral_trades(d: datetime.date, fund: str, engine: Engine) -> pd.DataFrame: df_cds = pd.read_sql_query( "SELECT cpty_id, folder, initial_margin_percentage * abs(notional) / 100 as IA " |