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
71
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()
|
