diff options
| -rw-r--r-- | python/bbg_helpers.py | 63 | ||||
| -rw-r--r-- | python/bbg_newids.py | 64 | ||||
| -rw-r--r-- | python/bbg_prices.py | 57 | ||||
| -rw-r--r-- | python/load_loanprices_data.py | 121 |
4 files changed, 184 insertions, 121 deletions
diff --git a/python/bbg_helpers.py b/python/bbg_helpers.py new file mode 100644 index 00000000..ea23cb3a --- /dev/null +++ b/python/bbg_helpers.py @@ -0,0 +1,63 @@ +import blpapi +import sys +import pandas as pd + +# Fill SessionOptions +def init_bbg_session(ipaddr, port=8184): + sessionOptions = blpapi.SessionOptions() + sessionOptions.setServerHost(ipaddr) + sessionOptions.setServerPort(port) + session = blpapi.Session(sessionOptions) + + # Start a Session + if not session.start(): + print "Failed to start session." + sys.exit(0) + + if not session.openService("//blp/refdata"): + print "Failed to open //blp/refdata" + sys.exit(0) + return session + +def retreive_data(session, securities, fields): + refDataService = session.getService("//blp/refdata") + request = refDataService.createRequest("ReferenceDataRequest") + for security in securities: + request.append("securities", security) + for field in fields: + request.append("fields", field) + session.sendRequest(request) + data = [] + try: + # Process received events + while(True): + # We provide timeout to give the chance to Ctrl+C handling: + ev = session.nextEvent(500) + if ev.eventType() in [blpapi.Event.PARTIAL_RESPONSE, blpapi.Event.RESPONSE]: + for msg in ev: + data.append(msg) + # Response completely received, so we could exit + if ev.eventType() == blpapi.Event.RESPONSE: + break + finally: + # Stop the session + session.stop() + return data + +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) diff --git a/python/bbg_newids.py b/python/bbg_newids.py new file mode 100644 index 00000000..fe317c17 --- /dev/null +++ b/python/bbg_newids.py @@ -0,0 +1,64 @@ +from sqlalchemy import create_engine +import pandas as pd +from db import conn +import numpy as np +from bbg_helper import init_bbgsession, retreive_data, process_msgs + +engine = create_engine('postgresql://et_user:Serenitas1@debian/ET') + +session = init_bbg_session('192.168.1.108', 8194) + +all_fields = ["ISSUE_DT", "LN_ISSUE_STATUS", "ID_CUSIP", "ID_BB_UNIQUE", + "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"] + +# append securities to request +currentdata = pd.read_sql_query("select id_bb_unique, substring(id_bb_unique from 3) as cusip " \ + "from bloomberg_corp_ref", engine, index_col='cusip') + +mapping = pd.read_csv("/home/share/CorpCDOs/data/bbg_loanxid.csv", index_col=0) +mapping = mapping.ix[mapping.index.difference(currentdata.index)] + +securities = ["{0} Corp".format(cusip) for cusip in mapping.index] +data = retreive_data(session, securities, fields) +df = process_msgs(data, all_fields) +df.security = df.security.str.slice(0,9) +df.set_index('security', inplace=True) +df['loanxid'] = mapping['loanxid'] +df.reset_index(inplace=True) + +sqlstr = "INSERT INTO bloomberg_corp_ref VALUES({0})".format(",".join(["%s"]*20)) +with conn.cursor() as c: + for i in range(df.shape[0]): + issue_size = df.iloc[i]['LN_TRANCHE_SIZE'] + if np.isnan(issue_size): + issue_size = df.iloc[i]['AMT_ISSUED'] + if np.isnan(issue_size): + issue_size = None + c.execute(sqlstr, + (df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['ID_CUSIP'], df.iloc[i]['ISSUER'], + df.iloc[i]['MATURITY'], df.iloc[i]['CPN'], df.iloc[i]['CPN_TYP'], + df.iloc[i]['CPN_FREQ'], df.iloc[i]['FLT_SPREAD'], df.iloc[i]['LIBOR_FLOOR'], + issue_size, df.iloc[i]["LN_COVENANT_LITE"], df.iloc[i]["SECOND_LIEN_INDICATOR"], + df.iloc[i]["SECURITY_TYP"], df.iloc[i]["ISSUE_DT"], df.iloc[i]["DEFAULTED"], + df.iloc[i]["DEFAULT_DATE"], df.iloc[i]["CALLED"], df.iloc[i]["CALLED_DT"], + df.iloc[i]["LN_ISSUE_STATUS"], [df.iloc[i]['loanxid']]) + ) +conn.commit() + +sqlstr = "INSERT INTO bloomberg_corp VALUES(%s, %s, %s, %s, %s, %s)" +with conn.cursor() as c: + for i in range(df.shape[0]): + margin = df.iloc[i]['LN_CURRENT_MARGIN'] + if np.isnan(margin): + margin = None + amt_outstanding = df.iloc[i]['AMT_OUTSTANDING'] + if np.isnan(amt_outstanding): + amt_outstanding = None + c.execute(sqlstr, (df.iloc[i]['ID_BB_UNIQUE'], df.iloc[i]['LAST_UPDATE_DT'], + df.iloc[i]['PX_LAST'], margin, amt_outstanding, + df.iloc[i]['PRICING_SOURCE'])) +conn.commit() diff --git a/python/bbg_prices.py b/python/bbg_prices.py new file mode 100644 index 00000000..c6a11a01 --- /dev/null +++ b/python/bbg_prices.py @@ -0,0 +1,57 @@ +from bbg_helpers import init_bbg_session, retreive_data, process_msgs +from sqlalchemy import create_engine +from db import conn +import numpy as np + +engine = create_engine('postgresql://et_user:Serenitas1@debian/ET') +session = init_bbg_session('192.168.1.108', 8194) + +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 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') + +securities = ["{0} Corp".format(cusip) for cusip in cusips.index] + +data = retreive_data(session, securities, fields_update) +df = process_msgs(data, fields_update) +df.security = df.security.str.slice(0,9) +df.set_index(['security'], inplace=True) +df['id_bb_unique'] = cusips['id_bb_unique'] +df.reset_index(inplace=True) + +with conn.cursor() as c: + for i in range(df.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", + (df.iloc[i]['DEFAULTED'], df.iloc[i]['DEFAULT_DATE'], df.iloc[i]['CALLED'], + df.iloc[i]['CALLED_DT'], df.iloc[i]['LN_ISSUE_STATUS'], df.iloc[i]['id_bb_unique'])) +conn.commit() + +currentdata = pd.Index(pd.read_sql_query("SELECT id_bb_unique, pricingdate from bloomberg_corp", + engine, + parse_dates=["pricingdate"])) +#no need to insert empty prices +df.dropna(subset=['PX_LAST'], inplace=True) +df.set_index(['id_bb_unique', 'LAST_UPDATE_DT'], inplace=True) +df = df.ix[df.index.difference(currentdata)] +df.reset_index(inplace=True) + +sqlstr = "INSERT INTO bloomberg_corp VALUES(%s, %s, %s, %s, %s, %s)" +with conn.cursor() as c: + for i in range(df.shape[0]): + margin = df.iloc[i]['LN_CURRENT_MARGIN'] + if np.isnan(margin): + margin = None + amt_outstanding = df.iloc[i]['AMT_OUTSTANDING'] + if np.isnan(amt_outstanding): + amt_outstanding = None + c.execute(sqlstr, (df.iloc[i]['id_bb_unique'], df.iloc[i]['LAST_UPDATE_DT'], + df.iloc[i]['PX_LAST'], margin, amt_outstanding, + df.iloc[i]['PRICING_SOURCE'])) +conn.commit() diff --git a/python/load_loanprices_data.py b/python/load_loanprices_data.py deleted file mode 100644 index 7c112b71..00000000 --- a/python/load_loanprices_data.py +++ /dev/null @@ -1,121 +0,0 @@ -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) - -# Fill SessionOptions -sessionOptions = blpapi.SessionOptions() -sessionOptions.setServerHost('192.168.1.108') -sessionOptions.setServerPort(8194) -session = blpapi.Session(sessionOptions) - -# Start a Session -if not session.start(): - print "Failed to start session." - sys.exit(0) - -if not session.openService("//blp/refdata"): - print "Failed to open //blp/refdata" - sys.exit(0) - -refDataService = session.getService("//blp/refdata") -request = refDataService.createRequest("ReferenceDataRequest") -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 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.index: - request.append("securities", "{0} Corp".format(cusip)) - -# append fields to request -for field in fields_update: - request.append("fields", field) - -session.sendRequest(request) - -data = [] -try: - # Process received events - while(True): - # We provide timeout to give the chance to Ctrl+C handling: - ev = session.nextEvent(500) - if ev.eventType() in [blpapi.Event.PARTIAL_RESPONSE, blpapi.Event.RESPONSE]: - for msg in ev: - data.append(msg) - # Response completely received, so we could exit - if ev.eventType() == blpapi.Event.RESPONSE: - break -finally: - # Stop the session - session.stop() - -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_update) -data.security = data.security.str.slice(0,9) -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() - -currentdata = pd.Index(pd.read_sql_query("SELECT id_bb_unique, pricingdate from bloomberg_corp", - engine, - parse_dates=["pricingdate"])) - -#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) - -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() |