diff options
| -rw-r--r-- | python/collateral_calc.py | 42 |
1 files changed, 27 insertions, 15 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 1b055d8a..96a7efe4 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -199,28 +199,37 @@ def download_citi_emails(count=20): if not p.exists(): p.write_bytes(attach.content) +def compare_notionals(df, positions, fcm: str): + check_notionals = (positions. + groupby(level=["security_id", "maturity"])[["notional"]]. + sum(). + join(df["NOTIONAL"], how="left")) + diff_notionals = check_notionals[check_notionals.notional != check_notionals.NOTIONAL] + if not diff_notionals.empty: + logging.error(f"Database and {fcm} FCM know different notionals") + for t in diff_notionals.itertuples(): + logging.error(f"{t.Index[0]}\t{t.Index[1].date()}\t{t.notional}\t{t.NOTIONAL}") + def baml_collateral(d): df = pd.read_csv(DAILY_DIR / "BAML_reports" / f"OTC_Open_Positions_-_Credit_-_LMCG_{d:%Y%m%d}.CSV", usecols=['MTM', 'ACCRUEDCPN', 'VARMARGIN', 'REDCODE', 'NOTIONAL', 'EODSETTLEMENTPRICE', 'PERIOD', 'BUYSELL'], - index_col=['REDCODE', 'PERIOD']) + index_col=['REDCODE']) + df.PERIOD = pd.to_datetime(df.PERIOD.astype('str') + '20') + df = df.set_index("PERIOD", append=True) df = df[df.EODSETTLEMENTPRICE.notnull()] - positions = pd.read_sql_query("SELECT security_id, security_desc, folder, notional, currency " + positions = pd.read_sql_query("SELECT security_id, security_desc, maturity, " + "folder, notional, currency " "FROM list_cds_positions_by_strat_fcm(%s, 'BAML')", dawn_engine, params=(d.date(),), - index_col=['security_id']) - df_helper = pd.read_sql_query("SELECT redindexcode, upper(tenor::text) AS tenor, " - "to_char(maturity, 'YYYYMM')::integer AS PERIOD FROM index_desc", - dawn_engine, - index_col=['redindexcode', 'tenor']) - positions['tenor'] = (positions.security_desc + "R").str.split(" ", expand=True)[4] - positions = positions.set_index('tenor', append=True) - positions['PERIOD'] = df_helper.loc[positions.index] - positions = positions.reset_index(['tenor']).set_index(['PERIOD'], append=True) - df['DIRTYUPFRONT'] = (df.MTM + df.ACCRUEDCPN ) / df.NOTIONAL - df['DIRTYUPFRONT'] = df.DIRTYUPFRONT.where(df.BUYSELL == 'Buy', -df.DIRTYUPFRONT) - df = df.groupby(level=[0, 1]).first() + index_col=['security_id', "maturity"]) + df["NOTIONAL"] = (df.NOTIONAL. + where(df.BUYSELL == 'Buy', -df.NOTIONAL). + astype("float")) + df["DIRTYUPFRONT"] = (df.MTM + df.ACCRUEDCPN ) / df.NOTIONAL + df.index.names = ["security_id", "maturity"] + compare_notionals(df, positions, "BAML") positions["dirtyupfront"] = df.reindex(positions.index)["DIRTYUPFRONT"] positions['amount'] = positions['notional'] * positions['dirtyupfront'] positions.folder = positions.folder.map({'HEDGE_MBS': 'MBSCDSCSH', @@ -280,9 +289,12 @@ def wells_collateral(d): "FROM list_cds_positions_by_strat_fcm(%s, 'WF')", dawn_engine, params=(d.date(),), index_col=["security_id", "maturity"]) + df["NOTIONAL"] = (df.NOTIONAL. + where(df.BUY_SELL == 1, -df.NOTIONAL). + astype("float")) df["DIRTYUPFRONT"] = df.MARKET_VALUE_NPV / df.NOTIONAL - df["DIRTYUPFRONT"] = df.DIRTYUPFRONT.where(df.BUY_SELL == 1, -df.DIRTYUPFRONT) df.index.names = ["security_id", "maturity"] + compare_notionals(df, positions, "Wells") positions = positions.join(df, how="left") positions["amount"] = positions["notional"] * positions["DIRTYUPFRONT"] positions.folder = positions.folder.map({'HEDGE_MBS': 'MBSCDSCSH', |