def fc_update_nav(ctx, optid):
lookback_days = 365
blacklist = [24, 32, 40]
factor_ids = [
'1200000%02d' % i for i in range(1, 40) if i not in blacklist
]
trade_dates = ATradeDate.month_trade_date(begin_date='2018-01-01')
date = trade_dates[-1]
start_date = (date -
datetime.timedelta(lookback_days)).strftime('%Y-%m-%d')
end_date = date.strftime('%Y-%m-%d')
corr0 = load_ind(factor_ids, start_date, end_date)
res = clusterKMeansBase(corr0, maxNumClusters=10, n_init=100)
asset_cluster = res[1]
asset_cluster = dict(
zip(sorted(asset_cluster), sorted(asset_cluster.values())))
factor_name = base_ra_index.load()
for k, v in asset_cluster.iteritems():
v = np.array(v).astype('int')
print factor_name.loc[v]
assets = {}
for factor_id in factor_ids:
assets[factor_id] = Asset.load_nav_series(factor_id)
df_assets = pd.DataFrame(assets)
db = database.connection('asset')
metadata = MetaData(bind=db)
t = Table('ra_composite_asset_nav', metadata, autoload=True)
for layer in asset_cluster.keys():
layer_id = 'FC.000001.%d' % (layer + 1)
layer_assets = asset_cluster[layer]
layer_nav = df_assets.loc[:, layer_assets]
layer_ret = layer_nav.pct_change().dropna()
layer_ret = layer_ret.mean(1)
layer_ret = layer_ret.reset_index()
layer_ret.columns = ['ra_date', 'ra_inc']
layer_ret['ra_nav'] = (1 + layer_ret['ra_inc']).cumprod()
layer_ret['ra_asset_id'] = layer_id
df_new = layer_ret.set_index(['ra_asset_id', 'ra_date'])
df_old = asset_ra_composite_asset_nav.load_nav(layer_id)
df_new = df_new.reindex(columns=['ra_nav', 'ra_inc'])
database.batch(db, t, df_new, df_old, timestamp=False)
def allocate_benchmark_comp(ctx):
index_ids = ['120000016', '120000010']
data = {}
for _id in index_ids:
data[_id] = base_ra_index_nav.load_series(_id)
df = pd.DataFrame(data)
composite_asset_ids = [
'20201', '20202', '20203', '20204', '20205', '20206', '20207', '20208'
]
data = {}
for _id in composite_asset_ids:
nav = asset_ra_composite_asset_nav.load_nav(_id)
nav = nav.reset_index()
nav = nav[['ra_date', 'ra_nav']]
nav = nav.set_index(['ra_date'])
data[_id] = nav.ra_nav
bench_df = pd.DataFrame(data)
benchmark_df = pd.concat([bench_df, df],
axis=1,
join_axes=[bench_df.index])
conn = MySQLdb.connect(**config.db_asset)
conn.autocommit(True)
dfs = []
for i in range(0, 10):
sql = 'select on_date as date, on_nav as nav from on_online_nav where on_online_id = 80000%d and on_type = 8' % i
df = pd.read_sql(sql, conn, index_col=['date'], parse_dates=['date'])
df.columns = ['risk_' + str(i)]
dfs.append(df)
df = pd.concat(dfs, axis=1)
conn.close()
df = pd.concat([df, benchmark_df], axis=1, join_axes=[df.index])
df = df.fillna(method='pad')
df = df.rename(
columns={
'risk_0': '风险10',
'risk_1': '风险1',
'risk_2': '风险2',
'risk_3': '风险3',
'risk_4': '风险4',
'risk_5': '风险5',
'risk_6': '风险6',
'risk_7': '风险7',
'risk_8': '风险8',
'risk_9': '风险9',
'20201': '风险2比较基准',
'20202': '风险3比较基准',
'20203': '风险4比较基准',
'20204': '风险5比较基准',
'20205': '风险6比较基准',
'20206': '风险7比较基准',
'20207': '风险8比较基准',
'20208': '风险9比较基准',
'120000016': '风险10比较基准',
'120000010': '风险1比较基准'
})
cols = [
'风险1', '风险2', '风险3', '风险4', '风险5', '风险6', '风险7', '风险8', '风险9', '风险10',
'风险1比较基准', '风险2比较基准', '风险3比较基准', '风险4比较基准', '风险5比较基准', '风险6比较基准',
'风险7比较基准', '风险8比较基准', '风险9比较基准', '风险10比较基准'
]
df = df[cols]
#df.to_csv('online_benchmark.csv', encoding='gbk')
result_df = pd.DataFrame(columns=df.columns)
last_day = df.index[-1]
#print(last_day)
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 当日'] = df.pct_change().iloc[-1]
result_df.loc[
df.index[-1].strftime('%Y-%m-%d') +
' 过去一周'] = df.loc[last_day] / df.loc[last_day - timedelta(weeks=1)] - 1
result_df.loc[
df.index[-1].strftime('%Y-%m-%d') +
' 过去一月'] = df.loc[last_day] / df.loc[last_day - timedelta(days=31)] - 1
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去三个月'] = df.loc[last_day] / df.loc[last_day -
timedelta(days=91)] - 1
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去六个月'] = df.loc[last_day] / df.loc[last_day - timedelta(
days=182)] - 1
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去一年'] = df.loc[last_day] / df.loc[last_day -
timedelta(days=365)] - 1
#result_df.to_csv('智能组合收益与比较基准收益比较.csv', encoding='gbk')
df = result_df
df = df.reset_index()
df['date'] = pd.Series(None)
df['statistics_type'] = pd.Series(None)
df = df.apply(split_ct, axis=1)
df.pop('index')
df.set_index('date', inplace=True)
cols_list = list(df.columns)
cols_list.insert(0, cols_list.pop(-1))
df = df[cols_list]
if len(df) < 1:
print("无数据需要插入")
return
# 开始注入数据
engine = database.connection('asset')
Session = sessionmaker(bind=engine)
session = Session()
for i in range(len(df)):
ins = asset_allocate.smart_risk(sir_date=df.index[i],
sir_statistics_type=df.iloc[i, 0],
sir_risk1=df.iloc[i, 1],
sir_risk2=df.iloc[i, 2],
sir_risk3=df.iloc[i, 3],
sir_risk4=df.iloc[i, 4],
sir_risk5=df.iloc[i, 5],
sir_risk6=df.iloc[i, 6],
sir_risk7=df.iloc[i, 7],
sir_risk8=df.iloc[i, 8],
sir_risk9=df.iloc[i, 9],
sir_risk10=df.iloc[i, 10],
sir_risk1_standard=df.iloc[i, 11],
sir_risk2_standard=df.iloc[i, 12],
sir_risk3_standard=df.iloc[i, 13],
sir_risk4_standard=df.iloc[i, 14],
sir_risk5_standard=df.iloc[i, 15],
sir_risk6_standard=df.iloc[i, 16],
sir_risk7_standard=df.iloc[i, 17],
sir_risk8_standard=df.iloc[i, 18],
sir_risk9_standard=df.iloc[i, 19],
sir_risk10_standard=df.iloc[i, 20])
session.add(ins)
session.commit()
print("成功插入了 %d 条数据;" % (i + 1))
def allocate_benchmark_comp(ctx):
index_ids = ['120000016', '120000010']
data = {}
for _id in index_ids:
data[_id] = base_ra_index_nav.load_series(_id)
df = pd.DataFrame(data)
composite_asset_ids = [
'20201', '20202', '20203', '20204', '20205', '20206', '20207', '20208'
]
data = {}
for _id in composite_asset_ids:
nav = asset_ra_composite_asset_nav.load_nav(_id)
nav = nav.reset_index()
nav = nav[['ra_date', 'ra_nav']]
nav = nav.set_index(['ra_date'])
data[_id] = nav.ra_nav
bench_df = pd.DataFrame(data)
benchmark_df = pd.concat([bench_df, df],
axis=1,
join_axes=[bench_df.index])
conn = MySQLdb.connect(**config.db_asset)
conn.autocommit(True)
dfs = []
for i in range(0, 10):
sql = 'select on_date as date, on_nav as nav from on_online_nav where on_online_id = 80000%d and on_type = 8' % i
df = pd.read_sql(sql, conn, index_col=['date'], parse_dates=['date'])
df.columns = ['risk_' + str(i)]
dfs.append(df)
df = pd.concat(dfs, axis=1)
conn.close()
df = pd.concat([df, benchmark_df], axis=1, join_axes=[df.index])
df = df.fillna(method='pad')
df = df.rename(
columns={
'risk_0': '风险10',
'risk_1': '风险1',
'risk_2': '风险2',
'risk_3': '风险3',
'risk_4': '风险4',
'risk_5': '风险5',
'risk_6': '风险6',
'risk_7': '风险7',
'risk_8': '风险8',
'risk_9': '风险9',
'20201': '风险2比较基准',
'20202': '风险3比较基准',
'20203': '风险4比较基准',
'20204': '风险5比较基准',
'20205': '风险6比较基准',
'20206': '风险7比较基准',
'20207': '风险8比较基准',
'20208': '风险9比较基准',
'120000016': '风险10比较基准',
'120000010': '风险1比较基准'
})
cols = [
'风险1', '风险2', '风险3', '风险4', '风险5', '风险6', '风险7', '风险8', '风险9', '风险10',
'风险1比较基准', '风险2比较基准', '风险3比较基准', '风险4比较基准', '风险5比较基准', '风险6比较基准',
'风险7比较基准', '风险8比较基准', '风险9比较基准', '风险10比较基准'
]
df = df[cols]
result_df = pd.DataFrame(columns=df.columns)
last_day = df.index[-1]
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 当日'] = df.pct_change().iloc[-1]
result_df.loc[
df.index[-1].strftime('%Y-%m-%d') +
' 过去一周'] = df.loc[last_day] / df.loc[last_day - timedelta(weeks=1)] - 1
result_df.loc[
df.index[-1].strftime('%Y-%m-%d') +
' 过去一月'] = df.loc[last_day] / df.loc[last_day - timedelta(days=31)] - 1
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去三个月'] = df.loc[last_day] / df.loc[last_day -
timedelta(days=91)] - 1
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去六个月'] = df.loc[last_day] / df.loc[last_day - timedelta(
days=182)] - 1
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去一年'] = df.loc[last_day] / df.loc[last_day -
timedelta(days=365)] - 1
result_df.to_csv('智能组合收益与比较基准收益比较.csv', encoding='gbk')
def allocate_benchmark_comp():
index_ids = ['120000016', '120000010']
data = {}
for _id in index_ids:
data[_id] = base_ra_index_nav.load_series(_id)
df = pd.DataFrame(data)
composite_asset_ids = [
'20201', '20202', '20203', '20204', '20205', '20206', '20207', '20208'
]
data = {}
for _id in composite_asset_ids:
nav = asset_ra_composite_asset_nav.load_nav(_id)
nav = nav.reset_index()
nav = nav[['ra_date', 'ra_nav']]
nav = nav.set_index(['ra_date'])
data[_id] = nav.ra_nav
bench_df = pd.DataFrame(data)
benchmark_df = pd.concat([bench_df, df],
axis=1,
join_axes=[bench_df.index])
conn = MySQLdb.connect(**config.db_asset)
conn.autocommit(True)
dfs = []
for i in range(0, 10):
sql = 'select on_date as date, on_nav as nav from on_online_nav where on_online_id = 80000%d and on_type = 8' % i
df = pd.read_sql(sql, conn, index_col=['date'], parse_dates=['date'])
df.columns = ['risk_' + str(i)]
dfs.append(df)
df = pd.concat(dfs, axis=1)
conn.close()
df = pd.concat([df, benchmark_df], axis=1, join_axes=[df.index])
df = df.fillna(method='pad')
df = df.rename(
columns={
'risk_0': '风险10',
'risk_1': '风险1',
'risk_2': '风险2',
'risk_3': '风险3',
'risk_4': '风险4',
'risk_5': '风险5',
'risk_6': '风险6',
'risk_7': '风险7',
'risk_8': '风险8',
'risk_9': '风险9',
'20201': '风险2比较基准',
'20202': '风险3比较基准',
'20203': '风险4比较基准',
'20204': '风险5比较基准',
'20205': '风险6比较基准',
'20206': '风险7比较基准',
'20207': '风险8比较基准',
'20208': '风险9比较基准',
'120000016': '风险10比较基准',
'120000010': '风险1比较基准'
})
cols = [
'风险1', '风险2', '风险3', '风险4', '风险5', '风险6', '风险7', '风险8', '风险9', '风险10',
'风险1比较基准', '风险2比较基准', '风险3比较基准', '风险4比较基准', '风险5比较基准', '风险6比较基准',
'风险7比较基准', '风险8比较基准', '风险9比较基准', '风险10比较基准'
]
df = df[cols]
result_df = pd.DataFrame(columns=df.columns)
last_day = df.index[-1]
print(last_day)
first_day = df.index[0]
print(first_day)
#result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 当日'] = df.pct_change().iloc[-1]
#result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去一周'] = df.loc[last_day] / df.loc[last_day - timedelta(weeks = 1)] - 1
#result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去一月'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 31)] - 1
#result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去三个月'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 91)] - 1
#result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去六个月'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 182)] - 1
#result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去一年'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 365)] - 1
# 非滚动战胜概率
length = len(df) // 7
# length = len(df)//31
# length = len(df)//91
# length = len(df)//182
# length = len(df)//365
k1 = 0
k2 = 0
k3 = 0
k4 = 0
k5 = 0
k6 = 0
k7 = 0
k8 = 0
k9 = 0
k10 = 0
days = df.index.values
for i in range(length):
num = -1 - i * 7
# num = -1 - i*31
# num = -1 - i*91
# num = -1 - i*182
# num = -1 - i*365
last_day = pd.to_datetime(str(days[num]))
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去一周'] = df.loc[last_day] / df.loc[
last_day - timedelta(weeks=1)] - 1
# result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去一月'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 31)] - 1
# result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去三个月'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 91)] - 1
# result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去六个月'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 182)] - 1
# result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去一年'] = df.loc[last_day] / df.loc[last_day - timedelta(days = 365)] - 1
if result_df['风险1'][0] > result_df['风险1比较基准'][0]:
k1 += 1
if result_df['风险2'][0] > result_df['风险2比较基准'][0]:
k2 += 1
if result_df['风险3'][0] > result_df['风险3比较基准'][0]:
k3 += 1
if result_df['风险4'][0] > result_df['风险4比较基准'][0]:
k4 += 1
if result_df['风险5'][0] > result_df['风险5比较基准'][0]:
k5 += 1
if result_df['风险6'][0] > result_df['风险6比较基准'][0]:
k6 += 1
if result_df['风险7'][0] > result_df['风险7比较基准'][0]:
k7 += 1
if result_df['风险8'][0] > result_df['风险8比较基准'][0]:
k8 += 1
if result_df['风险9'][0] > result_df['风险9比较基准'][0]:
k9 += 1
if result_df['风险10'][0] > result_df['风险10比较基准'][0]:
k10 += 1
# 滚动战胜概率
# length = len(df)-7
# length = len(df)//7
length = len(df) // 30
k1 = 0
k2 = 0
k3 = 0
k4 = 0
k5 = 0
k6 = 0
k7 = 0
k8 = 0
k9 = 0
k10 = 0
days = df.index.values
for i in range(length):
# num = -1 - i
# num = -1 - i*7
# num = -1 - i*30
last_day = pd.to_datetime(str(days[num]))
delta_day = last_day - timedelta(days=31)
choose_dates = df[df.index <= delta_day].index.values
if len(choose_dates) == 0:
break
delta_day = pd.to_datetime(str(choose_dates.max()))
# result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去一周'] = df.loc[last_day] / df.loc[delta_day] - 1
result_df.loc[df.index[-1].strftime('%Y-%m-%d') +
' 过去一月'] = df.loc[last_day] / df.loc[delta_day] - 1
# result_df.loc[df.index[-1].strftime('%Y-%m-%d') + ' 过去三个月'] = df.loc[last_day] / df.loc[delta_day] - 1
if result_df['风险1'][0] > result_df['风险1比较基准'][0]:
k1 += 1
if result_df['风险2'][0] > result_df['风险2比较基准'][0]:
k2 += 1
if result_df['风险3'][0] > result_df['风险3比较基准'][0]:
k3 += 1
if result_df['风险4'][0] > result_df['风险4比较基准'][0]:
k4 += 1
if result_df['风险5'][0] > result_df['风险5比较基准'][0]:
k5 += 1
if result_df['风险6'][0] > result_df['风险6比较基准'][0]:
k6 += 1
if result_df['风险7'][0] > result_df['风险7比较基准'][0]:
k7 += 1
if result_df['风险8'][0] > result_df['风险8比较基准'][0]:
k8 += 1
if result_df['风险9'][0] > result_df['风险9比较基准'][0]:
k9 += 1
if result_df['风险10'][0] > result_df['风险10比较基准'][0]:
k10 += 1
r1 = k1 / length
r2 = k2 / length
r3 = k3 / length
r4 = k4 / length
r5 = k5 / length
r6 = k6 / length
r7 = k7 / length
r8 = k8 / length
r9 = k9 / length
r10 = k10 / length
print([r1, r2, r3, r4, r5, r6, r7, r8, r9, r10])