path: root/python/collateral_calc.py

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 pathlib import Path
from time import sleep
from pandas.tseries.offsets import BDay
from paramiko import Transport, SFTPClient
from download_emails import GmailMessage
from sqlalchemy.exc import IntegrityError

DAILY_DIR = Path(os.environ["DAILY_DIR"])
logging.basicConfig(filename=os.path.join(os.getenv("LOG_DIR"),
                                          'collateral_calc.log'),
                    level=logging.WARNING,
                    format='%(asctime)s %(message)s')

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_sftp_files(d=None,
                        report_types=["OTC_CASH_ACTIVITY", "OTC_POSITIONS",
                                      "OTC_MARGIN", "OTC_MARGIN_EX_DEF",
                                      "OTC_STATEMENT"],
                        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("OTC_STATEMENT.xls"):
                print(f)
                sftp.get(f"OTC/{f}", localpath=DATA_DIR / f)
            else:
                for report_type in report_types[:-1]:
                    if f.endswith(f"{report_type}.csv"):
                        print(f)
                        sftp.get(f"OTC/{f}", localpath=DATA_DIR / f)
                else:
                    continue

    else:
        file_list = sftp.listdir('OTC')
        for report_type in report_types:
            if report_type == "OTC_STATEMENT":
                f = f"{d:%Y%m%d}_{report_type}.xls"
            else:
                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_from_gmail():
    DATA_DIR = DAILY_DIR / "MS_reports"
    for msg in GmailMessage.List_msg_ids('Globeop/Operations'):
        try:
            message = GmailMessage.from_id(msg['id'])
            subject = message['subject']
            if 'SERCX **Daily' in subject:
                for attach in message.iter_attachments():
                    fname = attach.get_filename()
                    if 'NETSwaps' in fname:
                        fname = "Trade_Detail_" + fname.split("_")[1]
                    elif 'NET_Collateral' in fname:
                        fname = "Collateral_Detail_" + fname.rsplit("_", 1)[1]
                    else:
                        continue
                    p = DATA_DIR / fname
                    if p.exists():

                        continue
                    else:
                        p.write_bytes(part.get_payload(decode=True))
        except (KeyError, UnicodeDecodeError) as e:
            logging.error("error decoding " + msg['id'])
            continue

def download_ms_emails(count=20):
    emails = get_msgs(path=["NYops", "Margin calls MS"],
                      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
            p =  DATA_DIR / fname
            if not p.exists():
                p.write_bytes(attach.content)


def download_gs_emails(count=20):
    emails = get_msgs(path=["NYops", "Margin calls"],
                      subject_filter="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'):
                p = DATA_DIR / fname
                if not p.exists():
                    p.write_bytes(attach.content)


def sg_collateral(d):
    df_activity = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_CASH_ACTIVITY.csv",
                              usecols=["Ticket Reference", "Record Type", "Currency", "Amount"])
    df_position = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_POSITIONS.csv",
                              usecols=["Ticket Reference", "Reference Entity", "Mtm Value"])
    df_activity = (df_activity.loc[df_activity['Record Type'] == "VM"].
                   set_index("Ticket Reference"))
    df_margin = pd.read_csv(DAILY_DIR / "SG_reports" /  f"{d:%Y%m%d}_OTC_MARGIN_EX_DEF.csv",
                            usecols=["Currency", "SG IMR"])
    df_position = df_position.set_index("Ticket Reference")
    # 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)
    # expired trade (need to figure out how to get them from the report)
    # df.loc['N201811090000A3K215946925849228U1', 'Mtm Value'] = 0.
    # df.loc['N201811090000A3K215946925849228U1', 'Reference Entity'] = 'CDX-NAIGS31V1-5Y'

    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)

    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',
                                   'IGOPTDEL': 'IGCDSCSH'})
    df_margin['account'] = 'SGNSCLMASW'
    df_margin = df_margin.rename(columns={'SG IMR': 'amount',
                                          'Currency': 'currency'})
    df_margin['date'] = d
    try:
        df_margin.to_sql("fcm_im", dbengine("dawndb"), if_exists='append', index=False)
    except IntegrityError:
        pass
    return df


def ms_collateral(d):
    df = pd.read_excel(DAILY_DIR / "MS_reports" / f"Collateral_Detail_{d:%Y%m%d}.xls")
    collat = df.loc[1, 'coll_val_ccy'].replace(",", "")
    if "(" in collat:
        collat = collat[1:-1]
        collat = - float(collat)
    else:
        collat = float(collat)
    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',
         'ACUSD': 'IRDEVCSH'}
    df.trade_book = df.trade_book.replace(d)
    df = df.groupby('trade_book')[["collat_req_in_agr_ccy"]].sum()
    df['Currency'] = "USD"
    df = df.reset_index()
    col_names= ['Strategy', 'Amount', 'Currency']
    df.columns = col_names
    return pd.concat([df,
                      pd.DataFrame.
                      from_records([('M_CSH_CASH', -collat - df.Amount.sum(), "USD")],
                                   columns=col_names)])


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']
    # TODO: need to break down SWO_CDIXOP between IG and HY
    df.Strategy = df.Strategy.replace({'SWAP_CDINDT': 'IGTCDSCSH',
                                       'GENERIC_IRSIRD': 'IRDEVCSH',
                                       'SWO_CDIXOP': 'IGCDSCSH'})
    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('<html><body>'
                       '<h3>At Morgan Stanley:</h3>'
                       '{}'
                       '<h3>At Societe Generale:</h3>'
                       '{}'
                       '<h3>At Goldman Sachs:</h3>'
                       '{}'
                       '</body><html>'.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_sftp_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)