1 files changed, 72 insertions, 0 deletions
diff --git a/python/load_bloomberg_data.py b/python/load_bloomberg_data.py
new file mode 100644
index 00000000..dba8ae1a
--- /dev/null
+++ b/python/load_bloomberg_data.py
@@ -0,0 +1,72 @@
+import psycopg2
+import os.path
+import datetime
+from datetime import date
+import pdb
+import csv
+
+
+if os.name =='nt':
+    root = "//WDsentinel/share/CorpCDOs/data/bloomberg"
+elif os.name == 'posix':
+    root = '/home/share/CorpCDOS/data/bloomberg'
+
+
+def convertToNone(s):
+    return None if (s=='' or s=='NA') else s
+
+conn = psycopg2.connect(database="ET",
+                        user="et_user",
+                        password="Serenitas1",
+                        host="192.168.1.108")
+cursor = conn.cursor()
+
+for filename in os.listdir(root):
+    cursor.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
+    corpcusips = dict(cursor)
+    with open( os.path.join(root, filename), "r") as fh:
+        dr = csv.DictReader(fh)
+        if "datacorp" in filename:
+            for line in dr:
+                if line["LAST_UPDATE_DT"] != 'NA':
+                        line["LAST_UPDATE_DT"] = \
+                            datetime.datetime.strptime(line["LAST_UPDATE_DT"], '%Y-%m-%d').date()
+                else:
+                    line["LAST_UPDATE_DT"] = \
+                        datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
+                    line["PX_LAST"] = None
+                    if line["MATURITY"] != 'NA':
+                        line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d')
+                row = [convertToNone(line[field]) for field in dr.fieldnames]
+                # cursor.execute("SELECT max(PricingDate) from bloomberg_corp where Cusip = %s", (line['CUSIP'],))
+                # currentpricingdate = cursor.fetchone()[0]
+                if line['CUSIP'] not in corpcusips or corpcusips[line['CUSIP']]<line['LAST_UPDATE_DT']:
+                    try:
+                        cursor.execute("INSERT INTO bloomberg_corp(Cusip, Price, PricingDate, Issuer,"
+                                       "Maturity, Coupon, CouponType, Frequency, Spread, Libor_floor,"
+                                       "loan_margin, covlite, secondlien, defaulted, Source)"
+                                       "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
+                                       tuple(row))
+                    except IndexError:
+                        pdb.set_trace()
+                conn.commit()
+        elif "datamtge" in filename:
+            cursor.execute("select * from bloomberg_mtge")
+            mtgecusips = {record[0]: None for record in cursor}
+            for line in dr:
+                if line["MATURITY"] != 'NA':
+                    line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d').date()
+                row = [convertToNone(line[field]) for field in dr.fieldnames]
+                # sqlstr = "SELECT * from bloomberg_mtge where Cusip=%s"
+                # cursor.execute(sqlstr, (line['CUSIP'],))
+                # found = cursor.fetchone()
+                if line['CUSIP'] not in mtgecusips:
+                    cursor.execute("INSERT INTO bloomberg_mtge(Cusip, Issuer,"
+                                   "Maturity, Coupon, CouponType, Frequency, Spread,"
+                                   "Moody, InitialMoody)"
+                                   "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
+                                   tuple(row))
+            conn.commit()
+
+cursor.close()
+conn.close()