diff options
| -rw-r--r-- | R/load_bloomberg_data.R | 8 | ||||
| -rw-r--r-- | python/load_bloomberg_data.py | 37 | ||||
| -rw-r--r-- | python/load_intex_collateral.py | 2 |
3 files changed, 20 insertions, 27 deletions
diff --git a/R/load_bloomberg_data.R b/R/load_bloomberg_data.R index 0b38c74a..c7a22a8f 100644 --- a/R/load_bloomberg_data.R +++ b/R/load_bloomberg_data.R @@ -3,7 +3,7 @@ library(Rbbg) root.dir <- "//WDSENTINEL/share/CorpCDOs"
source(file.path(root.dir, "code", "R", "etdb.R"))
load(file.path(root.dir, "data", "bloomberg_data.RData"))
-cusips <- dbGetQuery(dbCon, "select distinct cusip from et_collateral")
+cusips <- dbGetQuery(dbCon, "select distinct upper(cusip) from et_collateral")
bbgCon <- blpConnect(throw.ticker.errors=FALSE)
fields.corp <- c("PX_LAST","LAST_UPDATE_DT","ISSUER","MATURITY","CPN","CPN_TYP",
@@ -18,8 +18,8 @@ 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 )
-fh = file(file.path(root.dir, "data", "bloomberg",
-paste0("bloomberg_datacorp_",Sys.Date(),".csv")),"wb")
+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)
@@ -38,7 +38,7 @@ mtgecusips <- substr(rownames(dataMtge[which(!is.na(dataMtge$ISSUER)),]),1,9) dataMtge <- dataMtge[which(!is.na(dataMtge$ISSUER)),]
rownames(dataMtge) <- substr(rownames(dataMtge), 1, 9)
dataMtge <- data.frame(CUSIP=rownames(dataMtge), dataMtge )
-fh = file(file.path(root.dir, "data", "bloomberg",
+fh <- file(file.path(root.dir, "data", "bloomberg",
paste0("bloomberg_datamtge_", Sys.Date(), ".csv"), "wb"))
write.csv(dataMtge, file=fh, row.names=F)
close(fh)
diff --git a/python/load_bloomberg_data.py b/python/load_bloomberg_data.py index b5c0669d..1bca78bf 100644 --- a/python/load_bloomberg_data.py +++ b/python/load_bloomberg_data.py @@ -2,33 +2,24 @@ 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'
-
+import common
+import pdb
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()
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(common.root, "data", "bloomberg")
+
for filename in os.listdir(root):
- cursor.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
- corpcusips = dict(cursor)
+ common.cursor.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
+ corpcusips = dict(common.cursor)
with open( os.path.join(root, filename), "r") as fh:
dr = csv.DictReader(fh)
if "datacorp" in filename:
@@ -49,13 +40,13 @@ for filename in os.listdir(root): sqlstring = "INSERT INTO bloomberg_corp({0}) " \
"VALUES({1})".format(",".join(fields_corp), ",".join(["%s"] * len(fields_corp)))
try:
- cursor.execute(sqlstring, tuple(row))
+ common.cursor.execute(sqlstring, tuple(row))
except IndexError:
pdb.set_trace()
- conn.commit()
+ common.conn.commit()
elif "datamtge" in filename:
- cursor.execute("select * from bloomberg_mtge")
- mtgecusips = {record[0]: None for record in cursor}
+ common.cursor.execute("select * from bloomberg_mtge")
+ mtgecusips = {record[0]: None for record in common.cursor}
for line in dr:
if line["MATURITY"] != 'NA':
line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d').date()
@@ -67,9 +58,9 @@ for filename in os.listdir(root): sqlstring = "INSERT INTO bloomberg_mtge({0}) " \
"VALUES({1})".format(",".join(fields_mtge),
",".join(["%s"] * len(fields_mtge)))
- cursor.execute(sqlstring, tuple(row))
- conn.commit()
+ common.cursor.execute(sqlstring, tuple(row))
+ common.conn.commit()
-cursor.close()
-conn.close()
+common.cursor.close()
+common.conn.close()
print "done"
diff --git a/python/load_intex_collateral.py b/python/load_intex_collateral.py index 30e76806..c913be41 100644 --- a/python/load_intex_collateral.py +++ b/python/load_intex_collateral.py @@ -82,6 +82,8 @@ def upload_data(dealnames, workdate, conn, cursor): line['Life Floor'] = float('Nan') if line['CUSIP']== 'XAQ3930AAB43': line['CUSIP']='BL078321' + if line['CUSIP']: + line['CUSIP'] = line['CUSIP'].upper() r = [convertToNone(line[field]) for field in fields] if r[fields.index('CUSIP')] and len(r[fields.index('CUSIP')])>9: pdb.set_trace() |