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