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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
|
import os
import os.path
import pandas as pd
from intex_scenarios import dealname_from_cusip
from zipfile import ZipFile
import re
import datetime
import pdb
from db import query_db
import yaml
if os.name =='nt':
root = "//WDsentinel/share/CorpCDOs"
elif os.name == 'posix':
root = '/home/share/CorpCDOs'
def sanitize_float(string):
if isinstance(string, float):
return string
else:
string = string.replace(",","")
if "(" in string:
return - float(string[1:-1])
else:
return float(string)
def processzipfiles(workdate=datetime.date.today()):
pricesdir = os.path.join(root, "Scenarios", "Prices_" + str(workdate))
zipfiles = [os.path.join(pricesdir, f) for f in os.listdir(pricesdir) \
if f.endswith(".zip")]
zipfiles = sorted(zipfiles, key=lambda x: os.stat(x).st_ctime)
cusips_dict = {}
dealnames_dict = {}
for i, zip in enumerate(zipfiles):
with ZipFile(zip) as myzip:
allfiles = set([f.filename.split("-")[0] for f in myzip.infolist()])
allfiles = allfiles - {"Total"}
dealnames = set([f.filename.split("-")[0] for f in myzip.infolist() \
if re.search("COLLAT.*Scen100", f.filename)])
cusips = allfiles - dealnames
dealnames = [d.lower() for d in dealnames]
cusips_dict.update((cusip, i) for cusip in cusips)
dealnames_dict.update((dealname, i) for dealname in dealnames)
return {"dealnames": dealnames_dict, "cusips":cusips_dict, "zipfiles": zipfiles}
def get_configfile(dealname, workdate):
configfile = os.path.join(root, "Scenarios", "_".join(["Intex curves", str(workdate)]),
"csv", dealname + ".config")
try:
with open(configfile) as fh:
config = yaml.load(fh)
except FileNotFoundError:
config = {"reinvflag": True}
return config
def getdealcf(dealnames, cusips, zipfiles, workdate = datetime.date.today()):
fiels = ["Cashflow", "Principal", "Interest"]
n_scenarios = 100
for dealname, i in dealnames.items():
zip = zipfiles[i]
mv = query_db("select marketvalue from latest_deal_model_numbers where dealname = %s",
(dealname,))[0]
sqlstr = "select \"Curr Collat Bal\", \"Reinv End Date\", \"Deal Next Pay Date\"," \
"maturity, \"Principal Bal\" from historical_clo_universe(%s, %s)"
sqldata = query_db(sqlstr, (dealname, str(workdate)))
pdb.set_trace()
config = get_configfile(dealname, workdate)
if (not sqldate or config["reinvflag"]):
tranches = ["COLLAT"]
else:
tranches = ["COLLAT_INITIAL", "COLLAT_REINVEST"]
for tranche in tranches:
cfdata[dealname][tranche]
# fields = ["Price", "WAL", "Market Value", "Modified Duration"]
# dealdata = {}
# workdate = '2013-01-09'
# for dealname in ["abcl071", "ammcclo5"]:
# tranches = os.listdir(os.path.join(root, "Scenarios", "Prices_" + workdate, dealname))
# d = {}
# for tranche in tranches:
# data = pd.read_table(os.path.join(root, "Scenarios", "Prices_" + workdate, dealname, tranche))
# datamod = data[data.columns[2:-1]].T
# datamod.columns = data[data.columns[1]]
# for field in fields:
# datamod[field] = datamod[field].apply(sanitize_float)
# d[tranche[:-7]] = datamod[fields]
# dealdata[dealname] = pd.concat(d)
# dealdata = pd.concat(dealdata)
if __name__=="__main__":
pomme = processzipfiles()
getdealcf(**pomme)
|
