diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/load_loanprices_data.py | 104 |
1 files changed, 47 insertions, 57 deletions
diff --git a/python/load_loanprices_data.py b/python/load_loanprices_data.py index 28bf2dc3..7c112b71 100644 --- a/python/load_loanprices_data.py +++ b/python/load_loanprices_data.py @@ -2,6 +2,7 @@ import blpapi import sys from sqlalchemy import create_engine, MetaData, Table import pandas as pd +from db import conn engine = create_engine('postgresql://et_user:Serenitas1@debian/ET') metadata = MetaData(bind=engine) @@ -23,33 +24,28 @@ if not session.openService("//blp/refdata"): refDataService = session.getService("//blp/refdata") request = refDataService.createRequest("ReferenceDataRequest") -fields = ["ID_BB_UNIQUE", "ISSUE_DT", - "SECURITY_TYP", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT", - "ISSUER","MATURITY","CPN","CPN_TYP", "CPN_FREQ","FLT_SPREAD", - "LIBOR_FLOOR","LN_CURRENT_MARGIN", "LN_TRANCHE_SIZE", - "LN_COVENANT_LITE","SECOND_LIEN_INDICATOR","DEFAULTED", "PRICING_SOURCE"] +all_fields = ["ISSUE_DT", "LN_ISSUE_STATUS", "ID_CUSIP", + "SECURITY_TYP", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT", + "ISSUER", "MATURITY","CPN","CPN_TYP", "CPN_FREQ","FLT_SPREAD", + "LIBOR_FLOOR","LN_CURRENT_MARGIN", "LN_TRANCHE_SIZE", "AMT_ISSUED", + "LN_COVENANT_LITE","SECOND_LIEN_INDICATOR","DEFAULTED", "DEFAULT_DATE", + "CALLED", "CALLED_DT", "PRICING_SOURCE"] +fields_update = ["LN_ISSUE_STATUS", "AMT_OUTSTANDING", "PX_LAST","LAST_UPDATE_DT", + "LN_CURRENT_MARGIN", "DEFAULTED", "DEFAULT_DATE", + "CALLED", "CALLED_DT", "PRICING_SOURCE"] # append securities to request -#cusips = pd.read_sql_query("select distinct cusip from bloomberg_corp_ref where tranche_size is Null", engine) - -def split_clean(l): - a, b = line.rstrip().split(",") - if b == '': - b = None - return (a, b) - -with open("/home/share/CorpCDOs/data/bbg_loanxid.csv") as fh: - mapping = dict([split_clean(line) for line in fh]) -mapping = zip(*[(k, v) for k, v in mapping.iteritems()]) -cusips = mapping[0] -loanxids = mapping[1] +cusips = pd.read_sql_query("select id_bb_unique, substring(id_bb_unique from 3) as cusip from bloomberg_corp_ref " \ + "where (status is Null or status not in ('REFINANCED','RETIRED', 'REPLACED')) "\ + "and not called", engine, index_col='cusip') +cusips = pd.read_sql_query("select id_bb_unique, substring(id_bb_unique from 3) as cusip from bloomberg_corp_ref", engine, index_col='cusip') # cusips = set(cusips) -for cusip in cusips: +for cusip in cusips.index: request.append("securities", "{0} Corp".format(cusip)) # append fields to request -for field in fields: +for field in fields_update: request.append("fields", field) session.sendRequest(request) @@ -88,44 +84,38 @@ def process_msgs(data, fields): newdata.append(row) return pd.DataFrame.from_dict(newdata) -data = process_msgs(data, fields) -data = data[~data.ISSUER.isnull()] +data = process_msgs(data, fields_update) data.security = data.security.str.slice(0,9) -data.rename(columns={'PX_LAST': 'Price', - 'CPN': 'Coupon', - 'CPN_TYP': 'CouponType', - 'CPN_FREQ': 'Frequency', - 'FLT_SPREAD': 'Spread', - 'LN_CURRENT_MARGIN': 'loan_margin', - 'LN_COVENANT_LITE': 'covlite', - 'SECOND_LIEN_INDICATOR': 'secondlien', - 'PRICING_SOURCE': 'Source', - 'AMT_OUTSTANDING':'amount_outstanding', - 'SECURITY_TYP':'security_type', - 'LAST_UPDATE_DT':'PricingDate', - 'security': 'Cusip', - 'LN_TRANCHE_SIZE': 'tranche_size'}, inplace=True) -data.rename(columns=lambda x:x.lower(), inplace=True) -data = data[~data.pricingdate.isnull()] -data.set_index(['cusip', 'pricingdate'], inplace=True) - -engine = create_engine('postgresql://et_user:Serenitas1@debian/ET') -currentdata = pd.Index(pd.read_sql_query("select id_bb_unique from bloomberg_corp_ref", engine, parse_dates="pricingdate")) -data = data.ix[data.index.difference(currentdata)] -data = data.reset_index() -data[['id_bb_unique', 'pricingdate', 'price', 'loan_margin', 'amount_outstanding', 'defaulted', 'source']].to_sql("bloomberg_corp", engine, if_exists='append', index=False) -data[['loanxid','cusip']] = data[['loanxid','cusip']].applymap(lambda x: None if not x else [x]) -records =data[['id_bb_unique', 'cusip', 'issuer', 'maturity', 'coupon', 'coupontype', - 'frequency', 'spread', 'libor_floor', 'tranche_size', 'covlite', - 'secondlien', 'security_type', 'issue_dt', 'loanxid']].to_dict(orient='records') +data['id_bb_unique'] = cusips.ix[data.security]['id_bb_unique'].reset_index(drop=True) +with conn.cursor() as c: + for i in range(data.shape[0]): + c.execute("UPDATE bloomberg_corp_ref set defaulted = %s, default_date = %s, " \ + "called=%s, called_date = %s, status=%s " \ + "where id_bb_unique=%s", + (data.iloc[i]['DEFAULTED'], data.iloc[i]['DEFAULT_DATE'], data.iloc[i]['CALLED'], + data.iloc[i]['CALLED_DT'], data.iloc[i]['LN_ISSUE_STATUS'], data.iloc[i]['id_bb_unique'])) +conn.commit() -bloomberg_corp_ref = Table('bloomberg_corp_ref', metadata, autoload=True) -ins = bloomberg_corp_ref.insert() -engine.execute(ins, records) +currentdata = pd.Index(pd.read_sql_query("SELECT id_bb_unique, pricingdate from bloomberg_corp", + engine, + parse_dates=["pricingdate"])) -with engine.begin() as conn: - conn.execute("update bloomberg_corp_ref set tranche_size=%s where id_bb_unique=%s", - [(a, b) for a, b in data[['tranche_size', 'id_bb_unique']].to_records(index=False)]) +#no need to insert empty prices +data.dropna(subset=['PX_LAST'], inplace=True) +data.set_index(['id_bb_unique', 'LAST_UPDATE_DT'], inplace=True) +data = data.ix[data.index.difference(currentdata)] +data.reset_index(inplace=True) -for id_bb in data.id_bb_unique: - engine.execute("delete from bloomberg_corp_ref where id_bb_unique=%s", (id_bb,)) +sqlstr = "INSERT INTO bloomberg_corp VALUES(%s, %s, %s, %s, %s, %s)" +with conn.cursor() as c: + for i in range(data.shape[0]): + margin = data.iloc[i]['LN_CURRENT_MARGIN'] + if np.isnan(margin): + margin = None + amt_outstanding = data.iloc[i]['AMT_OUTSTANDING'] + if np.isnan(amt_outstanding): + amt_outstanding = None + c.execute(sqlstr, (data.iloc[i]['id_bb_unique'], data.iloc[i]['LAST_UPDATE_DT'], + data.iloc[i]['PX_LAST'], margin, amt_outstanding, + data.iloc[i]['PRICING_SOURCE'])) +conn.commit() |