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_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 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 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_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 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()
cur_day = get_cur_day(tmp_datetime)
data = pro.daily_basic(trade_date=cur_day)
try:
sql_1 = """
DELETE FROM ts_pro_daily WHERE `trade_date`='%s'
""" % cur_day
common.insert(sql_1)
except sqlalchemy.exc.ProgrammingError:
pass
if not data is None and len(data) > 0:
data = data.drop_duplicates(subset="ts_code", keep="last")
data.head(n=1)
if len(data) > 0:
common.insert_db(data, "ts_pro_daily", False,
"`ts_code`, `trade_date`")
else:
logger.debug("no data . stock_basics")
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_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_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_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_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)
def stat_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)
# 股票列表
try:
data = ak.stock_zh_a_spot_em()
# print(data.index)
# 解决ESP 小数问题。
# data["esp"] = data["esp"].round(2) # 数据保留2位小数
data.columns = [
'index', 'code', 'name', 'latest_price', 'quote_change',
'ups_downs', 'volume', 'turnover', 'amplitude', 'high', 'low',
'open', 'closed', 'quantity_ratio', 'turnover_rate', 'pe_dynamic',
'pb'
]
data = data.loc[data["code"].apply(stock_a)].loc[data["name"].apply(
stock_a_filter_st)].loc[data["latest_price"].apply(
stock_a_filter_price)]
print(data)
data['date'] = datetime_int # 修改时间成为int类型。
# 删除老数据。
del_sql = " DELETE FROM `stock_zh_ah_name` where `date` = '%s' " % datetime_int
common.insert(del_sql)
data.set_index('code', inplace=True)
data.drop('index', axis=1, inplace=True)
print(data)
# 删除index，然后和原始数据合并。
common.insert_db(data, "stock_zh_ah_name", True, "`date`,`code`")
except Exception as e:
print("error :", e)
# 龙虎榜-个股上榜统计
# 接口: stock_sina_lhb_ggtj
#
# 目标地址: http://vip.stock.finance.sina.com.cn/q/go.php/vLHBData/kind/ggtj/index.phtml
#
# 描述: 获取新浪财经-龙虎榜-个股上榜统计
#
try:
stock_sina_lhb_ggtj = ak.stock_sina_lhb_ggtj(recent_day="5")
print(stock_sina_lhb_ggtj)
stock_sina_lhb_ggtj.columns = [
'code', 'name', 'ranking_times', 'sum_buy', 'sum_sell',
'net_amount', 'buy_seat', 'sell_seat'
]
stock_sina_lhb_ggtj = stock_sina_lhb_ggtj.loc[
stock_sina_lhb_ggtj["code"].apply(stock_a)].loc[
stock_sina_lhb_ggtj["name"].apply(stock_a_filter_st)]
stock_sina_lhb_ggtj.set_index('code', inplace=True)
# data_sina_lhb.drop('index', axis=1, inplace=True)
# 删除老数据。
stock_sina_lhb_ggtj['date'] = datetime_int # 修改时间成为int类型。
# 删除老数据。
del_sql = " DELETE FROM `stock_sina_lhb_ggtj` where `date` = '%s' " % datetime_int
common.insert(del_sql)
common.insert_db(stock_sina_lhb_ggtj, "stock_sina_lhb_ggtj", True,
"`date`,`code`")
except Exception as e:
print("error :", e)
# 每日统计
# 接口: stock_dzjy_mrtj
#
# 目标地址: http://data.eastmoney.com/dzjy/dzjy_mrtj.aspx
#
# 描述: 获取东方财富网-数据中心-大宗交易-每日统计
try:
print("################ tmp_datetime : " + datetime_str)
stock_dzjy_mrtj = ak.stock_dzjy_mrtj(start_date=datetime_str,
end_date=datetime_str)
print(stock_dzjy_mrtj)
stock_dzjy_mrtj.columns = [
'index', 'trade_date', 'code', 'name', 'quote_change',
'close_price', 'average_price', 'overflow_rate', 'trade_number',
'sum_volume', 'sum_turnover', 'turnover_market_rate'
]
stock_dzjy_mrtj.set_index('code', inplace=True)
# data_sina_lhb.drop('index', axis=1, inplace=True)
# 删除老数据。
stock_dzjy_mrtj['date'] = datetime_int # 修改时间成为int类型。
stock_dzjy_mrtj.drop('trade_date', axis=1, inplace=True)
stock_dzjy_mrtj.drop('index', axis=1, inplace=True)
# 数据保留2位小数
try:
stock_dzjy_mrtj = stock_dzjy_mrtj.loc[
stock_dzjy_mrtj["code"].apply(stock_a)].loc[
stock_dzjy_mrtj["name"].apply(stock_a_filter_st)]
stock_dzjy_mrtj["average_price"] = stock_dzjy_mrtj[
"average_price"].round(2)
stock_dzjy_mrtj["overflow_rate"] = stock_dzjy_mrtj[
"overflow_rate"].round(4)
stock_dzjy_mrtj["turnover_market_rate"] = stock_dzjy_mrtj[
"turnover_market_rate"].round(6)
except Exception as e:
print("round error :", e)
# 删除老数据。
del_sql = " DELETE FROM `stock_dzjy_mrtj` where `date` = '%s' " % datetime_int
common.insert(del_sql)
print(stock_dzjy_mrtj)
common.insert_db(stock_dzjy_mrtj, "stock_dzjy_mrtj", True,
"`date`,`code`")
except Exception as e:
print("error :", e)