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
|
import psycopg2
import os
import os.path
import datetime
from datetime import date
import pdb
import csv
if os.name =='nt':
root = "//WDsentinel/share/CorpCDOs/data/markit"
elif os.name == 'posix':
root = '/home/share/CorpCDOS/data/markit'
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 = ["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"]
latestdate = cursor.execute("SELECT MAX(pricingdate) from markit_prices")
latestdate = cursor.fetchone()[0]
for filename in os.listdir(root):
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, 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)
for row in data:
cursor.execute("INSERT INTO markit_prices(LoanXID, Issuer, DealName,"
"Facility, Industry, SP, Moodys, Amount, Maturity,"
"Spread, Bid, Offer, Depth, STM, PricingDate)"
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
tuple(row) + (date,))
conn.commit()
|
