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
|
from exchange import get_msgs, get_account
from exchangelib import Mailbox, Message, HTMLBody
from ftplib import FTP
from pathlib import Path
from time import sleep
import os
import pandas as pd
from pandas.tseries.offsets import BDay
DAILY_DIR = Path(os.environ["DAILY_DIR"])
def download_files(d=None):
DATA_DIR = DAILY_DIR / "SG_reports"
with FTP('ftp.newedgegroup.com') as ftp:
ftp.login('SerenitasGamma@USA', "SSqrrLL99")
ftp.cwd('OTC')
if d is None:
for f in ftp.nlst():
if f.endswith("csv") and (("OTC_CASH_ACTIVITY" in f) or ("OTC_POSITIONS" in f)):
with open(DATA_DIR / f, "wb") as fh:
ftp.retrbinary('RETR ' + f, fh.write)
else:
i = 0
while i <= 20:
i +=1
file_list = ftp.nlst()
for report_type in ["OTC_CASH_ACTIVITY", "OTC_POSITIONS"]:
f = f"{d:%Y%m%d}_{report_type}.csv"
if f not in file_list:
sleep(500)
break
with open(DATA_DIR / f, "wb") as fh:
ftp.retrbinary('RETR ' + f, fh.write)
else:
break
def download_emails():
emails = get_msgs(path=["NYops"], subject_filter="SERCX **Daily")
DATA_DIR = DAILY_DIR / "MS_reports"
for msg in emails:
for attach in msg.attachments:
if 'NETSwaps' in attach.name:
fname = attach.name.split("_")[1]
with open(DATA_DIR / fname, "wb") as fh:
fh.write(attach.content)
def sg_collateral(d):
df_activity = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_CASH_ACTIVITY.csv")
df_position = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_POSITIONS.csv")
df_activity = (df_activity.loc[df_activity['Record Type'] == "VM",
["Ticket Reference", "Record Type", "Currency", "Amount"]].
set_index("Ticket Reference"))
df_position = df_position.set_index("Ticket Reference")[["Reference Entity", "Mtm Value"]]
# expired_trades
# df_position = df_position.append(
# pd.DataFrame({"Reference Entity": 'CDX-NAIGS29V1-5Y', "Mtm Value": 0.},
# index=['T2201711010000A3K20000045561220U']))
df = df_activity.join(df_position)
df['Collateral'] = df['Mtm Value'] - df['Amount']
d = {'ITRAXX': 'SER_ITRXCVCSH',
'IG': 'SER_IGCVECSH',
'HY': 'MBSCDSCSH'}
strat = []
r = []
for k, v in d.items():
r.append((v, df.loc[df['Reference Entity'].str.contains(k), 'Collateral'].sum(),
"EUR" if k == "ITRAXX" else "USD"))
return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency'])
def ms_collateral(d):
df = pd.read_excel(DAILY_DIR / "MS_reports" / f"{d:%Y%m%d}.xls" )
d = {'TRCDX': 'IGTCDSCSH',
'ABINT': 'MBSCDSCSH'}
r = []
acc = 0
for k, v in d.items():
amount = df.loc[df.trade_book == k, "collat_req_in_agr_ccy"].sum()
r.append((v, amount, "USD"))
acc += amount
r.append(["M_CSH_CASH", -6_810_000 - acc, "USD"])
return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency'])
def send_email(account, df_ms, df_sg):
content = HTMLBody('<html><body>' \
'<h3>At Morgan Stanley:</h3>' \
'{}' \
'<h3>At Societe Generale:</h3>'
'{}' \
'</body><html>'.format(df_ms.to_html(index=False), df_sg.to_html(index=False)))
m = Message(
account=account,
folder=account.sent,
subject='IAM booking',
body=content,
to_recipients=[Mailbox(email_address='serenitas.otc@sscinc.com')],
cc_recipients=['nyops@lmcg.com']
)
m.send_and_save()
if __name__ == "__main__":
download_emails()
d = (pd.Timestamp.today() - BDay()).normalize()
download_files(d)
df_ms = ms_collateral(d - BDay())
df_sg = sg_collateral(d)
account = get_account('ghorel@lmcg.com')
send_email(account, df_ms, df_sg)
|
