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
|
import datetime
import pandas as pd
import re
from env import DAILY_DIR
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", "")
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
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"]
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)
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 = pd.read_excel(fname, skiprows=6, nrows=3)
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
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),
)
|
