def other_indicator(model: OfflineTaskModel):
# 离线计算其他指标
indicatorInfo = model.taskModel
indicatorId = model.extVal
if del_data(sqlPa="delete from fof_index_value where INDICATOR_ID = '%s'" % indicatorId) == False:
raise Error("删除历史数据失败，任务结束")
if indicatorId not in INDEX_CODE_NAME_CACHE.keys():
raise Error("指标id不存在:{}".format(indicatorId))
indexName = INDEX_CODE_NAME_CACHE[indicatorId]
# fundAdjNav = getPublicData(['股票型', '混合型'])
fundAdjNav = getPublicData()
fundSymbols = list(fundAdjNav.columns)
updatedType = []
fundClass_1 = pd.Series('混合型基金', index=fundSymbols)
fundClass_tmp = getData_fundInformation(fundSymbols)['FUND_INVESTTYPE']
fundClass_1[fundClass_tmp.index] = fundClass_tmp
for symbol in fundSymbols:
allIndicators = []
fundType = fundClass_1[symbol]
if fundType not in updatedType:
for cycle in n_cycle_tp:
btParms = {'indexName': indexName, 'cycle': cycle, 'symbol': symbol, 'sample': '一级',
'marketIndex': '',
'otherPar': ''}
self = indexScore_fund(btParms, fundAdjNav, 'auto')
op = self.output
val = op['factorValue']
allIndicators.append(val)
updatedType.append(fundType)
df = pd.DataFrame(allIndicators).fillna(bus_const.blank)
cols = df.columns.tolist()
for col in cols:
res = df[col]
lst = res.values.tolist()
lst = [transformFloatIfAvaliable(l) for l in lst]
objId = uuid_util.gen_uuid()
lst.insert(0, str(objId))
lst.insert(1, str(indicatorId))
lst.insert(2, col)
date = formatDate2yyyymmdd()
lst.insert(3, str(date))
lst.append("sys")
lst.append(datetime.datetime.now())
lst.append("sys")
lst.append(datetime.datetime.now())
tp = tuple(lst)
sql = "INSERT INTO fof_index_value (`OBJECT_ID`, `INDICATOR_ID`, `J_WINDCODE`, `TRADE_DT`, `THISYEAR_VALUE`, `QUARTER_VALUE`, `HALFYEAR_VALUE`, `YEAR_VALUE`, `TWOYEA_VALUE`, `THREEYEAR_VALUE`, `FIVEYEAR_VALUE`, `N1_VALUE`, `N2_VALUE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`, `DELETE_FLAG`) VALUES ( %s, %s, %s, %s, %s, %s,%s,%s, %s,%s, %s, %s, %s, %s, %s, %s, %s,0)"
mysqlops.insert_one(MysqlConf.DB.fof, sql, tp)
allIndicators.clear()
def volatility(model: OfflineTaskModel):
# 离线计算年化波动率
if del_data('fof_variance') == False:
raise Error("删除历史数据失败，任务结束")
indicatorInfo = model.taskModel
taskName = indicatorInfo.value[0]
indiId = get_indicator_id(taskName)
# fundAdjNav = getPublicData(['股票型', '混合型'])
fundAdjNav = getPublicData()
fundSymbols = list(fundAdjNav.columns)
fundClass_1 = pd.Series('混合型基金', index=fundSymbols)
fundClass_tmp = getData_fundInformation(fundSymbols)['FUND_INVESTTYPE']
fundClass_1[fundClass_tmp.index] = fundClass_tmp
halfs = indicatorInfo.value[1]
for half in halfs:
updatedType = []
allIndicators = []
for symbol in fundSymbols:
fundType = fundClass_1[symbol]
if fundType not in updatedType:
for cycle in n_cycle_tp:
btParms = {'indexName': taskName, 'cycle': cycle, 'symbol': symbol, 'sample': '一级',
'marketIndex': '',
'otherPar': half}
self = indexScore_fund(btParms, fundAdjNav, 'auto')
op = self.output
val = op['factorValue']
allIndicators.append(val)
updatedType.append(fundType)
df = pd.DataFrame(allIndicators).fillna(bus_const.blank)
cols = df.columns.tolist()
for col in cols:
res = df[col]
lst = res.values.tolist()
lst = [transformFloatIfAvaliable(l) for l in lst]
objId = uuid_util.gen_uuid()
lst.insert(0, str(objId))
lst.insert(1, str(indiId))
lst.insert(2, str(col))
date = formatDate2yyyymmdd()
lst.insert(3, str(date))
lst.insert(4, transformString2Decimal(half))
lst.append("sys")
lst.append(datetime.datetime.now())
lst.append("sys")
lst.append(datetime.datetime.now())
tp = tuple(lst)
sql = "INSERT INTO fof_variance (`OBJECT_ID`, `INDICATOR_ID`, `J_WINDCODE`, `TRADE_DT`, `F_WEIGHT`, `THISYEAR_VALUE`, `QUARTER_VALUE`, `HALFYEAR_VALUE`, `YEAR_VALUE`, `TWOYEA_VALUE`, `THREEYEAR_VALUE`, `FIVEYEAR_VALUE`, `N1_VALUE`, `N2_VALUE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`, `DELETE_FLAG`) VALUES ( %s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,0)"
mysqlops.insert_one(MysqlConf.DB.fof, sql, tp)
allIndicators.clear()
def stockexpousre(model):
# factorName即为mongodb数据库相关collection的名字，一次仅支持传入一个因子值
sql = "delete from fof_stockexpousre "
mysqlops.fetch_one(MysqlConf.DB.fof, sql)
if not del_data('fof_stockexpousre'):
raise Error("删除历史数据失败，任务结束")
fs = [
'value',
'size',
'beta',
'earning',
# 'factorReturn',
'growth',
'leverage',
'liquidity',
'momentum',
'nonlinear_size',
'size',
'volatility'
]
sql = 'insert into ' \
'fof_stockexpousre (OBJECT_ID,trade_dt,s_windcode,indicator_code,factor_value,CREATE_USER_ID,CREATE_TIME,UPDATE_USER_ID,UPDATE_TIME)' \
'values (%s,%s,%s,%s,%s,%s,%s,%s,%s)'
for fName in fs:
valueExp = getData_factorExposure(fName)
valueExp = valueExp.fillna("9999.000000")
di = valueExp.to_dict()
for k in di:
# print("K===%s" % k)
for v in di.get(k):
s = uuid_util.gen_uuid()
lsd = []
oid = s[:5] + '-' + s[5:9] + '-' + s[9:13] + '-' + s[
13:18] + '-' + s[18:]
lsd.append(oid)
r = v._date_repr.replace("-", "")
lsd.append(r)
lsd.append(k[:-2] + "." + k[-2:])
lsd.append(fName)
lsd.append(transformFloatIfAvaliable(di.get(k).get(v)))
lsd.append("sys")
lsd.append(datetime.now())
lsd.append("sys")
lsd.append(datetime.now())
mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lsd))
def equ_timing(model):
if not del_data('fof_fund_timing'):
raise Error("删除历史数据失败，任务结束")
managerInfo = getData_fundManagerInfo_all()
fundSymbols = getData_fundSymbols(['股票型', '混合型'])
tmp = []
for row in managerInfo.index:
if managerInfo.loc[row, 'fund'] in fundSymbols:
tmp.append(managerInfo.loc[row].to_dict())
managerInfo = pd.DataFrame(tmp, columns=managerInfo.columns)
# 批量生成基金经理行业配置能力
for i in range(len(managerInfo)):
try:
btParms = {
'symbol': managerInfo.loc[i, 'fund'],
'manager': managerInfo.loc[i, 'manager']
}
test = equityTimingAbility_manager(btParms)
output = test.output
timing = output['hisTiming']
lst = timing.values.tolist()
for ls in lst:
ll = []
id_ = uuid_util.gen_uuid()
ll.append(id_)
ll.append(output['symbol'])
ll.append(output['managerId'])
ll.append(output['manager'])
ll.append(ls[0]) # start
ll.append(ls[1]) # end
ll.append(transformFloatIfAvaliable(ls[2])) # RISING
ll.append(transformFloatIfAvaliable(ls[3])) # HOLDINGS_BEGIN
ll.append(transformFloatIfAvaliable(ls[4])) # HOLDINGS_END
ll.append(transformFloatIfAvaliable(ls[5])) # TIMING_RESULT
ll.append(transformFloatIfAvaliable(ls[6])) # HOLDINGS_RATIO
ll.append(transformFloatIfAvaliable(output['winRatio']))
ll.append(transformFloatIfAvaliable4(output['avgTimingContr']))
ll.append("sys")
ll.append(datetime.now())
ll.append("sys")
ll.append(datetime.now())
sql = "INSERT INTO `fof`.`fof_fund_timing`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`, `MANAGER_NAME`, `START_DATE`, `END_DATE`, `RISING`, `HOLDINGS_BEGIN`, `HOLDINGS_END`, `TIMING_RESULT`, `HOLDINGS_RATIO`,`TIMING_WIN_RATIO`,`AVG_CONTRIBUTION`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
insert_one(MysqlConf.DB.fof, sql, tuple(ll))
except:
pass
def information_ratio(model: OfflineTaskModel):
# 离线计算信息比率
if del_data('fof_inforatio') == False:
raise Error("删除历史数据失败，任务结束")
# sql = "delete from fof_inforatio "
# mysqlops.fetch_one(MysqlConf.DB.fof, sql)
indicatorInfo = model.taskModel
taskName = indicatorInfo.value[0]
indiId = get_indicator_id(taskName)
# fundAdjNav = getPublicData(['股票型', '混合型'])
fundAdjNav = getPublicData()
fundSymbols = list(fundAdjNav.columns)
updatedType = []
fundClass_1 = pd.Series('混合型基金', index=fundSymbols)
fundClass_tmp = getData_fundInformation(fundSymbols)['FUND_INVESTTYPE']
fundClass_1[fundClass_tmp.index] = fundClass_tmp
for symbol in fundSymbols:
allIndicators = []
fundType = fundClass_1[symbol]
if fundType not in updatedType:
for cycle in n_cycle_tp:
btParms = {'indexName': taskName, 'cycle': cycle, 'symbol': symbol, 'sample': '一级',
'marketIndex': '',
'otherPar': ''}
self = indexScore_fund(btParms, fundAdjNav, 'auto')
op = self.output
val = op['factorValue']
allIndicators.append(val)
updatedType.append(fundType)
df = pd.DataFrame(allIndicators).fillna(bus_const.blank)
cols = df.columns.tolist()
for col in cols:
res = df[col]
dd = res.values.tolist()
lst = [transformFloatIfAvaliable(l) for l in dd]
objId = uuid_util.gen_uuid()
lst.insert(0, str(objId))
lst.insert(1, str(indiId))
lst.insert(2, col)
date = formatDate2yyyymmdd()
lst.insert(3, str(date))
# idxVal = None
# try:
# idxVal = CODE_INDEX_CACHE[col]
# except:
# # 缓存没有查一次数据库
# sql = "SELECT s_info_windcode,s_info_indexwindcode FROM chinamutualfundbenchmark where S_INFO_WINDCODE = '{}'".format(
# col)
# res = mysqlops.fetchmany(MysqlConf.DB.fof, sql)
# if res and 's_info_indexwindcode' in res and res['s_info_indexwindcode'] is not None:
# idxVal = res['s_info_indexwindcode'].decode()
lst.insert(4, indicatorInfo.value[1]) # 比较基准wind代码
lst.append("sys")
lst.append(datetime.datetime.now())
lst.append("sys")
lst.append(datetime.datetime.now())
tp = tuple(lst)
sql = "INSERT INTO fof_inforatio (`OBJECT_ID`, `INDICATOR_ID`, `J_WINDCODE`, `TRADE_DT`, `B_WINDCODE`, `THISYEAR_VALUE`, `QUARTER_VALUE`, `HALFYEAR_VALUE`, `YEAR_VALUE`, `TWOYEA_VALUE`, `THREEYEAR_VALUE`, `FIVEYEAR_VALUE`, `N1_VALUE`, `N2_VALUE`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`, `DELETE_FLAG`) VALUES ( %s, %s, %s, %s, %s, %s, %s,%s,%s,%s, %s, %s, %s, %s, %s, %s, %s, %s,0)"
mysqlops.insert_one(MysqlConf.DB.fof, sql, tp)
allIndicators.clear()
print(i)
test = equityTimingAbility_manager(btParms)
output = test.output
timing = output['hisTiming']
lst = timing.values.tolist()
for ls in lst:
print(ls)
ll = []
id_ = uuid_util.gen_uuid()
ll.append(id_)
ll.append(output['symbol'])
ll.append(output['managerId'])
ll.append(output['manager'])
ll.append(ls[0]) # start
ll.append(ls[1]) # end
ll.append(transformFloatIfAvaliable(ls[2])) # RISING
ll.append(transformFloatIfAvaliable(ls[3])) # HOLDINGS_BEGIN
ll.append(transformFloatIfAvaliable(ls[4])) # HOLDINGS_END
ll.append(transformFloatIfAvaliable(ls[5])) # TIMING_RESULT
ll.append(transformFloatIfAvaliable(ls[6])) # HOLDINGS_RATIO
ll.append("sys")
ll.append(datetime.now())
ll.append("sys")
ll.append(datetime.now())
sql = "INSERT INTO `fof`.`fof_fund_timing`(`OBJECT_ID`, `J_WINDCODE`, `MANAGER_ID`, `MANAGER_NAME`, `START_DATE`, `END_DATE`, `RISING`, `HOLDINGS_BEGIN`, `HOLDINGS_END`, `TIMING_RESULT`, `HOLDINGS_RATIO`, `CREATE_USER_ID`, `CREATE_TIME`, `UPDATE_USER_ID`, `UPDATE_TIME`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
from conf.mysqlops import *
insert_one(MysqlConf.DB.fof, sql, tuple(ll))
except Exception as e:
print(e)
output={'managerId':managerInfo.loc[i,'managerId'],'symbol':managerInfo.loc[i,'fund'],
'manager':managerInfo.loc[i,'manager'],
sql = 'insert into ' \
'fof_stockexpousre (OBJECT_ID,trade_dt,s_windcode,indicator_code,factor_value,CREATE_USER_ID,CREATE_TIME,UPDATE_USER_ID,UPDATE_TIME)' \
'values (%s,%s,%s,%s,%s,%s,%s,%s,%s)'
for fName in fs:
print('start indicator:%s' % fName)
valueExp = getData_factorExposure(fName)
valueExp = valueExp.fillna("9999.000000")
di = valueExp.to_dict()
for k in di:
# print("K===%s" % k)
for v in di.get(k):
s = uuid_util.gen_uuid()
lsd = []
oid = s[:5] + '-' + s[5:9] + '-' + s[9:13] + '-' + s[13:18] + '-' + s[18:]
lsd.append(oid)
r = v._date_repr.replace("-", "")
lsd.append(r)
lsd.append(k[:-2] + "." + k[-2:])
lsd.append(fName)
lsd.append(transformFloatIfAvaliable(di.get(k).get(v)))
lsd.append("sys")
lsd.append(datetime.now())
lsd.append("sys")
lsd.append(datetime.now())
mysqlops.insert_one(MysqlConf.DB.fof, sql, tuple(lsd))
# sizeExp = getData_factorExposure('size')
# 因子收益率可以一次性提取所有数据
# factorReturn = getData_factorReturn()