diff options
Diffstat (limited to 'python/clo_universe.py')
| -rw-r--r-- | python/clo_universe.py | 131 |
1 files changed, 62 insertions, 69 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" |