def rollingCumulRiskFrac(window= 36):
now = dt.datetime.now()
precalculatedDict = {}
iDate = dt.datetime(1996, 1, 31)
rollingCRF = []
tAxis = []
while iDate < now:
try:
eigVal, eigVec = PCA(endDate = iDate, window=window)
crf = CumulRiskFrac(eigVal)
rollingCRF.append(crf)
tAxis.append(iDate)
precalculatedDict[iDate]= [eigVal, eigVec]
iDate = addMonths(iDate, 1)
print iDate.strftime('%Y/%m/%d %H:%M:%S')
except:
print "Unexpected error:", sys.exc_info()[0]
iDate = now
Nameoutput = open('PCAPrecomputed.pkl', 'wb')
pickle.dump(precalculatedDict, Nameoutput)
fig, ax = plt.subplots()
ax.plot_date(tAxis, rollingCRF, linestyle='--')
ax.set_title('Rolling CRF')
#ax.annotate('Test', (mdates.date2num(tAxis[1]), rAC[1]), xytext=(15, 15), textcoords='offset points', arrowprops=dict(arrowstyle='-|>'))
fig.autofmt_xdate()
plt.show()
return [tAxis, rollingCRF]
def rollingCumulRiskFrac(window=36):
now = dt.datetime.now()
precalculatedDict = {}
iDate = dt.datetime(1996, 1, 31)
rollingCRF = []
tAxis = []
while iDate < now:
try:
eigVal, eigVec = PCA(endDate=iDate, window=window)
crf = CumulRiskFrac(eigVal)
rollingCRF.append(crf)
tAxis.append(iDate)
precalculatedDict[iDate] = [eigVal, eigVec]
iDate = addMonths(iDate, 1)
print iDate.strftime('%Y/%m/%d %H:%M:%S')
except:
print "Unexpected error:", sys.exc_info()[0]
iDate = now
Nameoutput = open('PCAPrecomputed.pkl', 'wb')
pickle.dump(precalculatedDict, Nameoutput)
fig, ax = plt.subplots()
ax.plot_date(tAxis, rollingCRF, linestyle='--')
ax.set_title('Rolling CRF')
#ax.annotate('Test', (mdates.date2num(tAxis[1]), rAC[1]), xytext=(15, 15), textcoords='offset points', arrowprops=dict(arrowstyle='-|>'))
fig.autofmt_xdate()
plt.show()
return [tAxis, rollingCRF]
def getNames(endDate, inst=["brokers", "banks", "insurers", "hedgefunds"], window=36, startDate="", numComps=25):
db = MySQLdb.connect(host=IPADDR, port=3306, user="******", passwd="guest123", db="rawdata")
cursor = db.cursor()
TABLENAMES = inst
cYear = int(endDate.strftime("%Y"))
cMonth = int(endDate.strftime("%m"))
if startDate == "":
startDate = addMonths(endDate, -1 * window - 1)
else:
window = (endDate.year - startDate.year) * 12 + endDate.month - startDate.month
# store the ticker and common name for stocks,
# store the hedge fund ID and asset value for hedge funds
nameArray = []
# store the past 48 months of return data
dataArray = []
for t in TABLENAMES:
print t
if t != "hedgefunds":
selectString1 = "select Ticker, CommonName, @curRank := @curRank + 1 as rank "
orderByString = "order by abs(Price)*SharesOut desc limit 0, %d;" % numComps
selectString2 = "select abs(Price)*SharesOut, Date "
else:
selectString1 = "select HFID, AssetValue, @curRank := @curRank + 1 as rank "
orderByString = "order by AssetValue desc limit 0, %d;" % numComps
selectString2 = "select ROR, Date "
fromString = "from rawdata.%s b, (select @curRank := 0) r " % (t)
whereString = "where Year(Date)='%d' and Month(Date)='%d' " % (cYear, cMonth)
sqlQuery = selectString1 + fromString + whereString + orderByString
cursor.execute(sqlQuery)
results = cursor.fetchall()
for r in results:
# print r
nameArray.append((r[0], r[1]))
if t == "hedgefunds":
identifier = "HFID = '%d'" % r[0]
else:
identifier = "Ticker = '%s'" % r[0]
orderByString2 = "order by Date desc;"
adStr = endDate.strftime("%Y%m%d")
bdStr = startDate.strftime("%Y%m%d")
wString1 = "where %s and Date<'%s' and Date>'%s' " % (identifier, adStr, bdStr)
sqlQuery2 = selectString2 + fromString + wString1 + orderByString2
cursor.execute(sqlQuery2)
nameResults = cursor.fetchall()
RORdata = []
# print len(nameResults)
for i in xrange(window):
if i >= len(nameResults) - 1:
RORdata.append(0)
elif t == "hedgefunds":
RORdata.append(round(nameResults[i][0], 5))
else:
NAV = float(nameResults[i][0])
NAV2 = float(nameResults[i + 1][0])
if NAV != 0:
ROR = 100.0 * (NAV2 - NAV) / NAV
else:
ROR = 0.0
RORdata.append(round(ROR, 5))
RORdata.reverse()
dataArray.append(RORdata[1:])
"""
Dataoutput = open('GrangerData.pkl', 'wb')
Nameoutput = open('GrangerNames.pkl', 'wb')
pickle.dump(dataArray, Dataoutput)
pickle.dump(dataArray, Nameoutput)
"""
db.close()
return nameArray, dataArray
def getNames(endDate,
inst=['brokers', 'banks', 'insurers', 'hedgefunds'],
window=36,
startDate="",
numComps=25):
db = MySQLdb.connect(host=IPADDR,
port=3306,
user="******",
passwd="guest123",
db="rawdata")
cursor = db.cursor()
TABLENAMES = inst
cYear = int(endDate.strftime('%Y'))
cMonth = int(endDate.strftime('%m'))
if startDate == "":
startDate = addMonths(endDate, -1 * window - 1)
else:
window = (endDate.year -
startDate.year) * 12 + endDate.month - startDate.month
#store the ticker and common name for stocks,
#store the hedge fund ID and asset value for hedge funds
nameArray = []
#store the past 48 months of return data
dataArray = []
for t in TABLENAMES:
print t
if t != 'hedgefunds':
selectString1 = "select Ticker, CommonName, @curRank := @curRank + 1 as rank "
orderByString = "order by abs(Price)*SharesOut desc limit 0, %d;" % numComps
selectString2 = "select abs(Price)*SharesOut, Date "
else:
selectString1 = "select HFID, AssetValue, @curRank := @curRank + 1 as rank "
orderByString = "order by AssetValue desc limit 0, %d;" % numComps
selectString2 = "select ROR, Date "
fromString = "from rawdata.%s b, (select @curRank := 0) r " % (t)
whereString = "where Year(Date)='%d' and Month(Date)='%d' " % (cYear,
cMonth)
sqlQuery = selectString1 + fromString + whereString + orderByString
cursor.execute(sqlQuery)
results = cursor.fetchall()
for r in results:
#print r
nameArray.append((r[0], r[1]))
if t == 'hedgefunds':
identifier = "HFID = '%d'" % r[0]
else:
identifier = "Ticker = '%s'" % r[0]
orderByString2 = "order by Date desc;"
adStr = endDate.strftime('%Y%m%d')
bdStr = startDate.strftime('%Y%m%d')
wString1 = "where %s and Date<'%s' and Date>'%s' " % (identifier,
adStr, bdStr)
sqlQuery2 = selectString2 + fromString + wString1 + orderByString2
cursor.execute(sqlQuery2)
nameResults = cursor.fetchall()
RORdata = []
#print len(nameResults)
for i in xrange(window):
if i >= len(nameResults) - 1:
RORdata.append(0)
elif t == 'hedgefunds':
RORdata.append(round(nameResults[i][0], 5))
else:
NAV = float(nameResults[i][0])
NAV2 = float(nameResults[i + 1][0])
if NAV != 0:
ROR = 100.0 * (NAV2 - NAV) / NAV
else:
ROR = 0.0
RORdata.append(round(ROR, 5))
RORdata.reverse()
dataArray.append(RORdata[1:])
'''
Dataoutput = open('GrangerData.pkl', 'wb')
Nameoutput = open('GrangerNames.pkl', 'wb')
pickle.dump(dataArray, Dataoutput)
pickle.dump(dataArray, Nameoutput)
'''
db.close()
return nameArray, dataArray
