def gen_res_common(table_name, sql_pro, cur_year): data = pd.read_sql(sql=sql_pro, con=common.engine(), params=[]) data = data.drop_duplicates(subset="ts_code", keep="last") data.insert(0, "year", [cur_year] * len(data)) logger.debug(data) data.head(n=1) data = data.drop_duplicates(subset=["ts_code", 'year'], keep="last") old_len = 0 try: sql_date = """ SELECT `ts_code` FROM %s WHERE `year`='%s' """ % (table_name, cur_year) exist_dates = pd.read_sql(sql=sql_date, con=common.engine(), params=[]) old_len = len(exist_dates) clear_sql = """ DELETE from %s WHERE `year`='%s' """ % (table_name, cur_year) common.insert(clear_sql) except sqlalchemy.exc.ProgrammingError: pass if len(data) > 0: try: common.insert_db(data, table_name, False, "`ts_code`,`year`") except sqlalchemy.exc.IntegrityError: pass if old_len >= len(data): logger.debug("guess %s: no new stock is found", table_name)
def stat_all_lite(tmp_datetime): datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`, `settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc` ,`kdjj`,`rsi_6`,`cci` FROM stock_data.guess_indicators_daily WHERE `date` = %s and kdjj > 100 and rsi_6 > 80 and cci > 100 """ try: # 删除老数据。 del_sql = " DELETE FROM `stock_data`.`guess_indicators_lite_daily` WHERE `date`= '%s' " % datetime_int common.insert(del_sql) except Exception as e: print("error :", e) data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int]) data = data.drop_duplicates(subset="code", keep="last") print("######## len data ########:", len(data)) try: common.insert_db(data, "guess_indicators_lite_daily", False, "`date`,`code`") except Exception as e: print("error :", e)
def stat_all_lite(tmp_datetime): datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) # 强弱指标保持高于50表示为强势市场,反之低于50表示为弱势市场。 # K值在80以上,D值在70以上,J值大于90时为超买。 # 当CCI>﹢100 时,表明股价已经进入非常态区间——超买区间,股价的异动现象应多加关注。 sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`, `settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc` ,`kdjj`,`rsi_6`,`cci` FROM stock_data.guess_indicators_daily WHERE `date` = %s and kdjk >= 80 and kdjd >= 70 and kdjj >= 90 and rsi_6 >= 50 and cci >= 100 """ # and kdjj > 100 and rsi_6 > 80 and cci > 100 # 调整参数,提前获得股票增长。 try: # 删除老数据。 del_sql = " DELETE FROM `stock_data`.`guess_indicators_lite_daily` WHERE `date`= '%s' " % datetime_int common.insert(del_sql) except Exception as e: print("error :", e) data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int]) data = data.drop_duplicates(subset="code", keep="last") print("######## len data ########:", len(data)) try: common.insert_db(data, "guess_indicators_lite_daily", False, "`date`,`code`") except Exception as e: print("error :", e)
def stat_all_lite_sell(tmp_datetime): datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) # 超卖区:K值在20以下,D值在30以下为超卖区。一般情况下,股价有可能上涨,反弹的可能性增大。局内人不应轻易抛出股票,局外人可寻机入场。 # J大于100时为超买,小于10时为超卖。 # 当六日强弱指标下降至20时,表示股市有超卖现象 # 当CCI<﹣100时,表明股价已经进入另一个非常态区间——超卖区间,投资者可以逢低吸纳股票。 sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`, `settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc` ,`kdjj`,`rsi_6`,`cci` FROM stock_data.guess_indicators_daily WHERE `date` = %s and kdjk <= 20 and kdjd <= 30 and kdjj <= 10 and rsi_6 <= 20 and cci <= -100 """ try: # 删除老数据。 del_sql = " DELETE FROM `stock_data`.`guess_indicators_lite_sell_daily` WHERE `date`= '%s' " % datetime_int common.insert(del_sql) except Exception as e: print("error :", e) data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int]) data = data.drop_duplicates(subset="code", keep="last") print("######## len data ########:", len(data)) try: common.insert_db(data, "guess_indicators_lite_sell_daily", False, "`date`,`code`") except Exception as e: print("error :", e)
def daily_common(cur_day, res_table, standard, pe, div_standard, pb, sort_by_standard=True): """ 不在此列表里的建议卖出 """ if sort_by_standard: sort_str = "(pb * pe) ASC, " else: sort_str = "" sql_pro = """ select *, (pb * pe) as standard from (select tb_res.ts_code, name, area, industry, market, list_date, GREATEST(ts_pro_daily.pb, total_mv * 10000 / div_ledger_asset) as pb, (total_mv * 10000 / average_income) as pe, (average_cash_div_tax / (total_mv / total_share)) as div_ratio from {res_table} tb_res INNER JOIN ts_pro_daily on tb_res.ts_code = ts_pro_daily.ts_code AND trade_date='{cur_day}') ts_res WHERE (((pb * pe) < {standard} AND div_ratio > {div_standard}) OR div_ratio > 0.05) AND pe < {pe} and pb < {pb} ORDER BY {sort_custom}div_ratio DESC, pb ASC, pe ASC """.format(res_table=res_table, cur_day=cur_day, standard=standard, pe=pe, div_standard=div_standard, pb=pb, sort_custom=sort_str) data = pd.read_sql(sql=sql_pro, con=common.engine(), params=[]) data = data.drop_duplicates(subset="ts_code", keep="last") return data
def stat_fina(tmp_datetime, method, max_year=11): sql_1 = """ SELECT `ts_code` FROM ts_pro_basics """ data_basic = pd.read_sql(sql=sql_1, con=common.engine(), params=[]) data_basic = data_basic.drop_duplicates(subset="ts_code", keep="last") logger.debug("######## len data_basic ########: %s", len(data_basic)) pro = ts.pro_api() cur_year = int((tmp_datetime).strftime("%Y")) start_year = cur_year - max_year start_date = "%s1231" % start_year table_name = "ts_pro_%s" % method sqlCol = common.get_columns(table_name) fields = sqlCol.columns for i, ts_code in enumerate(data_basic.ts_code): try: data = getattr(pro, method)(ts_code=ts_code, start_date=start_date, fields=','.join(fields)) except IOError: data = None logger.info("\ndone %s", ts_code) result = InsertOrUpdateData(data, ts_code, table_name, i, len(data_basic), sqlCol) # Exception: 抱歉,您每分钟最多访问该接口80次,权限的具体详情访问:https://tushare.pro/document/1?doc_id=108。 time.sleep(1)
def stat_index_all(tmp_datetime): datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) # 查询今日满足股票数据。剔除数据:创业板股票数据,中小板股票数据,所有st股票 # #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%' sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`, `settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc` FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20 and `code` not like %s and `code` not like %s and `name` not like %s """ print(sql_1) data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int, '002%', '300%', '%st%']) data = data.drop_duplicates(subset="code", keep="last") print("########data[trade]########:") # print(data["trade"]) # 使用 trade 填充数据 stock_guess = pd.DataFrame( { "date": data["date"], "code": data["code"], "5d": data["trade"], "10d": data["trade"], "20d": data["trade"], "60d": data["trade"], "5-10d": data["trade"], "5-20d": data["trade"], "return": data["trade"], "mov_vol": data["trade"] }, index=data.index.values) stock_guess = stock_guess.apply(apply_guess, axis=1) # , axis=1) # print(stock_guess.head()) # stock_guess.astype('float32', copy=False) stock_guess.drop('date', axis=1, inplace=True) # 删除日期字段,然后和原始数据合并。 # print(stock_guess["5d"]) data_new = pd.merge(data, stock_guess, on=['code'], how='left') print("#############") # 使用pandas 函数 : https://pandas.pydata.org/pandas-docs/stable/api.html#id4 data_new["return"] = data_new["return"].mul(100) # 扩大100 倍方便观察 data_new["mov_vol"] = data_new["mov_vol"].mul(100) data_new = data_new.round(2) # 数据保留2位小数 # 删除老数据。 del_sql = " DELETE FROM `stock_data`.`guess_return_daily` WHERE `date`= '%s' " % datetime_int common.insert(del_sql) # data_new["down_rate"] = (data_new["trade"] - data_new["wave_mean"]) / data_new["wave_base"] common.insert_db(data_new, "guess_return_daily", False, "`date`,`code`")
def InsertOrUpdateData(data, ts_code, table_name, i, total_num, sqlCol): if not data is None and len(data) > 0: # logger.info("Table %s: insert %s, %s(%s) / %s", table_name, ts_code, i, len(exist_data) + len(new_code), len(basic_data)) data.head(n=1) data = data.drop_duplicates(subset=["ts_code", 'end_date'], keep="last") try: sql_date = """ SELECT `end_date` FROM %s WHERE `ts_code`='%s' """ % (table_name, ts_code) exist_dates = pd.read_sql(sql=sql_date, con=common.engine(), params=[]) date_set = set(exist_dates.end_date) data_to_update = data[data['end_date'].isin(date_set)] data = data[-data['end_date'].isin(date_set)] except sqlalchemy.exc.ProgrammingError: pass if len(data) > 0: try: common.insert_db(data, table_name, False, "`ts_code`,`end_date`") except sqlalchemy.exc.IntegrityError: pass if len(data_to_update) > 0: for i, row in data_to_update.iterrows(): common.update_sql(table_name, row, sqlCol) logger.info("Table %s: insert %s, %s / %s", table_name, ts_code, i, total_num) return ts_code else: logger.debug("no data . table=%s ts_code=%s", table_name, ts_code)
def stat_current_fina(tmp_datetime, method): sql_1 = """ SELECT `ts_code` FROM ts_pro_basics """ basic_data = pd.read_sql(sql=sql_1, con=common.engine(), params=[]) basic_data = basic_data.drop_duplicates(subset="ts_code", keep="last") pro = ts.pro_api() # 每年都取上一年的财报 cur_year = int((tmp_datetime).strftime("%Y")) - 1 cur_date = "%s1231" % cur_year table_name = "ts_pro_%s" % method try: sql_exist = """ SELECT `ts_code` FROM %s WHERE `end_date`='%s' """ % (table_name, cur_date) exist_data = pd.read_sql(sql=sql_exist, con=common.engine(), params=[]) logger.info("[%s][mysql][%s]Begin: 已获取%s财报的公司共有%s家", tmp_datetime, table_name, cur_date, len(exist_data.ts_code)) exist_set = set(exist_data.ts_code) except sqlalchemy.exc.ProgrammingError: exist_set = set() new_code = [] sqlCol = common.get_columns(table_name) for i, ts_code in enumerate(basic_data.ts_code): if ts_code in exist_set: continue try: data = getattr(pro, method)(ts_code=ts_code, start_date=cur_date, fields=','.join(sqlCol.columns)) except IOError: data = None result = InsertOrUpdateData(data, ts_code, table_name, i, len(basic_data), sqlCol) if result: new_code.append(ts_code) # Exception: 抱歉,您每分钟最多访问该接口80次,权限的具体详情访问:https://tushare.pro/document/1?doc_id=108。 time.sleep(1) logger.info("[%s][mysql][%s]End: 新发布%s财报的公司共有%s家", tmp_datetime, table_name, cur_date, len(new_code))
def stat_all_batch(tmp_datetime): datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) try: # 删除老数据。 del_sql = " DELETE FROM `stock_data`.`guess_indicators_daily` WHERE `date`= %s " % datetime_int common.insert(del_sql) except Exception as e: print("error :", e) sql_count = """ SELECT count(1) FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20 and `code` not like %s and `name` not like %s """ # 修改逻辑,增加中小板块计算。 中小板:002,创业板:300 。and `code` not like %s and `code` not like %s and `name` not like %s # count = common.select_count(sql_count, params=[datetime_int, '002%', '300%', '%st%']) count = common.select_count(sql_count, params=[datetime_int, '300%', '%st%']) print("count :", count) batch_size = 100 end = int(math.ceil(float(count) / batch_size) * batch_size) print(end) for i in range(0, end, batch_size): print("loop :", i) # 查询今日满足股票数据。剔除数据:创业板股票数据,中小板股票数据,所有st股票 # #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%' sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`, `settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc` FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20 and `code` not like %s and `name` not like %s limit %s , %s """ print(sql_1) # data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int, '002%', '300%', '%st%', i, batch_size]) data = pd.read_sql( sql=sql_1, con=common.engine(), params=[datetime_int, '300%', '%st%', i, batch_size]) data = data.drop_duplicates(subset="code", keep="last") print("########data[trade]########:", len(data)) stat_index_all(data, i)
def stat_pro_basics(tmp_datetime): """ Pandas:让你像写SQL一样做数据分析(一): https://www.cnblogs.com/en-heng/p/5630849.html """ pro = ts.pro_api() data = pro.stock_basic(list_status='L') try: sql_1 = """ SELECT `ts_code` FROM ts_pro_basics """ exist_data = pd.read_sql(sql=sql_1, con=common.engine(), params=[]) exist_data = exist_data.drop_duplicates(subset="ts_code", keep="last") exist_set = set(exist_data.ts_code) except sqlalchemy.exc.ProgrammingError: exist_set = set() if not data is None and len(data) > 0: data = data.drop_duplicates(subset="ts_code", keep="last") data.head(n=1) data = data[-data['ts_code'].isin(exist_set)] if len(data) > 0: common.insert_db(data, "ts_pro_basics", False, "`ts_code`") else: logger.debug("no data . stock_basics")
def stat_index_calculate(tmp_datetime): # 要操作的数据库表名称。 table_name = "guess_indicators_lite_sell_daily" datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) sql_1 = """ SELECT `buy_date`, `code`, `name`, `changepercent`, `trade`, `turnoverratio`, `pb`, `kdjj`, `rsi_6`, `cci`, `wave_base`, `wave_crest`, `wave_mean`, `up_rate` FROM guess_indicators_lite_buy_daily where `buy_date` <= """ + datetime_int print(sql_1) data = pd.read_sql(sql=sql_1, con=common.engine(), params=[]) data = data.drop_duplicates(subset="code", keep="last") print(data["trade"]) data["trade_float32"] = data["trade"].astype('float32', copy=False) print(len(data)) data["date"] = datetime_int stock_merge = pd.DataFrame( { "date": data["date"], "code": data["code"], "today_trade": data["trade"] }, index=data.index.values) print(stock_merge.head(1)) stock_merge = stock_merge.apply(apply_merge, axis=1) # , axis=1) del stock_merge["date"] # 合并前删除 date 字段。 # 合并数据 data_new = pd.merge(data, stock_merge, on=['code'], how='left') data_new["income"] = (data_new["today_trade"] - data_new["trade_float32"]) * 100 data_new["income"] = data_new["income"].round(4) # 保留4位小数。 # 增加售出列。看看是否需要卖出。 stock_sell_merge = pd.DataFrame( { "date": data["date"], "code": data["code"], "sell": 0, "buy": 0, "sell_kdjj": 0, "sell_rsi_6": 0, "sell_cci": 0 }, index=data.index.values) print(stock_sell_merge.head(1)) merge_sell_data = stock_sell_merge.apply(apply_merge_sell, axis=1) # , axis=1) # 重命名 del merge_sell_data["date"] # 合并前删除 date 字段。 # 合并数据 data_new = pd.merge(data_new, merge_sell_data, on=['code'], how='left') # 删除老数据。 try: del_sql = " DELETE FROM `stock_data`.`" + table_name + "` WHERE `date`= '%s' " % datetime_int common.insert(del_sql) print("insert_db") except Exception as e: print("error :", e) del data_new["trade_float32"] try: common.insert_db(data_new, table_name, False, "`date`,`code`") print("insert_db") except Exception as e: print("error :", e) # 重命名 del data_new["name"] print(data_new)
def stat_all_batch(tmp_datetime): datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) try: # 删除老数据。 del_sql = " DELETE FROM `stock_data`.`%s` WHERE `date`= %s " % ( table_name, datetime_int) print("del_sql:", del_sql) common.insert(del_sql) except Exception as e: print("error :", e) sql_count = """ SELECT count(1) FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20 and `code` not like %s and `name` not like %s """ # 修改逻辑,增加中小板块计算。 中小板:002,创业板:300 。and `code` not like %s and `code` not like %s and `name` not like %s # count = common.select_count(sql_count, params=[datetime_int, '002%', '300%', '%st%']) count = common.select_count(sql_count, params=[datetime_int, '300%', '%st%']) print("count :", count) batch_size = 100 end = int(math.ceil(float(count) / batch_size) * batch_size) print(end) # for i in range(0, end, batch_size): for i in range(0, end, batch_size): print("loop :", i) # 查询今日满足股票数据。剔除数据:创业板股票数据,中小板股票数据,所有st股票 # #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%' sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`, `settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc` FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20 and `code` not like %s and `name` not like %s limit %s , %s """ print(sql_1) # data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int, '002%', '300%', '%st%', i, batch_size]) data = pd.read_sql( sql=sql_1, con=common.engine(), params=[datetime_int, '300%', '%st%', i, batch_size]) data = data.drop_duplicates(subset="code", keep="last") print("########data[trade]########:", len(data)) # 使用 trade 填充数据 stock_sklearn = pd.DataFrame( { "date": data["date"], "code": data["code"], "next_close": data["trade"], "sklearn_score": data["trade"] }, index=data.index.values) print(stock_sklearn.head()) stock_sklearn_apply = stock_sklearn.apply(apply_sklearn, axis=1) # , axis=1) # 重命名 del stock_sklearn_apply["date"] # 合并前删除 date 字段。 # 合并数据 data_new = pd.merge(data, stock_sklearn_apply, on=['code'], how='left') # for index, row in data.iterrows(): # next_stock, score = stat_index_all(row, i) # print(next_stock, score) data_new["next_close"] = data_new["next_close"].round(2) # 数据保留4位小数 data_new["sklearn_score"] = data_new["sklearn_score"].round( 2) # 数据保留2位小数 data_new["trade_float32"] = data["trade"].astype('float32', copy=False) data_new["up_rate"] = ( data_new["next_close"] - data_new["trade_float32"]) * 100 / data_new["trade_float32"] data_new["up_rate"] = data_new["up_rate"].round(2) # 数据保留2位小数 del data_new["trade_float32"] try: common.insert_db(data_new, table_name, False, "`date`,`code`") print("insert_db") except Exception as e: print("error :", e) # 重命名 del data_new["name"] print(data_new)
def stat_dividend_current(tmp_datetime, method="dividend"): sql_1 = """ SELECT `ts_code` FROM ts_pro_basics """ basic_data = pd.read_sql(sql=sql_1, con=common.engine(), params=[]) basic_data = basic_data.drop_duplicates(subset="ts_code", keep="last") pro = ts.pro_api() # 每年都取上一年的财报 cur_year = int((tmp_datetime).strftime("%Y")) - 1 cur_date = "%s1231" % cur_year table_name = "ts_pro_%s" % method try: sql_exist = """ SELECT `ts_code` FROM %s WHERE `end_date`='%s' AND `div_proc`='实施' """ % (table_name, cur_date) exist_data = pd.read_sql(sql=sql_exist, con=common.engine(), params=[]) logger.info("[%s][mysql][%s]Begin: 已获取%s财报的公司共有%s家", tmp_datetime, table_name, cur_date, len(exist_data.ts_code)) exist_set = set(exist_data.ts_code) except sqlalchemy.exc.ProgrammingError: exist_set = set() new_code = [] for i, ts_code in enumerate(basic_data.ts_code): if ts_code in exist_set: continue try: data = getattr(pro, method)(ts_code=ts_code, start_date=cur_date) except IOError: data = None if not data is None and len(data) > 0: clear_sql = """ DELETE from %s WHERE `div_proc`!='实施' and `ts_code`='%s' """ % (table_name, ts_code) common.insert(clear_sql) logger.info("Table %s: insert %s, %s(%s) / %s", table_name, ts_code, i, len(exist_data) + len(new_code), len(basic_data)) data.head(n=1) data = data.drop_duplicates(subset=["ts_code", 'end_date'], keep="last") try: sql_date = """ SELECT `end_date` FROM %s WHERE `ts_code`='%s' """ % (table_name, ts_code) exist_dates = pd.read_sql(sql=sql_date, con=common.engine(), params=[]) date_set = set(exist_dates.end_date) data = data[-data['end_date'].isin(date_set)] except sqlalchemy.exc.ProgrammingError: pass if len(data) > 0: try: common.insert_db(data, table_name, False, "`ts_code`,`end_date`") new_code.append(ts_code) except sqlalchemy.exc.IntegrityError: pass else: logger.debug("no data . method=%s ts_code=%s", method, ts_code) # Exception: 抱歉,您每分钟最多访问该接口80次,权限的具体详情访问:https://tushare.pro/document/1?doc_id=108。 time.sleep(1) logger.info("[%s][mysql][%s]End: 新发布%s财报的公司共有%s家", tmp_datetime, table_name, cur_date, len(new_code))
def stat_index_all_no_use(tmp_datetime): datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) # 查询今日满足股票数据。剔除数据:创业板股票数据,中小板股票数据,所有st股票 # #`code` not like '002%' and `code` not like '300%' and `name` not like '%st%' sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`, `open`, `high`, `low`, `settlement`, `volume`, `turnoverratio`, `amount`, `per`, `pb`, `mktcap`, `nmc` FROM stock_data.ts_today_all WHERE `date` = %s and `trade` > 0 and `open` > 0 and trade <= 20 and `code` not like %s and `code` not like %s and `name` not like %s """ print(sql_1) data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int, '002%', '300%', '%st%']) data = data.drop_duplicates(subset="code", keep="last") print("########data[trade]########:", len(data)) # print(data["trade"]) # 1), n天涨跌百分百计算 # open price change (in percent) between today and the day before yesterday ‘r’ stands for rate. # stock[‘close_-2_r’] # 可以看到,-n天数据和今天数据的百分比。 stock_column = ['close_-1_r', 'close_-2_r', 'code', 'date'] # close_-1_r close_-2_r code date data_new = concat_guess_data(stock_column, data) # 2), CR指标 # http://wiki.mbalib.com/wiki/CR%E6%8C%87%E6%A0%87 价格动量指标 # CR跌穿a、b、c、d四条线,再由低点向上爬升160时,为短线获利的一个良机,应适当卖出股票。 # CR跌至40以下时,是建仓良机。而CR高于300~400时,应注意适当减仓。 stock_column = ['code', 'cr', 'cr-ma1', 'cr-ma2', 'cr-ma3', 'date'] # code cr cr-ma1 cr-ma2 cr-ma3 date data_new = concat_guess_data(stock_column, data_new) # 3), KDJ指标 # http://wiki.mbalib.com/wiki/%E9%9A%8F%E6%9C%BA%E6%8C%87%E6%A0%87 # 随机指标(KDJ)一般是根据统计学的原理,通过一个特定的周期(常为9日、9周等)内出现过的最高价、 # 最低价及最后一个计算周期的收盘价及这三者之间的比例关系,来计算最后一个计算周期的未成熟随机值RSV, # 然后根据平滑移动平均线的方法来计算K值、D值与J值,并绘成曲线图来研判股票走势。 # (3)在使用中,常有J线的指标,即3乘以K值减2乘以D值(3K-2D=J),其目的是求出K值与D值的最大乖离程度, # 以领先KD值找出底部和头部。J大于100时为超买,小于10时为超卖。 stock_column = ['code', 'date', 'kdjd', 'kdjj', 'kdjk'] # code date kdjd kdjj kdjk data_new = concat_guess_data(stock_column, data_new) # 4), MACD指标 # http://wiki.mbalib.com/wiki/MACD # 平滑异同移动平均线(Moving Average Convergence Divergence,简称MACD指标),也称移动平均聚散指标 # MACD 则可发挥其应有的功能,但当市场呈牛皮盘整格局,股价不上不下时,MACD买卖讯号较不明显。 # 当用MACD作分析时,亦可运用其他的技术分析指标如短期 K,D图形作为辅助工具,而且也可对买卖讯号作双重的确认。 stock_column = ['code', 'date', 'macd', 'macdh', 'macds'] # code date macd macdh macds data_new = concat_guess_data(stock_column, data_new) # 5), BOLL指标 # http://wiki.mbalib.com/wiki/BOLL # 布林线指标(Bollinger Bands) stock_column = ['boll', 'boll_lb', 'boll_ub', 'code', 'date'] # boll boll_lb boll_ub code date data_new = concat_guess_data(stock_column, data_new) # 6), RSI指标 # http://wiki.mbalib.com/wiki/RSI # 相对强弱指标(Relative Strength Index,简称RSI),也称相对强弱指数、相对力度指数 # (2)强弱指标保持高于50表示为强势市场,反之低于50表示为弱势市场。 # (3)强弱指标多在70与30之间波动。当六日指标上升到达80时,表示股市已有超买现象, # 如果一旦继续上升,超过90以上时,则表示已到严重超买的警戒区,股价已形成头部,极可能在短期内反转回转。 stock_column = ['code', 'date', 'rsi_12', 'rsi_6'] # code date rsi_12 rsi_6 data_new = concat_guess_data(stock_column, data_new) # 7), W%R指标 # http://wiki.mbalib.com/wiki/%E5%A8%81%E5%BB%89%E6%8C%87%E6%A0%87 # 威廉指数(Williams%Rate)该指数是利用摆动点来度量市场的超买超卖现象。 stock_column = ['code', 'date', 'wr_10', 'wr_6'] # code date wr_10 wr_6 data_new = concat_guess_data(stock_column, data_new) # 8), CCI指标 # http://wiki.mbalib.com/wiki/%E9%A1%BA%E5%8A%BF%E6%8C%87%E6%A0%87 # 顺势指标又叫CCI指标,其英文全称为“Commodity Channel Index”, # 是由美国股市分析家唐纳德·蓝伯特(Donald Lambert)所创造的,是一种重点研判股价偏离度的股市分析工具。 # 1、当CCI指标从下向上突破﹢100线而进入非常态区间时,表明股价脱离常态而进入异常波动阶段, # 中短线应及时买入,如果有比较大的成交量配合,买入信号则更为可靠。 # 2、当CCI指标从上向下突破﹣100线而进入另一个非常态区间时,表明股价的盘整阶段已经结束, # 将进入一个比较长的寻底过程,投资者应以持币观望为主。 # CCI, default to 14 days stock_column = ['cci', 'cci_20', 'code', 'date'] # cci cci_20 code date data_new = concat_guess_data(stock_column, data_new) # 9), TR、ATR指标 # http://wiki.mbalib.com/wiki/%E5%9D%87%E5%B9%85%E6%8C%87%E6%A0%87 # 均幅指标(Average True Ranger,ATR) # 均幅指标(ATR)是取一定时间周期内的股价波动幅度的移动平均值,主要用于研判买卖时机。 stock_column = ['cci', 'cci_20', 'code', 'date'] # cci cci_20 code date data_new = concat_guess_data(stock_column, data_new) # 10), DMA指标 # http://wiki.mbalib.com/wiki/DMA # DMA指标(Different of Moving Average)又叫平行线差指标,是目前股市分析技术指标中的一种中短期指标,它常用于大盘指数和个股的研判。 # DMA, difference of 10 and 50 moving average # stock[‘dma’] stock_column = ['code', 'date', 'dma'] # code date dma data_new = concat_guess_data(stock_column, data_new) # 11), DMI,+DI,-DI,DX,ADX,ADXR指标 # http://wiki.mbalib.com/wiki/DMI # 动向指数Directional Movement Index,DMI) # http://wiki.mbalib.com/wiki/ADX # 平均趋向指标(Average Directional Indicator,简称ADX) # http://wiki.mbalib.com/wiki/%E5%B9%B3%E5%9D%87%E6%96%B9%E5%90%91%E6%8C%87%E6%95%B0%E8%AF%84%E4%BC%B0 # 平均方向指数评估(ADXR)实际是今日ADX与前面某一日的ADX的平均值。ADXR在高位与ADX同步下滑,可以增加对ADX已经调头的尽早确认。 # ADXR是ADX的附属产品,只能发出一种辅助和肯定的讯号,并非入市的指标,而只需同时配合动向指标(DMI)的趋势才可作出买卖策略。 # 在应用时,应以ADX为主,ADXR为辅。 stock_column = ['adx', 'adxr', 'code', 'date', 'dx', 'mdi', 'pdi'] # adx adxr code date dx mdi pdi data_new = concat_guess_data(stock_column, data_new) # 12), TRIX,MATRIX指标 # http://wiki.mbalib.com/wiki/TRIX # TRIX指标又叫三重指数平滑移动平均指标(Triple Exponentially Smoothed Average) stock_column = ['code', 'date', 'trix', 'trix_9_sma'] # code date trix trix_9_sma data_new = concat_guess_data(stock_column, data_new) # 13), VR,MAVR指标 # http://wiki.mbalib.com/wiki/%E6%88%90%E4%BA%A4%E9%87%8F%E6%AF%94%E7%8E%87 # 成交量比率(Volumn Ratio,VR)(简称VR),是一项通过分析股价上升日成交额(或成交量,下同)与股价下降日成交额比值, # 从而掌握市场买卖气势的中期技术指标。 stock_column = ['code', 'date', 'vr', 'vr_6_sma'] # code date vr vr_6_sma data_new = concat_guess_data(stock_column, data_new) data_new = data_new.round(2) # 数据保留2位小数 # 删除老数据。 del_sql = " DELETE FROM `stock_data`.`guess_indicators_daily` WHERE `date`= %s " % datetime_int common.insert(del_sql) # print(data_new.head()) # data_new["down_rate"] = (data_new["trade"] - data_new["wave_mean"]) / data_new["wave_base"] common.insert_db(data_new, "guess_indicators_daily", False, "`date`,`code`")
def stat_all_lite(tmp_datetime): # 要操作的数据库表名称。 table_name = "guess_indicators_lite_buy_daily" datetime_str = (tmp_datetime).strftime("%Y-%m-%d") datetime_int = (tmp_datetime).strftime("%Y%m%d") print("datetime_str:", datetime_str) print("datetime_int:", datetime_int) # try: # # 删除老数据。guess_indicators_lite_buy_daily 是一张单表,没有日期字段。 # del_sql = " DELETE FROM `stock_data`.`%s` WHERE `date`= '%s' " % (table_name, datetime_int) # print("del_sql:", del_sql) # common.insert(del_sql) # print("del_sql") # except Exception as e: # print("error :", e) sql_1 = """ SELECT `date`, `code`, `name`, `changepercent`, `trade`,`turnoverratio`, `pb` ,`kdjj`,`rsi_6`,`cci` FROM stock_data.guess_indicators_lite_daily WHERE `date` = %s and `changepercent` > 2 and `pb` > 0 """ # and `changepercent` > 2 and `pb` > 0 and `turnoverratio` > 5 去除掉换手率参数。 data = pd.read_sql(sql=sql_1, con=common.engine(), params=[datetime_int]) data = data.drop_duplicates(subset="code", keep="last") print("######## len data ########:", len(data)) # del data["name"] # print(data) data["trade_float32"] = data["trade"].astype('float32', copy=True) # 输入 date 用作历史数据查询。 stock_merge = pd.DataFrame( { "date": data["date"], "code": data["code"], "wave_mean": data["trade"], "wave_crest": data["trade"], "wave_base": data["trade"] }, index=data.index.values) print(stock_merge.head(1)) stock_merge = stock_merge.apply(apply_merge, axis=1) # , axis=1) del stock_merge["date"] # 合并前删除 date 字段。 # 合并数据 data_new = pd.merge(data, stock_merge, on=['code'], how='left') # 使用 trade_float32 参加计算。 data_new = data_new[data_new["trade_float32"] > data_new["wave_base"]] # 交易价格大于波谷价格。 data_new = data_new[data_new["trade_float32"] < data_new["wave_crest"]] # 小于波峰价格 # wave_base wave_crest wave_mean data_new["wave_base"] = data_new["wave_base"].round(2) # 数据保留2位小数 data_new["wave_crest"] = data_new["wave_crest"].round(2) # 数据保留2位小数 data_new["wave_mean"] = data_new["wave_mean"].round(2) # 数据保留2位小数 data_new["up_rate"] = (data_new["wave_mean"].sub( data_new["trade_float32"])).div(data_new["wave_crest"]).mul(100) data_new["up_rate"] = data_new["up_rate"].round(2) # 数据保留2位小数 data_new["buy"] = 1 data_new["sell"] = 0 data_new["today_trade"] = data_new["trade"] data_new["income"] = 0 # 重命名 date data_new.columns.values[0] = "buy_date" del data_new["trade_float32"] try: common.insert_db(data_new, table_name, False, "`code`") print("insert_db") except Exception as e: print("error :", e) # 重命名 del data_new["name"] print(data_new)
def stat_all(date_time): # tmp_datetime db = common.engine() # today='2018-04-16' # 填补以前的数据 # x=pd.date_range('20170101','20180312') # date_list = [ for i in list(pd.date_range('20170401','20171231')) date = date_time.strftime('%Y%m%d') if common.is_holiday(date_time): common.logger.info('Holiday') return common.logger.info("start") obj = ztb.GetZDT() obj.today = date data_jrj = obj.get_jrj_data() # 读取金融届网站的涨停数据 data_jrj = pd.DataFrame(data_jrj, columns=jrj_data_index) data_jrj['日期'] = date # 去掉ST 新股 退市股 科创板 data_jrj = data_jrj[~(data_jrj['名称'].str.contains('ST') | data_jrj['名称'].str.startswith("N") | data_jrj['名称'].str.endswith("退") | data_jrj['代码'].str.startswith("68"))] data_jrj['封成比'] = data_jrj['封成比'].map(lambda x: round(x * 100, 3)) data_jrj['封流比'] = data_jrj['封流比'].map(lambda x: round(x * 100, 3)) insert_or_update(data_jrj, ['日期', '代码']) print(data_jrj) data_east = obj.get_east_data() # 读取金融届网站的涨停数据 df_ztb = pd.DataFrame.from_dict(data_east["ztb"], orient='columns') df_ztb = df_ztb.drop(['m'], axis=1) # 根据列名进行删除(去掉不需要的列) df_ztb = df_ztb.rename(columns=east_data_index) # 列名重命名 df_ztb['日期'] = date # 去掉ST 新股 退市股 科创板 df_ztb = df_ztb[~( df_ztb['名称'].str.contains('ST') | df_ztb['名称'].str.startswith("N") | df_ztb['名称'].str.endswith("退") | df_ztb['代码'].str.startswith("68"))] df_ztb['涨跌幅'] = df_ztb['涨跌幅'].map(lambda x: round(x, 3)) df_ztb['换手率'] = df_ztb['换手率'].map(lambda x: round(x, 3)) df_ztb['流通市值'] = df_ztb['流通市值'].map(lambda x: round(x, 3)) df_ztb['总市值'] = df_ztb['总市值'].map(lambda x: round(x, 3)) insert_or_update(df_ztb, ['日期', '代码']) print(df_ztb) sql_update_stock = "update stockInfo s INNER JOIN (select `代码`, `名称`,`所属行业`,`成交额`,`流通市值`,`连板数`,`涨停统计` " \ "from stock_quotations where `代码` in (select `代码` from stockInfo) and " \ "`日期` = (select max(`日期`) from stock_quotations)) q on s.`代码`=q.`代码` set " \ "s.`名称`=q.`名称`,s.`所属行业`=q.`所属行业`,s.`成交额`=q.`成交额`,s.`流通市值`=q.`流通市值`," \ "s.`连板数`=q.`连板数`,s.`涨停统计`=q.`涨停统计`" common.insert(sql_update_stock) sql_insert_stock = "insert into stockInfo (`代码`, `名称`,`所属行业`,`成交额`,`流通市值`,`连板数`,`涨停统计`) " \ "select `代码`, `名称`,`所属行业`,`成交额`,`流通市值`,`连板数`,`涨停统计` from stock_quotations " \ "where `代码` not in (select `代码` from stockInfo) and " \ "`日期` = (select max(`日期`) from stock_quotations) " common.insert(sql_insert_stock) sum_df = pd.read_sql( "SELECT `日期`,CONCAT(名称,'[',代码,']') as 名称,连板数 from stock_quotations where `连板数`>1", db) today = pd.read_sql( "select `日期`, `代码`, `名称`, `最新价`, `涨跌幅`, `封成比`, `封流比`, `封单金额`, `成交额`, `流通市值`," " `总市值`, `换手率`, `连板数`, `首次封板时间`, `最后封板时间`, `打开次数`, `涨停统计`, `涨停强度`, `振幅`, " " `所属行业` from stock_quotations where 日期='%s' order by `涨停强度` desc" % date, db) today['涨停统计'].astype(str) today['涨停统计'] = today['涨停统计'].map( lambda x: str("{ct}/{days}".format(**eval(x)))) save_excel(date, today, sum_df)