import psycopg2 import os import datetime import csv import pdb from common import sanitize_float from db import dbconn from contextlib import closing import logging import re import sys logger = logging.getLogger(__name__) def convertToNone(s): return None if s in ["", "-", "NR"] else s def insert_new_cusip(conn, line): if line['Pari-Passu Tranches']: line['Pari-Passu Tranches'] = line['Pari-Passu Tranches'].split(",") to_insert = (line['CUSIP'], line['ISIN'], line['Bloomberg Ticker'], line['dealname'], line['tranche'], line['Pari-Passu Tranches']) for key in ['Orig Balance', 'Orig Attachment Point', 'Orig Detachment Point', 'Floater Spread/Margin']: if line[key]: line[key] = sanitize_float(line[key]) line[key] = convertToNone(line[key]) to_insert += (line['Orig Balance'], line.get('Orig Moody'), line['Orig Attachment Point'], line['Orig Detachment Point'], line['Floater Index'], line['Floater Spread/Margin'], line['Type']) sqlstr = ("INSERT INTO cusip_ref(Cusip, ISIN, bloomberg_ticker, dealname, " "tranche, paripassu_tranches, Orig_Balance, Orig_Moody, Orig_Attach, " "Orig_Detach, Floater_Index, Spread, type) VALUES({0}) " "RETURNING cusip_id".format(",".join(["%s"] * 13))) with conn.cursor() as c: try: c.execute(sqlstr, to_insert) cusip_id, = c.fetchone() return cusip_id except psycopg2.DataError as e: logger.error(e) conn.commit() def upload_cusip_data(conn, filename): dealupdate = {} with open( filename, "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') data = [] deals_to_update = [] for line in dr: if "ISIN" not in line: line['ISIN'] = None sp = line["Tranche"].split(",") if len(sp) == 2: line["dealname"], line["tranche"] = sp else: continue line["dealname"] = line["dealname"].lower() dealname = line['dealname'] line = {k: convertToNone(v) for k, v in line.items()} if dealname not in dealupdate: with conn.cursor() as c: c.execute("SELECT max(\"Latest Update\") FROM clo_universe " "WHERE dealname = %s", (dealname,)) try: dealupdate[dealname], = c.fetchone() except TypeError: logging.error(f'deal:{dealname} not in database') continue sqlstring = "SELECT cusip_id FROM cusip_ref WHERE cusip=%s and dealname=%s" with conn.cursor() as c: c.execute(sqlstring, (line['CUSIP'], dealname)) r = c.fetchone() if r is None: try: cusip_id = insert_new_cusip(conn, line) except ValueError: continue else: cusip_id = r[0] sqlstring = "SELECT max(updatedate) FROM cusip_update WHERE cusip_id = %s" with conn.cursor() as c: c.execute(sqlstring, (cusip_id,)) curr_date, = c.fetchone() if curr_date is None or curr_date < dealupdate[dealname]: try: for key in ['Curr Balance', 'Curr Attachment Point (def at MV)', 'Curr Detachment Point (def at MV)', 'Factor', 'Coupon']: if line[key]: line[key] = sanitize_float(line[key]) line[key] = convertToNone(line[key]) except ValueError: continue line['Curr Moody'] = line.get('Curr Moody') or line.get('Orig Moody') sqlstring = "INSERT INTO cusip_update VALUES({0})".format(",".join(["%s"] * 8)) to_insert = (cusip_id, line['Curr Balance'], line['Factor'], line['Coupon'], line['Curr Moody'], line['Curr Attachment Point (def at MV)'], line['Curr Detachment Point (def at MV)'], dealupdate[dealname]) with conn.cursor() as c: try: c.execute(sqlstring, to_insert) except (psycopg2.DataError, psycopg2.IntegrityError) as e: logger.error(e) logger.debug("uploaded: {0}".format(line['CUSIP'])) conn.commit() def upload_deal_data(conn, filename): sqlstr = 'SELECT dealname, array_agg("Latest Update") FROM clo_universe GROUP BY dealname' with conn.cursor() as c: c.execute(sqlstr) deallist1 = dict(c) sqlstr = "SELECT dealname FROM deal_indicative" with conn.cursor() as c: c.execute(sqlstr) deallist2 = [d[0] for d in c] conn.commit() with open(filename, "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') data = [] for line in dr: if not line['Deal Name, Tranche Name'] or (line['Deal Name, Tranche Name'] == 'Unknown Security'): continue if not line['Latest Update']: continue for key in line.keys(): line[key] = convertToNone(line[key]) ##simpler names line["CDOpercent"] = line["CDO Pct of Assets that are Structured Finance Obligations"] line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"] line["Paid Down"] = None if "Paid Down" in line["Latest Update"]: line["Paid Down"] = re.sub("Paid Down: ","", line["Latest Update"]) line["Latest Update"] = line["Paid Down"] for field in ["Deal Closing Date", "Deal Termination Date", "Reinv End Date", \ "Latest Update", "Pay Day", "Deal First Pay Date", "Paid Down", "Deal's Last Refi Date"]: if line[field]: try: line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date() except ValueError: logger.error("Can't parse date {}".format(line[field])) pdb.set_trace() if line["Pay Day"]: line["Pay Day"] = line["Pay Day"].day for key in ["Principal Collection Account", "Interest Collection Account", "Curr Deal Bal", "Tranche Curr Bal", "CDOpercent", "defaultedbal", "Orig Deal Bal", "Tranche Orig Bal"]: if line[key]: line[key] = sanitize_float(line[key]) line['Deal/Tranche ID'] = line['Deal/Tranche ID'].lower() dealname = line['Deal/Tranche ID'] if dealname not in deallist2: sqlstr = "INSERT INTO deal_indicative VALUES( %(Deal/Tranche ID)s, " \ "%(Deal Name)s, %(Collateral Manager)s, " \ "%(Deal Closing Date)s, %(Pay Day)s, " \ "%(Deal First Pay Date)s, %(Paid Down)s)" else: #we always update paid_down sqlstr = 'UPDATE deal_indicative SET paid_down=%(Paid Down)s ' \ 'WHERE dealname=%(Deal/Tranche ID)s' if line['Deal CUSIP List']: line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",") try: with conn.cursor() as c: c.execute(sqlstr, line) except (psycopg2.DataError, KeyError) as detail: logger.error(detail) continue if dealname not in deallist1 or line['Latest Update'] not in deallist1[dealname]: sqlstring = \ "INSERT INTO clo_universe " \ "VALUES (%(Deal/Tranche ID)s, %(Curr Deal Bal)s, %(Tranche Curr Bal)s, " \ "%(Tranche Factor)s, %(Principal Collection Account)s, " \ "%(Interest Collection Account)s, %(CDOpercent)s, %(defaultedbal)s, " \ "%(Coupon)s, %(Latest Update)s, %(Deal's Last Refi Date)s, " \ "%(Deal CUSIP List)s, %(Deal Termination Date)s, %(Reinv End Date)s, " \ "%(Orig Deal Bal)s, %(Tranche Orig Bal)s)" try: with conn.cursor() as c: c.execute(sqlstring, line) deallist1[dealname] = [line['Latest Update']] except (psycopg2.DataError, KeyError) as detail: logger.error(detail) pdb.set_trace() conn.commit() if __name__ == "__main__": if len(sys.argv) > 1: workdate = sys.argv[1] else: workdate = str(datetime.date.today()) files = [os.path.join(os.environ['DATA_DIR'], "Indicative_" + workdate, f) for f in os.listdir(os.path.join(os.environ['DATA_DIR'], "Indicative_" + workdate))] cusip_files = [f for f in files if "TrInfo" in f] deal_files = [f for f in files if "TrInfo" not in f] with closing(dbconn('etdb')) as etdb: #first load deal data for deal in deal_files: upload_deal_data(etdb, deal) #then load tranche data for cusip in cusip_files: upload_cusip_data(etdb, cusip)