1 files changed, 64 insertions, 0 deletions
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()