diff options
Diffstat (limited to 'python/collateral')
| -rw-r--r-- | python/collateral/baml_fcm.py | 35 | ||||
| -rw-r--r-- | python/collateral/wells.py | 60 |
2 files changed, 62 insertions, 33 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 diff --git a/python/collateral/wells.py b/python/collateral/wells.py index d27274c9..affd99a8 100644 --- a/python/collateral/wells.py +++ b/python/collateral/wells.py @@ -118,34 +118,40 @@ def collateral(d, positions, engine): .reset_index(["folder", "currency"]) ) df = df.rename(columns={"folder": "Strategy", "currency": "Currency"}) - col_names = ["CURRENCY_NAME", "VALUE_DATE", "CURRENT_IM"] - try: - df_margin = pd.read_csv( - DAILY_DIR - / "Wells_reports" - / f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv", - usecols=col_names, - parse_dates=[col_names[1]], - index_col=[col_names[0]], - ) - except ValueError as e: - # reports downloaded from the website have Title case column names - col_names = [c.replace("_", " ").title() for c in col_names] - # almost Title case... - col_names[-1] = "Current IM" - df_margin = pd.read_csv( - DAILY_DIR - / "Wells_reports" - / f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv", - usecols=col_names, - parse_dates=[col_names[1]], - index_col=[col_names[0]], - ) + df_margin = pd.read_csv( + DAILY_DIR + / "Wells_reports" + / f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv", + ) + table_cols = [ + "VALUE_DATE", + "CURRENCY_NAME", + "BEGINNING_BALANCE", + "CDS_INITIAL_COUPON", + "CDS_RESET_TO_PAR", + "PAI", + "CLEARING_FEES", + "TRANSACTION_FEES", + "NET_DEP_WITHDRAW", + "ENDING_BALANCE", + "ACCOUNT_VALUE_MARKET", + "REALIZED_PNL", + "CURRENT_IM", + "CURRENT_EXCESS_DEFICIT", + ] + if "VALUE_DATE" in df_margin: + df_margin.VALUE_DATE = pd.to_datetime(df_margin.VALUE_DATE) + else: + df_margin["Value Date"] = pd.to_datetime(df_margin["Value Date"]) + table_cols = [c.replace("_", " ").title() for c in table_cols] + table_cols[-2] = "Current IM" try: - engine.execute( - "INSERT INTO fcm_im " "VALUES(%s, 'WFNSCLMFCM', 'USD', %s)", - df_margin.loc["ZZZZZ", col_names[1:]].tolist(), - ) + place_holders = ",".join(["%s"] * (len(table_cols) - 1)) + with engine.connect() as conn: + conn.execute( + f"INSERT INTO fcm_moneyline VALUES(%s, 'WFNSCLMFCM', {place_holders})", + list(df_margin[table_cols].itertuples(index=False)), + ) except IntegrityError: pass df["date"] = d |