1 files changed, 48 insertions, 0 deletions
diff --git a/python/external_deriv_marks.py b/python/external_deriv_marks.py
new file mode 100644
index 00000000..ba002897
--- /dev/null
+++ b/python/external_deriv_marks.py
@@ -0,0 +1,48 @@
+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"])
+
+def gs_navs():
+    d = {}
+    for fname in (DAILY_DIR / "GS_reports").glob("Trade_Detail*.xls"):
+        try:
+            df = pd.read_excel(fname, skiprows=9, skipfooter=77,
+                               index_col='Trade Id')
+        except ValueError:
+            continue
+        df['Trade Date'] = pd.to_datetime(df['Trade Date'])
+        df = df[['Trade Date', 'Buy/Sell', 'Notional (USD)', 'NPV (USD)']]
+        name = fname.name.replace("9972734", "")
+        m = re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name)
+        if m:
+            date_string, = m.groups()
+            date = datetime.datetime.strptime(date_string, "%d_%b_%Y")
+        d[date] = df
+    return pd.concat(d)
+
+
+def ms_navs():
+    d = {}
+    for fname in (DAILY_DIR / "MS_reports").glob("Trade_Detail*.xls"):
+        df = pd.read_excel(fname, index_col='trade_id')
+        df.trade_date = pd.to_datetime(df.trade_date)
+        df = df[['trade_date', 'pay_rec', 'notional_in_trade_ccy', 'exposure_in_rpt_ccy']]
+        m = re.match(r"[^\d]*(\d{8})", fname.name)
+        if m:
+            date_string, = m.groups()
+            date = datetime.datetime.strptime(date_string, "%Y%m%d")
+        d[date] = df
+    return pd.concat(d)
+
+if __name__ == "__main__":
+    df = gs_navs()
+    with dbconn('dawndb') as conn:
+        with conn.cursor() as c:
+            for k, v in df[['NPV (USD)']].iterrows():
+                c.execute("INSERT INTO external_marks_deriv "
+                          "VALUES(%s, %s, %s, %s) ON CONFLICT DO NOTHING", (*k, float(v), "GS"))