aboutsummaryrefslogtreecommitdiffstats
path: root/python
diff options
context:
space:
mode:
Diffstat (limited to 'python')
-rw-r--r--python/load_cf.py119
1 files changed, 67 insertions, 52 deletions
diff --git a/python/load_cf.py b/python/load_cf.py
index a4793c0f..c749b712 100644
--- a/python/load_cf.py
+++ b/python/load_cf.py
@@ -1,21 +1,21 @@
import os
import os.path
import pandas as pd
+from pandas.tseries.offsets import BDay, DateOffset
+import numpy as np
+
from intex_scenarios import dealname_from_cusip
from zipfile import ZipFile
-import re
-import datetime
-from dateutil.relativedelta import relativedelta
-from db import query_db
-import yaml
-from quantlib.time.api import Schedule, Actual360, Period, Months, Calendar, Unadjusted, ModifiedFollowing
+import re, datetime, yaml
+
+from db import query_db, conn
+from quantlib.time.api import Schedule, Actual360, Period, Months, Calendar, Unadjusted, ModifiedFollowing, Date
from quantlib.util.converter import pydate_to_qldate, qldate_to_pydate
from quantlib.settings import Settings
from yieldcurve import YC
-import numpy as np
from optimization import KLfit
import rpy2.robjects as ro
-import pdb
+import sys
if os.name =='nt':
root = "//WDsentinel/share/CorpCDOs"
@@ -63,16 +63,19 @@ def get_configfile(dealname, tradedate):
config = {"reinvflag": True}
return config
-def get_dist(tradedate):
- distfile = os.path.join(root, "Scenarios", "marketdata-{0}.RData".format(str(tradedate)))
+def get_dist(date):
+ distfile = os.path.join(root, "Scenarios", "Calibration",
+ "marketdata-{0}.RData".format(date.strftime("%Y-%m-%d")))
dist = ro.r.load(distfile)
return {"L": np.array(dist[0]),"R": np.array(dist[1])}
def get_dealdata(dealname, tradedate):
- sqlstr = "select \"Curr Collat Bal\", \"Reinv End Date\", \"Deal Next Pay Date\"," \
+ sqlstr = "select \"Curr Collat Bal\" AS currbal, \"Reinv End Date\", \"Deal Next Pay Date\"," \
"maturity, \"Principal Bal\", \"Pay Day\" from historical_clo_universe(%s, %s)"
- sqldata = query_db(sqlstr, (dealname, str(tradedate)))
- return sqldata
+ data = {k: v for k, v in query_db(sqlstr, (dealname, tradedate)).items()}
+ data["mv"] = query_db("select marketvalue from latest_deal_model_numbers where dealname = %s",
+ (dealname,))[0]
+ return data
def get_cusipdata(cusip, tradedate):
sqlstring = "select curr_balance, spread from historical_cusip_universe(%s, %s)"
@@ -82,29 +85,37 @@ def get_cusipdata(cusip, tradedate):
def get_dealschedule(dealdata, freq='1Mo', adj=Unadjusted):
us_cal = Calendar.from_name('USA')
if not dealdata["Pay Day"] or abs((dealdata["Deal Next Pay Date"]- dealdata["Pay Day"]).days - 90)>10:
- dealdata["Pay Day"] = dealdata["Deal Next Pay Date"] + relativedelta(months=-3)
+ dealdata["Pay Day"] = dealdata["Deal Next Pay Date"] - DateOffset(months=3)
return Schedule(pydate_to_qldate(dealdata["Pay Day"]), pydate_to_qldate(dealdata["maturity"]),
Period(freq), us_cal, adj, adj)
+def dealname_from_cusip(conn, cusips):
+ with conn.cursor() as c:
+ c.callproc("dealname_from_cusip", cusips)
+ dealnames = [d[0] for d in c]
+ return dealnames
+
+def discounts(tradedate):
+ calibration_date = tradedate-BDay(1)
+ m = YC(calibration_date)
+ alldates = pd.date_range(pd.to_datetime(m.reference_date, format='%d/%m/%Y'),
+ pd.to_datetime(m.max_date, format='%d/%m/%Y'))
+ alldates_ql = [Date(d.day, d.month, d.year) for d in alldates]
+ df = [m.discount(day) for day in alldates_ql]
+ yearfrac = alldates.to_series()-alldates[0]
+ yearfrac = yearfrac.astype('timedelta64[D]')/365
+ return pd.DataFrame({'yearfrac':yearfrac, 'df': df}, index=alldates)
+
def getdealcf(dealnames, zipfiles, tradedate = datetime.date.today()):
- fiels = ["Cashflow", "Principal", "Interest"]
n_scenarios = 100
cfdata = {}
- m = YC(tradedate)
+ discounts_table= discounts(tradedate)
fields = ["Cashflow", "Principal", "Interest"]
for dealname, i in dealnames.items():
- zip = zipfiles[i]
- cfdata[dealname]={}
- mv = query_db("select marketvalue from latest_deal_model_numbers where dealname = %s",
- (dealname,))[0]
-
+ zipfile = zipfiles[i]
dealdata = get_dealdata(dealname, tradedate)
- cfdata[dealname] = {"mv":mv, "currbal": dealdata["Curr Collat Bal"]}
+ cfdata[dealname] = {k: dealdata[k] for k in ["mv", "currbal"]}
config = get_configfile(dealname, tradedate)
- sched = get_dealschedule(dealdata)
- pd_sched = [pd.to_datetime(str(day), format="%d/%m/%Y") for day in sched]
- discounts = pd.Series([m.discount(day) if day > m.settle_date else 1 for day in sched],
- index = pd_sched, name='df')
if (not dealdata["Reinv End Date"] or not config["reinvflag"]):
tranches = ["COLLAT"]
@@ -112,7 +123,7 @@ def getdealcf(dealnames, zipfiles, tradedate = datetime.date.today()):
tranches = ["COLLAT_INITIAL", "COLLAT_REINVEST"]
cf = {}
- with ZipFile(zip) as myzip:
+ with ZipFile(zipfile) as myzip:
for tranche in tranches:
scen = np.zeros((100,3))
for j in range(n_scenarios):
@@ -125,48 +136,49 @@ def getdealcf(dealnames, zipfiles, tradedate = datetime.date.today()):
for c in fields:
if data.dtypes[c] != np.dtype('float64'):
data[c] = data[c].apply(sanitize_float)
- data = data[fields].join(discounts)
- scen[j,:] = np.dot(data["df"], data[fields])
+ data = data[fields].join(discounts_table)
+ scen[j,:] = np.dot(data.df, data[fields])
cf[tranche] = pd.DataFrame(scen, columns=fields)
cfdata[dealname]["panel"] = pd.concat(cf)
cf = cfdata[dealname]["panel"].Cashflow.sum(level=1)
- cfdata[dealname]["wapbasis"] = (cf.mean() - mv)/mv
- program = KLfit(cf.values/1e8, np.ones(n_scenarios)/n_scenarios, mv/1e8)
+ cfdata[dealname]["wapbasis"] = (cf.mean() - dealdata["mv"])/dealdata["mv"]
+ program = KLfit(cf.values/1e8, np.ones(n_scenarios)/n_scenarios, dealdata["mv"]/1e8)
cfdata[dealname]["weight"] = program["weight"]
print(dealname)
return cfdata
-def getcusipcf(dealnames, cusip, cfdata, tradedate):
- dist = get_dist(tradedate)
+def getcusipcf(params, cfdata, tradedate):
+ calibration_date = tradedate-BDay(1)
+ dist = get_dist(calibration_date)
cusipdata = {}
n_scenarios = 100
- m = YC(tradedate)
intexfields = ["Cashflow", "Principal", "Interest", "Balance", "Accum Interest Shortfall"]
- fields = ["Cashflow", "Principal", "Interest", "wal", "duration"]
-
+ fields = ["Cashflow", "Principal", "Interest"]
+ cusips = list(params["cusips"].keys())
+ dealnames = dealname_from_cusip(conn, cusips)
+ cusips = [c for c, d in zip(cusips, dealnames) if d in cfdata.keys()]
+ discounts_table = discounts(tradedate)
for i, cusip in enumerate(cusips):
- zip = zipfiles[i]
+ zipfile = params["zipfiles"][params["cusips"][cusip]]
dealname = dealnames[i]
- dealdata = getdealdata(dealname)
- sched = getdealschedule(dealdata, ModifiedFollowing)
- pd_sched = [pd.to_datetime(str(day), format="%d/%m/%Y") for day in sched]
- discounts = pd.Series([m.discount(day) if day > m.settle_date else 1 for day in sched],
- index = pd_sched, name='df')
- with ZipFile(zip) as myzip:
+ dealdata = get_dealdata(dealname, tradedate)
+ with ZipFile(zipfile) as myzip:
+ scen = np.zeros((n_scenarios, 5))
for j in range(n_scenarios):
filename = "{0}-CF-Scen{1}.txt".format(cusip, j+1)
data = pd.read_table(myzip.open(filename), skiprows=[1, 2], parse_dates=[0],
thousands = ",",
date_parser = lambda x: datetime.datetime.strptime(x, "%b %d, %Y"),
- index_col = 0)
+ index_col = 0,
+ usecols=range(6))
for c in intexfields:
if data.dtypes[c] != np.dtype('float64'):
data[c] = data[c].apply(sanitize_float)
- data["Balance"] = np.maximum(data["Balance"]- data["Accum Interest Shortfall"], 0)
- data = data[fields].join(discounts)
- scen[j,:2] = np.dot(data["df"], data[fields])
- scen[j,3] = np.dot(np.diff(data.Balance), T[1:])/deldata["curr_balance"]
- scen[j,4] = if scen[j,0] np.dot(data.Cashflow, data.df * T)/scen[j,0] else 0
+ data["Balance"] = np.maximum(data.Balance - data["Accum Interest Shortfall"], 0)
+ data = data.join(discounts_table)
+ scen[j,:3] = data.df.dot(data[fields])
+ scen[j,3] = data.Balance.diff()[1:].dot(data.yearfrac.shift()[1:])/dealdata["currbal"]
+ scen[j,4] = np.dot(data.Cashflow, data.df * data.yearfrac)/scen[j,0] if scen[j,0] else 0
def compute_delta(dist, dealweight, cusip_pv, tradedate, K1 = 0, K2 = 1):
Ngrid, nT = dist["L"].shape
@@ -178,7 +190,10 @@ def compute_delta(dist, dealweight, cusip_pv, tradedate, K1 = 0, K2 = 1):
if __name__=="__main__":
- tradedate = datetime.date.today()
- #tradedate = datetime.datetime.strptime("2014-03-19", "%Y-%m-%d").date()
+ if len(sys.argv)>1:
+ tradedate = datetime.datetime.strptime(sys.argv[1], "%Y-%m-%d").date()
+ else:
+ tradedate = datetime.date.today()
params = processzipfiles(tradedate)
- pomme = getdealcf(params["dealnames"], params["zipfiles"], tradedate)
+ cfdata = getdealcf(params["dealnames"], params["zipfiles"], tradedate)
+ cusipdata = getcusipcf(params, cfdata, tradedate)
generated by cgit v1.2.3-70-g09d2 (git 2.52.0) at 2025-12-24 12:49:27 +0000