def strategyMixer_dual(data, criteria = 'roeFwd_mom_120_max'):
'''
calculate mix of two strategies
data : time sereis return
criteria : base basket
'''
colList = list(data.columns)
others = [col for col in colList if col != criteria]
crt = data[criteria]
final = pd.DataFrame()
for other in others:
proc_name = multiprocessing.current_process().name
print(other+'_'+proc_name)
add = data[other]
spot = (crt + add) / 2
spot = spot.to_frame(name = criteria + '/' + other)
spot = spot.reset_index()
if other == others[0]:
final = final.append(spot, ignore_index=True)
else:
final = final.merge(spot, left_on='date', right_on='date', how='left')
# final.index = final.date
# final = final.drop(columns=['date'])
df.insertDBLite(saveLoc+'strategyMix/','strategyMix_'+criteria,'strategyMix_'+criteria,final)
def longShortSimulator_basketDb_eom(basket_dir,
basket_db,
basket_table,
simulation_dir,
simulation_db,
ret_all,
base,
descriptor_list,
db_option=True,
period=1,
fee=0,
idx=72):
'''
retAll : all stock return
base : longShortSimSetting
weight : db.getBulk() ==> full weight change
descList : descriptro List
eom : end of month
idx : number
'''
eom = base.eom_trading_date
basket = df.dbLite(basket_dir,basket_db)
if idx + period > len(eom)-1:
end_date = len(eom) -1
else :
end_date = idx + period
weight_buy_all = basket.getDateBulk(basket_table,eom[idx])
weight_rebal_all = basket.getDateBulk(basket_table,eom[end_date])
baseDate = base.shortProductRet[(base.shortProductRet.index > eom[idx]) &
(base.shortProductRet.index <= eom[end_date])]
result = pd.DataFrame(index=baseDate.index)
for desc in descriptor_list:
proc_name = multiprocessing.current_process().name
print(desc, str(idx), proc_name)
weight_buy = weight_buy_all[weight_buy_all.strategy == desc].sort_values('weight', ascending=False)
weight_rebal = weight_rebal_all[weight_rebal_all.strategy == desc].sort_values('weight', ascending=False)
ret = get_ret_adj(ret_all, weight_buy, eom[idx], eom[end_date])
cum = simulation(weight_buy[['code', 'weight']], ret)
simul = cum.portfolioSimul()
cost = cum.getCost(weight_rebal[['code', 'weight']], fee=fee)
simul = simul.merge(base.shortProductRet, left_index=True, right_index=True)
simul['코스피 200'] = (1 + simul['코스피 200']).cumprod()
simul_pct = simul.pct_change()
simul_pct.iloc[0, 0] = simul.iloc[0, 0] - 1
simul_pct.iloc[0, 1] = simul.iloc[0, 1] - 1
simul_pct[desc] = simul_pct['simulation'] - simul_pct['코스피 200']
simul_pct.iloc[-1, 2] = simul_pct.iloc[-1, 2] - cost
if simul_pct[desc].isnull().values.any():
simul_pct[desc]=np.nan
if result.empty:
result = pd.DataFrame(simul_pct[desc], columns=[desc])
else:
result = result.merge(simul_pct[desc], left_index=True, right_index=True)
result = result.reset_index()
if db_option:
df.insertDBLite(simulation_dir, simulation_db, simulation_db, result)
else:
return result
def basket_weight_maker_flt(location,
dbInputName,
base,
iterable,
neutN=5,
stockN=20,
cut=0,
db_option=True,
market='',
idx=72):
'''
if iterable is multiple strategy : tuple input
if iterable is single strategy : list input
'''
base.k2_setting(base.eom_trading_date[idx])
neut = base.info_k2_refDate
descSetting = descriptor_setting(base.eom_trading_date[idx])
descSetting.set_desc_single_rank_basket(stockNumber=stockN,
cut=cut,
market=market)
# print(descSetting.stocks_filtered)
# stList = list(descSetting.stocks_filtered.keys())
if isinstance(iterable, list):
iterable = iterable.copy()
else:
iterable = iterable
# iterable = iterable[:3]
final = pd.DataFrame()
for iter in iterable:
proc_name = multiprocessing.current_process().name
print(iter, proc_name)
spot = pd.DataFrame()
name = ''
if isinstance(iterable, list):
spot = spot.append(descSetting.stocks_filtered[iter])
name = iter
else:
for it in iter:
spot = spot.append(descSetting.stocks_filtered[it])
name += it + '/'
name = name[:-1]
spot = pd.DataFrame(spot.code, columns=['code'])
spot = basketSetting(neut, spot, rank=neutN)
spot['date'] = base.eom_trading_date[idx]
spot['strategy'] = name
if 'weight' not in spot:
spot['weight'] = np.nan
elif spot.weight.sum() >= 0.99:
spot = spot[['date', 'strategy', 'code', 'weight']]
# elif iter == 'size_max': # when size_max vs neutral is same
# spot = spot[['date', 'strategy', 'code', 'weight']]
else:
spot['weight'] = np.nan
spot = spot[['date', 'strategy', 'code', 'weight']]
final = final.append(spot)
if db_option:
df.insertDBLite(location, dbInputName, dbInputName, final)
else:
return final
def strategyMix_summarySetter():
dbList = utils.findFiles(saveLoc + 'strategyMix/')
filt = pd.DataFrame()
for i in range(len(dbList)):
data = st.get_desc_ret(saveLoc + 'strategyMix/', dbList[i][:-3])
desc = st.descTest(data)
desc.make([st.annualized_ret, st.stdev, st.mdd, st.hit_ratio])
desc.ratio('annRet', 'stdev', 'riskAdj')
desc.rank('riskAdj')
filt = filt.append(desc.descriptor[0:20])
print(i)
df.insertDBLite(saveLoc + 'strategyMix/result/', 'strategyMix_summary', 'strategyMix_summary', filt)
def longShortSimulator_eom(location,
dbInputName,
retAll,
base,
weight ,
descList,
eom,
idx):
'''
retAll : all stock return
base : longShortSimSetting
weight : db.getBulk() ==> full weight change
descList : descriptro List
eom : end of month
idx : number
'''
weightBuyAll = weight[weight.date == eom[idx]]
weightRebalAll = weight[weight.date == eom[idx+1]]
baseDate = base.shortProductRet[(base.shortProductRet.index > eom[idx]) &
(base.shortProductRet.index <= eom[idx + 1])]
result = pd.DataFrame(index=baseDate.index)
for desc in descList:
proc_name = multiprocessing.current_process().name
print(desc, str(i), proc_name)
weightBuy = weightBuyAll[weightBuyAll.strategy == desc].sort_values('weight', ascending=False)
weightRebal = weightRebalAll[weightRebalAll.strategy == desc].sort_values('weight', ascending=False)
ret = get_ret_adj(retAll, weightBuy, eom[idx], eom[idx + 1])
cum = simulation(weightBuy[['code', 'weight']], ret)
simul = cum.portfolioSimul()
cost = cum.getCost(weightRebal[['code', 'weight']], fee=0.0015)
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[desc] = simulPct['simulation'] - simulPct['코스피 200']
simulPct.iloc[-1, 2] = simulPct.iloc[-1, 2] - cost
if simulPct[desc].isnull().values.any():
simulPct[desc]=np.nan
if result.empty:
result = pd.DataFrame(simulPct[desc], columns=[desc])
else:
result = result.merge(simulPct[desc], left_index=True, right_index=True)
result = result.reset_index()
# return result
df.insertDBLite(location, dbInputName, dbInputName, result)
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()
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()