diff options
| -rw-r--r-- | R/load_loanprices_data.R | 6 | ||||
| -rw-r--r-- | python/load_bloomberg_data.py | 75 |
2 files changed, 28 insertions, 53 deletions
diff --git a/R/load_loanprices_data.R b/R/load_loanprices_data.R index 99d4808d..d9caa2fc 100644 --- a/R/load_loanprices_data.R +++ b/R/load_loanprices_data.R @@ -23,11 +23,11 @@ corpcusips <- substr(rownames(dataCorp[which(!is.na(dataCorp$ISSUER)),]),1,9) dataCorp <- dataCorp[which(!is.na(dataCorp$ISSUER)),]
rownames(dataCorp) <- substr(rownames(dataCorp), 1, 9)
dataCorp <- data.frame(CUSIP=rownames(dataCorp), dataCorp, stringsAsFactors=FALSE)
+
fh <- file(file.path(root.dir, "data", "bloomberg",
paste0("bloomberg_datacorp_", Sys.Date(), ".csv")),"wb")
write.csv(dataCorp, file=fh, row.names=F)
close(fh)
+strdate <- format(Sys.Date(), "%Y-%m-%d")
+system(paste("python /home/share/CorpCDOs/code/python/load_bloomberg_data.py", strdate))
blpDisconnect(bbgCon)
-
-## save(corpcusips, mtgecusips, dataCorp, dataMtge, file="bloomberg_data.RData")
-save(corpcusips, dataCorp, file=file.path(root.dir, "data", "bloomberg_data.RData"))
diff --git a/python/load_bloomberg_data.py b/python/load_bloomberg_data.py index b7df0433..71bd1aff 100644 --- a/python/load_bloomberg_data.py +++ b/python/load_bloomberg_data.py @@ -3,7 +3,8 @@ import datetime from datetime import date
import csv
from common import root
-from db import conn
+from db import conn, with_connection
+import sys
def convertToNone(s):
return None if (s=='' or s=='NA') else s
@@ -11,60 +12,34 @@ def convertToNone(s): fields_corp = ["Cusip", "Price", "PricingDate", "Issuer", "Maturity", "Coupon",
"CouponType", "Frequency", "Spread", "Libor_floor", "loan_margin",
"covlite", "secondlien", "defaulted", "Source"]
-fields_mtge = ["Cusip", "Issuer", "Maturity", "Coupon", "CouponType", "Frequency",
- "Spread", "Moody", "InitialMoody"]
root = os.path.join(root, "data", "bloomberg")
-for filename in os.listdir(root):
+@with_connection
+def upload_data(conn, p_date):
+ sqlstring = "INSERT INTO bloomberg_corp({0}) " \
+ "VALUES({1})".format(",".join(fields_corp), ",".join(["%s"] * len(fields_corp)))
with conn.cursor() as c:
c.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
corpcusips = dict(c)
- with open( os.path.join(root, filename), "r") as fh:
+ allrows = []
+ with open( os.path.join(root, "bloomberg_datacorp_" + p_date + ".csv"), "r") as fh:
dr = csv.DictReader(fh)
- if "datacorp" in filename:
- c = conn.cursor()
- 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']:
- sqlstring = "INSERT INTO bloomberg_corp({0}) " \
- "VALUES({1})".format(",".join(fields_corp), ",".join(["%s"] * len(fields_corp)))
- try:
- c.execute(sqlstring, tuple(row))
- except IndexError:
- pdb.set_trace()
- c.close()
- conn.commit()
-
- elif "datamtge" in filename:
- c = conn.cursor()
- c.execute("select * from bloomberg_mtge")
- mtgecusips = {record[0]: None for record in c}
- 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:
- sqlstring = "INSERT INTO bloomberg_mtge({0}) " \
- "VALUES({1})".format(",".join(fields_mtge),
- ",".join(["%s"] * len(fields_mtge)))
- c.execute(sqlstring, tuple(row))
- c.close()
- conn.commit()
+ 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(p_date, '%Y-%m-%d').date()
+ line["PX_LAST"] = None
+ if line["MATURITY"] != 'NA':
+ line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d')
+ if line['CUSIP'] not in corpcusips or corpcusips[line['CUSIP']] < line['LAST_UPDATE_DT']:
+ allrows.append(tuple([convertToNone(line[field]) for field in dr.fieldnames]))
+ with conn.cursor() as c:
+ c.executemany(sqlstring, allrows)
+ conn.commit()
-conn.close()
-print("done")
+if __name__=="__main__":
+ upload_data(sys.argv[1])
+ conn.close()
|