diff options
| -rw-r--r-- | python/intex/__init__.py | 1 | ||||
| -rw-r--r-- | python/intex/__main__.py | 5 | ||||
| -rw-r--r-- | python/intex/common.py | 5 | ||||
| -rw-r--r-- | python/intex/intex_scenarios.py | 148 | ||||
| -rw-r--r-- | python/intex/load_indicative.py | 205 | ||||
| -rw-r--r-- | python/intex/load_intex_collateral.py | 186 |
6 files changed, 373 insertions, 177 deletions
diff --git a/python/intex/__init__.py b/python/intex/__init__.py index 889702f8..168432ea 100644 --- a/python/intex/__init__.py +++ b/python/intex/__init__.py @@ -1,3 +1,4 @@ import sys + sys.path.append("..") from utils.db import dbconn diff --git a/python/intex/__main__.py b/python/intex/__main__.py index ffbaee36..ddc677d7 100644 --- a/python/intex/__main__.py +++ b/python/intex/__main__.py @@ -2,13 +2,14 @@ import sys import datetime from .load_intex_collateral import intex_data import logging -logger = logging.getLogger('intex') + +logger = logging.getLogger("intex") logger.addHandler(logging.StreamHandler()) if len(sys.argv) > 1: workdate = sys.argv[1] else: workdate = str(datetime.date.today()) -with dbconn('etdb') as etdb: +with dbconn("etdb") as etdb: intex_data(etdb, workdate) print("done") diff --git a/python/intex/common.py b/python/intex/common.py index d93f9c18..65bae4a0 100644 --- a/python/intex/common.py +++ b/python/intex/common.py @@ -1,12 +1,13 @@ import os + def sanitize_float(intex_float): try: intex_float = intex_float.replace(",", "") - if " " in intex_float: #case of combo notes + if " " in intex_float: # case of combo notes return float(intex_float.split(" ")[0]) if "(" in intex_float: - return - float(intex_float[1:-1]) + return -float(intex_float[1:-1]) else: return float(intex_float) except (AttributeError, ValueError): diff --git a/python/intex/intex_scenarios.py b/python/intex/intex_scenarios.py index 75da23a6..79b50639 100644 --- a/python/intex/intex_scenarios.py +++ b/python/intex/intex_scenarios.py @@ -27,8 +27,9 @@ pattern9 = re.compile("(?P<a>SEVERITY\[\w+,\d+\]=)mkt\(70\)") global_reinvfloatpercentage = 84 global_reinvfixedpercentage = 16 + def get_reinv_assets(conn, dealname, workdate): - sqlstr = 'SELECT * FROM et_historicaldealinfo(%s, %s) WHERE ReinvFlag IS TRUE' + sqlstr = "SELECT * FROM et_historicaldealinfo(%s, %s) WHERE ReinvFlag IS TRUE" d = {} with conn.cursor() as c: c.execute(sqlstr, (dealname, workdate)) @@ -37,10 +38,13 @@ def get_reinv_assets(conn, dealname, workdate): conn.commit() return d + def get_recovery(conn, dealname, workdate, defaultrecovery=50): """ compute average price of defaulted assets """ - sqlstr = ("select sum(coalesce(price, %s) * currentbalance)/sum(currentbalance) AS recov " - "from et_aggdealinfo_historical(%s, %s) where defaultedflag is True") + sqlstr = ( + "select sum(coalesce(price, %s) * currentbalance)/sum(currentbalance) AS recov " + "from et_aggdealinfo_historical(%s, %s) where defaultedflag is True" + ) with conn.cursor() as c: try: c.execute(sqlstr, (defaultrecovery, dealname, workdate)) @@ -53,8 +57,9 @@ def get_recovery(conn, dealname, workdate, defaultrecovery=50): conn.commit() return float(recovery) + def get_reinvenddate(conn, dealname, workdate): - sqlstr = 'SELECT reinv_end_date FROM historical_clo_universe(%s, %s)' + sqlstr = "SELECT reinv_end_date FROM historical_clo_universe(%s, %s)" with conn.cursor() as c: c.execute(sqlstr, (dealname, workdate)) reinvenddate, = c.fetchone() @@ -64,10 +69,13 @@ def get_reinvenddate(conn, dealname, workdate): else: raise Exception("missing reinvestment end date") + def generate_scenarios(workdate, dealname, conn): - prometheus = os.path.join(os.environ['BASE_DIR'], "Scenarios", "prometheus.sss") + prometheus = os.path.join(os.environ["BASE_DIR"], "Scenarios", "prometheus.sss") n_scenarios = 100 - basedir = os.path.join(os.environ['BASE_DIR'], "Scenarios", "Intex curves_" + workdate) + basedir = os.path.join( + os.environ["BASE_DIR"], "Scenarios", "Intex curves_" + workdate + ) defaultedprice = get_recovery(conn, dealname, workdate) replace = "\g<a>{0:.3f}".format(defaultedprice) try: @@ -76,22 +84,26 @@ def generate_scenarios(workdate, dealname, conn): except IOError: logger.error("{0}: config file doesn't exist".format(dealname)) return - reinvflag = config['reinvflag'] + reinvflag = config["reinvflag"] if reinvflag: reinvenddate = get_reinvenddate(conn, dealname, workdate) reinv_assets = get_reinv_assets(conn, dealname, workdate) - n_float_assets = len([v for v in reinv_assets.values() if v[0] == 'FLOAT']) - n_fixed_assets = len([v for v in reinv_assets.values() if v[0] == 'FIXED']) - rollingmat = config['rollingmat'] + n_float_assets = len([v for v in reinv_assets.values() if v[0] == "FLOAT"]) + n_fixed_assets = len([v for v in reinv_assets.values() if v[0] == "FIXED"]) + rollingmat = config["rollingmat"] if n_fixed_assets == 0: reinvfixedpercentage = 0 else: reinvfixedpercentage = global_reinvfixedpercentage / n_fixed_assets if n_float_assets > 0: - reinvfloatpercentage = (100 - n_fixed_assets * reinvfixedpercentage)/n_float_assets + reinvfloatpercentage = ( + 100 - n_fixed_assets * reinvfixedpercentage + ) / n_float_assets try: - with open(os.path.join(basedir, "csv", dealname + "-reinvprices.csv"), "r") as fh: + with open( + os.path.join(basedir, "csv", dealname + "-reinvprices.csv"), "r" + ) as fh: dr = csv.DictReader(fh) reinvprices = {f: [] for f in dr.fieldnames} for line in dr: @@ -99,7 +111,9 @@ def generate_scenarios(workdate, dealname, conn): try: val = float(line[f]) except ValueError: - logger.error("Incorrect value in reinvprices for {}".format(dealname)) + logger.error( + "Incorrect value in reinvprices for {}".format(dealname) + ) else: reinvprices[f].append("{0:.3f}".format(val)) @@ -119,15 +133,17 @@ def generate_scenarios(workdate, dealname, conn): cdrline = next(csvcdr) cdrline = "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline]) recoveryline = next(csvrecovery) - recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) for recovery in recoveryline]) + recoveryline = "\t".join( + ["{0:.3f}".format(float(recovery)) for recovery in recoveryline] + ) - i=1 + i = 1 with open(prometheus) as fh: for line in fh: line = line.rstrip() if "DEAL_NAME" in line: - newline = "DEAL_NAME=" + dealname.upper() + "\r\n" + newline = "DEAL_NAME=" + dealname.upper() + "\r\n" fhsss.write(newline) continue if not reinvflag and pattern8.match(line): @@ -147,33 +163,39 @@ def generate_scenarios(workdate, dealname, conn): coupon = 4 elif fixedorfloat == "FIXED": coupon = 7 - line = "REINVEST[{0}::REINV_TBA{1}][DEAL,{2}]=".format(dealname.upper(), - reinv_number, - scen_number) - line += "COUP_SPR={0}|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE={1}|".format(coupon, rollingmat) + line = "REINVEST[{0}::REINV_TBA{1}][DEAL,{2}]=".format( + dealname.upper(), reinv_number, scen_number + ) + line += "COUP_SPR={0}|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE={1}|".format( + coupon, rollingmat + ) if liborfloor is not None: line += "|ARM_LIFE_FLOOR={}".format(liborfloor) fhsss.write(line + "\r\n") continue if reinvflag and pattern2.match(line): - line = re.sub(pattern2, r"\1{0}\2{1}", line).format(dealname.upper(), reinvenddate) + line = re.sub(pattern2, r"\1{0}\2{1}", line).format( + dealname.upper(), reinvenddate + ) fhsss.write(line + "\r\n") continue m = pattern3.match(line) if reinvflag and m: reinv_number, scen_number = m.groups() - if dealname=="litpt3" and reinv_number=="1": - line = "STANDARD_VAR[LITPT3::#REINVLOANP100,{0}]".format(scen_number) + \ - "={0}".format(" ".join(reinvprices["REINV_TBA1"])) + if dealname == "litpt3" and reinv_number == "1": + line = "STANDARD_VAR[LITPT3::#REINVLOANP100,{0}]".format( + scen_number + ) + "={0}".format(" ".join(reinvprices["REINV_TBA1"])) fhsss.write(line + "\r\n") continue reinv_name = "REINV_TBA" + reinv_number if reinv_name in reinvprices: - line = \ - "STANDARD_VAR[{0}::#PRICE100_TBA{1},{2}]={3}".format(dealname.upper(), - reinv_number, - scen_number, - " ".join(reinvprices[reinv_name])) + line = "STANDARD_VAR[{0}::#PRICE100_TBA{1},{2}]={3}".format( + dealname.upper(), + reinv_number, + scen_number, + " ".join(reinvprices[reinv_name]), + ) fhsss.write(line + "\r\n") continue m = pattern5.match(line) @@ -182,24 +204,32 @@ def generate_scenarios(workdate, dealname, conn): reinv_name = "REINV_TBA" + reinv_number if reinv_number == "1": if dealname == "litpt3": - line = "STANDARD_VAR[LITPT3::#LOANREINVPCT,{0}]=100".format(scen_number) + line = "STANDARD_VAR[LITPT3::#LOANREINVPCT,{0}]=100".format( + scen_number + ) fhsss.write(line + "\r\n") continue if dealname == "flags4": - line = "STANDARD_VAR[FLAGS4::#PCT100_TBA1,{0}]=100".format(scen_number) + line = "STANDARD_VAR[FLAGS4::#PCT100_TBA1,{0}]=100".format( + scen_number + ) fhsss.write(line + "\r\n") continue if reinv_name in reinv_assets: - if reinv_assets[reinv_name][0] == 'FIXED': - line = "STANDARD_VAR[{0}::#REINVPCT_TBA{1},{2}]={3}".format(dealname.upper(), - reinv_number, - scen_number, - reinvfixedpercentage) - elif reinv_assets[reinv_name][0] == 'FLOAT': - line = "STANDARD_VAR[{0}::#REINVPCT_TBA{1},{2}]={3}".format(dealname.upper(), - reinv_number, - scen_number, - reinvfloatpercentage) + if reinv_assets[reinv_name][0] == "FIXED": + line = "STANDARD_VAR[{0}::#REINVPCT_TBA{1},{2}]={3}".format( + dealname.upper(), + reinv_number, + scen_number, + reinvfixedpercentage, + ) + elif reinv_assets[reinv_name][0] == "FLOAT": + line = "STANDARD_VAR[{0}::#REINVPCT_TBA{1},{2}]={3}".format( + dealname.upper(), + reinv_number, + scen_number, + reinvfloatpercentage, + ) fhsss.write(line + "\r\n") continue @@ -217,10 +247,20 @@ def generate_scenarios(workdate, dealname, conn): i = i + 1 if i <= n_scenarios: cdrline = next(csvcdr) - cdrline = "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline]) + "\r\n" + cdrline = ( + "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline]) + + "\r\n" + ) recoveryline = next(csvrecovery) - recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) \ - for recovery in recoveryline]) + "\r\n" + recoveryline = ( + "\t".join( + [ + "{0:.3f}".format(float(recovery)) + for recovery in recoveryline + ] + ) + + "\r\n" + ) continue if "LOSS_NONPERF_SEVERITY" in line: line = re.sub(pattern9, replace, line) @@ -230,9 +270,11 @@ def generate_scenarios(workdate, dealname, conn): fhcdr.close() logger.info("generated scenarios for: {0}".format(dealname)) + if __name__ == "__main__": sys.path.append(".") from utils.db import dbconn + if len(sys.argv) > 1: workdate = sys.argv[1] else: @@ -240,11 +282,19 @@ if __name__ == "__main__": if len(sys.argv) > 2: dealnames = sys.argv[2:] else: - dealnames = [d.split(".")[0] for d in - os.listdir(os.path.join(os.environ['BASE_DIR'], "Scenarios", - "Intex curves_" + workdate, "csv")) - if "RData" in d] - ET = dbconn('etdb') + dealnames = [ + d.split(".")[0] + for d in os.listdir( + os.path.join( + os.environ["BASE_DIR"], + "Scenarios", + "Intex curves_" + workdate, + "csv", + ) + ) + if "RData" in d + ] + ET = dbconn("etdb") for dealname in dealnames: generate_scenarios(workdate, dealname, ET) ET.close() diff --git a/python/intex/load_indicative.py b/python/intex/load_indicative.py index 4e6c05c5..53124801 100644 --- a/python/intex/load_indicative.py +++ b/python/intex/load_indicative.py @@ -12,28 +12,46 @@ 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["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))) + 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) @@ -46,34 +64,37 @@ def insert_new_cusip(conn, line): def upload_cusip_data(conn, filename): dealupdate = {} - with open( filename, "r") as fh: - dr = csv.DictReader(fh, dialect='excel-tab') + 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 + 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'] + 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,)) + 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') + 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)) + c.execute(sqlstring, (line["CUSIP"], dealname)) r = c.fetchone() if r is None: try: @@ -88,26 +109,41 @@ def upload_cusip_data(conn, filename): 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']: + 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]) + 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'])) + 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: @@ -119,52 +155,76 @@ def upload_deal_data(conn, filename): deallist2 = set([d for d, in c]) conn.commit() with open(filename, "r") as fh: - dr = csv.DictReader(fh, dialect='excel-tab') + 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'): + if not line["Deal Name, Tranche Name"] or ( + line["Deal Name, Tranche Name"] == "Unknown Security" + ): continue - if not line['Latest Update']: + 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["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["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 Last Refi Date"]: + for field in [ + "Deal Closing Date", + "Deal Termination Date", + "Reinv End Date", + "Latest Update", + "Pay Day", + "Deal First Pay Date", + "Paid Down", + "Deal Last Refi Date", + ]: if line[field]: try: - line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date() + 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"]: + 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'] + 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)" + 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' + # 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(",") + if line["Deal CUSIP List"]: + line["Deal CUSIP List"] = line["Deal CUSIP List"].split(",") try: with conn.cursor() as c: c.execute(sqlstr, line) @@ -175,38 +235,47 @@ def upload_deal_data(conn, filename): # update deallist2 if dealname not in deallist2: deallist2.add(dealname) - 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 Last Refi Date)s, " \ - "%(Deal CUSIP List)s, %(Deal Termination Date)s, %(Reinv End Date)s, " \ + 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 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']] + 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))] + 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 + with closing(dbconn("etdb")) as etdb: + # first load deal data for deal in deal_files: upload_deal_data(etdb, deal) - #then load tranche data + # then load tranche data for cusip in cusip_files: upload_cusip_data(etdb, cusip) diff --git a/python/intex/load_intex_collateral.py b/python/intex/load_intex_collateral.py index d848887b..9fdfa9d6 100644 --- a/python/intex/load_intex_collateral.py +++ b/python/intex/load_intex_collateral.py @@ -11,30 +11,77 @@ import logging logger = logging.getLogger(__name__) -fields = ['Asset Name', 'Issuer', 'Contributed Balance', 'Asset Maturity Date', - 'Asset Subtype', 'Asset Type', 'Gross Coupon', 'Spread', \ - 'Frequency', 'Next Paydate', 'Second Lien', 'LoanX ID', 'CUSIP', - 'Market Price', 'Market Price Source', 'Market Price Date', 'Fixed or Float', \ - 'Defaulted Flag', 'Security Sub-Category', 'Structured Finance Security', \ - 'Life Floor', 'Reinvest Collat', 'Native Currency', "Moody's Industry Name", - "Country", "Amortization Schedule"] +fields = [ + "Asset Name", + "Issuer", + "Contributed Balance", + "Asset Maturity Date", + "Asset Subtype", + "Asset Type", + "Gross Coupon", + "Spread", + "Frequency", + "Next Paydate", + "Second Lien", + "LoanX ID", + "CUSIP", + "Market Price", + "Market Price Source", + "Market Price Date", + "Fixed or Float", + "Defaulted Flag", + "Security Sub-Category", + "Structured Finance Security", + "Life Floor", + "Reinvest Collat", + "Native Currency", + "Moody's Industry Name", + "Country", + "Amortization Schedule", +] + def upload_data(conn, workdate): - sql_fields = ["dealname", "updatedate", "name", "IssuerName", "CurrentBalance", - "Maturity", "AssetSubtype", "AssetType", "GrossCoupon", - "Spread", "Frequency", "NextPaydate", "SecondLien", "LoanXID", - "Cusip", "IntexPrice", "IntexPriceSource", "IntexPriceDate", - "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO", - "Liborfloor", "ReinvFlag", "Currency", "Industry", "Country", - "amort_schedule_dates", "amort_schedule_amounts"] - sqlstr = "INSERT INTO ET_COLLATERAL({0}) VALUES({1})".format(",".join(sql_fields), - ",".join(["%s"] * len(sql_fields))) - basedir = Path(os.environ['DATA_DIR']) / ("Collaterals_" + workdate) + sql_fields = [ + "dealname", + "updatedate", + "name", + "IssuerName", + "CurrentBalance", + "Maturity", + "AssetSubtype", + "AssetType", + "GrossCoupon", + "Spread", + "Frequency", + "NextPaydate", + "SecondLien", + "LoanXID", + "Cusip", + "IntexPrice", + "IntexPriceSource", + "IntexPriceDate", + "FixedOrFloat", + "DefaultedFlag", + "CovLite", + "isCDO", + "Liborfloor", + "ReinvFlag", + "Currency", + "Industry", + "Country", + "amort_schedule_dates", + "amort_schedule_amounts", + ] + sqlstr = "INSERT INTO ET_COLLATERAL({0}) VALUES({1})".format( + ",".join(sql_fields), ",".join(["%s"] * len(sql_fields)) + ) + basedir = Path(os.environ["DATA_DIR"]) / ("Collaterals_" + workdate) for fname in basedir.iterdir(): fh = open(fname, encoding="windows-1252") dealname = fname.name.rsplit("_", 1)[0].lower() - dr = csv.DictReader(fh, dialect='excel-tab') - missingfields = set(fields).union({'Gross Margin'}) - set(dr.fieldnames) + dr = csv.DictReader(fh, dialect="excel-tab") + missingfields = set(fields).union({"Gross Margin"}) - set(dr.fieldnames) if "LoanX ID" in missingfields: msg = "{0}: LoanX ID column is missing. Probably an error in exporting from intex" logger.warning(msg.format(dealname)) @@ -42,70 +89,85 @@ def upload_data(conn, workdate): for line in dr: for f in missingfields: line[f] = None - for key in ['LoanX ID', 'CUSIP', 'Fixed or Float']: + for key in ["LoanX ID", "CUSIP", "Fixed or Float"]: if line[key]: line[key] = line[key].upper() - if line['Asset Subtype']: - line['Asset Subtype'] = line['Asset Subtype'].replace("Reinvest ", "").replace("Reinv ","") - if line['Second Lien']: - line['Second Lien'] = line['Second Lien'].replace("Second Lien", "Y") - for key, l in [('LoanX ID', 8), ('CUSIP', 9), ('Asset Subtype', 10)]: + if line["Asset Subtype"]: + line["Asset Subtype"] = ( + line["Asset Subtype"].replace("Reinvest ", "").replace("Reinv ", "") + ) + if line["Second Lien"]: + line["Second Lien"] = line["Second Lien"].replace("Second Lien", "Y") + for key, l in [("LoanX ID", 8), ("CUSIP", 9), ("Asset Subtype", 10)]: if line[key]: if len(line[key]) > l: logger.warning("dubious {0} found: {1}".format(key, line[key])) line[key] = line[key][:l] - if 'Reinvest Collat' not in missingfields and line['Reinvest Collat'].upper() == 'Y': + if ( + "Reinvest Collat" not in missingfields + and line["Reinvest Collat"].upper() == "Y" + ): # assume it's a reinvestment asset - line['Reinvest Collat'] = True - line['Issuer'] = line['ID Number'] - if not line['Spread']: - line['Spread'] = line['Gross Margin'] + line["Reinvest Collat"] = True + line["Issuer"] = line["ID Number"] + if not line["Spread"]: + line["Spread"] = line["Gross Margin"] - for field in ['Spread', 'Gross Coupon', 'Market Price', 'Contributed Balance']: + for field in [ + "Spread", + "Gross Coupon", + "Market Price", + "Contributed Balance", + ]: line[field] = sanitize_float(line[field]) - if line['Market Price'] == 0: - line['Market Price'] = None - #we store the Libor Floor in the database, so Life Floor is really Libor Floor - if line['Life Floor'] == "No limit": - line['Life Floor'] = 0 - elif line['Life Floor']: + if line["Market Price"] == 0: + line["Market Price"] = None + # we store the Libor Floor in the database, so Life Floor is really Libor Floor + if line["Life Floor"] == "No limit": + line["Life Floor"] = 0 + elif line["Life Floor"]: try: - line['Life Floor'] = float(line['Life Floor']) - float(line['Spread']) + line["Life Floor"] = float(line["Life Floor"]) - float( + line["Spread"] + ) except ValueError: - line['Life Floor'] = float('Nan') + line["Life Floor"] = float("Nan") # we take care of reinvestment asset lines - if not line['Asset Name']: - line['Asset Name'] = 'Reinv' + if not line["Asset Name"]: + line["Asset Name"] = "Reinv" try: - line['Asset Maturity Date'] = datetime.datetime.strptime(line['Asset Maturity Date'], - '%b %d, %Y').date() + line["Asset Maturity Date"] = datetime.datetime.strptime( + line["Asset Maturity Date"], "%b %d, %Y" + ).date() except (ValueError, TypeError): - line['Asset Maturity Date'] = None + line["Asset Maturity Date"] = None r = [line[field] or None for field in fields] if line["Amortization Schedule"]: temp = line["Amortization Schedule"].split("; ") temp = [e.split(" @ ") for e in temp] amounts, dates = zip(*temp) - if '%' in amounts[0]: #for reinvestment assets amort is in percentage. + if "%" in amounts[0]: # for reinvestment assets amort is in percentage. amounts = [float(e.replace("%", "")) / 100 for e in amounts] else: amounts = [float(e.replace(",", "")) for e in amounts] - dates = [datetime.datetime.strptime(e, "%b %d, %Y").date() for e in dates] + dates = [ + datetime.datetime.strptime(e, "%b %d, %Y").date() for e in dates + ] r[-1] = dates r.append(amounts) else: r.append(None) - #sometimes the Asset Name is not unique (we add random tag in this case) + # sometimes the Asset Name is not unique (we add random tag in this case) if r[0] in data: r[0] = r[0] + str(uuid.uuid4())[:3] data[r[0]] = r[1:] fh.close() sqlstr1 = "select distinct(updatedate) from et_collateral where dealname= %s" - sqlstr2 = "select max(\"Latest Update\") from clo_universe where dealname= %s and \"Latest Update\"<=%s" + sqlstr2 = 'select max("Latest Update") from clo_universe where dealname= %s and "Latest Update"<=%s' with conn.cursor() as c: c.execute(sqlstr1, (dealname,)) old_update_dates = [date[0] for date in c] @@ -120,9 +182,13 @@ def upload_data(conn, workdate): c.execute(sql_str, (dealname, updatedate)) currlen = c.fetchone()[0] conn.commit() - if currlen != len(data): #then we delete and just reupload - logger.warning("{0} has {1} rows in the database " \ - "and current collateral file has {2}".format(dealname, currlen, len(data))) + if currlen != len(data): # then we delete and just reupload + logger.warning( + "{0} has {1} rows in the database " + "and current collateral file has {2}".format( + dealname, currlen, len(data) + ) + ) with conn.cursor() as c: sql_str = "DELETE FROM et_collateral where dealname = %s and updatedate = %s" c.execute(sql_str, (dealname, updatedate)) @@ -132,21 +198,29 @@ def upload_data(conn, workdate): if reinsert or not old_update_dates or updatedate not in old_update_dates: with conn.cursor() as c: try: - c.executemany(sqlstr, [(dealname, updatedate, k) + tuple(v) for k, v in data.items()]) - except (psycopg2.DataError, psycopg2.IntegrityError, TypeError) as detail: + c.executemany( + sqlstr, + [(dealname, updatedate, k) + tuple(v) for k, v in data.items()], + ) + except ( + psycopg2.DataError, + psycopg2.IntegrityError, + TypeError, + ) as detail: logger.error(detail) conn.rollback() else: conn.commit() + def intex_data(conn, workdate): - basedir = Path(os.environ['DATA_DIR']) / ("Indicative_" + workdate) + basedir = Path(os.environ["DATA_DIR"]) / ("Indicative_" + workdate) cusip_files = [f for f in basedir.iterdir() if "TrInfo" in f.name] deal_files = [f for f in basedir.iterdir() if "TrInfo" not in f.name] - #first load deal data + # first load deal data for deal_file in deal_files: upload_deal_data(conn, deal_file) - #then load tranche data + # then load tranche data for cusip_file in cusip_files: upload_cusip_data(conn, cusip_file) |