import os import os.path import datetime import csv from csv import reader import json import re import psycopg2 import common import sys pattern11 = re.compile("(REINVEST\[)\w+(::REINV_TBA1\]\[DEAL,\d+\]=)(.*$)") pattern12 = re.compile("(REINVEST\[)\w+(::REINV_TBA2\]\[DEAL,\d+\]=)(.*$)") # 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("GOLDL3") reinvfloatpercentage = 85 reinvfixedpercentage = 15 def get_reinv_assets(dealname, workdate, cursor): d = {'REINV_TBA1': None, 'REINV_TBA2': None} sqlstr = 'select * from et_historicaldealinfo(%s, %s) where ReinvFlag IS TRUE' cursor.execute(sqlstr, (dealname, workdate)) reinvassets = cursor.fetchall() for line in reinvassets: d[line[3]] = line[22] return d def convert_reinvtoperct(d): newd = {'REINV_TBA1': None, 'REINV_TBA2': None} if not d['REINV_TBA2']: newd['REINV_TBA1'] = 100 return newd if d['REINV_TBA1'] == d['REINV_TBA2']: # case when we have two float or two fixed assets newd['REINV_TBA1'] = reinvfloatpercentage newd['REINV_TBA2'] = reinvfixedpercentage elif d['REINV_TBA1'] == 'FLOAT': newd['REINV_TBA1'] = reinvfloatpercentage newd['REINV_TBA2'] = reinvfixedpercentage elif d['REINV_TBA1'] == 'FIXED': newd['REINV_TBA1'] = reinvfixedpercentage newd['REINV_TBA2'] = reinvfloatpercentage return newd def generate_scenarios(workdate, dealnames, conn, cursor): prometheus = os.path.join(common.root, "Scenarios", "prometheus.sss") n_scenarios = 100 for dealname in dealnames: 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") reinvflag = True else: print "missing reinvestment end date" reinvflag = False reinv_assets = get_reinv_assets(dealname, workdate, cursor) perct_reinv_assets = convert_reinvtoperct(reinv_assets) basedir = os.path.join(common.root, "Scenarios", "Intex curves_" + workdate) floatreinvprices = [] fixedreinvprices = [] try: with open(os.path.join(basedir, "csv", dealname + "-rollingmat"), "rb") as fh: rollingmat = fh.readline().rstrip() with open(os.path.join(basedir, "csv", dealname + "-floatreinvprices.csv"), "rb") as fhreinv: for line in fhreinv: floatreinvprices.append(line.rstrip("\n").split(",")) with open(os.path.join(basedir, "csv", dealname + "-fixedreinvprices.csv"), "rb") as fhreinv: for line in fhreinv: fixedreinvprices.append(line.rstrip("\n").split(",")) except IOError: reinvflag=False 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]) recoveryline = csvrecovery.next() recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) for recovery in recoveryline]) i=1 with open(prometheus) as fh: for line in fh: line = line.rstrip() if "DEAL_NAME" in line: newline = "DEAL_NAME=" + dealname.upper() + "\r\n" fh2.write(newline) continue if not reinvflag and "DO_REINV" in line: fh2.write("DO_REINV[DEAL]=0\r\n") continue if reinvflag: if pattern11.match(line): line = re.sub(pattern11, r"\1{0}\2", line).format(dealname.upper()) if reinv_assets["REINV_TBA1"] == "FLOAT": coupon = 4 elif reinv_assets["REINV_TBA1"] == "FIXED": coupon = 7 line = line + \ "COUP_SPR={0}|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE={1}|".format(coupon, rollingmat) fh2.write(line + "\r\n") continue if pattern12.match(line): line = re.sub(pattern12, r"\1{0}\2", line).format(dealname.upper()) if reinv_assets["REINV_TBA2"] == "FLOAT": coupon = 4 elif reinv_assets["REINV_TBA2"] == "FIXED": coupon = 7 line = line + \ "COUP_SPR={0}|AMORT=Bullet|USE_REINVEST_PIP=1|MAT_DATE={1}|".format(coupon, rollingmat) fh2.write(line + "\r\n") continue if pattern2.match(line): line = re.sub(pattern2, r"\1{0}\2{1}", line).format(dealname.upper(), reinvenddate) fh2.write(line + "\r\n") 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[i-2])) elif reinv_assets['REINV_TBA1'] == 'FLOAT': line = re.sub(pattern3, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(floatreinvprices[i-2])) fh2.write(line + "\r\n") 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[i-2])) elif reinv_assets['REINV_TBA2'] == 'FLOAT': line = re.sub(pattern4, r"\1{0}\2{1}", line).format(dealname.upper(), " ".join(floatreinvprices[i-2])) fh2.write(line + "\r\n") continue if pattern5.match(line): if reinv_assets['REINV_TBA1']: line = re.sub(pattern5, r"\1{0}\2{1}", line).format( dealname.upper(), perct_reinv_assets['REINV_TBA1']).rstrip() fh2.write(line + "\r\n") continue if pattern6.match(line): if reinv_assets['REINV_TBA2']: line = re.sub(pattern6, r"\1{0}\2{1}", line).format( dealname.upper(), perct_reinv_assets['REINV_TBA2']).rstrip() fh2.write(line + "\r\n") continue if pattern7.search(line): line = re.sub(pattern7, dealname.upper(), line) fh2.write(line + "\r\n") 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 + "\r\n") continue if "LOSS_SEVERITY[DEAL,{0}]".format(i) in line: newrecoveryline = "LOSS_SEVERITY[DEAL,{0}]=".format(i) + recoveryline fh2.write(newrecoveryline + "\r\n") i = i + 1 if i <= n_scenarios: cdrline = csvcdr.next() cdrline = "\t".join(["{0:.3f}".format(float(cdr)) for cdr in cdrline]) + "\r\n" recoveryline = csvrecovery.next() recoveryline = "\t".join(["{0:.3f}".format(float(recovery)) \ for recovery in recoveryline]) + "\r\n" continue fh2.write(line + "\r\n") fh2.close() fhrecovery.close() fhcdr.close() print "generated scenarios for: {0}".format(dealname) if __name__ == "__main__": if len(sys.argv) > 1: workdate = sys.argv[1] else: workdate = str(datetime.date.today()) if len(sys.argv) > 2: dealnames = sys.argv[2:] else: dealnames = [d.split(".")[0] for d in os.listdir(os.path.join(common.root, "Scenarios", "Intex curves_" + workdate, "csv")) if "RData" in d] generate_scenarios(workdate, dealnames, common.conn, common.cursor) common.cursor.close() common.conn.close()