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