diff options
| -rw-r--r-- | python/intex_prices.py | 52 | ||||
| -rw-r--r-- | python/load_cf.py | 95 |
2 files changed, 95 insertions, 52 deletions
diff --git a/python/intex_prices.py b/python/intex_prices.py deleted file mode 100644 index 9b36ffb7..00000000 --- a/python/intex_prices.py +++ /dev/null @@ -1,52 +0,0 @@ -import os
-import os.path
-import psycopg2
-import pdb
-import pandas as pd
-
-
-if os.name =='nt':
- root = "//WDsentinel/share/CorpCDOs"
-elif os.name == 'posix':
- root = '/home/share/CorpCDOs'
-
-conn = psycopg2.connect(database="ET",
- user="et_user",
- password="Serenitas1",
- host="192.168.1.108")
-cursor = conn.cursor()
-
-def cusips_from_dealname(dealname, curr = cursor):
- curr.execute("SELECT \"Deal Cusip List\" FROM latest_clo_universe "
- " WHERE dealname = %s", (dealname,))
- return curr.fetchone()[0]
-
-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)
-
-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)
-
-cursor.close()
-conn.close()
diff --git a/python/load_cf.py b/python/load_cf.py new file mode 100644 index 00000000..ce70af0f --- /dev/null +++ b/python/load_cf.py @@ -0,0 +1,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)
|