import os import os.path import datetime from datetime import date import csv from csv import reader import json import codecs import re import psycopg2 from datetime import datetime if os.name =='nt': root = "//WDsentinel/share/CorpCDOs" elif os.name == 'posix': root = '/home/share/CorpCDOs' input = os.path.join(root, "Scenarios", "prometheus.sss") pattern11 = re.compile("(REINVEST\[)\w+(::REINV_TBA1\]\[DEAL,1\]=)(.*$)") pattern12 = re.compile("(REINVEST\[)\w+(::REINV_TBA2\]\[DEAL,1\]=)(.*$)") # reinv end date pattern2 = re.compile("(STANDARD_VAR\[)\w+(::#REINV_END,\d+\]=)(\d*$)") # reinvprices float pattern3 = re.compile("(STANDARD_VAR\[)\w+(::#PRICE100_TBA1,\d+\]=)(.*$)") # reinvprices fixed pattern4 = re.compile("(STANDARD_VAR\[)\w+(::#PRICE100_TBA2,\d+\]=)(.*$)") # reinv float percentage pattern5 = re.compile("(STANDARD_VAR\[)\w+(::#REINVPCT_TBA1,\d+\]=)(.*$)") # reinv fixed percentage pattern6 = re.compile("(STANDARD_VAR\[)\w+(::#REINVPCT_TBA2,\d+\]=)(.*$)") pattern7 = re.compile("GOLDL5") reinvfloatpercentage = 85 reinvfixedpercentage = 15 conn = psycopg2.connect(database="ET", user="et_user", password="Serenitas1", host="192.168.1.108") cursor = conn.cursor() # workdate = date.today().strftime('%Y-%m-%d') workdate = '2013-01-15' def get_reinv_assets(dealname): # collatdates = sorted([datetime.strptime(d.split("_")[1], "%Y-%m-%d").date() # for d in os.listdir(os.path.join(root, "data")) # if "Collaterals" in d], reverse=True) collatfolders = sorted([d for d in os.listdir(os.path.join(root, "data")) if "Collaterals" in d], reverse = True) d = {'REINV_TBA1': None, 'REINV_TBA2': None} filename = dealname.upper() + ",AD.txt" for folder in collatfolders: if filename not in os.listdir(os.path.join(root, "data", folder)): continue else: with open(os.path.join(root, "data", folder, filename )) as fh: dr = csv.DictReader(fh, dialect = 'excel-tab') headers = dr.fieldnames for line in dr: if line['ID Number'] == 'REINV_TBA1': d['REINV_TBA1'] = line['Fixed or Float'] if line['ID Number'] == 'REINV_TBA2': d['REINV_TBA2'] = line['Fixed or Float'] return d for dealname in ["ares12", "callid7", "symph5", "lafasq", "marath1", "drydn8"]: cursor.execute('SELECT \"Reinv End Date\" from latest_clo_universe where dealname=%s', (dealname,)) reinvenddate = cursor.fetchone()[0] if reinvenddate: reinvenddate = reinvenddate.strftime("%Y%m%d") else: print "missing reinvestment end date" pdb.set_trace() reinv_assets = get_reinv_assets(dealname) basedir = os.path.join(root, "Scenarios", "Intex curves_" + workdate) with open(os.path.join(basedir, "csv", dealname + "-reinvprices.csv"), "r") as fhreinv: floatreinvprices = fhreinv.readline().rstrip("\n").split(",") fixedreinvprices = fhreinv.readline().rstrip("\n").split(",") output = os.path.join(basedir, "sss", dealname + ".sss") if not os.path.exists(os.path.join(basedir, "sss")): os.makedirs(os.path.join(basedir, "sss")) cdrscenarios = os.path.join(basedir, "csv", dealname + "-cdr.csv") recoveryscenarios = os.path.join(basedir, "csv", dealname + "-recovery.csv") fh2 = open(output, "w") fhcdr = open(cdrscenarios, "r") fhrecovery = open(recoveryscenarios, "r") csvcdr = reader(fhcdr) csvrecovery = reader(fhrecovery) cdrline = csvcdr.next() cdrline = "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline]) +"\n" recoveryline = csvrecovery.next() recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) for recovery in recoveryline]) + "\n" i=1 with open(input) as fh: for line in fh: if "DEAL_NAME" in line: newline = "DEAL_NAME=" + dealname.upper() fh2.write(newline) continue if pattern11.match(line): line = re.sub(pattern11, r"\1{0}\2", line).format(dealname.upper()).rstrip() if reinv_assets["REINV_TBA1"] == "Float": line = line + "COUP_SPR=2.5|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n" elif reinv_assets["REINV_TBA1"] == "Fixed": line = line + "COUP_SPR=7|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n" fh2.write(line) continue if pattern12.match(line): line = re.sub(pattern12, r"\1{0}\2", line).format(dealname.upper()).rstrip() if reinv_assets["REINV_TBA2"] == "Float": line = line + "COUP_SPR=2.5|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n" elif reinv_assets["REINV_TBA2"] == "Fixed": line = line + "COUP_SPR=7|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE=84|\n" fh2.write(line) continue if pattern2.match(line): line = re.sub(pattern2, r"\1{0}\2{1}", line).format(dealname.upper(), reinvenddate) fh2.write(line) continue if pattern3.match(line): if reinv_assets['REINV_TBA1'] == 'Fixed': line = re.sub(pattern3, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(fixedreinvprices)) elif reinv_assets['REINV_TBA1'] == 'Float': line = re.sub(pattern3, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(floatreinvprices)) fh2.write(line) continue if pattern4.match(line): if reinv_assets['REINV_TBA2'] == 'Fixed': line = re.sub(pattern4, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(fixedreinvprices)) elif reinv_assets['REINV_TBA2'] == 'Float': line = re.sub(pattern4, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(floatreinvprices)) fh2.write(line) continue if pattern5.match(line): if not reinv_assets['REINV_TBA2']: line = re.sub(pattern5, r"\1{0}\2{1}", line).format(dealname.upper(), 100) elif reinv_assets['REINV_TBA1'] == 'Fixed': line = re.sub(pattern5, r"\1{0}\2{1}", line).format(dealname.upper(), reinvfixedpercentage) elif reinv_assets['REINV_TBA1'] == 'Float': line = re.sub(pattern5, r"\1{0}\2{1}", line).format(dealname.upper(), reinvfloatpercentage) fh2.write(line) continue if pattern6.match(line): if reinv_assets['REINV_TBA2'] == 'Float': line = re.sub(pattern6, r"\1{0}\2{1}", line).format(dealname.upper(), reinvfloatpercentage) elif reinv_assets['REINV_TBA2'] == 'Fixed': line = re.sub(pattern6, r"\1{0}\2{1}", line).format(dealname.upper(), reinvfixedpercentage) fh2.write(line) continue if pattern7.search(line): line = re.sub(pattern7, dealname.upper(), line) fh2.write(line) continue # if "STANDARD_VAR" in line: # newline = "STANDARD_VAR[REINVEST_PRICE,1]=" + " ".join(reinvprices) # fh2.write(newline) # continue if "LOSS_RATE[DEAL,{0}]".format(i) in line: newcdrline = "LOSS_RATE[DEAL,{0}]=".format(i) + cdrline fh2.write(newcdrline) continue if "LOSS_SEVERITY[DEAL,{0}]".format(i) in line: newrecoveryline = "LOSS_SEVERITY[DEAL,{0}]=".format(i) + recoveryline fh2.write(newrecoveryline) i=i+1 if i<=100: cdrline = csvcdr.next() cdrline = "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline]) + "\n" recoveryline = csvrecovery.next() recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) \ for recovery in recoveryline]) + "\n" continue fh2.write(line) fh2.close() fhrecovery.close() fhcdr.close() cursor.close() conn.close() # sed -i -e "s/\(LOSS_NONPERF_SEVERITY\\[DEAL,[0-9]*\\]\)=.*$/\1=mkt(70)/g" stonln1_100.sss