diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/download_socgen.py | 85 | ||||
| -rw-r--r-- | python/exchange.py | 27 | ||||
| -rw-r--r-- | python/parse_gs_exchange.py | 19 |
3 files changed, 113 insertions, 18 deletions
diff --git a/python/download_socgen.py b/python/download_socgen.py new file mode 100644 index 00000000..c917926e --- /dev/null +++ b/python/download_socgen.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) diff --git a/python/exchange.py b/python/exchange.py new file mode 100644 index 00000000..23943c4f --- /dev/null +++ b/python/exchange.py @@ -0,0 +1,27 @@ +from exchangelib import Credentials, Configuration, Account, DELEGATE +from pathlib import Path +import json + +def get_account(email_address): + with open(Path('.credentials') / (email_address + '.json')) as fh: + creds = json.load(fh) + credentials = Credentials(**creds) + config = Configuration(server='autodiscover.lmcg.com', credentials=credentials) + return Account(primary_smtp_address=email_address, config=config, + autodiscover=False, access_type=DELEGATE) + +def get_msgs(account=None, email_address='ghorel@lmcg.com', count=None, path=['GS', 'Swaptions'], + subject_filter=None): + if account is None: + account = get_account(email_address) + folder = account.inbox + for p in path: + folder /= p + if subject_filter is not None: + folder = folder.filter(subject__contains=subject_filter) + if count: + for msg in folder.all().order_by('-datetime_sent')[:count]: + yield msg + else: + for msg in folder.all().order_by('-datetime_sent'): + yield msg diff --git a/python/parse_gs_exchange.py b/python/parse_gs_exchange.py index 9f22ac0f..a39b034a 100644 --- a/python/parse_gs_exchange.py +++ b/python/parse_gs_exchange.py @@ -1,33 +1,16 @@ from db import dbconn -from exchangelib import Credentials, Mailbox, Configuration, Account, DELEGATE +from exchange import get_msgs from pytz import timezone from parse_emails import write_todb import datetime -import json import logging -import os import pandas as pd import re class ParseError(Exception): pass -def get_msgs(email_address='ghorel@lmcg.com', count=None): - with open(os.path.join('.credentials', email_address + '.json')) as fh: - creds = json.load(fh) - credentials = Credentials(**creds) - config = Configuration(server='autodiscover.lmcg.com', credentials=credentials) - account = Account(primary_smtp_address=email_address, config=config, - autodiscover=False, access_type=DELEGATE) - folder = account.root.get_folder_by_name('GS').get_folder_by_name('Swaptions') - if count: - for msg in folder.all().order_by('-datetime_sent')[:count]: - yield msg - else: - for msg in folder.all().order_by('-datetime_sent'): - yield msg - def parse_email(email, fwd_index): m = re.search("(IG|HY)(\d{2}) 5y (?:.*)SWAPTION (?:UPDATE|CLOSES|CLOSE) - Ref\D+(.+)$", email.subject) |