import psycopg2 import os import os.path import datetime from datetime import date import csv import pdb import sys import common from common import query_db def convertToNone(s): return None if s=="" or s=="-" or s=="NR" else s def sanitize_float(intex_float): if intex_float.count(".") > 1: raise ValueError intex_float = intex_float.replace(",", "") if "(" in intex_float: intex_float = - float(intex_float[1:-1]) else: intex_float = float(intex_float) return intex_float def upload_cusip_data(filename, conn): 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 if "," in line['Tranche']: line["dealname"], line["tranche"] = line["Tranche"].split(",") 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: dealupdate[dealname] = query_db(conn, "SELECT \"Latest Update\" FROM clo_universe " \ "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", params = (dealname,))[0] sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s" curr_date = query_db(conn, sqlstring, params = (line['CUSIP'],)) if not curr_date or curr_date[0] < dealupdate[dealname]: if dealname not in deals_to_update: deals_to_update.append(dealname) line['updatedate'] = dealupdate[dealname] try: for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point', 'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon', 'Floater Spread']: if line[key]: line[key] = sanitize_float(line[key]) line[key] = convertToNone(line[key]) except ValueError: continue if "Curr Moody" not in line: line['Curr Moody'] = line['Orig Moody'] sqlstring = "INSERT INTO cusip_universe(Cusip, ISIN, dealname, tranche, " \ "Coupon, Orig_Balance, Curr_Balance, Factor, Orig_Moody, Curr_Moody, " \ "Orig_Attach, Orig_Detach, Curr_Attach, Curr_Detach, Floater_Index, " \ "Spread, updatedate)" \ "VALUES (%(CUSIP)s, %(ISIN)s, %(dealname)s, %(tranche)s, %(Coupon)s, " \ "%(Orig Balance)s, %(Curr Balance)s, %(Factor)s, %(Orig Moody)s, %(Curr Moody)s, " \ "%(Orig Attachment Point)s, %(Orig Detachment Point)s, %(Curr Attachment Point)s," \ "%(Curr Detachment Point)s, %(Floater Index)s, %(Floater Spread)s, " \ "%(updatedate)s)" try: with conn.cursor() as c: c.execute(sqlstring, line) except psycopg2.DataError: pdb.set_trace() print("uploaded: {0}".format(line['CUSIP'])) conn.commit() for dealname in deals_to_update: data = query_db(conn, "SELECT p_cusip, p_curr_subordination, "\ "p_curr_thickness from et_deal_subordination(%s)", params = (dealname,), one = False) data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in data] with conn.cursor() as c: c.executemany("UPDATE cusip_universe SET subordination = %s, " "thickness = %s WHERE cusip = %s AND " "updatedate = %s", data) conn.commit() def upload_deal_data(filename, conn): sqlstr = "select dealname, max(\"Latest Update\") from clo_universe group by dealname" deallist = dict(query_db(conn, sqlstr, one=False)) with open( filename, "r") as fh: dr = csv.DictReader(fh, dialect='excel-tab') data = [] c = conn.cursor() for line in dr: if not line ['Deal Name'] or (line['Deal Name'] == 'Unknown Security'): continue if not line['Latest Update']: break for key in line.keys(): line[key] = convertToNone(line[key]) line["CDOpercent"] = line["CDO Pct of Assets that are Structured Finance Obligations"] 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 Issue Date", "Deal Termination Date", \ "Deal Next Pay Date", "Reinv End Date", "Latest Update"]: if line[field]: try: line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date() except ValueError: pdb.set_trace() for key in ["Collection Account Principal Balance", "Collection Account Interest Balance", "Orig Deal Bal", "Curr Deal Bal", "Tranche Curr Bal", "Tranche Orig Bal", "CDO Pct of Assets that are Structured Finance Obligations", "CDO Defaulted Security Balance (Reported)"]: if line[key]: line[key] = sanitize_float(line[key]) dealname = line['Deal,Tr/CUSIP/ISIN'] line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"] line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",") if dealname not in deallist or line['Latest Update'] > deallist[dealname]: sqlstring = \ "INSERT INTO clo_universe " \ "VALUES (%(Deal,Tr/CUSIP/ISIN)s, %(Deal Name)s, %(Collateral Manager)s, %(Orig Deal Bal)s," \ "%(Curr Deal Bal)s, %(Tranche Orig Bal)s, %(Tranche Curr Bal)s, %(Tranche Factor)s," \ "%(Collection Account Principal Balance)s," \ "%(Collection Account Interest Balance)s, %(CDOpercent)s, %(defaultedbal)s, " \ "%(Coupon)s, %(Deal Issue Date)s," \ "%(Deal Termination Date)s, %(Deal Next Pay Date)s," \ "%(Reinv End Date)s, %(Latest Update)s, %(Deal CUSIP List)s, %(Paid Down)s)" try: with conn.cursor() as c: c.execute(sqlstring, line) deallist[dealname] = line['Latest Update'] except psycopg2.DataError as detail: print(detail) pdb.set_trace() except KeyError as detail: print(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(common.root, "data", "Indicative_" + workdate, f) for f in os.listdir(os.path.join(common.root, "data", "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] #first load deal data for f in deal_files: upload_deal_data(f, common.conn) #then load tranche data for f in cusip_files: upload_cusip_data(f, common.conn) common.conn.close()