diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/load_loanprices_data.py | 106 |
1 files changed, 83 insertions, 23 deletions
diff --git a/python/load_loanprices_data.py b/python/load_loanprices_data.py index 8f1d42bb..28bf2dc3 100644 --- a/python/load_loanprices_data.py +++ b/python/load_loanprices_data.py @@ -1,5 +1,10 @@ import blpapi import sys +from sqlalchemy import create_engine, MetaData, Table +import pandas as pd + +engine = create_engine('postgresql://et_user:Serenitas1@debian/ET') +metadata = MetaData(bind=engine) # Fill SessionOptions sessionOptions = blpapi.SessionOptions() @@ -18,15 +23,28 @@ if not session.openService("//blp/refdata"): refDataService = session.getService("//blp/refdata") request = refDataService.createRequest("ReferenceDataRequest") -fields = ["PX_LAST","LAST_UPDATE_DT","ISSUER","MATURITY","CPN","CPN_TYP", - "CPN_FREQ","FLT_SPREAD","LIBOR_FLOOR","LN_CURRENT_MARGIN", +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"] # append securities to request -cusips=set([]) -with open("/home/share/CorpCDOs/data/bbgcusips.txt") as fh: - cusips = [line.rstrip() for line in fh] -cusips = set(cusips) +#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 = set(cusips) for cusip in cusips: request.append("securities", "{0} Corp".format(cusip)) @@ -52,20 +70,62 @@ finally: # Stop the session session.stop() -i=0 -data2=[] -for msg in data: - if msg.messageType() == blpapi.Name("ReferenceDataResponse"): - securityDataArray = msg.getElement("securityData") - for securityData in securityDataArray.values(): - i+=1 - securityName = securityData.getElementValue("security") - fieldData = securityData.getElement("fieldData") - row = {} - for fieldName in fields: - try: - fieldValue = fieldData.getElementValue(fieldName) - row[fieldName] = fieldValue - except blpapi.NotFoundException: - row[fieldName] = None - data2.append(row) +def process_msgs(data, fields): + newdata = [] + for msg in data: + if msg.messageType() == blpapi.Name("ReferenceDataResponse"): + securityDataArray = msg.getElement("securityData") + for securityData in securityDataArray.values(): + securityName = securityData.getElementValue("security") + fieldData = securityData.getElement("fieldData") + row = {'security': securityName} + for fieldName in fields: + try: + fieldValue = fieldData.getElementValue(fieldName) + row[fieldName] = fieldValue + except blpapi.NotFoundException: + row[fieldName] = None + newdata.append(row) + return pd.DataFrame.from_dict(newdata) + +data = process_msgs(data, fields) +data = data[~data.ISSUER.isnull()] +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') + +bloomberg_corp_ref = Table('bloomberg_corp_ref', metadata, autoload=True) +ins = bloomberg_corp_ref.insert() +engine.execute(ins, records) + +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)]) + +for id_bb in data.id_bb_unique: + engine.execute("delete from bloomberg_corp_ref where id_bb_unique=%s", (id_bb,)) |