1 files changed, 33 insertions, 27 deletions
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