diff options
Diffstat (limited to 'python/load_indicative.py')
| -rw-r--r-- | python/load_indicative.py | 187 |
1 files changed, 0 insertions, 187 deletions
diff --git a/python/load_indicative.py b/python/load_indicative.py deleted file mode 100644 index 8fd75b6e..00000000 --- a/python/load_indicative.py +++ /dev/null @@ -1,187 +0,0 @@ -import psycopg2 -import os -import datetime -from datetime import date -import csv, sys, re -import pdb -from common import root, sanitize_float -from db import conn -import logging - -def convertToNone(s): - return None if s in ["", "-", "NR"] else s - -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 \"Latest Update\" FROM clo_universe " \ - "WHERE dealname = %s ORDER BY \"Latest Update\" DESC", (dealname,)) - dealupdate[dealname] = c.fetchone()[0] - - sqlstring = "SELECT updatedate FROM latest_cusip_universe WHERE cusip = %s" - with conn.cursor() as c: - c.execute(sqlstring, (line['CUSIP'],)) - curr_date = c.fetchone() - conn.commit() - 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 (def at MV)', 'Orig Detachment Point', - 'Curr Detachment Point (def at MV)', 'Factor', 'Coupon', - 'Floater Spread/Margin']: - if line[key]: - line[key] = sanitize_float(line[key]) - line[key] = convertToNone(line[key]) - except ValueError: - continue - line['Curr Attachment Point'] = line['Curr Attachment Point (def at MV)'] - line['Curr Detachment Point'] = line['Curr Detachment Point (def at MV)'] - if "Curr Moody" not in line: - if 'Orig Moody' in line: - line['Curr Moody'] = line['Orig Moody'] - else: - line['Curr Moody'] = None - line['Orig Moody'] = None - sqlstring = "INSERT INTO cusip_universe(Cusip, ISIN, \"Bloomberg Ticker\", 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, %(Bloomberg Ticker)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/Margin)s, %(updatedate)s)" - try: - with conn.cursor() as c: - c.execute(sqlstring, line) - except psycopg2.DataError as e: - logging.error(e) - logging.debug("uploaded: {0}".format(line['CUSIP'])) - conn.commit() - # for dealname in deals_to_update: - # with conn.cursor() as c: - # c.execute("SELECT p_cusip, p_curr_subordination, "\ - # "p_curr_thickness from et_deal_subordination(%s)", - # (dealname,)) - # data = [ (t[1], t[2], t[0], dealupdate[dealname]) for t in c] - # c.executemany("UPDATE cusip_universe SET subordination = %s, " - # "thickness = %s WHERE cusip = %s AND " - # "updatedate = %s", data) - # 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]) - 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", "Reinv End Date", \ - "Latest Update", "Pay Day", "Deal First Pay Date", "Paid Down"]: - if line[field]: - try: - line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date() - except ValueError: - pdb.set_trace() - if line["Pay Day"]: - line["Pay Day"] = line["Pay Day"].day - for key in ["Collection Account Principal Balance", "Collection Account Interest Balance", - "Curr Deal Bal", "Tranche Curr Bal", - "CDO Pct of Assets that are Structured Finance Obligations", - "CDO Defaulted Security Balance (Reported)"]: - if line[key]: - line[key] = sanitize_float(line[key]) - line['Deal/Tranche ID'] = line['Deal/Tranche ID'].lower() - dealname = line['Deal/Tranche ID'] - line['defaultedbal'] = line["CDO Defaulted Security Balance (Reported)"] - if dealname not in deallist2: - for key in ["Orig Deal Bal", "Tranche Orig Bal"]: - if line[key]: - line[key] = sanitize_float(line[key]) - if line['Deal CUSIP List']: - line['Deal CUSIP List'] = line['Deal CUSIP List'].split(",") - sqlstr = "INSERT INTO deal_indicative VALUES( %(Deal/Tranche ID)s, %(Deal Name)s, " \ - "%(Collateral Manager)s, %(Deal Issue Date)s, %(Deal Termination Date)s, " \ - "%(Pay Day)s, %(Reinv End Date)s, %(Deal First Pay Date)s, %(Orig Deal Bal)s, " \ - "%(Tranche Orig Bal)s, %(Deal CUSIP List)s, %(Paid Down)s)" - try: - with conn.cursor() as c: - c.execute(sqlstr, line) - except (psycopg2.DataError, KeyError) as detail: - logging.error(detail) - pdb.set_trace() - with conn.cursor() as c: - if line['Paid Down']: - c.execute("UPDATE deal_indicative SET paid_down=%s WHERE dealname=%s", - (line['Paid Down'], 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, %(Collection Account Principal Balance)s, " \ - "%(Collection Account Interest Balance)s, %(CDOpercent)s, %(defaultedbal)s, " \ - "%(Coupon)s, %(Latest Update)s)" - try: - with conn.cursor() as c: - c.execute(sqlstring, line) - deallist1[dealname] = [line['Latest Update']] - except (psycopg2.DataError, KeyError) as detail: - logging.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(root, "data", "Indicative_" + workdate, f) for f in - os.listdir(os.path.join(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 deal in deal_files: - upload_deal_data(conn, deal) - #then load tranche data - for cusip in cusip_files: - upload_cusip_data(conn, cusip) - conn.close() |