diff options
Diffstat (limited to 'python/cusip_universe.py')
| -rw-r--r-- | python/cusip_universe.py | 112 |
1 files changed, 52 insertions, 60 deletions
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() |