diff options
Diffstat (limited to 'python/collateral')
| -rw-r--r-- | python/collateral/__init__.py | 10 | ||||
| -rw-r--r-- | python/collateral/__main__.py | 85 | ||||
| -rw-r--r-- | python/collateral/baml_fcm.py | 86 | ||||
| -rw-r--r-- | python/collateral/citi.py | 129 | ||||
| -rw-r--r-- | python/collateral/common.py | 90 | ||||
| -rw-r--r-- | python/collateral/ms.py | 53 | ||||
| -rw-r--r-- | python/collateral/sg.py | 137 | ||||
| -rw-r--r-- | python/collateral/wells.py | 138 |
8 files changed, 728 insertions, 0 deletions
diff --git a/python/collateral/__init__.py b/python/collateral/__init__.py new file mode 100644 index 00000000..d33b2db4 --- /dev/null +++ b/python/collateral/__init__.py @@ -0,0 +1,10 @@ +import sys + +sys.path.append("..") +try: + from env import DAILY_DIR, LOG_DIR +except KeyError: + sys.exit("Please set 'DAILY_DIR' and 'LOG_DIR' in the environment") + +from exchange import ExchangeMessage +from dates import bus_day diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py new file mode 100644 index 00000000..3a326f27 --- /dev/null +++ b/python/collateral/__main__.py @@ -0,0 +1,85 @@ +import pandas as pd + +from importlib import import_module +from utils import SerenitasFileHandler +from utils.db import dawn_engine, dbconn + +from . import bus_day +from .common import get_dawn_trades, send_email +from pandas.tseries.offsets import BDay + +import argparse +import logging + +fh = SerenitasFileHandler("collateral_calc.log") +logger = logging.getLogger("collateral_calc") +logger.addHandler(fh) +logger.setLevel(logging.WARNING) + +parser = argparse.ArgumentParser() +parser.add_argument( + "workdate", + nargs="?", + type=lambda s: pd.datetime.strptime(s, "%Y-%m-%d").date(), + default=pd.Timestamp.today().normalize(), +) +parser.add_argument( + "-d", "--download", action="store_true", help="download counterparty reports" +) +parser.add_argument( + "-s", "--send-email", action="store_true", help="send email to Globeop" +) +args = parser.parse_args() +counterparties = ["citi", "ms", "gs", "baml_fcm", "wells"] + +if args.download: + for cp in counterparties: + cp_mod = import_module(f".{cp}", "collateral") + cp_mod.download_files() + +dawn_trades = get_dawn_trades(args.workdate, dawn_engine) + +df = {} +mapping = {"baml_fcm": "BAML", "wells": "WF"} +for cp in counterparties: + print(cp) + cp_mod = import_module("." + cp, "collateral") + if cp in ["baml_fcm", "wells"]: + positions = pd.read_sql_query( + "SELECT security_id, security_desc, maturity, " + "folder, notional, currency " + "FROM list_cds_positions_by_strat_fcm(%s, %s)", + dawn_engine, + params=(args.workdate.date(), mapping[cp]), + index_col=["security_id", "maturity"], + ) + else: + positions = dawn_trades + try: + df[cp.upper()] = cp_mod.collateral(args.workdate, positions, dawn_engine) + except FileNotFoundError as e: + logger.info(e) + df[cp.upper()] = cp_mod.collateral( + args.workdate - bus_day, positions, dawn_engine + ) + except ValueError as e: + logger.error(e) + if cp == "citi": + args.workdate = args.workdate - BDay() + +df = pd.concat(df, names=["broker", "strategy"]).reset_index() +df.strategy = df.strategy.str.replace("^(M_|SER_)?", "", 1) +df = df[["date", "broker", "strategy", "Amount", "Currency"]] +conn = dbconn("dawndb") +sql_str = ( + "INSERT INTO strategy_im VALUES(%s, %s, %s, %s, %s) " + "ON CONFLICT (date, strategy, broker) DO UPDATE " + "SET currency=EXCLUDED.currency, amount=EXCLUDED.amount" +) +with conn.cursor() as c: + for t in df.itertuples(index=False): + c.execute(sql_str, t) +conn.commit() +conn.close() +if args.send_email: + send_email(args.workdate, df) diff --git a/python/collateral/baml_fcm.py b/python/collateral/baml_fcm.py new file mode 100644 index 00000000..a7c960ed --- /dev/null +++ b/python/collateral/baml_fcm.py @@ -0,0 +1,86 @@ +from . import DAILY_DIR +from .common import compare_notionals +from paramiko import Transport, SFTPClient, RSAKey +import os.path +import pandas as pd +from sqlalchemy.exc import IntegrityError + + +def get_sftp_client(): + transport = Transport(("ftps.b2b.ml.com", 22)) + pkey = RSAKey.from_private_key_file(os.path.expanduser("~/.ssh/id_rsa_lmcg")) + transport.connect(username="lmcginvs", pkey=pkey) + return SFTPClient.from_transport(transport) + + +def download_files(d=None): + DATA_DIR = DAILY_DIR / "BAML_reports" + sftp = get_sftp_client() + for f in sftp.listdir("outgoing"): + local_file = DATA_DIR / f + if not local_file.exists(): + sftp.get(f"outgoing/{f}", localpath=DATA_DIR / f) + + +def collateral(d, positions, engine): + df = pd.read_csv( + DAILY_DIR + / "BAML_reports" + / f"OTC_Open_Positions_-_Credit_-_LMCG_{d:%Y%m%d}.CSV", + usecols=[ + "MTM", + "ACCRUEDCPN", + "VARMARGIN", + "REDCODE", + "NOTIONAL", + "EODSETTLEMENTPRICE", + "PERIOD", + "BUYSELL", + ], + index_col=["REDCODE"], + ) + df.PERIOD = pd.to_datetime(df.PERIOD.astype("str") + "20") + df = df.set_index("PERIOD", append=True) + df = df[df.EODSETTLEMENTPRICE.notnull()] + df["NOTIONAL"] = df.NOTIONAL.where(df.BUYSELL == "Buy", -df.NOTIONAL).astype( + "float" + ) + df["DIRTYUPFRONT"] = (df.MTM + df.ACCRUEDCPN) / df.NOTIONAL + df.index.names = ["security_id", "maturity"] + compare_notionals(df, positions, "BAML") + positions["dirtyupfront"] = df.reindex(positions.index)["DIRTYUPFRONT"] + positions["amount"] = positions["notional"] * positions["dirtyupfront"] + positions.folder = positions.folder.map( + { + "HEDGE_MBS": "MBSCDSCSH", + "SER_ITRXCURVE": "SER_ITRXCVCSH", + "SER_IGCURVE": "SER_IGCVECSH", + "HYOPTDEL": "COCSH", + "IGOPTDEL": "COCSH", + "IGINX": "TCSH", + "HYINX": "TCSH", + } + ) + df = ( + positions.groupby("folder") + .agg({"amount": "sum", "currency": "first"}) + .reset_index("folder") + ) + df.columns = ["Strategy", "Amount", "Currency"] + df_margin = pd.read_csv( + DAILY_DIR / "BAML_reports" / f"OTC_Moneyline_{d:%Y%m%d}.CSV", + usecols=["Statement Date", "AT CCY", "Initial Margin Requirement"], + parse_dates=["Statement Date"], + ) + df_margin.columns = ["date", "currency", "amount"] + df_margin["account"] = "V0NSCLMFCM" + try: + engine.execute( + "INSERT INTO fcm_im " + "VALUES(%(date)s, %(account)s, %(currency)s, %(amount)s)", + df_margin.iloc[-1].to_dict(), + ) + except IntegrityError: + pass + df["date"] = d + return df.set_index("Strategy") diff --git a/python/collateral/citi.py b/python/collateral/citi.py new file mode 100644 index 00000000..f7d0818d --- /dev/null +++ b/python/collateral/citi.py @@ -0,0 +1,129 @@ +import pandas as pd +import subprocess +from bs4 import BeautifulSoup +from pandas.tseries.offsets import BDay +from . import ExchangeMessage, DAILY_DIR, bus_day + + +def load_file(d): + try: + fname = next( + (DAILY_DIR / "CITI_reports").glob( + f"262966_Portfolio_{d.strftime('%Y%m%d')}*" + ) + ) + except StopIteration: + raise FileNotFoundError(f"CITI file not found for date {d}") + return pd.read_excel(fname, skiprows=6, skipfooter=2) + + +def download_files(count=20): + em = ExchangeMessage() + emails = em.get_msgs( + path=["NYops", "Margin Calls Citi"], count=count, subject__startswith="262966" + ) + DATA_DIR = DAILY_DIR / "CITI_reports" + for msg in emails: + for attach in msg.attachments: + fname = attach.name + p = DATA_DIR / fname + if not p.exists(): + p.write_bytes(attach.content) + + +def load_pdf(file_path): + proc = subprocess.run( + ["pdftohtml", "-xml", "-stdout", "-i", file_path.as_posix()], + capture_output=True, + ) + soup = BeautifulSoup(proc.stdout, features="lxml") + l = soup.findAll("text") + l = sorted(l, key=lambda x: (int(x["top"]), int(x["left"]))) + return l + + +def get_col(l, top, bottom, left, right): + return [ + c.text + for c in l + if int(c["left"]) >= left + and int(c["left"]) < right + and int(c["top"]) >= top + and int(c["top"]) < bottom + ] + + +def parse_num(s): + s = s.replace(",", "") + if s[0] == "(": + return -float(s[1:-1]) + else: + return float(s) + + +def get_df(l, col1, col2, col3): + df = pd.DataFrame( + {"amount": get_col(l, *col2), "currency": get_col(l, *col3)}, + index=get_col(l, *col1), + ) + df.amount = df.amount.apply(parse_num) + df.index = df.index.str.lstrip() + return df + + +def get_total_collateral(d): + try: + fname = next( + (DAILY_DIR / "CITI_reports").glob( + f"262966_MarginNotice_{d.strftime('%Y%m%d')}_*.pdf" + ) + ) + except StopIteration: + raise FileNotFoundError(f"CITI file not found for date {d.date()}") + l = load_pdf(fname) + col1 = (370, 500, 70, 100) + col2 = (370, 500, 100, 500) + col3 = (370, 500, 500, 600) + + variation_margin = get_df(l, col1, col2, col3) + anchor = next(c for c in l if c.text == "Non Regulatory Initial Margin") + top = int(anchor["top"]) + 10 + bottom = top + 150 + col1 = (top, bottom, 70, 100) + col2 = (top, bottom, 100, 505) + col3 = (top, bottom, 505, 600) + initial_margin = get_df(l, col1, col2, col3) + return ( + variation_margin.loc["VM Total Collateral", "amount"] + + initial_margin.loc["Non Reg IM Total Collateral", "amount"] + ) + + +def collateral(d, dawn_trades, *args): + df = load_file(d) + collat = get_total_collateral(d - BDay()) + df = df[["Operations File", "Market Value", "BasicAmt"]].dropna( + subset=["Operations File"] + ) # missing Operations File means assignment usually + df = df.merge( + dawn_trades, how="left", left_on="Operations File", right_on="cpty_id" + ) + missing_ids = df.loc[df.cpty_id.isnull(), "Operations File"] + if not missing_ids.empty: + raise ValueError(f"{missing_ids.tolist()} not in the database") + df = df.groupby("folder").sum() + df = df.sum(axis=1).to_frame(name="Amount") + df["Currency"] = "USD" + df = df.reset_index() + df.columns = ["Strategy", "Amount", "Currency"] + df.Amount *= -1 + df = df.append( + { + "Strategy": "M_CSH_CASH", + "Amount": collat - df.Amount.sum(), + "Currency": "USD", + }, + ignore_index=True, + ) + df["date"] = d - bus_day + return df.set_index("Strategy") diff --git a/python/collateral/common.py b/python/collateral/common.py new file mode 100644 index 00000000..de63f35e --- /dev/null +++ b/python/collateral/common.py @@ -0,0 +1,90 @@ +import logging +import pandas as pd +from exchangelib import HTMLBody +from . import ExchangeMessage + +logger = logging.getLogger(__name__) + + +def compare_notionals(df, positions, fcm: str): + check_notionals = ( + positions.groupby(level=["security_id", "maturity"])[["notional"]] + .sum() + .join(df["NOTIONAL"], how="left") + ) + diff_notionals = check_notionals[ + check_notionals.notional != check_notionals.NOTIONAL + ] + if not diff_notionals.empty: + logger.error(f"Database and {fcm} FCM know different notionals") + for t in diff_notionals.itertuples(): + logger.error( + f"{t.Index[0]}\t{t.Index[1].date()}\t{t.notional}\t{t.NOTIONAL}" + ) + + +def get_dawn_trades(d, engine): + df_cds = pd.read_sql_query( + "SELECT cpty_id, folder FROM cds " + "WHERE cpty_id IS NOT NULL AND trade_date <= %s", + engine, + params=(d,), + ) + df_swaptions = pd.read_sql_query( + "SELECT cpty_id, folder FROM swaptions " + "WHERE cpty_id IS NOT NULL " + "AND trade_date <= %s", + engine, + params=(d,), + ) + df_caps = pd.read_sql_query( + "SELECT cpty_id, folder FROM capfloors " + "WHERE cpty_id IS NOT NULL " + "AND trade_date <= %s", + engine, + params=(d,), + ) + df = pd.concat([df_cds, df_swaptions, df_caps]) + df = df.replace( + { + "folder": { + "IGREC": "COCSH", + "IGPAYER": "COCSH", + "HYPAYER": "COCSH", + "HYREC": "COCSH", + "STEEP": "IRDEVCSH", + "FLAT": "IRDEVCSH", + "MBSCDS": "MBSCDSCSH", + "IGMEZ": "TCSH", + "IGSNR": "TCSH", + "IGEQY": "TCSH", + "HYMEZ": "TCSH", + "HYEQY": "TCSH", + "BSPK": "TCSH", + } + } + ) + return df + + +def send_email(d, df): + pd.set_option("display.float_format", "{:.2f}".format) + df = df.drop("date", axis=1).set_index("broker") + cp_mapping = { + "CITI": "Citi", + "MS": "Morgan Stanley", + "GS": "Goldman Sachs", + "BAML_FCM": "Baml FCM", + # "BAML_ISDA": "Baml OTC", + "WELLS": "Wells Fargo", + } + html = "<html><body>" + for cp, name in cp_mapping.items(): + html += f"<h3> At {name}:</h3>\n{df.loc[cp].to_html(index=False)}" + em = ExchangeMessage() + em.send_email( + f"IAM booking {d:%Y-%m-%d}", + HTMLBody(html), + ["serenitas.otc@sscinc.com"], + ["nyops@lmcg.com"], + ) diff --git a/python/collateral/ms.py b/python/collateral/ms.py new file mode 100644 index 00000000..f71356d6 --- /dev/null +++ b/python/collateral/ms.py @@ -0,0 +1,53 @@ +import pandas as pd +from . import ExchangeMessage, DAILY_DIR + + +def download_files(count=20): + em = ExchangeMessage() + emails = em.get_msgs( + path=["NYops", "Margin calls MS"], + count=count, + subject__contains="SERCX **Daily", + ) + 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 collateral(d, dawn_trades, *args): + 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") + df = df.dropna(subset=["trade_ccy"]) + df = df.merge(dawn_trades, how="left", left_on="trade_id", right_on="cpty_id") + missing_ids = df.loc[df.cpty_id.isnull(), "trade_id"] + if not missing_ids.empty: + raise ValueError(f"{missing_ids.tolist()} not in the database") + df = df.groupby("folder")[["collat_req_in_agr_ccy"]].sum() + df["Currency"] = "USD" + df = df.reset_index() + df.columns = ["Strategy", "Amount", "Currency"] + df = df.append( + { + "Strategy": "M_CSH_CASH", + "Amount": -collat - df.Amount.sum(), + "Currency": "USD", + }, + ignore_index=True, + ) + df["date"] = d + return df.set_index("Strategy") diff --git a/python/collateral/sg.py b/python/collateral/sg.py new file mode 100644 index 00000000..064890b6 --- /dev/null +++ b/python/collateral/sg.py @@ -0,0 +1,137 @@ +from . import DAILY_DIR +from paramiko import Transport, SFTPClient + + +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: + logger.info("File not here yet, trying again in 500s...") + logger.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 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)", + dawn_engine, + 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", dawn_engine, if_exists="append", index=False) + except IntegrityError: + pass + df["date"] = d + return df diff --git a/python/collateral/wells.py b/python/collateral/wells.py new file mode 100644 index 00000000..95fbfe50 --- /dev/null +++ b/python/collateral/wells.py @@ -0,0 +1,138 @@ +import pandas as pd +import socket +from . import DAILY_DIR +from .common import compare_notionals +from paramiko import Transport, SFTPClient +from sqlalchemy.exc import IntegrityError +from ssh2.session import Session +from ssh2.sftp import LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR, LIBSSH2_SFTP_S_IFREG + + +def get_wells_sftp_client(): + transport = Transport(("axst.wellsfargo.com", 10022)) + transport.connect(username="LMCHsWC6EP", password="HI2s2h19+") + return SFTPClient.from_transport(transport) + + +def get_wells_sftp_client2(): + sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM) + sock.connect(("axst.wellsfargo.com", 10022)) + session = Session() + session.handshake(sock) + session.userauth_password("LMCHsWC6EP", "HI2s2h19+") + sftp = session.sftp_init() + return sftp + + +def download_files2(d=None): + DATA_DIR = DAILY_DIR / "Wells_reports" + sftp = get_wells_sftp_client() + base_dir = "/RECEIVE/339425_DATO2" + for f in sftp.listdir(base_dir): + if not (DATA_DIR / f).exists(): + sftp.get(f"{base_dir}/{f}", localpath=DATA_DIR / f) + + +def download_files(d=None): + DATA_DIR = DAILY_DIR / "Wells_reports" + sftp = get_wells_sftp_client2() + files = [] + with sftp.opendir("/RECEIVE/339425_DATO2") as fh: + for size, buf, attrs in fh.readdir(): + if attrs.permissions & LIBSSH2_SFTP_S_IFREG: + files.append(buf.decode()) + for f in files: + local_file = DATA_DIR / f + if not local_file.exists(): + with sftp.open( + f"/RECEIVE/339425_DATO2/{f}", LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR + ) as remote_handle, local_file.open("wb") as local_handle: + for size, data in remote_handle: + local_handle.write(data) + + +def collateral(d, positions, engine): + account = "A5882186" + file_name = ( + DAILY_DIR + / "Wells_reports" + / f"OTC_CDS_Position_Activity_{account}_{d:%m%d%Y}.csv" + ) + try: + df = pd.read_csv( + file_name, + usecols=[ + "TENOR", + "MARKET_VALUE_NPV", + "PAIR_CLIP", + "BUY_SELL", + "NOTIONAL", + "MATURITY_DATE", + "TRADE_PRICE", + ], + parse_dates=["MATURITY_DATE"], + index_col=["PAIR_CLIP", "MATURITY_DATE"], + ) + except ValueError: + # backpopulated files have a different format... + df = pd.read_csv( + file_name, + usecols=[ + "Tenor", + "NPV", + "Reference_Entity_ID", + "Fixed_Rate_Notional_Buy", + "Amount", + "Scheduled_Termination_Date", + ], + parse_dates=["Scheduled_Termination_Date"], + index_col=["Reference_Entity_ID", "Scheduled_Termination_Date"], + ) + df = df.rename( + columns={"Tenor": "TENOR", "NPV": "MARKET_VALUE_NPV", "Amount": "NOTIONAL"} + ) + df["BUY_SELL"] = 1 + df.loc[df.Fixed_Rate_Notional_Buy.isnull(), "BUY_SELL"] = 2 + del df["Fixed_Rate_Notional_Buy"] + df = df[df.TRADE_PRICE != 0.0] + del df["TRADE_PRICE"] + df["NOTIONAL"] = df.NOTIONAL.where(df.BUY_SELL == 1, -df.NOTIONAL).astype("float") + df["DIRTYUPFRONT"] = df.MARKET_VALUE_NPV / df.NOTIONAL + df.index.names = ["security_id", "maturity"] + compare_notionals(df, positions, "Wells") + positions = positions.join(df, how="left") + positions["amount"] = positions["notional"] * positions["DIRTYUPFRONT"] + positions.folder = positions.folder.map( + { + "HEDGE_MBS": "MBSCDSCSH", + "SER_ITRXCURVE": "SER_ITRXCVCSH", + "SER_IGCURVE": "SER_IGCVECSH", + "HYOPTDEL": "COCSH", + "IGOPTDEL": "COCSH", + "IGINX": "TCSH", + "HYINX": "TCSH", + } + ) + df = ( + positions.groupby("folder") + .agg({"amount": "sum", "currency": "first"}) + .reset_index("folder") + ) + df.columns = ["Strategy", "Amount", "Currency"] + df_margin = pd.read_csv( + DAILY_DIR + / "Wells_reports" + / f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv", + usecols=["CURRENCY_NAME", "CURRENT_IM", "VALUE_DATE"], + parse_dates=["VALUE_DATE"], + index_col=["CURRENCY_NAME"], + ) + try: + engine.execute( + "INSERT INTO fcm_im " "VALUES(%s, 'WFNSCLMFCM', 'USD', %s)", + df_margin.loc["ZZZZZ", ["VALUE_DATE", "CURRENT_IM"]].tolist(), + ) + except IntegrityError: + pass + df["date"] = d + return df.set_index("Strategy") |