def readData(self,
worksheetName,
dbName,
tableName,
cellRange='A1:A1',
saveLoc=saveLoc,
dbInput=True):
sh = self.wb.sheets[worksheetName]
data = pd.DataFrame(sh.range(cellRange).value)
header = data.iloc[0]
data = pd.DataFrame(data.values[1:], columns=header)
data.replace('', np.nan, inplace=True)
data = data.dropna(thresh=4)
data['date'] = data['date'].apply(
lambda x: dt.datetime.strftime(x, '%Y-%m-%d'))
if dbInput == True:
df.insertDBLite(saveLoc, dbName, tableName, data)
else:
return data
def upload(self):
files = utils.findFiles(dbLoc)
if self.dbName + '.db' in files:
os.remove(dbLoc + self.dbName + '.db')
wb = xw.Book(self.excelLoc + self.excelName)
sht = wb.sheets(self.worksheetName)
if self.totalUpdate == True:
wb.macro('QntWS_RefresAll')()
data = pd.DataFrame(sht.range(self.dataLoc).value)
header = data.iloc[0]
data = pd.DataFrame(data.values[1:], columns=header)
data.replace('', np.nan, inplace=True)
data = data.dropna(thresh=1)
dateLists = list(data.columns)
dateLists = [dateList for dateList in dateLists if 'date' in dateList]
for dateList in dateLists:
data[dateList] = data[dateList].apply(lambda x: x if pd.isna(
x) else dt.datetime.strftime(x, '%Y-%m-%d'))
df.insertDBLite(self.dbLoc, self.dbName, self.tableName, data)
wb.save()
wb.close()
simul = simul.merge(base.shortProductRet, left_index = True, right_index = True)
simul['코스피 200'] = (1+simul['코스피 200']).cumprod()
simulPct = simul.pct_change()
simulPct.iloc[0,0] = simul.iloc[0,0]-1
simulPct.iloc[0,1] = simul.iloc[0,1]-1
simulPct[descList[strategyNumber]] =simulPct['simulation'] - simulPct['코스피 200']
if result.empty:
result = pd.DataFrame(simulPct[descList[strategyNumber]],columns=[descList[strategyNumber]])
else:
result = result.merge(simulPct[descList[strategyNumber]], left_index = True, right_index = True)
result = result.reset_index()
df.insertDBLite(saveLoc,'descriptor_ret', 'descriptor_ret', result)
#%%
if __name__ == '__main__':
# date setting
baseDbs = dh.getAlldatabase(dbLoc)
descDb = dh.getAlldatabase(saveLoc)
trdDate = dh.getTradingDate()
eom = utils.getEomTrdDate(trdDate)
# short product base setting
short = dh.getBenchmark(dbLoc)
descAdj.winsorization()
descAdj.normalization()
data = descAdj.normData
reg = regression(
ret,
data,
infoColumns=['date', 'code', 'name', 'mkt', 'tmv', 'industry'])
reg.setDummies(dummyGroup='industry', dummyColumn=dummyList)
reg.getSimpleRegressionLoop(dummyV=True)
factorRet = reg.factorRet
factorScore = reg.factorScore
df.insertDBLite(saveLoc, 'descRet', 'descRet', factorRet)
df.insertDBLite(saveLoc, 'descScore', 'descScore', factorScore)
# print('---------' + eom[i] + '-----------')
#%% pca
if __name__ == '__main__':
database1 = dh.getAlldatabase(saveLoc)
tradingDate = dh.getTradingDate()
eom = utils.getEomTrdDate(tradingDate)
# starting = 72
# i = 72
starting = 72
ending = len(eom)
descriptor = df.dbLite(saveLoc, 'descriptor')
# dummyList = descriptor.getList('descriptor', 'industry')
for i in range(starting, ending):
def upload(self, thresh=4):
files = utils.findFiles(dbLoc)
if self.dbName + '.db' in files:
# 부분업데이트
db = df.dbLite(dbLoc, self.dbName)
lastUpdatedDate = db.getList(self.tableName, 'date')[-1]
updateStart = self.tradingDate.index[
self.tradingDate['date'] == lastUpdatedDate].tolist()[0] + 1
start = updateStart
end = len(self.tradingDate)
if start < end:
wb = xw.Book(self.excelLoc + self.excelName)
sht = wb.sheets(self.worksheetName)
sht.cells(7, 2).value = self.tradingDate.date[start]
# if crss is false then update before looping
if self.crss == False:
if self.totalUpdate == True:
wb.macro('QntWS_RefresAll')()
for i in range(start, end):
sht.cells(7, 2).value = self.tradingDate.date[i]
if (self.econ == True) and (i > 0):
sht.cells(10, 2).value = self.tradingDate.date[i - 1]
if self.crss == True:
wb.macro('QntWS_RefresAll')()
data = pd.DataFrame(sht.range(self.dataLoc).value)
header = data.iloc[0]
data = pd.DataFrame(data.values[1:], columns=header)
data.replace('', np.nan, inplace=True)
data = data.dropna(thresh=thresh)
data['date'] = data['date'].apply(
lambda x: dt.datetime.strftime(x, '%Y-%m-%d'))
df.insertDBLite(self.dbLoc, self.dbName, self.tableName,
data)
print('----------', self.dbName, str(i),
len(self.tradingDate), '---------')
wb.save()
wb.close()
else:
print(self.dbName, 'do not need to update')
else:
#전체 업데이트
wb = xw.Book(self.excelLoc + self.excelName)
sht = wb.sheets(self.worksheetName)
sht.cells(7, 2).value = self.tradingDate.date[0]
if (self.totalUpdate == True) and (self.crss == False):
wb.macro('QntWS_RefreshAll')()
for i in range(len(self.tradingDate)):
sht.cells(7, 2).value = self.tradingDate.date[i]
if (self.econ == True) and (i > 0):
sht.cells(10, 2).value = self.tradingDate.date[i - 1]
if self.crss == True:
wb.macro('QntWS_RefreshAll')()
data = pd.DataFrame(sht.range(self.dataLoc).value)
header = data.iloc[0]
data = pd.DataFrame(data.values[1:], columns=header)
data.replace('', np.nan, inplace=True)
data = data.dropna(thresh=thresh)
data['date'] = data['date'].apply(
lambda x: dt.datetime.strftime(x, '%Y-%m-%d'))
df.insertDBLite(self.dbLoc, self.dbName, self.tableName, data)
print('----------', self.dbName, str(i), len(self.tradingDate),
'---------')
# wb.macro('QntWS_RefreshAll')()
wb.save()
wb.close()