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
|
import os
import os.path
import datetime
from datetime import date
import pdb
import csv
from db import conn
from common import root
def convertToNone(s):
return None if (s=='' or s=='NA') else s
fields = ["LoanX ID", "Issuer", "Deal Name", "Facility", "Industry", \
"Current SP Rating", "Current Moodys Rating", "Initial Amount",\
"Final Maturity", "Initial Spread", "Bid", "Offer", "Depth", \
"Spread To Maturity"]
c = conn.cursor()
c.execute("SELECT MAX(pricingdate) from markit_prices")
latestdate = c.fetchone()[0]
c.close()
sql_fields = ["LoanXID", "Issuer", "DealName", "Facility", "Industry", "SP",
"Moodys", "Amount", "Maturity", "Spread", "Bid", "Offer", "Depth",
"STM", "PricingDate"]
for filename in os.listdir(os.path.join(root, "data", "markit")):
date = datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
# only update the new data
if latestdate is None or date > latestdate:
with open( os.path.join(root, "data", "markit", filename) ) as fh:
dr = csv.DictReader(fh)
data = []
for line in dr:
try:
line['Final Maturity'] = \
datetime.datetime.strptime(line['Final Maturity'], '%d-%b-%y')
except ValueError:
if line['Final Maturity'] !='':
print('Maturity non empty')
if line['Depth'] == 'implied':
line['Depth'] = 0
try:
temp = [convertToNone(line[key]) for key in fields]
except KeyError:
pdb.set_trace()
data.append(temp)
c = conn.cursor()
for row in data:
sqlstring = "INSERT INTO markit_prices({0}) " \
"VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields)))
c.execute(sqlstring, tuple(row) + (date,))
conn.commit()
c.close()
conn.close()
print("done")
|
