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
|
import pandas as pd
from . import DAILY_DIR
from exchangelib import FileAttachment
from serenitas.analytics.utils import get_fx
paths = {
"Serenitas": ["NYops", "Margin calls MS"],
"Brinker": ["NYops", "Margin Calls MS-Brinker"],
"BowdSt": ["BowdoinOps", "Margin MS"],
}
subjects = {
"Serenitas": "SERCX **Daily",
"Brinker": "061761QY1***BRINKER",
"BowdSt": "BOSTON PATRIOT BOWDOIN",
}
def download_files(em, count=20, *, fund="Serenitas", **kwargs):
emails = em.get_msgs(
path=paths[fund],
count=count,
subject__contains=subjects[fund],
)
DATA_DIR = DAILY_DIR / fund / "MS_reports"
for msg in emails:
for attach in msg.attachments:
if isinstance(attach, FileAttachment):
if "NETSwaps" in attach.name:
fname = "Trade_Detail_" + attach.name.split("_")[1]
elif "NETFX" in attach.name:
fname = "Trade_Detail_FX_" + attach.name.split("_")[1]
elif "NET_Collateral" in attach.name:
fname = "Collateral_Detail_" + attach.name.rsplit("_", 1)[1]
elif "Statement" in attach.name and attach.name.endswith("pdf"):
ending = attach.name.rsplit("_", 1)[1]
fname = "Statement_" + ending.split(".")[0] + ".pdf"
else:
continue
p = DATA_DIR / fname
if not p.exists():
p.write_bytes(attach.content)
def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs):
df = pd.read_excel(
DAILY_DIR / fund / "MS_reports" / f"Collateral_Detail_{d:%Y%m%d}.xls"
)
collat = df.loc[1, "coll_val_ccy"].replace(",", "")
if "(" in collat:
collat = collat[1:-1]
collat = -float(collat)
else:
collat = float(collat)
df = pd.read_excel(DAILY_DIR / fund / "MS_reports" / f"Trade_Detail_{d:%Y%m%d}.xls")
df_fx = pd.read_excel(
DAILY_DIR / fund / "MS_reports" / f"Trade_Detail_FX_{d:%Y%m%d}.xls"
)
net_fx_exposure = (
df_fx.loc[df_fx.buy_ccy == "EUR", "amt_buy_ccy"].sum()
- df_fx.loc[df_fx.sell_ccy == "EUR", "amt_sell_ccy"].sum()
)
fx_ia = net_fx_exposure * 0.05 * get_fx(d, "EUR")
df = pd.concat([df, df_fx])
# df = df.dropna(subset=["trade_ccy"])
df = df.merge(dawn_trades, how="left", left_on="trade_id", right_on="cpty_id")
missing_ids = df.loc[df.cpty_id.isnull(), "trade_id"]
if not missing_ids.empty:
raise ValueError(f"{missing_ids.tolist()} not in the database for {fund}")
df = df.groupby("folder")[["collat_req_in_agr_ccy"]].sum()
df["Currency"] = "USD"
df = df.reset_index()
df.columns = ["Strategy", "Amount", "Currency"]
df.loc[df.Strategy == "TCSH", "Amount"] -= fx_ia
df = df.append(
{
"Strategy": "M_CSH_CASH",
"Amount": -collat - df.Amount.sum(),
"Currency": "USD",
},
ignore_index=True,
)
df["date"] = d
return df.set_index("Strategy")
|
