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
|
import datetime
import pandas as pd
import pathlib
import subprocess
from . import DAILY_DIR
from bs4 import BeautifulSoup
from operator import itemgetter
from pandas.tseries.offsets import BDay
from xlrd import open_workbook
def download_files(count=20):
from exchange import ExchangeMessage
DATA_DIR = DAILY_DIR / "CS_reports"
em = ExchangeMessage()
emails = em.get_msgs(
path=["NYops", "Margin Calls CS"], count=count, subject__contains="DERV048829"
)
for msg in emails:
for attach in msg.attachments:
fname = attach.name
if fname.endswith("xlsx"):
p = DATA_DIR / fname
if not p.exists():
p.write_bytes(attach.content)
emails = em.get_msgs(
path=["NYops", "Margin Calls CS"],
count=count,
sender="americas.collateralmgt@credit-suisse.com",
)
for msg in emails:
for attach in msg.attachments:
fname = attach.name
if "Serenitas CGMF" in fname:
p = DATA_DIR / fname
p = p.parent / f"{msg.datetime_sent:%Y-%m-%d} {p.stem}{p.suffix}"
if not p.exists():
p.write_bytes(attach.content)
def load_pdf(file_path: pathlib.Path):
proc = subprocess.run(
["pdftohtml", "-xml", "-l", "1", "-stdout", "-i", file_path.as_posix()],
capture_output=True,
)
soup = BeautifulSoup(proc.stdout, features="lxml")
l = soup.findAll("text")
for e in l:
if e.text.startswith(
"Market Value of Collateral required pursuant to this notice"
):
sib = e.next_siblings
next(sib)
return float(next(sib).text.replace(",", ""))
def get_collateral(d):
DATA_DIR = DAILY_DIR / "CS_reports"
# get most recent file before current date
def get_date(p):
return datetime.date.fromisoformat(p.stem.split(" ", 1)[0])
files = ((f, get_date(f)) for f in DATA_DIR.glob("*.xls"))
files = sorted(filter(lambda t: t[1] <= d, files), key=itemgetter(1), reverse=True)
excel_file, date = files[0]
pdf_file = DATA_DIR / f"{date} Margin_Notice161 Serenitas CGMF RVM.pdf"
last_margin_call = load_pdf(pdf_file)
if date == d: # margin call is current do not include
last_margin_call = 0
wb = open_workbook(files[0][0])
s = wb.sheet_by_index(0)
i = 0
im = 0.0
for i, v in enumerate(s.col_values(0)):
if s.cell_value(i, 4) == "Total IM (USD):":
im = s.cell_value(i, 5)
if v.startswith("Total Value of Collateral"):
return s.cell_value(i, 1) + last_margin_call + im
if v.startswith("No Positions to Report"):
return 0.0
def collateral(d, dawn_trades, *args):
collateral = get_collateral(d + BDay())
df = pd.read_excel(
f"/home/serenitas/Daily/CS_reports/DERV048829_{d:%b%d%Y}.xlsx",
header=9,
skipfooter=50,
)
df = df[["Order No", "Mid Price", "Notional Currency"]]
df["Mid Price"] = (
df["Mid Price"]
.str.replace(",", "")
.apply(lambda s: -float(s[1:-1]) if s.startswith("(") else float(s))
)
df["Order No"] = df["Order No"].astype("str")
df = df.merge(dawn_trades, how="left", left_on="Order No", right_on="cpty_id")
missing_ids = df.loc[df.cpty_id.isnull(), "Order No"]
if not missing_ids.empty:
raise ValueError(f"{missing_ids.tolist()} not in the database")
df.ia = df.ia.fillna(0.0)
df["Amount"] = df.ia + df["Mid Price"]
df = df[["folder", "Amount", "Notional Currency"]]
df = df.groupby(["folder", "Notional Currency"], as_index=False).sum()
df = df.rename(columns={"folder": "Strategy", "Notional Currency": "Currency"})
df.Amount *= -1
df = df.append(
{
"Strategy": "M_CSH_CASH",
"Amount": -collateral - df.Amount.sum(),
"Currency": "USD",
},
ignore_index=True,
)
df["date"] = d
return df.set_index("Strategy")
|
