diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/clo_universe.py | 131 | ||||
| -rw-r--r-- | python/cusip_universe.py | 112 |
2 files changed, 114 insertions, 129 deletions
diff --git a/python/clo_universe.py b/python/clo_universe.py index ff0aa422..75d8fd72 100644 --- a/python/clo_universe.py +++ b/python/clo_universe.py @@ -1,4 +1,3 @@ -import psycopg2 import os import os.path import datetime @@ -6,77 +5,71 @@ from datetime import date import csv import pdb import re - -if os.name=='nt': - root = "//WDSENTINEL/share/CorpCDOs/" -elif os.name=='posix': - root = "/home/share/CorpCDOs/" - -workdate = '2013-01-16' -universe = os.path.join("data", "clo_universe_intex_" + workdate + ".txt") +import sys +import common def convertToNone(s): - return None if s=="-" or s=="" else s - -conn = psycopg2.connect(database="ET", - user="et_user", - password="Serenitas1", - host="192.168.1.108") -cursor = conn.cursor() + return None if s=="-" or s=="" or s == "NR" else s -cursor.execute("select dealname, max(\"Latest Update\") from clo_universe group by dealname") -deallist = dict(cursor.fetchall()) - -with open( os.path.join(root, universe), "r") as fh: - dr = csv.DictReader(fh, dialect='excel-tab') - data = [] - for line in dr: - if 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"] - if line["CDOpercent"] == "NR": - line["CDOpercent"] = None - 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]: +def upload_data(workdate, conn, cursor): + cursor.execute("select dealname, max(\"Latest Update\") from clo_universe group by dealname") + deallist = dict(cursor.fetchall()) + universe = os.path.join("data", "clo_universe_intex_" + workdate + ".txt") + with open( os.path.join(common.root, universe), "r") as fh: + dr = csv.DictReader(fh, dialect='excel-tab') + data = [] + for line in dr: + if 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] = line[key].replace(",", "") + 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, " \ + "%(Curr 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: - line[field] = datetime.datetime.strptime(line[field], '%b %d, %Y').date() - except ValueError: + cursor.execute(sqlstring, line) + deallist[dealname] = line['Latest Update'] + except psycopg2.DataError as detail: + print detail 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] = line[key].replace(",", "") - 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, " \ - "%(Curr 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: - cursor.execute(sqlstring, line) - except psycopg2.DataError: - pdb.set_trace() - -conn.commit() + conn.commit() -cursor.close() -conn.close() -print "done" +if __name__ == "__main__": + if len(sys.argv) > 1: + workdate = sys.argv[1] + else: + workdate = str(datetime.date.today()) + upload_data(workdate, common.conn, common.cursor) + common.cursor.close() + common.conn.close() + print "done" diff --git a/python/cusip_universe.py b/python/cusip_universe.py index eceb3325..982e00a5 100644 --- a/python/cusip_universe.py +++ b/python/cusip_universe.py @@ -5,25 +5,12 @@ import datetime from datetime import date import csv import pdb - -if os.name=='nt': - root = "//WDSENTINEL/share/CorpCDOs/" -elif os.name=='posix': - root = "/home/share/CorpCDOs/" +import sys +import common def convertToNone(s): return None if s=="" else s -conn = psycopg2.connect(database="ET", - user="et_user", - password="Serenitas1", - host="192.168.1.108") -cursor = conn.cursor() - -# cursor.execute("delete from cusip_universe") -workdate = '2013-01-17' -count = 0 - def sanitize_float(intex_float): intex_float = intex_float.replace(",", "") if "(" in intex_float: @@ -32,50 +19,55 @@ def sanitize_float(intex_float): intex_float = float(intex_float) return intex_float -data = [] -for cusip_universe_file in os.listdir(os.path.join(root, "data", "Trinfo_" + workdate)): - with open( os.path.join(root, "data", "Trinfo_" + workdate, cusip_universe_file), "r") as fh: - dr = csv.DictReader(fh, dialect='excel-tab') - data = [] - for line in dr: - count += 1 - if "," in line['Tranche']: - line["dealname"], line["tranche"] = line["Tranche"].split(",") - else: - continue - line["dealname"] = line["dealname"].lower() - sqlstring = "SELECT * FROM cusip_universe WHERE cusip = %s" - cursor.execute(sqlstring, (line['CUSIP'],)) - line = {k: convertToNone(v) for k, v in line.iteritems()} - if cursor.fetchone(): - for key in ['Curr Balance', 'Curr Attachment Point', 'Curr Detachment Point', 'Factor']: - if line[key]: - line[key] = sanitize_float(line[key]) - line[key] = convertToNone(line[key]) - sqlstring = "UPDATE cusip_universe SET Curr_Balance = %(Curr Balance)s, " \ - "Factor = %(Factor)s, Curr_moody = %(Curr Moody)s, " \ - "Curr_attach = %(Curr Attachment Point)s WHERE cusip = %(CUSIP)s" - else: - for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point', - 'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon']: - if line[key]: - line[key] = sanitize_float(line[key]) - line[key] = convertToNone(line[key]) - 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) " \ - "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)" - cursor.execute(sqlstring, line) - print count - conn.commit() +def upload_data(workdate, conn, cursor): + data = [] + for cusip_universe_file in os.listdir(os.path.join(common.root, "data", "Trinfo_" + workdate)): + with open( os.path.join(common.root, "data", "Trinfo_" + workdate, cusip_universe_file), "r") as fh: + dr = csv.DictReader(fh, dialect='excel-tab') + data = [] + for line in dr: + if "," in line['Tranche']: + line["dealname"], line["tranche"] = line["Tranche"].split(",") + else: + continue + line["dealname"] = line["dealname"].lower() + sqlstring = "SELECT * FROM cusip_universe WHERE cusip = %s" + cursor.execute(sqlstring, (line['CUSIP'],)) + line = {k: convertToNone(v) for k, v in line.iteritems()} + if cursor.fetchone(): + for key in ['Curr Balance', 'Curr Attachment Point', 'Curr Detachment Point', 'Factor']: + if line[key]: + line[key] = sanitize_float(line[key]) + line[key] = convertToNone(line[key]) + sqlstring = "UPDATE cusip_universe SET Curr_Balance = %(Curr Balance)s, " \ + "Factor = %(Factor)s, Curr_moody = %(Curr Moody)s, " \ + "Curr_attach = %(Curr Attachment Point)s WHERE cusip = %(CUSIP)s" + else: + for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point', + 'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon']: + if line[key]: + line[key] = sanitize_float(line[key]) + line[key] = convertToNone(line[key]) + 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) " \ + "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)" + cursor.execute(sqlstring, line) + print "uploaded: {0}".format(line['CUSIP']) + conn.commit() +if __name__=="__main__": + if len(sys.argv) > 1: + workdate = sys.argv[1] + else: + workdate = str(datetime.date.today()) -cursor.close() -conn.close() -print "done" + upload_data(workdate, common.conn, common.cursor) + common.cursor.close() + common.conn.close() |