def __init__(self, ts_code: str):
session: Session = db_client.get_session()
self.arr = session.query(MqDcfConfig).filter(MqDcfConfig.ts_code == ts_code)\
.order_by(MqDcfConfig.update_date.asc()).all()
self.global_arr = session.query(MqDcfConfig).filter(MqDcfConfig.ts_code == 'all')\
.order_by(MqDcfConfig.update_date.asc()).all()
session.close()
def get_normal_price_in(self, ts_code_arr: list, date_arr: list):
"""
获取股票的每日价格信息，不复权
:param ts_code_arr: 股票编码列表，为空时全取
:param date_arr: 日期列表
"""
if len(date_arr) == 0:
return []
all_ret = []
code_filter = self.__get_code_filter(ts_code_arr)
page_to_get = []
s: Session = db_client.get_session()
for i in date_arr:
if i in self.__cache:
all_ret.extend(self.__cache[i])
else:
page_to_get.append(i)
if len(page_to_get) == self.__page_size:
mq_list: list = s.query(MqDailyPrice) \
.filter(code_filter, MqDailyPrice.trade_date.in_(page_to_get)) \
.all()
all_ret.extend(mq_list)
page_to_get = []
if len(page_to_get) > 0:
mq_list: list = s.query(MqDailyPrice) \
.filter(code_filter, MqDailyPrice.trade_date.in_(page_to_get)) \
.all()
all_ret.extend(mq_list)
s.close()
return all_ret
def refresh_by_code(to_date: str = date_utils.get_current_dt()):
session: Session = db_client.get_session()
basic_list = session.query(TsBasic).all()
session.close()
for basic in basic_list: # type: TsBasic
ts_code = basic.ts_code
session: Session = db_client.get_session()
session.query(TsDailyTradeInfo).filter(
TsDailyTradeInfo.ts_code == ts_code).delete()
session.close()
from_date = fetch_from_date(ts_code)
fetch.common_fetch(TsDailyTradeInfo,
'fetch_daily_bar',
ts_code=ts_code,
from_date=from_date,
to_date=to_date)
def get_daily_metrics_with_period(self,
from_date: str,
to_date: str,
metrics: list,
ts_codes: list = None):
"""
获取日指标
:param from_date: 开始日期
:param to_date: 结束日期
:param metrics: 指标列表 见 mq_daily_metric_enum
:param ts_codes: 股票编码列表
:return:
"""
if not date_utils.is_valid_dt(from_date):
raise Exception('Invalid date %s' % from_date)
if not date_utils.is_valid_dt(to_date):
raise Exception('Invalid date %s' % to_date)
session: Session = db_client.get_session()
ml: list = session.query(MqDailyMetric) \
.filter(MqDailyMetric.ts_code.in_(ts_codes) if ts_codes is not None else 1 == 1,
MqDailyMetric.update_date >= from_date, MqDailyMetric.update_date <= to_date,
MqDailyMetric.name.in_(metrics)) \
.all()
session.close()
return ml
def run(dt: str, min_val_score=50):
"""
参考 cal_val
筛选最后 val_score > min_val_score 的
:return:
"""
s: Session = db_client.get_session()
s.query(MqSharePool).filter(MqSharePool.strategy == __strategy,
MqSharePool.dt == dt).delete()
daily_list: list = s.query(MqDailyMetric) \
.filter(MqDailyMetric.update_date == dt, MqDailyMetric.name == mq_daily_metric_enum.val_score.name,
MqDailyMetric.value > min_val_score) \
.all()
if len(daily_list) == 0:
log.info('No dividend pool in %s' % dt)
return
to_insert = []
for mq in daily_list: # type: MqDailyMetric
to_insert.append(
MqSharePool(dt=dt, strategy=__strategy, ts_code=mq.ts_code))
db_client.batch_insert(to_insert)
log.info('Dividend strategy done. Dt: %s. Total: %d' %
(dt, len(to_insert)))
def default_previous_trade_date(self, dt: str):
session: Session = db_client.get_session()
cal_arr = session.query(TsTradeCal).filter(TsTradeCal.is_open == '1', TsTradeCal.cal_date < dt) \
.order_by(TsTradeCal.cal_date.desc()).limit(1).all()
session.close()
return cal_arr[0].cal_date if len(
cal_arr) > 0 else date_utils.format_delta(dt, -1)
def run():
args = env_utils.get_args()
ts_code = args.code
to_date = args.date
if to_date is None:
to_date = date_utils.get_current_dt()
fetch_broker_recommend.fetch(to_date)
fetch_trade_cal.fetch(to_date)
fetch_dividend.update_dividend_to(to_date)
init_ts_basic.init()
basic_list = []
session: Session = db_client.get_session()
if ts_code is not None and ts_code != '':
basic_list = session.query(TsBasic).filter(
TsBasic.ts_code == ts_code).all()
else:
basic_list = session.query(TsBasic).all()
session.close()
for basic in basic_list:
if env_utils.parallel():
threadpool.submit(fetch_by_code,
ts_code=basic.ts_code,
to_date=to_date)
else:
fetch_by_code(ts_code=basic.ts_code, to_date=to_date)
threadpool.join()
strategy.generate_strategy_pool(to_date)
def fix_dividend(ts_code: str = None,
from_date: str = None,
to_date: str = None):
"""
清理一些脏数据
:param ts_code:
:param from_date:
:param to_date:
:return:
"""
for i in to_fix_list:
if ts_code is not None and ts_code != i['ts_code']:
continue
if from_date is not None and to_date is not None \
and from_date <= i['imp_ann_date'] <= to_date:
session: Session = db_client.get_session()
d: TsDividend = session.query(TsDividend) \
.filter(TsDividend.ts_code == i['ts_code'], TsDividend.end_date == i['end_date'],
TsDividend.imp_ann_date == i['imp_ann_date'], TsDividend.div_proc == i['div_proc']).one()
for key in i:
setattr(d, key, i[key])
d.is_fix = 1
session.flush([d])
session.close()
def init():
session: Session = db_client.get_session()
session.query(TsBasic).delete()
stock_data = ts_client.fetch_all_stock()
if not stock_data.empty:
db_client.store_dataframe(stock_data, TsBasic.__tablename__)
def fetch_from_month():
session: Session = db_client.get_session()
result = session.query(func.max(TsBrokerRecommend.month)).all()
session.close()
from_month = fetch_data_start_date[0:6]
if len(result) > 0 and not result[0][0] is None:
from_month = date_utils.month_delta(result[0][0], 1)
return from_month
def calculate_by_code(ts_code: str, to_date: str = date_utils.get_current_dt()):
session: Session = db_client.get_session()
basic: TsBasic = session.query(TsBasic).filter(TsBasic.ts_code == ts_code).one()
session.close()
if basic is None:
log.error("Cant find ts_basic of %s" % ts_code)
return
calculate_and_insert(ts_code, basic.name, to_date)
def cal_by_ths_index_code(index_code,
index_name,
to_date: str = date_utils.get_current_dt()):
from_date = get_from_date(index_code)
history_avl = get_history_percent(index_code, from_date)
s: Session = db_client.get_session()
member_list = s.query(ThsMember).filter(
ThsMember.ts_code == index_code).all()
s.close()
share_code_list = [m.code for m in member_list]
result_list = []
while from_date <= to_date:
log.info('Calculating %s %s %s' %
(MqIndexTradeAmount.__tablename__, index_code, from_date))
s: Session = db_client.get_session()
trade_list = s.query(TsDailyTradeInfo) \
.filter(TsDailyTradeInfo.trade_date == from_date) \
.all()
s.close()
if len(trade_list) > 0:
total_amount = Decimal(0)
target_amount = Decimal(0)
for i in trade_list: # type: TsDailyTradeInfo
total_amount += i.amount
if i.ts_code in share_code_list:
target_amount += i.amount
val = float(decimal_utils.div(target_amount, total_amount))
history_percent = get_history_high_percent(history_avl, val)
history_avl.add(val)
result_list.append(
MqIndexTradeAmount(index_code=index_code,
index_name=index_name,
trade_date=from_date,
amount=target_amount,
percent=val,
history_high_ratio=history_percent))
from_date = date_utils.format_delta(from_date, 1)
if len(result_list) >= 1000:
db_client.batch_insert(result_list)
result_list = []
db_client.batch_insert(result_list)
def refresh_cache(self):
self.list_date = {}
session: Session = db_client.get_session()
basic_list = session.query(TsBasic).all()
session.close()
for b in basic_list: # type: TsBasic
self.list_date[b.ts_code] = b.list_date
def fetch_from_period():
s: Session = db_client.get_session()
result = s.query(func.max(TsCashFlow.end_date)) \
.all()
s.close()
from_date = fetch_data_start_period
if len(result) > 0 and not result[0][0] is None:
from_date = date_utils.period_delta(result[0][0], -4)
return from_date
def fetch_from_date(exchange: str):
session: Session = db_client.get_session()
result = session.query(func.max(
TsTradeCal.cal_date)).filter(TsTradeCal.exchange == exchange).all()
session.close()
from_date = fetch_data_start_date
if len(result) > 0 and not result[0][0] is None:
from_date = date_utils.format_delta(result[0][0], day_num=1)
return from_date
def fetch_from_date(ts_code: str = None):
s: Session = db_client.get_session()
result = s.query(func.max(TsCashFlow.mq_ann_date)) \
.filter(TsCashFlow.ts_code == ts_code if ts_code is not None else True) \
.all()
s.close()
from_date = fetch_data_start_date
if len(result) > 0 and not result[0][0] is None:
from_date = date_utils.format_delta(result[0][0], day_num=-7)
return from_date
def init_dividend():
"""
按股票初始化分红数据
:return:
"""
session: Session = db_client.get_session()
basic_list = session.query(TsBasic).all()
session.close()
for basic in basic_list: # type: TsBasic
session: Session = db_client.get_session()
td: list = session.query(TsDividend).filter(
TsDividend.ts_code == basic.ts_code).limit(1).all()
session.close()
if len(td) == 0:
common_fetch_dividend(basic.ts_code)
db_client.execute_sql(
clear_after_fetch.clear_duplicate_dividend(basic.ts_code))
db_client.execute_sql(
clear_after_fetch.clear_duplicate_dividend_2(basic.ts_code))
log.info('clear duplicate %s' % basic.ts_code)
def init_quarter_store(ts_code,
from_period=mq_calculate_start_date) -> MqQuarterStore:
store = MqQuarterStore()
session: Session = db_client.get_session()
arr = session.query(MqQuarterMetric).filter(
MqQuarterMetric.ts_code == ts_code,
MqQuarterMetric.period >= from_period).all()
for i in arr:
store.add(i)
session.close()
return store
def ready_data(ts_code: str, from_date: str):
session: Session = db_client.get_session()
income_arr = session.query(TsIncome) \
.filter(
TsIncome.ts_code == ts_code, TsIncome.mq_ann_date >= from_date, TsIncome.report_type == 1) \
.order_by(TsIncome.mq_ann_date.asc(), TsIncome.end_date.asc()).all()
adjust_income_arr = session.query(TsIncome) \
.filter(
TsIncome.ts_code == ts_code, TsIncome.mq_ann_date >= from_date, TsIncome.report_type == 4) \
.order_by(TsIncome.mq_ann_date.asc(), TsIncome.end_date.asc()).all()
balance_arr = session.query(TsBalanceSheet) \
.filter(TsBalanceSheet.ts_code == ts_code, TsBalanceSheet.mq_ann_date >= from_date,
TsBalanceSheet.report_type == 1) \
.order_by(TsBalanceSheet.mq_ann_date.asc(), TsBalanceSheet.end_date.asc()).all()
adjust_balance_arr = session.query(TsBalanceSheet) \
.filter(TsBalanceSheet.ts_code == ts_code, TsBalanceSheet.mq_ann_date >= from_date,
TsBalanceSheet.report_type == 4) \
.order_by(TsBalanceSheet.mq_ann_date.asc(), TsBalanceSheet.end_date.asc()).all()
cash_arr = session.query(TsCashFlow) \
.filter(TsCashFlow.ts_code == ts_code, TsCashFlow.mq_ann_date >= from_date,
TsCashFlow.report_type == 1) \
.order_by(TsCashFlow.mq_ann_date.asc(), TsCashFlow.end_date.asc()).all()
adjust_cash_arr = session.query(TsCashFlow) \
.filter(TsCashFlow.ts_code == ts_code, TsCashFlow.mq_ann_date >= from_date,
TsCashFlow.report_type == 4) \
.order_by(TsCashFlow.mq_ann_date.asc(), TsCashFlow.end_date.asc()).all()
fina_arr = session.query(TsFinaIndicator) \
.filter(TsFinaIndicator.ts_code == ts_code, TsFinaIndicator.ann_date >= from_date,
TsFinaIndicator.ann_date != None) \
.order_by(TsFinaIndicator.ann_date.asc(), TsFinaIndicator.end_date.asc()).all()
# 只看有分红的就够了
dividend_arr = session.query(TsDividend) \
.filter(TsDividend.ts_code == ts_code, TsDividend.imp_ann_date >= from_date, TsDividend.div_proc == '实施',
or_(TsDividend.cash_div > 0, TsDividend.cash_div_tax > 0)) \
.order_by(TsDividend.imp_ann_date.asc()).all()
forecast_arr = session.query(TsForecast) \
.filter(TsForecast.ts_code == ts_code, TsForecast.ann_date >= from_date) \
.order_by(TsForecast.ann_date.asc(), TsForecast.end_date.asc()).all()
express_arr = session.query(TsExpress) \
.filter(TsExpress.ts_code == ts_code, TsExpress.ann_date >= from_date) \
.order_by(TsExpress.ann_date.asc(), TsExpress.end_date.asc()).all()
session.close()
return income_arr, adjust_income_arr, balance_arr, adjust_balance_arr, cash_arr, adjust_cash_arr, \
fina_arr, dividend_arr, forecast_arr, express_arr
def fetch_from_date(date_column: dict(type=str,
help='对应发布日期的字段名 用于获取该类型数据在DB中最新日期'),
code_column: dict(type=str, help='对应股票编码的字段名'),
ts_code: dict(type=str, help='股票编码')):
session: Session = db_client.get_session()
result = session.query(
func.max(date_column)).filter(code_column == ts_code).all()
from_date = fetch_data_start_date
if len(result) > 0 and not result[0][0] is None:
from_date = date_utils.format_delta(result[0][0], day_num=1)
session.close()
return from_date
def delete_old(dt: str):
s: Session = db_client.get_session()
s.query(MqStrategyTrade).filter(
MqStrategyTrade.strategy == __strategy,
MqStrategyTrade.strategy_version == __version,
MqStrategyTrade.buy_date == dt).delete()
s.query(MqStrategyTrade).filter(
MqStrategyTrade.strategy == __strategy,
MqStrategyTrade.strategy_version == __version,
MqStrategyTrade.sell_date == dt).delete()
s.query(MqSharePool).filter(MqSharePool.dt == dt).delete()
s.close()
def init_daily_store_by_date(ts_code,
from_date=mq_calculate_start_date
) -> MqDailyStore:
store = MqDailyStore()
session: Session = db_client.get_session()
arr = session.query(MqDailyMetric).filter(
MqDailyMetric.ts_code == ts_code,
MqDailyMetric.update_date >= from_date).all()
session.close()
for i in arr:
store.add(i)
return store
def remove_from_date(ts_code: str, from_date: str):
"""
删除指定日期后的数据
:param ts_code: 股票编码
:param from_date: 指定日期
:return:
"""
session: Session = db_client.get_session()
session.query(MqDailyPrice).filter(
MqDailyPrice.ts_code == ts_code,
MqDailyPrice.trade_date >= from_date).delete()
session.close()
def recalculate_by_code(ts_code: str,
to_date: str = date_utils.get_current_dt()):
"""
删除相关数据然后重算
:param ts_code: 股票编码
:param to_date: 重刷到哪天
:return:
"""
session: Session = db_client.get_session()
session.query(MqDailyPrice).filter(
MqDailyPrice.ts_code == ts_code).delete()
session.close()
calculate_by_code(ts_code, to_date)
def refresh_cache(self, from_date, to_date):
cal_date_set = set()
session: Session = db_client.get_session()
cal_arr = session.query(TsTradeCal) \
.filter(TsTradeCal.is_open == '1',
TsTradeCal.cal_date >= from_date,
TsTradeCal.cal_date <= to_date) \
.order_by(TsTradeCal.cal_date.asc()).all()
session.close()
for cal in cal_arr: # type: TsTradeCal
cal_date_set.add(cal.cal_date)
self.cal_date_list = list(cal_date_set)
self.cal_date_list.sort()
def get_from_date(index_code):
"""
获取指标已有的最大日期，计算从哪开始补数
:param index_code: 指标代码
:return:
"""
s: Session = db_client.get_session()
result = s.query(func.max(MqIndexTradeAmount.trade_date)).filter(
MqIndexTradeAmount.index_code == index_code).all()
s.close()
from_date = mq_calculate_start_date
if len(result) > 0 and result[0][0] is not None:
from_date = date_utils.format_delta(result[0][0], day_num=1)
return from_date
def update_dividend_to(to_date: str):
session: Session = db_client.get_session()
td: list = session.query(TsDividend).order_by(
TsDividend.imp_ann_date.desc()).limit(1).all()
session.close()
now: str = fetch_data_start_date if len(
td) == 0 else date_utils.format_delta(td[0].imp_ann_date, 1)
dt_code_map = {}
while now <= to_date:
current = common_fetch_dividend(ts_code=None, dt=now)
fetch.merge_from_map(dt_code_map, current)
now = date_utils.format_delta(now, 1)
return dt_code_map
def get_dividend_in_record_day(self, rd: str):
"""
获取该日的分红登记股权
:param rd: 股权登记日
"""
d_list: list = []
if rd in self.__cache:
d_list = self.__cache[rd]
else:
session: Session = db_client.get_session()
d_list: list = session.query(TsDividend).filter(
TsDividend.record_date == rd,
TsDividend.div_proc == '实施').all()
session.close()
return d_list
def ready_manual_data(ts_code: str, from_date: str):
session: Session = db_client.get_session()
manual_forecast = session.query(MqManualMetric) \
.filter(
MqManualMetric.ts_code == ts_code, MqManualMetric.update_date >= from_date,
MqManualMetric.report_type == mq_report_type.man_forecast) \
.order_by(MqManualMetric.update_date.asc(), MqManualMetric.period.asc()).all()
report_fix = session.query(MqManualMetric) \
.filter(
MqManualMetric.ts_code == ts_code, MqManualMetric.update_date >= from_date,
MqManualMetric.report_type == mq_report_type.report_fix) \
.order_by(MqManualMetric.update_date.asc(), MqManualMetric.period.asc()).all()
return manual_forecast, report_fix
def get_history_percent(index_code, max_date):
"""
获取一个指数的历史占比，返回一个avl，用于后续二分查找确定百分位
:param index_code: 指数代码
:param max_date: 最大日期
:return:
"""
s: Session = db_client.get_session()
arr = s.query(MqIndexTradeAmount) \
.filter(MqIndexTradeAmount.index_code == index_code,
MqIndexTradeAmount.trade_date < max_date).all()
s.close()
avl = AvlTree()
for i in arr: # type: MqIndexTradeAmount
avl.add(i.percent)
return avl
