diff options
Diffstat (limited to 'python/collateral/baml_fcm.py')
| -rw-r--r-- | python/collateral/baml_fcm.py | 35 |
1 files changed, 29 insertions, 6 deletions
diff --git a/python/collateral/baml_fcm.py b/python/collateral/baml_fcm.py index 6b7e6df8..d6a676f8 100644 --- a/python/collateral/baml_fcm.py +++ b/python/collateral/baml_fcm.py @@ -70,18 +70,41 @@ def collateral(d, positions, engine): .reset_index(["folder", "currency"]) ) df = df.rename(columns={"folder": "Strategy", "currency": "Currency"}) + col_names = [ + "Statement Date", + "AT", + "AT CCY", + "Beginning Balance", + "CDS Initial Coupon", + "CDS Trade Variation", + "Price Aligned Interest", + "Clearing Fee", + "Commission", + "Cash Amounts", + "Ending Balance", + "Account Value at Market", + "Net P&L", + "Initial Margin Requirement", + "Margin Excess/Deficit", + ] df_margin = pd.read_csv( DAILY_DIR / "BAML_reports" / f"OTC_Moneyline_{d:%Y%m%d}.CSV", - usecols=["Statement Date", "AT CCY", "Initial Margin Requirement"], + usecols=col_names, parse_dates=["Statement Date"], + index_col=["Statement Date"], ) - df_margin.columns = ["date", "currency", "amount"] - df_margin["account"] = "V0NSCLMFCM" + df_margin.AT = df_margin.AT.replace( + {"Q4": "EUR", "F4": "EUR", "Q1": "USD", "F2": "USD", "9Z": "ZZZZZ",} + ) + df_margin = df_margin.set_index("AT", append=True) + df_margin = df_margin.groupby(level=(0, 1)).sum() + df_margin = df_margin.reset_index() + col_names.pop(2) try: + place_holders = ",".join(["%s"] * (len(col_names) - 1)) engine.execute( - "INSERT INTO fcm_im " - "VALUES(%(date)s, %(account)s, %(currency)s, %(amount)s)", - df_margin.iloc[-1].to_dict(), + f"INSERT INTO fcm_moneyline VALUES(%s, 'V0NSCLMFCM', {place_holders})", + list(df_margin[col_names].itertuples(index=False)), ) except IntegrityError: pass |