diff options
| -rw-r--r-- | python/load_intex_collateral.py | 15 | ||||
| -rw-r--r-- | sql/et_tables.sql | 6 |
2 files changed, 15 insertions, 6 deletions
diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py index 8afc97b5..49990155 100644 --- a/python/load_intex_collateral.py +++ b/python/load_intex_collateral.py @@ -10,7 +10,7 @@ import sys fields = ['Asset Name', 'Issuer', 'Contributed 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'] + 'Defaulted Flag', 'Security Sub-Category', 'Structured Finance Security', 'Life Floor'] def convertToNone(s): return None if s=='' else s @@ -59,12 +59,18 @@ def upload_data(workdate, conn, cursor): break except AttributeError as detail: print dealname + if 'Spread' in missingfields: + line['Spread'] = line['Gross Margin'] for field in ['Spread', 'Gross Coupon', 'Market Price', 'Contributed Balance']: if line[field]: line[field] = sanitize_float(line[field]) - r = [convertToNone(line[field]) for field in fields] + #we store the Libor FLoor in the database, so Life Floor is really Libor Floor + if line['Life Floor'] == "No limit": + line['Life Floor'] = 0 + elif line['Life Floor']: + line['Life Floor'] = float(line['Life Floor']) - float(line['Spread']) data.append(r) cursor.execute( "select distinct(updatedate) from et_collateral where dealname='{0}'".format(dealname)) @@ -96,7 +102,7 @@ def upload_data(workdate, conn, cursor): "Maturity", "AssetSubtype", "AssetType", "GrossCoupon", "Spread", "Frequency", "NextPaydate", "SecondLien", "LoanXID", "Cusip", "IntexPrice", "IntexPriceSource", "IntexPriceDate", - "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO"] + "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO", "Liborfloor"] sqlstr = "INSERT INTO ET_COLLATERAL({0}) VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields))) @@ -113,6 +119,8 @@ def upload_data(workdate, conn, cursor): row[0] = row[0] + "_tag_" + str(tag) tag = tag+1 cursor.execute(sqlstr, (dealname, updatedate) + tuple(row)) + except TypeError: + pdb.set_trace() conn.commit() if __name__ == "__main__": @@ -124,4 +132,3 @@ if __name__ == "__main__": common.cursor.close() common.conn.close() print "done" - diff --git a/sql/et_tables.sql b/sql/et_tables.sql index 4c4c9fae..5bd1aca4 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -27,8 +27,10 @@ CREATE TABLE et_collateral ( DefaultedFlag boolean, CovLite text, isCDO boolean, + Liborfloor float, PRIMARY KEY (updatedate, Name, dealname) ); + CREATE INDEX dealname_idx ON et_collateral(dealname, updatedate); ALTER TABLE et_collateral OWNER TO et_user; @@ -332,7 +334,7 @@ CREATE OR REPLACE FUNCTION et_historicaldealinfo (p_dealname varchar(10), p_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, userpricedate date, fixedorfloat intex_fixedorfloat, - defaultedflag boolean, covlite text, iscdo boolean) AS $$ + defaultedflag boolean, covlite text, iscdo boolean, liborfloor float) AS $$ DECLARE latestdate date; BEGIN SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral @@ -350,7 +352,7 @@ CREATE OR REPLACE FUNCTION et_latestdealinfo (p_dealname varchar(10)) secondlien boolean, loanxid varchar(10), et_loanxid varchar(10), cusip varchar(9), et_cusip varchar(10), intexprice float, intexpricesource text, intexpricedate date, userprice float, userpricedate date, fixedorfloat intex_fixedorfloat, - defaultedflag boolean, covlite text, iscdo boolean) AS $$ + defaultedflag boolean, covlite text, iscdo boolean, liborfloor float) AS $$ DECLARE latestdate date; BEGIN SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral |