1 files changed, 85 insertions, 0 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py
new file mode 100644
index 00000000..c917926e
--- /dev/null
+++ b/python/collateral_calc.py
@@ -0,0 +1,85 @@
+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('<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@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)