diff options
| -rw-r--r-- | python/load_intex_collateral.py | 26 | ||||
| -rw-r--r-- | sql/et_tables.sql | 10 |
2 files changed, 24 insertions, 12 deletions
diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py index 00b6f027..33c59c0a 100644 --- a/python/load_intex_collateral.py +++ b/python/load_intex_collateral.py @@ -7,10 +7,12 @@ import pdb import common 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', 'Life Floor'] +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', 'Life Floor', 'Reinvest Collat'] def convertToNone(s): return None if s=='' else s @@ -56,8 +58,11 @@ def upload_data(workdate, conn, cursor): try: # make sure the string is utf8 safe line['Issuer'] = line['Issuer'].decode('windows-1252') - if line['Issuer']=='' or line['Issuer'].lower() == 'dummy': - break + if line['Reinvest Collat'].upper() == 'Y' or line['Issuer'] == '' \ + or line['Issuer'].lower() == 'dummy': + # assume it's a reinvestment asset + line['Reinvest Collat'] = True + line['Issuer'] = 'Reinvestment Asset' except AttributeError as detail: print dealname if 'Spread' in missingfields: @@ -70,10 +75,12 @@ def upload_data(workdate, conn, cursor): if line['Life Floor'] == "No limit": line['Life Floor'] = 0 elif line['Life Floor']: - line['Life Floor'] = float(line['Life Floor']) - float(line['Spread']) + try: + line['Life Floor'] = float(line['Life Floor']) - float(line['Spread']) + except ValueError: + line['Life Floor'] = float('Nan') r = [convertToNone(line[field]) for field in fields] data.append(r) - cursor.execute( "select distinct(updatedate) from et_collateral where dealname='{0}'".format(dealname)) old_update_dates = [date[0] for date in cursor.fetchall()] @@ -103,7 +110,8 @@ def upload_data(workdate, conn, cursor): "Maturity", "AssetSubtype", "AssetType", "GrossCoupon", "Spread", "Frequency", "NextPaydate", "SecondLien", "LoanXID", "Cusip", "IntexPrice", "IntexPriceSource", "IntexPriceDate", - "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO", "Liborfloor"] + "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO", + "Liborfloor", "ReinvFlag"] sqlstr = "INSERT INTO ET_COLLATERAL({0}) VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields))) diff --git a/sql/et_tables.sql b/sql/et_tables.sql index bb306904..9deaae6c 100644 --- a/sql/et_tables.sql +++ b/sql/et_tables.sql @@ -28,6 +28,7 @@ CREATE TABLE et_collateral ( CovLite text, isCDO boolean, Liborfloor float, + ReinvFlag boolean, PRIMARY KEY (updatedate, Name, dealname) ); @@ -299,7 +300,8 @@ 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, liborfloor float) AS $$ + defaultedflag boolean, covlite text, iscdo boolean, liborfloor float, + reinvflag boolean) AS $$ DECLARE latestdate date; BEGIN SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral @@ -317,7 +319,8 @@ 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, liborfloor float) AS $$ + defaultedflag boolean, covlite text, iscdo boolean, liborfloor float, + reinvflag boolean) AS $$ DECLARE latestdate date; BEGIN SELECT max(et_collateral.updatedate) INTO latestdate FROM et_collateral @@ -344,6 +347,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo (p_dealname varchar(10)) bool_or(a.defaultedflag) AS defaultedflag FROM et_latestdealinfo(p_dealname) a LEFT JOIN latest_markit_prices2 b ON a.loanxid = b.loanxid LEFT JOIN bloomberg_corp c ON a.cusip=c.cusip WHERE a.dealname = p_dealname + and a.ReinvFlag IS NOT TRUE GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat, a.frequency, a.cusip ORDER BY issuername; END; @@ -411,7 +415,7 @@ CREATE OR REPLACE FUNCTION et_aggdealinfo_historical (p_dealname varchar(10), p_ FROM et_historicaldealinfo(p_dealname, p_date) a LEFT JOIN historical_markit_prices2(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 + WHERE a.dealname = p_dealname AND a.ReinvFlag IS NOT TRUE GROUP BY a.issuername, a.maturity, a.loanxid, a.fixedorfloat, a.frequency, a.cusip, a.et_loanxid, a.et_cusip ORDER BY issuername; END; |