diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/external_deriv_marks.py | 46 |
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: |