diff options
Diffstat (limited to 'python/intex/load_intex_collateral.py')
| -rw-r--r-- | python/intex/load_intex_collateral.py | 19 |
1 files changed, 16 insertions, 3 deletions
diff --git a/python/intex/load_intex_collateral.py b/python/intex/load_intex_collateral.py index b1a82e6d..cb736240 100644 --- a/python/intex/load_intex_collateral.py +++ b/python/intex/load_intex_collateral.py @@ -17,7 +17,7 @@ fields = ['Asset Name', 'Issuer', 'Contributed Balance', 'Asset Maturity Date', 'Market Price', 'Market Price Source', 'Market Price Date', 'Fixed or Float', \ 'Defaulted Flag', 'Security Sub-Category', 'Structured Finance Security', \ 'Life Floor', 'Reinvest Collat', 'Native Currency', "Moody's Industry Name", - "Country"] + "Country", "Amortization Schedule"] def upload_data(conn, workdate): sql_fields = ["dealname", "updatedate", "name", "IssuerName", "CurrentBalance", @@ -25,14 +25,15 @@ def upload_data(conn, workdate): "Spread", "Frequency", "NextPaydate", "SecondLien", "LoanXID", "Cusip", "IntexPrice", "IntexPriceSource", "IntexPriceDate", "FixedOrFloat", "DefaultedFlag", "CovLite", "isCDO", - "Liborfloor", "ReinvFlag", "Currency", "Industry", "Country"] + "Liborfloor", "ReinvFlag", "Currency", "Industry", "Country", + "amort_schedule_dates", "amort_schedule_amounts"] sqlstr = "INSERT INTO ET_COLLATERAL({0}) VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields))) basedir = Path(os.environ['DATA_DIR']) / ("Collaterals_" + workdate) for fname in basedir.iterdir(): fh = open(fname, encoding="windows-1252") dealname = fname.name.rsplit("_", 1)[0].lower() - dr = csv.DictReader(fh, dialect = 'excel-tab') + dr = csv.DictReader(fh, dialect='excel-tab') missingfields = set(fields).union({'Gross Margin'}) - set(dr.fieldnames) if "LoanX ID" in missingfields: msg = "{0}: LoanX ID column is missing. Probably an error in exporting from intex" @@ -83,6 +84,18 @@ def upload_data(conn, workdate): except (ValueError, TypeError): line['Asset Maturity Date'] = None r = [line[field] or None for field in fields] + + if line["Amortization Schedule"]: + temp = line["Amortization Schedule"].split("; ") + temp = [e.split(" @ ") for e in temp] + amounts, dates = zip(*temp) + amounts = [float(e.replace(",", "")) for e in amounts] + dates = [datetime.datetime.strptime(e, "%b %d, %Y").date() for e in dates] + r[-1] = dates + r.append(amounts) + else: + r.append(None) + #sometimes the Asset Name is not unique (we add random tag in this case) if r[0] in data: r[0] = r[0] + str(uuid.uuid4())[:3] |