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
|
import datetime
import pandas as pd
from . import DAILY_DIR
paths = {
"Serenitas": ["NYops", "Margin Calls BNP"],
"BowdSt": ["BowdoinOps", "Margin BNP"],
}
def download_files(em, count: int = 20, *, fund="Serenitas", **kwargs):
if fund not in paths:
return
emails = em.get_msgs(
path=paths[fund],
count=count,
sender="bnppnycollateralmgmt@us.bnpparibas.com",
)
DATA_DIR = DAILY_DIR / fund / "BNP_reports"
for msg in emails:
for attach in msg.attachments:
p = DATA_DIR / attach.name
if not p.exists() and hasattr(attach, "content"):
p.write_bytes(attach.content)
def load_file(d: datetime.date, report_type: str, fund: str):
fund_mapping = {
"Serenitas": "SERENITAS CREDIT GAMMA MASTER FUND, LP",
"BowdSt": "BOSTON PATRIOT BOWDOIN ST LLC",
}
fname = (
f"{report_type} - BNP PARIBAS - {fund_mapping[fund]} " f"- COB {d:%Y%m%d}.XLS"
)
return pd.read_excel(DAILY_DIR / fund / "BNP_reports" / fname, skiprows=7)
def collateral(
d: datetime.date, dawn_trades: pd.DataFrame, *, fund="Serenitas", **kwargs
):
df = load_file(d, "Collateral Positions", fund)
if df.at[0, "Held/Posted"] == "Posted":
sign = 1.0
else:
sign = -1.0
collateral = sign * df.at[0, "Mkt Val (Agmt Ccy)"]
df = load_file(d, "Exposure Statement", fund)
df = df[["Trade Ref", "Exposure Amount (Agmt Ccy)", "Lock Up (Agmt Ccy)"]]
df["Trade Ref"] = df["Trade Ref"].str.replace("(FOC-|MBO-)", "", regex=True)
df = df.merge(dawn_trades, how="left", left_on="Trade Ref", right_on="cpty_id")
missing_ids = df.loc[df.cpty_id.isnull(), "Trade Ref"]
if not missing_ids.empty:
raise ValueError(f"{missing_ids.tolist()} not in the database")
df = df[["folder", "Exposure Amount (Agmt Ccy)", "Lock Up (Agmt Ccy)"]]
df = df.groupby("folder").sum()
df = df.sum(axis=1).to_frame(name="Amount")
df["Currency"] = "USD"
df = df.reset_index()
df.columns = ["Strategy", "Amount", "Currency"]
df.Amount *= -1
df.loc[len(df.index)] = ["M_CSH_CASH", collateral - df.Amount.sum(), "USD"]
df["date"] = d
return df.set_index("Strategy")
|
