1 files changed, 47 insertions, 13 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py
index 9d0eef3a..c6eb7ccb 100644
--- a/python/external_deriv_marks.py
+++ b/python/external_deriv_marks.py
@@ -2,12 +2,12 @@ import datetime
 import pandas as pd
 import re
 from env import DAILY_DIR
-from utils.db import dbconn
 
 
-def gs_navs():
+def gs_navs(date: datetime.date = None):
     d = {}
-    for fname in (DAILY_DIR / "GS_reports").glob("Trade_Detail*.xls"):
+    date_str = date.strftime("%d_%b_%Y") if date else ""
+    for fname in (DAILY_DIR / "GS_reports").glob(f"Trade_Detail*{date_str}*.xls"):
         try:
             df = pd.read_excel(fname, skiprows=9, skipfooter=77, index_col="Trade Id")
         except ValueError:
@@ -27,9 +27,10 @@ def gs_navs():
     return df
 
 
-def ms_navs():
+def ms_navs(date: datetime.date = None):
     d = {}
-    for fname in (DAILY_DIR / "MS_reports").glob("Trade_Detail*.xls"):
+    date_str = date.strftime("%Y%m%d") if date else "*"
+    for fname in (DAILY_DIR / "MS_reports").glob(f"Trade_Detail_{date_str}.xls"):
         df = pd.read_excel(fname, index_col="trade_id")
         df.trade_date = pd.to_datetime(df.trade_date)
         df = df[
@@ -44,9 +45,13 @@ def ms_navs():
     return pd.concat(d)
 
 
-def citi_navs():
-    l = []
-    for fname in (DAILY_DIR / "CITI_reports").glob("262966_Portfolio_*.xlsx"):
+def citi_navs(date: datetime.date = None):
+    d = {}
+    glob_str = date.strftime("%Y%m%d*") if date else "*"
+    for fname in (DAILY_DIR / "CITI_reports").glob(f"262966_Portfolio_{glob_str}.xlsx"):
+        date_parsed = datetime.datetime.strptime(
+            fname.stem.rsplit("_", 1)[1][:-3], "%Y%m%d%H%M%S%f"
+        )
         df = pd.read_excel(
             fname, skiprows=6, skipfooter=2, parse_dates=["Trade Date", "Value Date"]
         )
@@ -55,17 +60,24 @@ def citi_navs():
         )
         df = df[["Trade Date", "Party Position", "Notional", "Market Value"]]
         df.columns = ["trade_date", "buy/sell", "notional", "nav"]
-        l.append(df)
-    df = pd.concat(l)
+        d[date_parsed] = df
+    # there can be multiple files per day, we take the latest one
+    df = (
+        pd.concat(d)
+        .sort_index()
+        .groupby(level=["Value Date", "Operations File"])
+        .last()
+    )
     # nav is from Citi's point of view
     df.nav *= -1.0
     return df
 
 
-def baml_navs():
+def baml_navs(date: datetime.date = None):
     d = {}
+    glob_str = date.strftime("%d-%b-%Y") if date else "*"
     for fname in (DAILY_DIR / "BAML_ISDA_reports").glob(
-        "Interest Rates Trade Summary_*.xls"
+        f"Interest Rates Trade Summary_{glob_str}.xls"
     ):
         date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y")
         df = pd.read_excel(fname, skiprows=6, nrows=1)
@@ -77,8 +89,30 @@ def baml_navs():
 
 
 if __name__ == "__main__":
+    import argparse
+    from utils.db import dbconn
+    from pandas.tseries.offsets import BDay
+
+    parser = argparse.ArgumentParser()
+    parser.add_argument(
+        "date",
+        type=datetime.datetime.fromisoformat,
+        nargs="?",
+        default=datetime.date.today(),
+    )
+    parser.add_argument(
+        "-a", "--all", action="store_true", default=False, help="download everything"
+    )
+    args = parser.parse_args()
+    date = None if args.all else args.date
+
     for cp in ["MS", "CITI", "GS", "BAML"]:
-        df = globals()[f"{cp.lower()}_navs"]()
+        if cp != "CITI":
+            date_arg = (date - BDay()).date()
+        else:
+            date_arg = date
+        df = globals()[f"{cp.lower()}_navs"](date_arg)
+        print(df)
         with dbconn("dawndb") as conn:
             with conn.cursor() as c:
                 for k, v in df[["nav"]].iterrows():