1 files changed, 39 insertions, 7 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py
index 12cf9b41..9d0eef3a 100644
--- a/python/external_deriv_marks.py
+++ b/python/external_deriv_marks.py
@@ -1,11 +1,8 @@
 import datetime
-import os
 import pandas as pd
 import re
-from db import dbconn
-from pathlib import Path
-
-DAILY_DIR = Path(os.environ["DAILY_DIR"])
+from env import DAILY_DIR
+from utils.db import dbconn
 
 
 def gs_navs():
@@ -24,7 +21,10 @@ def gs_navs():
             date_string, = m.groups()
             date = datetime.datetime.strptime(date_string, "%d_%b_%Y")
         d[date] = df
-    return pd.concat(d)
+    df = pd.concat(d)
+    # nav is from Goldman's point of view
+    df.nav *= -1.0
+    return df
 
 
 def ms_navs():
@@ -44,8 +44,40 @@ def ms_navs():
     return pd.concat(d)
 
 
+def citi_navs():
+    l = []
+    for fname in (DAILY_DIR / "CITI_reports").glob("262966_Portfolio_*.xlsx"):
+        df = pd.read_excel(
+            fname, skiprows=6, skipfooter=2, parse_dates=["Trade Date", "Value Date"]
+        )
+        df = df.dropna(subset=["Operations File"]).set_index(
+            ["Value Date", "Operations File"]
+        )
+        df = df[["Trade Date", "Party Position", "Notional", "Market Value"]]
+        df.columns = ["trade_date", "buy/sell", "notional", "nav"]
+        l.append(df)
+    df = pd.concat(l)
+    # nav is from Citi's point of view
+    df.nav *= -1.0
+    return df
+
+
+def baml_navs():
+    d = {}
+    for fname in (DAILY_DIR / "BAML_ISDA_reports").glob(
+        "Interest Rates Trade Summary_*.xls"
+    ):
+        date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y")
+        df = pd.read_excel(fname, skiprows=6, nrows=1)
+        df = df.set_index("Trade ID")
+        df = df[["Trade Date", "Flow Direction", "Notional", "MTM(USD)"]]
+        df.columns = ["trade_date", "buy/sell", "notional", "nav"]
+        d[date] = df
+    return pd.concat(d)
+
+
 if __name__ == "__main__":
-    for cp in ["MS", "GS"]:
+    for cp in ["MS", "CITI", "GS", "BAML"]:
         df = globals()[f"{cp.lower()}_navs"]()
         with dbconn("dawndb") as conn:
             with conn.cursor() as c: