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
|
import logging
import msoffcrypto
import pandas as pd
from io import BytesIO
from pikepdf import Pdf
from . import DAILY_DIR
from .common import load_pdf, get_col, parse_num
logger = logging.getLogger(__name__)
paths = {
"Serenitas": ["NYops", "Margin Calls JPM"],
"BowdSt": ["BowdoinOps", "Margin JPM"],
"Selene": ["SeleneOps", "Margin JPM"],
}
accounts = {
"BowdSt": "909271",
"Serenitas": "923550",
"Selene": "1001279",
}
passwords = {"BowdSt": "tm64EO", "Serenitas": "JV3RJu", "Selene": "s3agvz"}
def load_file(d, fund, ext="pdf"):
try:
fname = next(
(DAILY_DIR / fund / "JPM_reports").glob(
f"CSCFTCSTMT-*-{d:%y%m%d}-{accounts[fund]}_2.{ext}"
)
)
except StopIteration:
raise FileNotFoundError(f"JPM file not found for date {d}")
return fname
def get_collateral(collat_page):
return float(get_col(collat_page, 200, 300, 1000, 1100)[0].replace(",", ""))
def load_positions(positions_page, bottom_offset=25):
anchor = next(c for c in positions_page if c.text.startswith("Total Product Group"))
bottom = int(anchor["top"]) - bottom_offset
widths = (10, 160, 340, 400, 480, 520, 600, 690, 780, 850, 950, 1035, 1100, 1250)
cols = [
get_col(positions_page, 200, bottom, l, r) for l, r in zip(widths, widths[1:])
]
def combine(l):
return [f"{l[0]} {l[1]}", *l[2:]]
cols = [combine(c) if len(c) == (len(cols[0]) + 1) else c for c in cols]
df = pd.DataFrame({c[0]: c[1:] for c in cols})
for col in ["Pay Notional", "Rec Notional", "MTM Amount", "IM Amount"]:
df[col] = df[col].apply(parse_num)
for col in ["Trade Date", "Maturity Date"]:
df[col] = pd.to_datetime(df[col], format="%d-%b-%y")
df["Deal ID"] = "810RI" + df["Deal ID"].str.extract(r"([^-]*)-.*")
return df
def download_files(em, count=20, *, fund="BowdSt", **kwargs):
if fund not in paths:
return
emails = em.get_msgs(
path=paths[fund], count=count, subject__contains=accounts[fund]
)
DATA_DIR = DAILY_DIR / fund / "JPM_reports"
for msg in emails:
for attach in msg.attachments:
fname = attach.name
p = DATA_DIR / fname
if not p.exists():
stream = BytesIO(attach.content)
if fname.endswith("pdf"):
pdf = Pdf.open(stream, password=passwords[fund])
pdf.save(p)
elif fname.endswith("xls"):
xl_file = msoffcrypto.OfficeFile(stream)
xl_file.load_key(password=passwords[fund])
with p.open("wb") as fh:
xl_file.decrypt(fh)
def collateral(d, dawn_trades, *, fund="BowdSt", **kwargs):
try:
excel_file = load_file(d, fund, ext="xls")
df = pd.read_excel(
excel_file,
sheet_name="Open Positions",
skiprows=14,
skipfooter=12,
)
# JP has a total column only if there is more than 1 trade
df = df.iloc[:-1] if len(df) > 1 else df
df["Deal ID"] = "810RI" + df["Deal ID"].str.extract(r"([^-]*)-.*")
collat_df = pd.read_excel(excel_file, sheet_name="Collateral Holdings")
collat = collat_df.iloc[-1][-1]
except StopIteration: # PDF method
pdf_file = load_file(d, fund)
pages = load_pdf(pdf_file, pages=True)
try:
collat = get_collateral(pages[3])
except IndexError:
collat = 0.0
try:
df = pd.concat([load_positions(pages[4]), load_positions(pages[5])])
except StopIteration:
# No TRS
df = load_positions(pages[4])
df = df.merge(dawn_trades, how="left", left_on="Deal ID", right_on="cpty_id")
missing_ids = df.loc[df.cpty_id.isnull(), "Deal ID"]
if not missing_ids.empty:
raise ValueError(f"{missing_ids.tolist()} not in the database for {fund}")
df = df[["folder", "MTM Amount", "IM Amount"]]
df = df.groupby("folder", dropna=False).sum()
df = df.sum(axis=1).to_frame(name="Amount")
df["Currency"] = "USD"
df = df.reset_index()
df.columns = ["Strategy", "Amount", "Currency"]
df.loc[len(df.index)] = ["M_CSH_CASH", -collat - df.Amount.sum(), "USD"]
df["date"] = d
return df.set_index("Strategy")
|
