import os import logging import pandas as pd from db import dbengine 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 from pandas.tseries.offsets import BDay from paramiko import Transport, SFTPClient DAILY_DIR = Path(os.environ["DAILY_DIR"]) def get_sftp_client(): transport = Transport(('prmssp.amer.sgcib.com', 22)) transport.connect(username='SerenitasGamma@USA', password='SSqrrLL99') return SFTPClient.from_transport(transport) def download_files(d=None, report_types=["OTC_CASH_ACTIVITY", "OTC_POSITIONS", "OTC_MARGIN"], retry_count=0): if retry_count > 20: return 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"): for report_type in report_types: if f.endswith(f"{report_type}.csv"): with open(DATA_DIR / f, "wb") as fh: ftp.retrbinary('RETR ' + f, fh.write) else: file_list = ftp.nlst() for report_type in report_types: f = f"{d:%Y%m%d}_{report_type}.csv" if f not in file_list: logging.info("File not here yet, trying again in 500s...") logging.info(f"Try count: {retry_count}") sleep(500) ftp.close() download_files(d, report_types, retry_count + 1) else: with open(DATA_DIR / f, "wb") as fh: ftp.retrbinary('RETR ' + f, fh.write) def download_sftp_files(d=None, report_types=["OTC_CASH_ACTIVITY", "OTC_POSITIONS", "OTC_MARGIN"], retry_count=0): if retry_count > 20: return DATA_DIR = DAILY_DIR / "SG_reports" sftp = get_sftp_client() if d is None: for f in sftp.listdir('OTC'): if f.endswith("csv"): for report_type in report_types: if f.endswith(f"{report_type}.csv"): sftp.get(f"OTC/{f}", localpath=DATA_DIR / f) else: file_list = sftp.listdir('OTC') for report_type in report_types: f = f"{d:%Y%m%d}_{report_type}.csv" if f not in file_list: logging.info("File not here yet, trying again in 500s...") logging.info(f"Try count: {retry_count}") sleep(500) sftp.close() download_sftp_files(d, report_types, retry_count + 1) else: sftp.get(f"OTC/{f}", localpath=DATA_DIR / f) sftp.close() def download_ms_emails(count=20): emails = get_msgs(path=["NYops"], subject_filter="SERCX **Daily", count=count) DATA_DIR = DAILY_DIR / "MS_reports" for msg in emails: for attach in msg.attachments: if 'NETSwaps' in attach.name: fname = "Trade_Detail_" + attach.name.split("_")[1] elif 'NET_Collateral' in attach.name: fname = "Collateral_Detail_" + attach.name.rsplit("_", 1)[1] else: continue with open(DATA_DIR / fname, "wb") as fh: fh.write(attach.content) def download_gs_emails(count=20): emails = get_msgs(path=["NYops", "Margin calls"], subject_filter="Regulatory VM Margin", count=count) DATA_DIR = DAILY_DIR / "GS_reports" for msg in emails: for attach in msg.attachments: fname = attach.name if fname.endswith('xls'): 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'] ref_entity = df['Reference Entity'].str.split("-", expand=True) del ref_entity[0] ref_entity.columns = ['to_split', 'tenor'] ref_entity = ref_entity.join(ref_entity['to_split'].str. extract("(IG|HY|EUROPE)S(\d+)V(\d+)$", expand=True)) del ref_entity['to_split'] ref_entity.columns = ['tenor', 'index_type', 'series', 'version'] ref_entity.index_type[ref_entity.index_type == "EUROPE"] = "EU" df = df.join(ref_entity) df = df.groupby(['index_type', 'series', 'tenor'])['Collateral'].sum() positions = pd.read_sql_query("SELECT security_desc, folder, notional, currency " "FROM list_cds_positions_by_strat(%s)", dbengine("dawndb"), params=(d.date(),)) instruments = positions.security_desc.str.split(expand=True)[[1, 3, 4]] instruments.columns = ['index_type', 'series', 'tenor'] instruments.series = instruments.series.str.extract("S(\d+)") instruments.index_type[instruments.index_type == "EUR"] = "EU" positions = positions.join(instruments) del positions['security_desc'] positions = positions.set_index(['index_type', 'series', 'tenor']) df = positions.join(df) d = {} def f(g): g.Collateral = g.Collateral * g.notional / g.notional.sum() return g df = (df.groupby(level=['index_type', 'series', 'tenor']). apply(f)) df = df.groupby(['folder']).agg({'Collateral': 'sum', 'currency': 'first'}) df = df.reset_index('folder') df = df.rename(columns= {'folder': 'Strategy', 'currency': 'Currency', 'Collateral': 'Amount'}) df.Strategy = df.Strategy.map({'HEDGE_MBS': 'MBSCDSCSH', 'SER_ITRXCURVE': 'SER_ITRXCVCSH', 'SER_IGCURVE': 'SER_IGCVECSH', 'HYOPTDEL': 'HYCDSCSH'}) return df def ms_collateral(d): df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Collateral_Detail_{d:%Y%m%d}.xls") collat = float(df.loc[1, 'coll_val_ccy'].replace(",", "")) df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Trade_Detail_{d:%Y%m%d}.xls" ) d = {'TRCDX': 'IGTCDSCSH', 'ABINT': 'MBSCDSCSH', 'ACSWN': 'IRDEVCSH', 'ABOPB': 'HYCDSCSH'} 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", -collat - acc, "USD"]) return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency']) def load_gs_file(d, pattern): try: fname = next((DAILY_DIR / "GS_reports"). glob(f"{pattern}*{d.strftime('%d_%b_%Y')}*")) except StopIteration: raise FileNotFoundError(f"GS {pattern} file not found for date {d}") df = pd.read_excel(fname, skiprows=9, skipfooter=77) return df def gs_collateral(d): df = load_gs_file(d, "Collateral_Detail") collateral = float(df.Quantity) df = load_gs_file(d, "Trade_Detail") df = df[['Transaction Type', 'NPV (USD)', 'Initial Margin Required']] df = df.groupby('Transaction Type').sum() df = df.sum(axis=1).to_frame(name='Amount') df['Currency'] = 'USD' df = df.reset_index() df.columns = ['Strategy', 'Amount', 'Currency'] df.Strategy = df.Strategy.replace({'SWAP_CDINDT': 'IGTCDSCSH', 'GENERIC_IRSIRD': 'IRDEVCSH'}) df.Amount *= -1 df = df.append({'Strategy': "M_CSH_CASH", 'Amount': -collateral - df.Amount.sum(), 'Currency': "USD"}, ignore_index=True) return df def send_email(account, df_ms, df_sg, df_gs): pd.set_option('display.float_format', '{:.2f}'.format) content = HTMLBody('' '

At Morgan Stanley:

' '{}' '

At Societe Generale:

' '{}' '

At Goldman Sachs:

' '{}' ''.format(df_ms.to_html(index=False), df_sg.to_html(index=False), df_gs.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_ms_emails() download_gs_emails() d = (pd.Timestamp.today() - BDay()).normalize() download_files(d) try: df_ms = ms_collateral(d) except FileNotFoundError as e: logging.info(e) df_ms = ms_collateral(d - BDay()) df_sg = sg_collateral(d) try: df_gs = gs_collateral(d) except FileNotFoundError as e: logging.info(e) df_gs = gs_collateral(d - BDay()) account = get_account('ghorel@lmcg.com') send_email(account, df_ms, df_sg, df_gs)