45 files changed, 964 insertions, 0 deletions
diff --git a/bandits.R b/R/bandits.R
index ee547e3a..ee547e3a 100644
--- a/bandits.R
+++ b/R/bandits.R
diff --git a/build_SC.R b/R/build_SC.R
index 11e2ec7b..11e2ec7b 100644
--- a/build_SC.R
+++ b/R/build_SC.R
diff --git a/calibrate_tranches.R b/R/calibrate_tranches.R
index 3e2599fc..3e2599fc 100644
--- a/calibrate_tranches.R
+++ b/R/calibrate_tranches.R
diff --git a/cds_functions.R b/R/cds_functions.R
index 7404328d..7404328d 100644
--- a/cds_functions.R
+++ b/R/cds_functions.R
diff --git a/cds_functions_generic.R b/R/cds_functions_generic.R
index e6e5a654..e6e5a654 100644
--- a/cds_functions_generic.R
+++ b/R/cds_functions_generic.R
diff --git a/cds_utils.R b/R/cds_utils.R
index afb8f400..afb8f400 100644
--- a/cds_utils.R
+++ b/R/cds_utils.R
diff --git a/clopricer.R b/R/clopricer.R
index d428c2e8..d428c2e8 100644
--- a/clopricer.R
+++ b/R/clopricer.R
diff --git a/deal_pricer.R b/R/deal_pricer.R
index 0fb821de..0fb821de 100644
--- a/deal_pricer.R
+++ b/R/deal_pricer.R
diff --git a/dumpdata.R b/R/dumpdata.R
index d0f4bc73..d0f4bc73 100644
--- a/dumpdata.R
+++ b/R/dumpdata.R
diff --git a/ema.R b/R/ema.R
index b4af957e..b4af957e 100644
--- a/ema.R
+++ b/R/ema.R
diff --git a/etdb.R b/R/etdb.R
index 93b72e27..93b72e27 100644
--- a/etdb.R
+++ b/R/etdb.R
diff --git a/filterruns.R b/R/filterruns.R
index b6d93f73..b6d93f73 100644
--- a/filterruns.R
+++ b/R/filterruns.R
diff --git a/index_definitions.R b/R/index_definitions.R
index 71c61688..71c61688 100644
--- a/index_definitions.R
+++ b/R/index_definitions.R
diff --git a/interpweights.R b/R/interpweights.R
index 03de58d7..03de58d7 100644
--- a/interpweights.R
+++ b/R/interpweights.R
diff --git a/interpweights_2.R b/R/interpweights_2.R
index 189915f5..189915f5 100644
--- a/interpweights_2.R
+++ b/R/interpweights_2.R
diff --git a/intex_deals_functions.R b/R/intex_deals_functions.R
index a85b9f24..a85b9f24 100644
--- a/intex_deals_functions.R
+++ b/R/intex_deals_functions.R
diff --git a/l1tf.R b/R/l1tf.R
index eab5208d..eab5208d 100644
--- a/l1tf.R
+++ b/R/l1tf.R
diff --git a/latestprices.R b/R/latestprices.R
index 6394e695..6394e695 100644
--- a/latestprices.R
+++ b/R/latestprices.R
diff --git a/load_bloomberg_data.R b/R/load_bloomberg_data.R
index b3dff18a..b3dff18a 100644
--- a/load_bloomberg_data.R
+++ b/R/load_bloomberg_data.R
diff --git a/loadcashflows.R b/R/loadcashflows.R
index 26ea12f1..26ea12f1 100644
--- a/loadcashflows.R
+++ b/R/loadcashflows.R
diff --git a/loan_universe.R b/R/loan_universe.R
index 821022a3..821022a3 100644
--- a/loan_universe.R
+++ b/R/loan_universe.R
diff --git a/lossdistrib.c b/R/lossdistrib.c
index c873df9e..c873df9e 100644
--- a/lossdistrib.c
+++ b/R/lossdistrib.c
diff --git a/mapping.R b/R/mapping.R
index 2dfafe64..2dfafe64 100644
--- a/mapping.R
+++ b/R/mapping.R
diff --git a/mapping_fast.R b/R/mapping_fast.R
index 1692f3fd..1692f3fd 100644
--- a/mapping_fast.R
+++ b/R/mapping_fast.R
diff --git a/optimization.R b/R/optimization.R
index 8f662b4e..8f662b4e 100644
--- a/optimization.R
+++ b/R/optimization.R
diff --git a/R/parse_intex.R b/R/parse_intex.R
new file mode 100644
index 00000000..b9c52e07
--- /dev/null
+++ b/R/parse_intex.R
@@ -0,0 +1,18 @@
+root = "//WDSENTINEL/share/CorpCDOs"
+source(file.path(root, "R", "intex_deals_functions.R"))
+source(file.path(root, "R", "etdb.R"))
+dealnames <- c("limes", "stonln1")
+cusips <- cusipsfromdealnames(dealnames)
+
+deals.universe <- dbGetQuery(dbCon, "select distinct dealname from clo_universe order by dealname asc")$dealname
+cusips.universe <- cusipsfromdealnames(deals.universe)
+n.scenarios <- 100
+offset <- 2
+r <- data.frame()
+for(cusip in cusips){
+    data <- read.table(paste(cusip, "-PY.txt", sep=""), sep="\t", header=T, nrow=3)
+    price <- sum(as.numeric(sub("\\((.*)\\)", "-\\1", data[1,1:n.scenarios+offset])), na.rm=T)/n.scenarios
+    wal <- sum(as.numeric(sub("\\((.*)\\)", "-\\1", data[2,1:n.scenarios+offset])), na.rm=T)/n.scenarios
+    duration <- sum(as.numeric(sub("\\((.*)\\)", "-\\1", data[3,1:n.scenarios+offset])), na.rm=T)/n.scenarios
+    r <- rbind(r, data.frame(cusip, price, wal, duration))
+}
diff --git a/plot_distributions.R b/R/plot_distributions.R
index 0b369065..0b369065 100644
--- a/plot_distributions.R
+++ b/R/plot_distributions.R
diff --git a/reinvestingdistribution.R b/R/reinvestingdistribution.R
index e2c42e3e..e2c42e3e 100644
--- a/reinvestingdistribution.R
+++ b/R/reinvestingdistribution.R
diff --git a/test.cds_functions.R b/R/test.cds_functions.R
index c30651ba..c30651ba 100644
--- a/test.cds_functions.R
+++ b/R/test.cds_functions.R
diff --git a/time_of_default.R b/R/time_of_default.R
index 369621ab..369621ab 100644
--- a/time_of_default.R
+++ b/R/time_of_default.R
diff --git a/tranche_functions.R b/R/tranche_functions.R
index e1f0ab91..e1f0ab91 100644
--- a/tranche_functions.R
+++ b/R/tranche_functions.R
diff --git a/transactions.R b/R/transactions.R
index e36f9c31..e36f9c31 100644
--- a/transactions.R
+++ b/R/transactions.R
diff --git a/yieldcurve.R b/R/yieldcurve.R
index a21937b9..a21937b9 100644
--- a/yieldcurve.R
+++ b/R/yieldcurve.R
diff --git a/python/clo_universe.py b/python/clo_universe.py
new file mode 100644
index 00000000..ae3f0988
--- /dev/null
+++ b/python/clo_universe.py
@@ -0,0 +1,92 @@
+import psycopg2
+import os
+import os.path
+import datetime
+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/"
+
+universe = "data/clo_universe_intex_2012-11-21.txt"
+
+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()
+
+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 line['Latest Update']=='' or line['Latest Update'] is None:
+            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] is not None:
+                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)"]
+        if dealname not in deallist or line['Latest Update'] > deallist[dealname]:
+            sqlstring = \
+                "INSERT INTO clo_universe(dealname, \"Deal Name\", Manager, \"Orig Deal Bal\"," \
+                "\"Curr Deal Bal\", \"Orig Collat Bal\", \"Curr Collat Bal\", \"Tranche Factor\"," \
+                "\"Principal Bal\", \"Interest Bal\",\"CDO Percentage\", \"Defaulted Bal\", \"Curr Coupon\"," \
+                "\"Deal Issue Date\", Maturity, \"Deal Next Pay Date\", \"Reinv End Date\"," \
+                "\"Latest Update\", \"Deal Cusip List\", PaidDown)" \
+                "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()
+
+# cursor.execute("select dealname,\"Deal Cusip List\" from clo_universe");
+# cursor2 = conn.cursor()
+# for record in cursor:
+#     for cusip in record[1].split(","):
+#         tuple = (record[0], cusip)
+#         cursor2.execute("INSERT INTO dealcusipmapping(dealname, cusip) VALUES(%s, %s)", tuple)
+# cursor2.close()
+# conn.commit()
+
+cursor.close()
+conn.close()
diff --git a/python/cusip_universe.py b/python/cusip_universe.py
new file mode 100644
index 00000000..d88a44e0
--- /dev/null
+++ b/python/cusip_universe.py
@@ -0,0 +1,74 @@
+import psycopg2
+import os
+import os.path
+import datetime
+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/"
+
+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()
+
+prog = re.compile("\((.*)\)")
+
+cursor.execute("delete from cusip_universe")
+count = 0
+for i in xrange(9):
+    cusip_universe_file = "TrInfo{0}.txt".format(i+1)
+    with open( os.path.join(root, "data", 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()
+            for key in ['Curr Balance', 'Orig Balance', 'Orig Attachment Point', 'Curr Attachment Point',
+                        'Orig Detachment Point', 'Curr Detachment Point', 'Factor', 'Coupon']:
+                line[key] = line[key].replace(",", "")
+                if prog.match(line[key]):
+                    try:
+                        line[key] = float(prog.match(line[key]).group(1))
+                    except ValueError:
+                        pdb.set_trace()
+                line[key] = convertToNone(line[key])
+            if "," in str(line['Floater Spread']):
+                line['Floater Spread'] = line['Floater Spread'].split(",")[0]
+            for key in ['Floater Spread', 'Floater Index']:
+                if prog.match(line[key]):
+                    try:
+                        line[key] = float(prog.match(line[key]).group(1))
+                    except ValueError:
+                        pdb.set_trace()
+                line[key] = convertToNone(line[key])
+            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)"
+            try:
+                cursor.execute(sqlstring, line)
+            except psycopg2.DataError as error:
+                pdb.set_trace()
+        print count
+    conn.commit()
+
+cursor.close()
+conn.close()
diff --git a/python/intex_scenarios.py b/python/intex_scenarios.py
new file mode 100644
index 00000000..70bf8226
--- /dev/null
+++ b/python/intex_scenarios.py
@@ -0,0 +1,42 @@
+import os
+import os.path
+import datetime
+from datetime import date
+import pdb
+import csv
+
+if os.name =='nt':
+    root = "//WDsentinel/share/CorpCDOs/Scenarios"
+elif os.name == 'posix':
+    root = '/home/share/CorpCDOs/Scenarios'
+input = os.path.join(root, "prometheus.sss")
+for dealname in ["stonln1", "babs072", "flags5", "cent11", "wasatl", "oceant2", "acacl071", "limes"]:
+    output = os.path.join(root, dealname + ".sss")
+    cdrscenarios = os.path.join(root, dealname + "-cdr.csv")
+    recoveryscenarios = os.path.join(root, dealname + "-recovery.csv")
+    fh2 = open(output, "w")
+    fhcdr = open(cdrscenarios, "r")
+    fhrecovery = open(recoveryscenarios, "r")
+    cdrline = "\t".join(fhcdr.readline().rstrip().split(",")) +"\n"
+    recoveryline = "\t".join(fhrecovery.readline().rstrip().split(",")) + "\n"
+
+    i=1
+    with open(input) as fh:
+        for line in fh:
+            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
+                cdrline = "\t".join(fhcdr.readline().rstrip().split(",")) + "\n"
+                recoveryline = "\t".join(fhrecovery.readline().rstrip().split(",")) + "\n"
+                continue
+            fh2.write(line)
+    fh2.close()
+    fhrecovery.close()
+    fhcdr.close()
+
+# sed -i -e "s/\(LOSS_NONPERF_SEVERITY\\[DEAL,[0-9]*\\]\)=.*$/\1=mkt(70)/g" stonln1_100.sss
diff --git a/python/list_dealnames.py b/python/list_dealnames.py
new file mode 100644
index 00000000..8d6a09d4
--- /dev/null
+++ b/python/list_dealnames.py
@@ -0,0 +1,6 @@
+#! /usr/bin/python2
+import os
+filenames = os.listdir("/home/guillaume/share/CorpCDOs/Collaterals")
+
+for name in sorted([fn.split(",")[0].lower() for fn in filenames]):
+	print name
diff --git a/python/load_bloomberg_data.py b/python/load_bloomberg_data.py
new file mode 100644
index 00000000..dba8ae1a
--- /dev/null
+++ b/python/load_bloomberg_data.py
@@ -0,0 +1,72 @@
+import psycopg2
+import os.path
+import datetime
+from datetime import date
+import pdb
+import csv
+
+
+if os.name =='nt':
+    root = "//WDsentinel/share/CorpCDOs/data/bloomberg"
+elif os.name == 'posix':
+    root = '/home/share/CorpCDOS/data/bloomberg'
+
+
+def convertToNone(s):
+    return None if (s=='' or s=='NA') else s
+
+conn = psycopg2.connect(database="ET",
+                        user="et_user",
+                        password="Serenitas1",
+                        host="192.168.1.108")
+cursor = conn.cursor()
+
+for filename in os.listdir(root):
+    cursor.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
+    corpcusips = dict(cursor)
+    with open( os.path.join(root, filename), "r") as fh:
+        dr = csv.DictReader(fh)
+        if "datacorp" in filename:
+            for line in dr:
+                if line["LAST_UPDATE_DT"] != 'NA':
+                        line["LAST_UPDATE_DT"] = \
+                            datetime.datetime.strptime(line["LAST_UPDATE_DT"], '%Y-%m-%d').date()
+                else:
+                    line["LAST_UPDATE_DT"] = \
+                        datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
+                    line["PX_LAST"] = None
+                    if line["MATURITY"] != 'NA':
+                        line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d')
+                row = [convertToNone(line[field]) for field in dr.fieldnames]
+                # cursor.execute("SELECT max(PricingDate) from bloomberg_corp where Cusip = %s", (line['CUSIP'],))
+                # currentpricingdate = cursor.fetchone()[0]
+                if line['CUSIP'] not in corpcusips or corpcusips[line['CUSIP']]<line['LAST_UPDATE_DT']:
+                    try:
+                        cursor.execute("INSERT INTO bloomberg_corp(Cusip, Price, PricingDate, Issuer,"
+                                       "Maturity, Coupon, CouponType, Frequency, Spread, Libor_floor,"
+                                       "loan_margin, covlite, secondlien, defaulted, Source)"
+                                       "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
+                                       tuple(row))
+                    except IndexError:
+                        pdb.set_trace()
+                conn.commit()
+        elif "datamtge" in filename:
+            cursor.execute("select * from bloomberg_mtge")
+            mtgecusips = {record[0]: None for record in cursor}
+            for line in dr:
+                if line["MATURITY"] != 'NA':
+                    line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d').date()
+                row = [convertToNone(line[field]) for field in dr.fieldnames]
+                # sqlstr = "SELECT * from bloomberg_mtge where Cusip=%s"
+                # cursor.execute(sqlstr, (line['CUSIP'],))
+                # found = cursor.fetchone()
+                if line['CUSIP'] not in mtgecusips:
+                    cursor.execute("INSERT INTO bloomberg_mtge(Cusip, Issuer,"
+                                   "Maturity, Coupon, CouponType, Frequency, Spread,"
+                                   "Moody, InitialMoody)"
+                                   "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
+                                   tuple(row))
+            conn.commit()
+
+cursor.close()
+conn.close()
diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py
new file mode 100644
index 00000000..1b1a0861
--- /dev/null
+++ b/python/load_intex_collateral.py
@@ -0,0 +1,101 @@
+import psycopg2
+import os.path
+import re
+import datetime
+import csv
+from datetime import date
+import pdb
+
+if os.name=='nt':
+    root = "//WDSENTINEL/share/CorpCDOs/"
+elif os.name=='posix':
+    root = "/home/share/CorpCDOs/"
+
+root = root + "Collaterals_2012-11-20"
+
+fields = ['Asset Name', 'Issuer', 'Current Balance', 'Maturity Date', 'Asset Subtype', \
+          'Asset Type', 'Gross Coupon', 'Spread', 'Frequency', 'Next Paydate', 'Second Lien', \
+          'LoanX ID', 'CUSIP', 'Market Price', 'Market Price Source', 'Price Date', 'Fixed or Float', \
+          'Defaulted Flag', 'Security Sub-Category', 'Structured Finance Security']
+
+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()
+prog = re.compile("\((.*)\)")
+
+for dealname in os.listdir(root):
+    #dealname, updatedate = line.rstrip().split()
+    # updatedate = datetime.datetime.strptime(updatedate, '%m/%d/%Y')
+    #dealname = dealname.upper() + ",AD.txt"
+    with open( os.path.join(root, dealname), "r") as fh:
+        dr = csv.DictReader(fh, dialect = 'excel-tab')
+        dealname , ending = dealname.split(',')
+        dealname = dealname.lower()
+        data = []
+        for line in dr:
+            missingfields = list(set(fields) - set(dr.fieldnames))
+            for field in missingfields:
+                line[field] = None
+            if line['Fixed or Float'] is not None:
+                line['Fixed or Float'] = line['Fixed or Float'].upper()
+            if 'LoanX ID' in dr.fieldnames and len(line['LoanX ID']) >8:
+                print "dubious id found: {0}".format(line['LoanX ID'])
+                line['LoanX ID'] = line['LoanX ID'][:8]
+            try:
+                # make sure the string is utf8 safe
+                line['Issuer']= line['Issuer'].decode('windows-1252')
+                if line['Issuer']=='' or line['Issuer'].lower() == 'dummy':
+                    break
+            except AttributeError as detail:
+                print dealname
+            #convert (x) to -x
+            for field in ['Spread', 'Gross Coupon']:
+                if line[field] and prog.match(line[field]):
+                    line[field] = - float(prog.match(line[field]).group(1))
+            if line['Market Price'] != '' and isinstance(line['Market Price'], str):
+                line['Market Price'] = float(line['Market Price'].replace(',',''))
+            if isinstance(line['Current Balance'], str):
+                line['Current Balance'] = float(line['Current Balance'].replace(',',''))
+            try:
+                r = [convertToNone(line[field]) for field in fields]
+            except KeyError as detail:
+                print detail
+                pdb.set_trace()
+            data.append(r)
+    cursor.execute( "select \"Latest Update\" from clo_universe "
+                    "where dealname='{0}'".format(dealname))
+    try:
+        updatedate = cursor.fetchone()[0]
+    except TypeError:
+        pdb.set_trace()
+    cursor.execute( "select max(updatedate) from et_collateral where dealname='{0}'".format(dealname))
+    updatedate2 = cursor.fetchone()[0]
+    if updatedate2 is None:
+        pdb.set_trace()
+    if updatedate>updatedate2:
+        for row in data:
+            # print dealname, row
+            sqlstr = "INSERT INTO ET_COLLATERAL(dealname, updatedate, name, IssuerName," \
+                "CurrentBalance, Maturity, AssetSubtype, AssetType, GrossCoupon," \
+                "Spread, Frequency, NextPaydate, SecondLien, LoanXID, Cusip, IntexPrice," \
+                "IntexPriceSource, IntexPriceDate, FixedOrFloat, DefaultedFlag, CovLite, isCDO)" \
+                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s," \
+                "%s, %s, %s)"
+            try:
+                cursor.execute(sqlstr, (dealname, updatedate) + tuple(row))
+            except psycopg2.DataError:
+                pdb.set_trace()
+            except psycopg2.IntegrityError:
+                # crazy hack intex unique id is not really unique
+                if dealname in ["caval1", "octag11", "windriv2"]:
+                    conn.rollback()
+                    row[0] = "Affinion Group - Tranche B Term Loan - 18"
+                    cursor.execute(sqlstr, (dealname, updatedate) + tuple(row))
+                else:
+                    pdb.set_trace()
+            conn.commit()
+
+cursor.close()
+conn.close()
diff --git a/python/load_markit_data.py b/python/load_markit_data.py
new file mode 100644
index 00000000..a58341ac
--- /dev/null
+++ b/python/load_markit_data.py
@@ -0,0 +1,59 @@
+import psycopg2
+import os
+import os.path
+import datetime
+from datetime import date
+import pdb
+import csv
+
+if os.name =='nt':
+    root = "//WDsentinel/share/CorpCDOs/data/markit"
+elif os.name == 'posix':
+    root = '/home/share/CorpCDOS/data/markit'
+
+def convertToNone(s):
+    return None if (s=='' or s=='NA') else s
+
+conn = psycopg2.connect(database="ET",
+                        user="et_user",
+                        password="Serenitas1",
+                        host="192.168.1.108")
+cursor = conn.cursor()
+
+fields = ["LoanX ID", "Issuer", "Deal Name", "Facility", "Industry", \
+          "Current SP Rating", "Current Moodys Rating", "Initial Amount",\
+          "Final Maturity", "Initial Spread", "Bid", "Offer", "Depth", \
+          "Spread To Maturity"]
+
+latestdate = cursor.execute("SELECT MAX(pricingdate) from markit_prices")
+latestdate = cursor.fetchone()[0]
+
+for filename in os.listdir(root):
+    date = datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
+    # only update the new data
+    if latestdate is None or date > latestdate:
+        with open( os.path.join(root, filename) ) as fh:
+            dr = csv.DictReader(fh)
+            data = []
+            for line in dr:
+                try:
+                    line['Final Maturity'] = \
+                        datetime.datetime.strptime(line['Final Maturity'], '%d-%b-%y')
+                except ValueError:
+                    if line['Final Maturity'] !='':
+                        print 'Maturity non empty'
+                if line['Depth'] == 'implied':
+                    line['Depth']=0
+                try:
+                    temp = [convertToNone(line[key]) for key in fields]
+                except KeyError:
+                    pdb.set_trace()
+                data.append(temp)
+
+        for row in data:
+            cursor.execute("INSERT INTO markit_prices(LoanXID, Issuer, DealName,"
+                           "Facility, Industry, SP, Moodys, Amount, Maturity,"
+                           "Spread, Bid, Offer, Depth, STM, PricingDate)"
+                           "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
+                           tuple(row) + (date,))
+        conn.commit()
diff --git a/python/markit_download.py b/python/markit_download.py
new file mode 100644
index 00000000..b4145c22
--- /dev/null
+++ b/python/markit_download.py
@@ -0,0 +1,6 @@
+import requests
+
+# r=requests.get('https://loans.markit.com/loanx/LoanXMarks.csv?LEGALENTITY=serecap&USERNAME=serecapuser&PASSWORD=Welcome1&EOD=Y')
+r = requests.get('https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY=serecap&USERNAME=serecapuser&PASSWORD=Welcome1')
+for line in r.text.split('\n'):
+    print line
diff --git a/python/query_runner.py b/python/query_runner.py
new file mode 100644
index 00000000..45595f98
--- /dev/null
+++ b/python/query_runner.py
@@ -0,0 +1,12 @@
+import psycopg2
+conn = psycopg2.connect(database="ET", user="guillaume")
+cursor = conn.cursor()
+
+# cursor.execute("select select a.name,a.issuername,a.loanxid,b.bid from et_collateral a left outer join markit_prices b on a.loanxid=b.loanxid where a.dealname='octagon8'")
+cursor.execute("select a.name, COALESCE(b.bid,c.price) as price, a.currentbalance from et_collateral a left outer join markit_prices b on a.loanxid=b.loanxid left outer join bloomberg_prices c on a.cusip=c.cusip where a.dealname='octagon8'")
+
+for line in cursor:
+    # import pdb;pdb.set_trace()
+    print  "\t".join(map(str,line))
+cursor.close()
+conn.close()
diff --git a/python/select_clean.py b/python/select_clean.py
new file mode 100644
index 00000000..f08fc0b2
--- /dev/null
+++ b/python/select_clean.py
@@ -0,0 +1,12 @@
+import os
+root = "/home/guillaume/share/CorpCDOs/Collaterals"
+for dealname in os.listdir(root):
+
+    with open( os.path.join(root, dealname), "r") as fh:
+        # import pdb; pdb.set_trace()
+        header = fh.readline().rstrip().split("\t")
+        if "Underlying Deal" in header:
+            print dealname
+        line = fh.readline().split("\t")
+        if line[0] == "Sum/WAVG":
+            print dealname
diff --git a/sql/et_tables.sql b/sql/et_tables.sql
new file mode 100644
index 00000000..797468cf
--- /dev/null
+++ b/sql/et_tables.sql
@@ -0,0 +1,367 @@
+CREATE TYPE intex_fixedorfloat AS ENUM('FIXED', 'FLOAT');
+
+CREATE TABLE et_collateral (
+    dealname varchar(8) NOT NULL,
+    updatedate date,
+    Name text,
+    IssuerName text,
+    CurrentBalance float,
+    Maturity date,
+    AssetSubtype varchar(10),
+    AssetType varchar(25),
+    GrossCoupon float default NULL,
+    Spread float default NULL,
+    Frequency varchar(1),
+    NextPaydate date,
+    SecondLien boolean,
+    LoanXID varchar(10),
+    ET_LoanXID varchar(10) default NULL,
+    Cusip varchar(9),
+    ET_Cusip varchar(10) default NULL,
+    IntexPrice float,
+    IntexPriceSource text,
+    IntexPriceDate date,
+    UserPrice float default NULL,
+    UserPriceDate date default NULL,
+    FixedOrFloat intex_fixedorfloat,
+    DefaultedFlag boolean,
+    CovLite text,
+    isCDO boolean,
+    PRIMARY KEY (updatedate, Name, dealname)
+);
+CREATE INDEX dealname_idx ON et_collateral(dealname, updatedate);
+
+ALTER TABLE et_collateral OWNER TO et_user;
+
+CREATE TABLE cusip_user_mapping (
+       IssuerName text,
+       Maturity date,
+       GrossCoupon float default NULL,
+       Spread float default NULL,
+       LoanXID varchar(8) default NULL,
+       Cusip varchar(9) default NULL
+);
+
+CREATE TABLE loanx_user_mapping (
+       IssuerName text,
+       Maturity date,
+       Spread float default NULL,
+       LoanxID varchar(8) default NULL
+);
+
+ALTER TABLE cusip_user_mapping OWNER TO et_user;
+ALTER TABLE loanx_user_mapping OWNER TO et_user;
+
+CREATE TABLE markit_prices (
+       LoanXID varchar(8),
+       Issuer text,
+       DealName text,
+       Facility text,
+       Industry text,
+       SP varchar(4),
+       Moodys varchar(4),
+       Amount float,
+       Maturity date,
+       Spread float,
+       Bid float,
+       Offer float,
+       Depth int,
+       STM float,
+       PricingDate date,
+       PRIMARY KEY (LoanXID, PricingDate)
+);
+
+ALTER TABLE markit_prices OWNER TO et_user;
+
+CREATE TABLE markit_facility (
+       LoanXID varchar(8),
+       PMDID float(10),
+       IssuerName varchar(100),
+       dealname varchar(60),
+       facility_type varchar(40),
+       loanx_facility_type varchar(50),
+       industry varchar(50),
+       initial_amount float(15),
+       initial_spread float(7),
+       maturity date,
+       created_time date,
+       modified_time date
+);
+
+
+CREATE TYPE bloomberg_source AS ENUM('TRAC', 'BGN', 'MSG1', 'BVAL', 'EXCH', 'BCMP', 'LCPR', 'BFV');
+
+CREATE TYPE bloomberg_cpntype AS ENUM('FIXED', 'FLOATING', 'EXCHANGED', 'DEFAULTED',
+'PAY-IN-KIND', 'VARIABLE', 'ZERO COUPON', 'STEP CPN', 'FLAT TRADING', 'NONE', 'FUNGED',
+'STEP', 'ZERO');
+
+CREATE TABLE bloomberg_corp (
+       Cusip varchar(9),
+       Price float,
+       PricingDate date,
+       Issuer text,
+       Maturity date,
+       Coupon float,
+       CouponType bloomberg_cpntype,
+       Frequency int,
+       Spread float,
+       Libor_floor float,
+       loan_margin float,
+       covlite boolean,
+       secondlien boolean,
+       defaulted boolean,
+       Source bloomberg_source,
+       PRIMARY KEY (Cusip, PricingDate)
+);
+
+GRANT ALL ON bloomberg_corp TO et_user;
+
+CREATE TABLE bloomberg_mtge (
+       Cusip varchar(9),
+       Issuer text,
+       Maturity date,
+       Coupon float,
+       CouponType bloomberg_cpntype,
+       Frequency int,
+       Spread float,
+       Moody text,
+       InitialMoody text,
+       PRIMARY KEY (CUSIP)
+);
+
+GRANT ALL ON bloomberg_mtge TO et_user;
+
+CREATE TABLE clo_universe (
+       dealname varchar(10),
+       "Deal Name" text,
+       Manager text,
+       "Orig Deal Bal" float,
+       "Curr Deal Bal" float,
+       "Orig Collat Bal" float,
+       "Curr Collat Bal" float,
+       "Tranche Factor" float,
+       "Principal Bal" float,
+       "Interest Bal" float,
+       "CDO Percentage" float,
+       "Defaulted Bal" float,
+       "Curr Coupon" float,
+       "Deal Issue Date" date,
+       Maturity date,
+       "Deal Next Pay Date" date,
+       "Reinv End Date" date,
+       "Latest Update" date,
+       "Deal Cusip List" text,
+       PaidDown date,
+       PRIMARY KEY (dealname, "Latest Update")
+);
+
+GRANT ALL ON clo_universe TO et_user;
+
+CREATE TABLE cusip_universe (
+       Cusip varchar(9),
+       ISIN varchar(12),
+       dealname varchar(10),
+       tranche text,
+       Coupon float,
+       Orig_Balance float,
+       Curr_Balance float,
+       Factor float,
+       Orig_Moody text,
+       Curr_Moody text,
+       Orig_Attach float,
+       Orig_Detach float,
+       Curr_Attach float,
+       Curr_Detach float,
+       Floater_Index text,
+       Spread float,
+       PRIMARY KEY(Cusip)
+);
+
+GRANT ALL ON cusip_universe TO et_user;
+
+CREATE VIEW latest_clo_universe AS
+       SELECT b.*
+       FROM (SELECT MAX("Latest Update") AS latestdate, dealname FROM clo_universe GROUP BY dealname) a
+       JOIN clo_universe b ON a.dealname = b.dealname AND a.latestdate= b."Latest Update"
+       ORDER by dealname asc;
+
+GRANT ALL ON latest_clo_universe TO et_user;
+
+CREATE TABLE dealcusipmapping (
+       dealname varchar(10),
+       Cusip varchar(9),
+       PRIMARY KEY(Cusip)
+);
+
+GRANT ALL ON dealcusipmapping TO et_user;
+
+CREATE VIEW latest_markit_prices AS
+       SELECT b.*
+       FROM (SELECT MAX(pricingdate) AS latestdate, loanxid FROM markit_prices GROUP BY loanxid) a
+       JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate;
+
+GRANT ALL ON latest_markit_prices TO et_user;
+
+CREATE OR REPLACE FUNCTION et_historical_collateral(p_date date)
+       RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text,
+       currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25),
+       grosscoupon float, spread float, frequency varchar(1), nextpaydate date,
+       secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9),
+       et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date,
+       userprice float, userparicedate date, fixedorfloat intex_fixedorfloat,
+       defaultedflag boolean, covlite text, iscdo boolean) AS $$
+       DECLARE latestdate date;
+       BEGIN
+	RETURN QUERY SELECT a.* FROM (SELECT max(et_collateral.updatedate) AS latestdate,et_collateral.dealname
+	FROM et_collateral GROUP BY et_collateral.dealname) b
+	JOIN et_collateral a ON a.dealname=b.dealname AND a.updatedate=b.latestdate;
+       END;
+       $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION best_price (price1 float, pricedt1 date, price2 float, pricedt2 date)
+       RETURNS float
+AS $$
+   if price1 is None:
+      return price2
+   if price2 is None:
+      return price1
+   return price1 if pricedt1> pricedt2 else price2
+$$ LANGUAGE plpythonu;
+
+CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10))
+       RETURNS TABLE(issuername text, currentbalance float, maturity date,
+       fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float,
+       frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9),
+       covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean) AS $$
+       BEGIN
+	RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
+	currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
+	avg(coalesce((b.bid+b.offer)/2, c.price)) AS price,
+	avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
+	spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
+	a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
+	bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
+	bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
+	bool_or(a.defaultedflag) AS defaultedflag from et_collateral a
+	left join latest_markit_prices b on a.loanxid=b.loanxid left
+	join bloomberg_corp c on a.cusip=c.cusip where a.dealname = p_dealname
+	group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
+	a.frequency, a.cusip ORDER BY issuername;
+       END;
+       $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10), p_date date)
+       RETURNS TABLE(dealname varchar(8), updatedate date, name text, issuername text,
+       currentbalance float, maturity date, assetsubtype varchar(10), assettype varchar(25),
+       grosscoupon float, spread float, frequency varchar(1), nextpaydate date,
+       secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9),
+       et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date,
+       userprice float, userparicedate date, fixedorfloat intex_fixedorfloat,
+       defaultedflag boolean, covlite text, iscdo boolean) AS $$
+       DECLARE latestdate date;
+       BEGIN
+	SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral
+	WHERE et_collateral.dealname = p_dealname
+	AND et_collateral.updatedate <= p_date;
+	RETURN QUERY SELECT a.* FROM et_collateral a WHERE a.dealname = p_dealname AND
+	a.updatedate=latestdate ORDER BY lower(a.issuername);
+       END;
+       $$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION et_ReadMapped(p_issuername text, p_maturity date, p_spread float)
+       RETURNS TABLE(loanxid varchar(10), cusip varchar(9)) AS $$
+       BEGIN
+	RETURN QUERY SELECT loanxid, cusip from et_user_mapping a where a.issuername = p_issuername and a.maturity = p_maturity and a.spread = p_spread;
+       END;
+       $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION dealname_from_cusip(p_cusip varchar(9), OUT p_dealname varchar(8)) AS $$
+       BEGIN
+       SELECT dealname INTO p_dealname from dealcusipmapping where cusip=p_cusip;
+       END;
+       $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION historical_markit_prices(p_date date)
+       RETURNS TABLE(loanxid varchar(8), issuer text, dealname text, facility text,
+       industry text, sp varchar(4), moodys varchar(4), amount float, maturity date,
+       spread float, bid float, offer float, depth integer, stm float, pricingdate date) AS $$
+       BEGIN
+       RETURN QUERY SELECT b.loanxid, b.issuer, b.dealname, b.facility, b.industry, b.sp,
+       b.moodys, b.amount, b.maturity, b.spread, b.bid, b.offer, b.depth, b.stm,
+       a.latestdate
+       FROM (SELECT MAX(c.pricingdate) AS latestdate, c.loanxid FROM markit_prices c
+       WHERE c.pricingdate<=p_date GROUP BY c.loanxid) a
+       JOIN markit_prices b ON a.loanxid = b.loanxid AND a.latestdate= b.pricingdate;
+       END;
+       $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION historical_bloomberg_corp(p_date date)
+  RETURNS TABLE(cusip character varying, price double precision, pricingdate date, issuer text, maturity date, coupon double precision, coupontype bloomberg_cpntype, frequency integer, spread double precision, libor_floor double precision, loan_margin double precision, covlite boolean, secondlien boolean, defaulted boolean, source bloomberg_source) AS
+$BODY$
+       BEGIN
+       RETURN QUERY SELECT b.* FROM
+       (SELECT MAX(c.pricingdate) AS latestdate, c.cusip FROM bloomberg_corp c
+       WHERE c.pricingdate<=p_date GROUP BY c.cusip) a
+       JOIN bloomberg_corp b ON a.cusip = b.cusip AND a.latestdate= b.pricingdate;
+       END;
+       $BODY$
+  LANGUAGE plpgsql;
+
+-- CREATE OR REPLACE FUNCTION historical_bloomberg_mtge(p_date date)
+--        RETURNS TABLE(CUSIP varchar(9), updatedate date, issuer text, maturity date,
+--        coupon float, coupontype bloomberg_cpntype, frequency integer, spread float, Moody text,
+--        InitialMoody text) AS $$
+--        BEGIN
+--        RETURN QUERY SELECT b.* FROM
+--        (SELECT MAX(c.updatedate) AS updatedate, c.cusip FROM bloomberg_mtge c
+--        WHERE c.updatedate<=p_date GROUP BY c.cusip) a
+--        JOIN bloomberg_mtge b ON a.cusip = b.cusip AND a.updatedate= b.updatedate;
+--        END;
+--        $$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date)
+       RETURNS TABLE(issuername text, currentbalance float, maturity date,
+       fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float,
+       frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9),
+       covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, et_loanxid varchar(10), et_cusip varchar(9)) AS $$
+       BEGIN
+	RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
+	currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
+	avg(coalesce((b.bid+b.offer)/2, c.price)) AS price,
+	avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
+	spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
+	a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
+	bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
+	bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
+	bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip from et_latestdealinfo(p_dealname, p_date) a
+	left join historical_markit_prices(p_date) b on a.loanxid=b.loanxid left
+	join historical_bloomberg_corp(p_date) c on a.cusip=c.cusip where a.dealname = p_dealname
+	group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
+	a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername;
+       END;
+       $$ LANGUAGE plpgsql;
+
+ALTER FUNCTION et_aggdealinfo_historical(varchar(10), date)
+    OWNER TO et_user;
+
+CREATE OR REPLACE FUNCTION et_cusip_details(p_cusips VARIADIC varchar(10)[])
+       RETURNS TABLE(orig_moody text, curr_moody text, empty1 unknown, issuer text,
+       manager text, empty2 unknown, spread float, issuedate date,
+       reinvenddate date, maturity date, stale_percentage float,
+       cdo_percentage float, wap_basis float, portfolio_spread float,
+       subordination float, thickness float ) AS $$
+       BEGIN
+       RETURN QUERY SELECT a.Orig_Moody, a.Curr_Moody, NULL, b."Deal Name", b.manager,NULL,
+       a.Spread/100, b."Deal Issue Date", b."Reinv End Date", b.Maturity,
+       CAST(NULL AS float), Cast(NULL AS float), CAST(NULL AS FLOAT), CAST(NULL AS float),
+       a.Curr_Attach/100, (a.Curr_Detach-a.Curr_Attach)/100
+       FROM cusip_universe a LEFT JOIN latest_clo_universe b
+       ON a.dealname = b.dealname
+       JOIN (SELECT unnest($1) AS cusip, generate_subscripts(p_cusips, 1) AS id) c
+       ON a.cusip = c.cusip ORDER BY c.id;
+       END;
+       $$ LANGUAGE plpgsql;
+
+ALTER FUNCTION et_cusip_details(varchar(10)[])
+    OWNER TO et_user;
diff --git a/sql/test_queries.sql b/sql/test_queries.sql
new file mode 100644
index 00000000..af656d5b
--- /dev/null
+++ b/sql/test_queries.sql
@@ -0,0 +1,103 @@
+select sum(currentbalance* coalesce(b.bid,c.price))/sum(currentbalance) as wap, sum(currentbalance) as pricedbalance from et_collateral a 
+	left join latest_markit_prices b on a.loanxid=b.loanxid
+	left join bloomberg_prices c on a.cusip=c.cusip
+	where a.dealname='octagon8' and coalesce(b.bid,c.price) is not Null;
+
+select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat, 
+	avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon,
+	avg(a.spread) as grossmargin, a.frequency,  min(a.nextpaydate) AS nextpaydate,
+	a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
+	bool_or(c.covlite))) as covlite, (bool_or(a.secondlien) OR
+	bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo, bool_or(defaultedflag) as defaultedflag 
+	from et_collateral  a  left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_prices c
+	on a.cusip=c.cusip where a.dealname='babs062' 
+group by issuername, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by issuername;
+
+select issuername, sum(currentbalance) AS currentbalance, a.maturity, fixedorfloat, a.dealname,
+	avg(coalesce((b.bid+b.offer)/2, c.price)) as price, avg(grosscoupon) as grosscoupon,
+	avg(a.spread) as grossmargin, a.frequency,  min(a.nextpaydate) AS nextpaydate,
+	a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
+	bool_or(c.covlite))) as covlite, (bool_or(a.secondlien) OR
+	bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo, bool_or(defaultedflag) as defaultedflag 
+	from et_collateral  a  left join latest_markit_prices b on a.loanxid=b.loanxid left join bloomberg_corp c
+	on a.cusip=c.cusip
+group by issuername, a.dealname, a.maturity, a.loanxid, fixedorfloat, a.frequency, a.cusip order by dealname;
+
+select * from latest_markit_prices;
+select * from bloomberg_prices where cusip='038521AG5';
+select distinct cusip from et_collateral;
+select count(a.currentbalance) from et_collateral a where dealname='octagon8';
+select dealname from clo_universe;
+
+--query latest prices from markit_prices
+select b.loanxid,b.issuer,b.dealname,b.facility,b.industry,b.sp, b.moodys,b.amount,b.maturity,b.spread,b.bid,b.offer,b.depth,b.stm,a.latestdate from (select max(pricingdate) as latestdate, loanxid from markit_prices group by loanxid) a join markit_prices b
+		on a.loanxid = b.loanxid and a.latestdate= b.pricingdate;
+
+select * from dealcusipmapping;
+
+select * from et_aggdealinfo('octagon8');
+
+select * from et_ReadMapped('abcde',2016-4-12,3);
+
+insert into et_user_mapping (issuername, maturity, grosscoupon, spread, loanxid) VALUES ('Aramark','1/24/2014',2.34,1.88,'LX063469');
+
+select * from cusip_user_mapping;
+
+
+select * from et_collateral where;
+
+select * from latest_markit_prices where issuer like '%Acxiom%';
+
+insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','4.25','LX113368');
+insert into loanx_user_mapping (issuername, maturity, spread, loanxid) VALUES ('RH Donnelley','10/24/2014','5.25','LX213368');
+update loanx_user_mapping SET issuername = 'RH Donnelley', maturity = '10/24/2014', spread = '4.25', loanxid = 'LX113368' WHERE issuername = 'RH Donnelley' and maturity = '10/24/2014'and spread = '4.25'and loanxid = 'LX113368'
+
+select * from loanx_user_mapping;
+delete from loanx_user_mapping;
+
+select distinct loanxid,cusip from et_collateral where loanxid is not NULL and cusip is not NULL;
+
+select * from bloomberg_corp;
+
+select * from et_latestdealinfo('abrlf','8/24/2012');
+
+UPDATE et_collateral a SET et_loanxid = 'LX050789' WHERE  a.dealname = 'abrlf' and a.name = 'AVIO S.p.A - Term Loan B2' and a.maturity = '2014-12-13';
+select * from et_collateral where dealname = 'abrlf' and et_loanxid= 'LX050789';
+select * from et_collateral where dealname = 'abrlf';
+
+Drop function et_aggdealinfo_historical (varchar(10), date);
+select count(*) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25'
+count(loanxid) from loanx_user_mapping a where a.issuername = 'RH Donnelley' and a.maturity = '10/24/2014' and a.spread = '4.25'
+
+CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_date date)
+       RETURNS TABLE(issuername text, currentbalance float, maturity date,
+       fixedorfloat intex_fixedorfloat, assettype text, price float, grosscoupon float, spread float,
+       frequency varchar(1), nextpaydate date, loanxid varchar(10), cusip varchar(9),
+       covlite boolean, secondlien boolean, iscdo boolean, defaultedflag boolean, et_loanxid varchar(10), et_cusip varchar(9)) AS $$
+       BEGIN
+	RETURN QUERY SELECT a.issuername, sum(a.currentbalance) AS
+	currentbalance, a.maturity, a.fixedorfloat, max(a.assettype),
+	avg(coalesce((b.bid+b.offer)/2, c.price)) AS price,
+	avg(a.grosscoupon) AS grosscoupon, avg(a.spread) AS
+	spread, a.frequency, min(a.nextpaydate) AS nextpaydate,
+	a.loanxid, a.cusip, (max(a.covlite)='Cov_Lite' or (max(a.covlite)='COV_LITE' or
+	bool_or(c.covlite))) AS covlite, (bool_or(a.secondlien) OR
+	bool_or(c.secondlien)) AS secondlien, bool_or(a.iscdo) AS iscdo,
+	bool_or(a.defaultedflag) AS defaultedflag, a.et_loanxid, a.et_cusip from et_latestdealinfo(p_dealname, p_date) a
+	left join historical_markit_prices(p_date) b on b.loanxid=Coalesce(a.loanxid,a.et_loanxid) left
+	join historical_bloomberg_corp(p_date) c on a.cusip=c.cusip where a.dealname = p_dealname
+	group by a.issuername, a.maturity, a.loanxid, a.fixedorfloat,
+	a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername;
+       END;
+       $$ LANGUAGE plpgsql;
+
+select * from et_aggdealinfo_historical('abrlf', '8/27/2012');
+
+CREATE TABLE loanx_user_mapping (
+       IssuerName text,
+       Maturity date,
+       Spread float default NULL,
+       LoanxID varchar(8) default NULL
+);
+
+Drop TABLE loanx_user_mapping;
+\ No newline at end of file