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
|
import pandas as pd
import subprocess
from bs4 import BeautifulSoup
from pandas.tseries.offsets import BDay
from . import DAILY_DIR, bus_day
def load_file(d):
try:
fname = next(
(DAILY_DIR / "CITI_reports").glob(
f"262966_Portfolio_{d.strftime('%Y%m%d')}*"
)
)
except StopIteration:
raise FileNotFoundError(f"CITI file not found for date {d}")
return pd.read_excel(fname, skiprows=6, skipfooter=2)
def download_files(count=20):
from exchange import ExchangeMessage
em = ExchangeMessage()
emails = em.get_msgs(
path=["NYops", "Margin Calls Citi"], count=count, subject__startswith="262966"
)
DATA_DIR = DAILY_DIR / "CITI_reports"
for msg in emails:
for attach in msg.attachments:
fname = attach.name
p = DATA_DIR / fname
if not p.exists():
p.write_bytes(attach.content)
def load_pdf(file_path):
proc = subprocess.run(
["pdftohtml", "-xml", "-stdout", "-i", file_path.as_posix()],
capture_output=True,
)
soup = BeautifulSoup(proc.stdout, features="lxml")
l = soup.findAll("text")
l = sorted(l, key=lambda x: (int(x["top"]), int(x["left"])))
return l
def get_col(l, top, bottom, left, right):
return [
c.text
for c in l
if int(c["left"]) >= left
and int(c["left"]) < right
and int(c["top"]) >= top
and int(c["top"]) < bottom
]
def parse_num(s):
s = s.replace(",", "")
if s[0] == "(":
return -float(s[1:-1])
else:
return float(s)
def get_df(l, col1, col2, col3):
df = pd.DataFrame(
{"amount": get_col(l, *col2), "currency": get_col(l, *col3)},
index=get_col(l, *col1),
)
df.amount = df.amount.apply(parse_num)
df.index = df.index.str.lstrip()
return df
def get_total_collateral(d):
try:
fname = next(
(DAILY_DIR / "CITI_reports").glob(
f"262966_MarginNotice_{d.strftime('%Y%m%d')}_*.pdf"
)
)
except StopIteration:
raise FileNotFoundError(f"CITI file not found for date {d.date()}")
l = load_pdf(fname)
col1 = (370, 500, 70, 100)
col2 = (370, 500, 100, 500)
col3 = (370, 500, 500, 600)
variation_margin = get_df(l, col1, col2, col3)
anchor = next(c for c in l if c.text == "Non Regulatory Initial Margin")
top = int(anchor["top"]) + 10
bottom = top + 150
col1 = (top, bottom, 70, 100)
col2 = (top, bottom, 100, 505)
col3 = (top, bottom, 505, 600)
initial_margin = get_df(l, col1, col2, col3)
return (
variation_margin.loc["VM Total Collateral", "amount"]
+ initial_margin.loc["Non Reg IM Total Collateral", "amount"]
)
def collateral(d, dawn_trades, *args):
df = load_file(d)
collat = get_total_collateral(d - BDay())
df = df[["Operations File", "Market Value", "BasicAmt"]].dropna(
subset=["Operations File"]
) # missing Operations File means assignment usually
df = df.merge(
dawn_trades, how="left", left_on="Operations File", right_on="cpty_id"
)
missing_ids = df.loc[df.cpty_id.isnull(), "Operations File"]
if not missing_ids.empty:
raise ValueError(f"{missing_ids.tolist()} not in the database")
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 = df.append(
{
"Strategy": "M_CSH_CASH",
"Amount": collat - df.Amount.sum(),
"Currency": "USD",
},
ignore_index=True,
)
df["date"] = d - bus_day
return df.set_index("Strategy")
|
