1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
import os.path
import datetime
from datetime import date
import csv
from common import root
from db import conn
def convertToNone(s):
return None if (s=='' or s=='NA') else 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 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:
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()
conn.close()
print("done")
|
