from exchange import get_msgs, get_account
from exchangelib import Mailbox, Message, HTMLBody
from ftplib import FTP
from pathlib import Path
import os
import pandas as pd
from pandas.tseries.offsets import BDay
DAILY_DIR = Path(os.environ["DAILY_DIR"])
def download_files():
ftp = FTP('ftp.newedgegroup.com')
ftp.login('SerenitasGamma@USA', "SSqrrLL99")
ftp.cwd('OTC')
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)
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"]]
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': 'IG_TCDSCSH',
'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_CSH", -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('' \
'At Morgan Stanley:
' \
'{}' \
'At Societe Generale:
'
'{}' \
''.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@lmcg.com')]
)
m.send_and_save()
if __name__ == "__main__":
download_emails()
download_files()
d = (pd.Timestamp.today() - BDay()).normalize()
df_ms = ms_collateral(d)
df_sg = sg_collateral(d - BDay())
account = get_account('ghorel@lmcg.com')
send_email(account, df_ms, df_sg)