1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
|
import datetime
import pandas as pd
import re
from env import DAILY_DIR
from collateral.baml_isda import baml_load_excel
def gs_navs(date: datetime.date = None):
d = {}
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:
continue
df = df.dropna(subset=["GS Entity"])
df["Trade Date"] = pd.to_datetime(df["Trade Date"])
df = df[["Trade Date", "Buy/Sell", "Notional (USD)", "NPV (USD)"]]
df.columns = ["trade_date", "buy/sell", "notional", "nav"]
name = fname.name.replace("9972734", "")
if m := re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name):
date_string, = m.groups()
date = datetime.datetime.strptime(date_string, "%d_%b_%Y")
d[date] = df
df = pd.concat(d)
# nav is from Goldman's point of view
df.nav *= -1.0
return df
def ms_navs(date: datetime.date = None):
d = {}
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[
["trade_date", "pay_rec", "notional_in_trade_ccy", "exposure_in_rpt_ccy"]
]
df.columns = ["trade_date", "buy/sell", "notional", "nav"]
if m := re.match(r"[^\d]*(\d{8})", fname.name):
date_string, = m.groups()
date = datetime.datetime.strptime(date_string, "%Y%m%d")
d[date] = df
return pd.concat(d)
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"]
)
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"]
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(date: datetime.date = None):
d = {}
glob_str = date.strftime("%d-%b-%Y") if date else "*"
for fname in (DAILY_DIR / "BAML_ISDA_reports").glob(
f"Interest Rates Trade Summary_{glob_str}.xls"
):
date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y")
df = baml_load_excel(fname)
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)
def bnp_navs(date: datetime.date = None):
d = {}
date_str = date.strftime("%Y%m%d") if date else ""
for fname in (DAILY_DIR / "BNP_reports").glob(f"Exposure*{date_str}.XLS"):
try:
df = pd.read_excel(fname, skiprows=7)
except ValueError:
continue
df["Trade Ref"] = df["Trade Ref"].str.replace("MBO-", "")
df = df.set_index("Trade Ref")
df["Trade Date"] = pd.to_datetime(df["Trade Date"], dayfirst=True)
df = df[["Trade Date", "Buy/Sell", "Notional 1", "Exposure Amount (Agmt Ccy)"]]
df.columns = ["trade_date", "buy/sell", "notional", "nav"]
d[datetime.datetime.strptime(fname.stem[-8:], "%Y%m%d").date()] = df
df = pd.concat(d)
# nav is from BNP's point of view
df.nav *= -1.0
return df
# def bnp_navs_old(date: datetime.date = None):
# d = {}
# date_str = date.strftime("%d%b%Y") if date else ""
# for fname in (DAILY_DIR / "BNP_reports").glob(f"SERENITAS*0_*{date_str}.csv"):
# try:
# df = pd.read_csv(fname)
# except ValueError:
# continue
# df = df.set_index("Contract")
# df["COB Date"] = pd.to_datetime(df["COB Date"])
# df = df[["COB Date", "B/S", "Notional", "Reval PV"]]
# df.columns = ["trade_date", "buy/sell", "notional", "nav"]
# d[datetime.datetime.strptime(fname.name.split("_")[3], "%d%b%Y").date()] = df
# df = pd.concat(d)
# return df
if __name__ == "__main__":
import argparse
import logging
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"
)
parser.add_argument(
"-d", "--debug", action="store_true", default=False, help="more verbose logging"
)
args = parser.parse_args()
date = None if args.all else args.date
logging.basicConfig()
logger = logging.getLogger("external_marks")
logger.setLevel(logging.DEBUG if args.debug else logging.INFO)
for cp in ["MS", "CITI", "GS", "BAML", "BNP"]:
logger.info(cp)
if date and cp != "CITI":
date_arg = (date - BDay()).date()
else:
date_arg = date
try:
df = globals()[f"{cp.lower()}_navs"](date_arg)
except ValueError:
continue
logger.debug(df)
with dbconn("dawndb") as conn:
with conn.cursor() as c:
for k, v in df[["nav"]].iterrows():
c.execute(
"INSERT INTO external_marks_deriv "
"VALUES(%s, %s, %s, %s) ON CONFLICT DO NOTHING",
(*k, float(v), cp),
)
|
