1 files changed, 28 insertions, 38 deletions
diff --git a/python/collateral/gs_fcm.py b/python/collateral/gs_fcm.py
index 0ea49917..a035f5f5 100644
--- a/python/collateral/gs_fcm.py
+++ b/python/collateral/gs_fcm.py
@@ -10,17 +10,22 @@ def download_files(*args, **kwargs):
     sftp.download_files("outgoing", DAILY_DIR / "BowdSt" / "GS_fcm_reports")
 
 
-def load_file(d: datetime.date):
+def get_filename(d: datetime.date, name: str):
     try:
         fname = next(
             (DAILY_DIR / "BowdSt" / "GS_fcm_reports").glob(
-                f"GS-OTCSDIReporting-Open_Trades_Redcode-*-{d:%Y%m%d}-*.csv"
+                f"GS-OTCSDIReporting-{name}-*-{d:%Y%m%d}-*.csv"
             )
         )
     except StopIteration:
-        raise FileNotFoundError("GS fcm file not found for date {d}")
-    return pd.read_csv(
-        fname,
+        raise FileNotFoundError(f"GS fcm file {name} not found for date {d}")
+    else:
+        return fname
+
+
+def collateral(d: datetime.date, positions, *, engine, **kwargs):
+    df = pd.read_csv(
+        get_filename(next_business_day(d), "Open_Trades_Redcode"),
         usecols=[
             "Notional",
             "Direction",
@@ -33,10 +38,6 @@ def load_file(d: datetime.date):
         index_col=["Red Code", "Maturity Date"],
         thousands=",",
     )
-
-
-def collateral(d: datetime.date, positions, *, engine, **kwargs):
-    df = load_file(next_business_day(d))
     df.Notional = df.Notional.where(df.Direction == "Buy", -df.Notional)
     df.index.names = ["security_id", "maturity"]
     cob_date = df["COB Date"][0]
@@ -61,39 +62,28 @@ def collateral(d: datetime.date, positions, *, engine, **kwargs):
         .reset_index(["folder", "currency"])
     )
     df = df.rename(columns={"folder": "Strategy", "currency": "Currency"})
+    df["date"] = cob_date
+
     df_margin = pd.read_csv(
-        DAILY_DIR
-        / "BowdSt"
-        / "GS_fcm_reports"
-        / f"Account_Balances_and_Margin_Report_LMCG_51338_{d:%Y%m%d}.csv",
+        get_filename(next_business_day(d), "Account_Balances_and_Margin_extended"),
         parse_dates=["COB Date"],
         thousands=",",
     )
-    df["date"] = cob_date
-    df_margin = df_margin.rename(
-        columns={
-            "Opening Balance (local)": "beginning_balance",
-            "Ending Balance (local)": "ending_balance",
-            "PAI (local)": "pai",
-            "Account Value (local)": "account_value_market",
-            "Initial Margin Requirement (local)": "current_im",
-            "Excess/Deficit (local)": "current_excess_deficit",
-            "Currency": "currency",
-            "GS Account Number": "account",
-            "COB Date": "date",
-        }
-    )
-    cols = [
-        "date",
-        "account",
-        "beginning_balance",
-        "ending_balance",
-        "pai",
-        "account_value_market",
-        "current_im",
-        "current_excess_deficit",
-        "currency",
-    ]
+    col_mapping = {
+        "Opening Balance (local)": "beginning_balance",
+        "Ending Balance (local)": "ending_balance",
+        "PAI (local)": "pai",
+        "Account Value (local)": "account_value_market",
+        "Initial Margin Requirement (local)": "current_im",
+        "Excess/Deficit (local)": "current_excess_deficit",
+        "Currency": "currency",
+        "GS Account Number": "account",
+        "COB Date": "date",
+        "CCP Fees (local)": "clearing_fees",
+        "Commissions (local)": "transaction_fees",
+    }
+    df_margin = df_margin.rename(columns=col_mapping)
+    cols = col_mapping.values()
     place_holders = ",".join(["%s"] * len(cols))
     engine.execute(
         f"INSERT INTO fcm_moneyline({','.join(cols)}) VALUES({place_holders})",