def corr_update(corr, codes):
ra_index = base_ra_index.find(corr['ra_index_id'])
if ra_index is None:
click.echo(click.style(
"unknown index [%s]for calc corr!" % (corr['ra_index_id']), fg="yellow"))
return False
yesterday = (datetime.now() - timedelta(days=1));
enddate = yesterday.strftime("%Y-%m-%d")
#
# 加载指数数据
#
index_code = ra_index['ra_code']
if corr['ra_date_type'] == 1:
df_nav_index = DBData.db_index_value_daily('2015-10-08', enddate, codes=[index_code])
else:
df_nav_index = DBData.db_index_value('2015-10-08', enddate, codes=[index_code])
df_inc_index = df_nav_index.pct_change().fillna(0.0)
#
# 加载基金列表
#
df_fund = base_ra_fund.load(codes=codes)
data = []
with click.progressbar(length=len(df_fund.index), label=('update corr for corr %d' % (corr['globalid'])).ljust(30)) as bar:
for _,fund in df_fund.iterrows():
bar.update(1)
tmp = corr_update_fund(corr, fund, df_inc_index)
if tmp is not None:
data.append([
corr['globalid'],
fund['globalid'],
fund['ra_code'],
"%.4f" % (tmp),
])
df_new = pd.DataFrame(data, columns=['ra_corr_id', 'ra_fund_id', 'ra_fund_code', 'ra_corr'])
df_new = df_new.set_index(['ra_corr_id', 'ra_fund_id'])
db = database.connection('base')
# 加载旧数据
t2 = Table('ra_corr_fund', MetaData(bind=db), autoload=True)
columns2 = [
t2.c.ra_corr_id,
t2.c.ra_fund_id,
t2.c.ra_fund_code,
t2.c.ra_corr,
]
stmt_select = select(columns2, (t2.c.ra_corr_id == corr['globalid']))
if codes is not None:
stmt_select = stmt_select.where(t2.c.ra_fund_code.in_(codes))
df_old = pd.read_sql(stmt_select, db, index_col=['ra_corr_id', 'ra_fund_id'])
if not df_old.empty:
df_old['ra_corr'] = df_old['ra_corr'].map("{:.4f}".format)
# 更新数据库
database.batch(db, t2, df_new, df_old, timestamp=True)
def fund_update_factor_pool(pool, adjust_points, optlimit, optcalc):
''' re calc fund for single fund pool
'''
lookback = pool.ra_lookback
limit = optlimit
if optcalc:
#
# 计算每个调仓点的最新配置
#
# ffr = asset_fund_factor.load_fund_factor_return()
ffr = asset_stock_factor.load_stock_factor_return()
ffr = ffr.unstack().T
ffr.index = ffr.index.levels[1]
ffe = asset_fund_factor.load_fund_factor_exposure()
# ffe.to_csv('data/factor/fund_factor_exposure.csv', index_label = ['fund_id', 'ff_id', 'trade_date'])
# ffe = pd.read_csv('data/factor/fund_factor_exposure.csv', index_col = ['fund_id', 'ff_id', 'trade_date'], parse_dates = ['trade_date'])
ffe = ffe.reset_index()
data = []
pre_codes = None
with click.progressbar(length=len(adjust_points), label='calc pool %s' % (pool.id)) as bar:
for day in adjust_points:
bar.update(1)
# codes, codes_pool = pool_by_factor(pool, day, lookback, limit, ffr, ffe)
codes, codes_pool = pool_by_single_factor(pool, day, lookback, limit, ffr, ffe)
if pre_codes is None:
pre_codes = codes
else:
pre_codes_origin = pre_codes.copy()
pre_codes = np.intersect1d(pre_codes, codes_pool)
new_codes = np.setdiff1d(codes, pre_codes)
fund_lack_num = limit - len(pre_codes)
codes = np.append(pre_codes, new_codes[:fund_lack_num])
pre_codes = codes
print('turnover rate:', 1 - len(np.intersect1d(codes, pre_codes_origin))/float(limit))
print(day, len(codes), codes)
if codes is None or len(codes) == 0:
continue
ra_fund = base_ra_fund.load(codes = codes)
ra_fund = ra_fund.set_index(['ra_code'])
ra_pool = pool['id']
for code in ra_fund.index:
ra_fund_id = ra_fund.loc[code, 'globalid']
data.append([ra_pool, day, ra_fund_id, code])
fund_df = pd.DataFrame(data, columns = ['ra_pool', 'ra_date', 'ra_fund_id', 'ra_fund_code'])
fund_df = fund_df.set_index(['ra_pool', 'ra_date', 'ra_fund_id'])
df_new = fund_df
columns = [literal_column(c) for c in (df_new.index.names + list(df_new.columns))]
s = select(columns)
db = database.connection('asset')
ra_pool_fund_t = Table('ra_pool_fund', MetaData(bind=db), autoload=True)
s = s.where(ra_pool_fund_t.c.ra_pool.in_(df_new.index.get_level_values(0).tolist()))
df_old = pd.read_sql(s, db, index_col = df_new.index.names)
database.batch(db, ra_pool_fund_t, df_new, df_old)
def fund_update_corr_jensen(pool, adjust_points, optlimit, optcalc):
''' re calc fund for single fund pool
'''
lookback = pool.ra_lookback
limit = optlimit
if optcalc:
#
# 计算每个调仓点的最新配置
#
db = database.connection('asset')
ra_pool_sample_t = Table('ra_pool_sample',
MetaData(bind=db),
autoload=True)
ra_pool_fund_t = Table('ra_pool_fund',
MetaData(bind=db),
autoload=True)
data = []
with click.progressbar(length=len(adjust_points),
label='calc pool %s' % (pool.id)) as bar:
for day in adjust_points:
bar.update(1)
codes = pool_by_corr_jensen(pool, day, lookback, limit)
print day, codes
if codes is None or len(codes) == 0:
continue
ra_fund = base_ra_fund.load(codes=codes)
ra_fund = ra_fund.set_index(['ra_code'])
ra_pool = pool['id']
for code in ra_fund.index:
ra_fund_id = ra_fund.loc[code, 'globalid']
data.append([ra_pool, day, ra_fund_id, code])
fund_df = pd.DataFrame(
data, columns=['ra_pool', 'ra_date', 'ra_fund_id', 'ra_fund_code'])
fund_df = fund_df.set_index(['ra_pool', 'ra_date', 'ra_fund_id'])
df_new = fund_df
columns = [
literal_column(c)
for c in (df_new.index.names + list(df_new.columns))
]
s = select(columns)
s = s.where(
ra_pool_fund_t.c.ra_pool.in_(
df_new.index.get_level_values(0).tolist()))
df_old = pd.read_sql(s, db, index_col=df_new.index.names)
database.batch(db, ra_pool_fund_t, df_new, df_old)
def fund_update_monetary_pool(pool, adjust_points, optlimit, optcalc):
''' re calc fund for single fund pool
'''
lookback = pool.ra_lookback
limit = optlimit
if optcalc:
#
# 计算每个调仓点的最新配置
#
data = []
with click.progressbar(length=len(adjust_points), label='calc pool %s' % (pool.id)) as bar:
for day in adjust_points:
bar.update(1)
mnf = MonetaryFundFilter()
mnf.handle()
asset_ids = MonetaryAllocate.all_monetary_fund_globalid()
trade_dates = ATradeDate.week_trade_date()
dict_nav = {}
for asset_id in asset_ids:
dict_nav[asset_id] = Asset.load_nav_series(asset_id, reindex=trade_dates)
df_nav = pd.DataFrame(dict_nav).fillna(method='pad')
df_inc = df_nav.pct_change().fillna(0.0)
codes = pool_by_scale_return(pool, day, lookback, limit, mnf, df_inc)
print(day, len(codes), codes)
if codes is None or len(codes) == 0:
continue
ra_fund = base_ra_fund.load(globalids=codes)
ra_fund = ra_fund.set_index(['ra_code'])
ra_pool = pool['id']
for code in ra_fund.index:
ra_fund_id = ra_fund.loc[code, 'globalid']
data.append([ra_pool, day, ra_fund_id, code])
fund_df = pd.DataFrame(data, columns = ['ra_pool', 'ra_date', 'ra_fund_id', 'ra_fund_code'])
fund_df = fund_df.set_index(['ra_pool', 'ra_date', 'ra_fund_id'])
df_new = fund_df
columns = [literal_column(c) for c in (df_new.index.names + list(df_new.columns))]
s = select(columns)
db = database.connection('asset')
ra_pool_fund_t = Table('ra_pool_fund', MetaData(bind=db), autoload=True)
s = s.where(ra_pool_fund_t.c.ra_pool.in_(df_new.index.get_level_values(0).tolist()))
df_old = pd.read_sql(s, db, index_col = df_new.index.names)
database.batch(db, ra_pool_fund_t, df_new, df_old)