def yesterday_zt_location(date='20180404'):
engine_zdt = get_engine('db_zdt')
engine_basic = get_engine('db_stock')
df = pd.read_sql(date+'zdt',engine_zdt,index_col='index')
df_basic = pd.read_sql('basic_info',engine_basic,index_col='index')
result={}
for code in df[u'代码'].values:
try:
area=df_basic[df_basic['code']==code]['area'].values[0]
result.setdefault(area,0)
result[area]+=1
except Exception,e:
print e
def basic_info(self, retry=5):
engine = get_engine('db_stock')
# 需要添加异常处理 重试次数
count = 0
while count < retry:
try:
df = ts.get_stock_basics()
except Exception as e:
logger.info(e)
time.sleep(10)
count += 1
continue
else:
if df is not None:
df = df.reset_index()
df['更新日期'] = datetime.datetime.now()
df.to_sql('tb_basic_info', engine, if_exists='replace')
logger.info('入库成功')
break
else:
count += 1
time.sleep(10)
continue
def clone_database():
local_db =get_mysql_conn('db_zdt',local=True)
cur = local_db.cursor()
cur.execute('show tables')
tables=cur.fetchall()
local_engine = get_engine('db_zdt',local=True)
dfs=[]
for table in tables:
try:
result =re.findall('(\d+)zdt$', table[0])
if result:
print(table[0])
current = result[0]
# d= datetime.datetime.strptime(current,'%Y%m%d').strftime('%Y-%m-%d')
# print(d)
df =pd.read_sql(table[0],local_engine,index_col='index')
# df['涨停日期']=d
df=df.rename(columns={'最后一次涨停时间A':'最后一次涨停时间','第一次涨停时间A':'第一次涨停时间'})
try:
print(df.head())
df.to_sql(table[0],local_engine,if_exists='replace')
except Exception as e:
print(e)
except Exception as e:
print(e)
print(table[0])
def store(self):
self.df_today_all = self.gettodaymarket()
# 存储每天 涨幅排行 榜,避免每次读取耗时过长
filename = self.today + '_all_.xls'
# 放在data文件夹下
full_filename = os.path.join(self.path, filename)
if not os.path.exists(full_filename):
if self.df_today_all is not None:
# 保留小数点的后两位数
self.df_today_all['turnoverratio'] = self.df_today_all[
'turnoverratio'].map(lambda x: round(x, 2))
self.df_today_all['per'] = self.df_today_all['per'].map(
lambda x: round(x, 2))
self.df_today_all['pb'] = self.df_today_all['pb'].map(
lambda x: round(x, 2))
try:
self.df_today_all.to_excel(full_filename)
except Exception as e:
logger.error(e)
engine = get_engine('db_daily')
# print(self.df_today_all)
try:
self.df_today_all.to_sql(self.today,
engine,
if_exists='fail')
except Exception as e:
# print(e)
logger.error(e)
def store(self):
self.df_today_all = self.gettodaymarket()
# 存储每天 涨幅排行 榜,避免每次读取耗时过长
filename = self.today + '_all_.xls'
# 放在data文件夹下
full_filename = os.path.join(self.path, filename)
if not os.path.exists(full_filename):
if self.df_today_all is not None:
# 保留小数点的后两位数
self.df_today_all['turnoverratio'] = map(
lambda x: round(x, 2), self.df_today_all['turnoverratio'])
self.df_today_all['per'] = map(lambda x: round(x, 2),
self.df_today_all['per'])
self.df_today_all['pb'] = map(lambda x: round(x, 2),
self.df_today_all['pb'])
try:
self.df_today_all.to_excel(full_filename)
except Exception, e:
print e
engine = get_engine('db_daily')
try:
self.df_today_all.to_sql(self.today, engine)
except Exception, e:
print e
pass
def save_to_dataframe(self, data, indexx, choice, post_fix):
engine = setting.get_engine('db_zdt')
if not data:
exit()
data_len = len(data)
if choice == 1:
for i in range(data_len):
data[i][choice] = data[i][choice].decode('gbk')
df = pd.DataFrame(data, columns=indexx)
if choice == 2:
df = df.set_index(u'序号')
filename = os.path.join(self.path, self.today + "_" + post_fix + ".xls")
if choice == 1:
df.to_excel(filename, encoding='gbk')
if choice == 2:
df[u'最大涨幅'] = map(lambda x: round(x * 100, 3), df[u'最大涨幅'])
df[u'最大跌幅'] = map(lambda x: round(x * 100, 3), df[u'最大跌幅'])
df[u'今日开盘涨幅'] = map(lambda x: round(x * 100, 3), df[u'今日开盘涨幅'])
df[u'昨日涨停强度'] = map(lambda x: round(x, 0), df[u'昨日涨停强度'])
df[u'今日涨停强度'] = map(lambda x: round(x, 0), df[u'今日涨停强度'])
if choice == 1:
df.to_sql(self.today + post_fix, engine, if_exists='replace')
if choice == 2:
df.to_sql(self.today + post_fix, engine, if_exists='fail')
def find_pattern(code):
pattern = ''
engine = get_engine('history')
try:
df = pd.read_sql(
code,
engine,
index_col='index',
)
except Exception as e:
print(e)
return
# print(df)
# for i in df['open'].astype('float').values:
# print(type(i))
# print(df['high'].values)
# print(df['low'].values)
# print(df['close'].values)
result = talib.CDLDARKCLOUDCOVER(df['open'].astype('float').values,
df['high'].astype('float').values,
df['low'].astype('float').values,
df['close'].astype('float').values)
df['CDL2CROWS'] = result
if len(df[df['CDL2CROWS'] != 0]) != 0:
print(df[df['CDL2CROWS'] != 0])
def get_new_stock(self, start='2010', end='2011'):
'''
:param start: 开始年份 如 '2010'
:param end: 结束年份 如 '2011'
:return:
'''
df = pd.read_sql('tb_basic_info', get_engine('db_stock'), index_col='index')
df = df[df['timeToMarket'] != 0]
df['timeToMarket'] = pd.to_datetime(df['timeToMarket'], format='%Y%m%d')
df = df.set_index('timeToMarket', drop=True)
# print(len(df['2010']))
years = OrderedDict()
values = []
# for year in range(1994, 2019):
# years[year] = len(df[str(year)])
# values.append(len(df[str(year)]))
# x = np.arange(1994, 2019)
# plt.figure(figsize=(10, 9))
# rect = plt.bar(x, values)
# self.rect_show(rect)
# plt.xticks(x[::2])
# plt.show()
new_stock = df[start:end]
return new_stock
def __init__(self):
self.engine = get_engine('db_stock')
self.cb_code, self.name, self.yjl = self.bond()
self.stocks = dict(zip(self.cb_code, self.name))
self.stocks_yjl = dict(zip(self.cb_code, self.yjl))
self.api = ts.get_apis()
# python3 这个返回的不是list,需要手工转换
self.kzz_code_list = list(self.stocks.keys())
pool_code, pool_name = self.stock_pool()
self.pool_dict = dict(zip(pool_code, pool_name))
self.pool_list = list(self.pool_dict.keys())
# 添加一部分持仓数据 或者 监测仓
self.df = pd.read_table(file, encoding='gbk', dtype={'证券代码': np.str})
del self.df['Unnamed: 15']
code_list = list(self.df['证券代码'].values)
# 移除非法证券代码 中签
t = [
code_list.remove(i) for i in code_list.copy()
if i.startswith('7') or i[:2] == '07'
]
self.code_lists = code_list
def get_current_position(self):
engine = get_engine('db_position')
df = pd.read_sql('tb_position_2019-06-17', con=engine)
# 只关注可转债
df = df[df['证券代码'].map(self.identify_market)]
# print(df)
kzz_stocks = dict(zip(list(df['证券代码'].values),
list(df['证券名称'].values)))
cons = get_mysql_conn('db_stock', 'local')
cursor = cons.cursor()
query_cmd = 'select 正股代码,正股名称,溢价率 from tb_bond_jisilu where 可转债代码=%s'
zg_stocks = {}
kzz_yjl = {}
zg_yjl = {}
for code in kzz_stocks:
cursor.execute(query_cmd, (code))
ret = cursor.fetchone()
if ret:
zg_stocks[ret[0]] = ret[1]
kzz_yjl[code] = ret[2]
zg_yjl[ret[0]] = ret[2]
kzz_code = list(df['证券代码'].values)
return (kzz_code, kzz_stocks, zg_stocks, kzz_yjl, zg_yjl)
def __init__(self):
# path=os.path.join(os.getcwd(),'data')
path = DATA_PATH
if os.path.exists(path) == False:
os.mkdir(path)
os.chdir(path)
self.name = 'simulation.xls'
self.df = pd.read_excel(self.name)
self.df['代码'] = self.df['代码'].map(lambda x: str(x).zfill(6))
self.engine = get_engine('db_stock')
self.engine1 = get_engine('db_daily')
# self.base = pd.read_sql('tb_basic_info', self.engine, index_col='index')
self.money = 10000
self.today = datetime.datetime.now().strftime('%Y-%m-%d')
def monitor():
engine = get_engine('db_zdt')
table = '20180409zdt'
api = ts.get_apis()
df = pd.read_sql(table, engine, index_col='index')
# print df
price_list = []
percent_list = []
amplitude_list = []
start = datetime.datetime.now()
for i in df[u'代码'].values:
try:
curr = ts.quotes(i, conn=api)
last_close = curr['last_close'].values[0]
curr_price = curr['price'].values[0]
amplitude = round(
((curr['high'].values[0] - curr['low'].values[0]) * 1.00 /
last_close) * 100, 2)
# if last_close>=curr_price:
# print i,
# print df[df[u'代码']==i][u'名称'].values[0],
# print percent
except Exception, e:
print e
curr_price = 0
if last_close == 0:
percent = np.nan
percent = round((curr_price - last_close) * 100.00 / last_close, 2)
percent_list.append(percent)
price_list.append(curr_price)
amplitude_list.append(amplitude)
def __init__(self):
# path=os.path.join(os.getcwd(),'data')
path = DATA_PATH
if os.path.exists(path) == False:
os.mkdir(path)
os.chdir(path)
self.name = 'simulation.xls'
self.df = pd.read_excel(self.name)
self.df['代码'] = self.df['代码'].map(lambda x: str(x).zfill(6))
self.engine = get_engine('db_stock')
self.engine1=get_engine('db_daily')
# self.base = pd.read_sql('tb_basic_info', self.engine, index_col='index')
self.money = 10000
self.today = datetime.datetime.now().strftime('%Y-%m-%d')
def get_bond_low_down_possibility():
engine = get_engine('db_stock', local='local')
df = pd.read_sql('tb_bond_jisilu', con=engine)
# df = df[df['下调次数'] == 0]
df['需要的天数内'] = df['下修条件'].map(get_low_down)
df['维持天数'] = df['下修条件'].map(get_low_down_least)
df['低于比例'] = df['下修条件'].map(get_percent)
df['当前比例'] = df['正股现价'] / df['最新转股价'] * 100
df['当前比例'] = df['当前比例'].map(lambda x: round(x, 2))
df['需要的天数内'] = df['需要的天数内'].map(convert_ch_eng)
df['维持天数'] = df['维持天数'].map(convert_ch_eng)
df = df[df['转股起始日'].map(compare_date) == True]
tb_name = 'tb_bond_low_down_price_{}'.format(datetime.datetime.now().strftime('%Y-%m-%d'))
del df['正股现价']
del df['正股涨跌幅']
del df['可转债涨幅']
del df['下修条件']
del df['成交额(万元)']
del df['强制赎回条款']
del df['回售']
del df['下修提示']
del df['发行规模']
del df['剩余规模']
del df['下调次数']
df['更新日期'] = datetime.datetime.now().strftime('%Y-%m-%d')
df.to_sql(tb_name, con=engine, if_exists='replace', index=None)
def plot_stock_line(code, name, table_name, start='2017-10-01', save=False):
today = datetime.datetime.now().strftime('%Y-%m-%d')
title = u'{} {} {} {}'.format(today, code, name, table_name)
if os.path.exists(title + '.png'):
return
engine = get_engine('db_stock', local=True)
fig = plt.figure(figsize=(10, 8))
base_info = pd.read_sql('tb_basic_info', engine, index_col='index')
# fig,(ax,ax2)=plt.subplots(2,1,sharex=True,figsize=(16,10))
ax = fig.add_axes([0, 0.3, 1, 0.55])
ax2 = fig.add_axes([0, 0.1, 1, 0.25])
if code is None and name is not None:
code = base_info[base_info['name'] == name]['code'].values[0]
print code
df = None
for _ in range(4):
api = ts.get_apis()
try:
df = ts.bar(code, conn=api, start_date=start)
break
except Exception, e:
print e
ts.close_apis(api)
time.sleep(random.random() * 3)
continue
def _xiayingxian(self, row, ratio):
'''
下影线的逻辑 ratio 下影线的长度比例，数字越大，下影线越长
row: series类型
'''
open_p = float(row['open'])
# print open_p
closed = float(row['close'])
# print closed
low = float(row['low'])
# print low
high = float(row['high'])
p = min(closed,open_p)
try:
diff = (p - low) * 1.00 / (high - low)
diff=round(diff,3)
except ZeroDivisionError:
diff = 0
if diff > ratio:
xiayinxian_engine = get_engine('db_selection')
date,code,name,ocupy_ration ,standards = row['datetime'],row['code'],row['name'],diff,ratio
df = pd.DataFrame(
{'datetime': [date], 'code': [code], 'name': [name], 'ocupy_ration': [ocupy_ration],
'standards': [standards]})
try:
df1=pd.read_sql_table('xiayingxian',xiayinxian_engine,index_col='index')
df = pd.concat([df1, df])
except Exception,e:
print e
#return None
df = df.reset_index(drop=True)
df.to_sql('xiayingxian',xiayinxian_engine,if_exists='replace')
return row
def basic_info(self,retry=5):
engine = get_engine('db_stock')
# 需要添加异常处理 重试次数
count = 0
while count < retry:
try:
df = ts.get_stock_basics()
except Exception as e:
logger.info(e)
time.sleep(10)
count+=1
continue
else:
if df is not None:
df=df.reset_index()
df['更新日期']=datetime.datetime.now()
df.to_sql('tb_basic_info',engine,if_exists='replace')
logger.info('入库成功')
break
else:
count+=1
time.sleep(10)
continue
def caculation(self):
self.name = 'simulation.xls'
self.df = pd.read_excel(self.name)
self.df[u'代码'] = self.df[u'代码'].map(lambda x: str(x).zfill(6))
self.base = pd.read_csv('bases.csv', dtype={'code': np.str})
engine = get_engine('db_daily')
df_t = pd.read_sql(self.today, engine, index_col='index')
print self.df[u'代码'].values
for i in self.df[u'代码'].values:
name = self.base[self.base['code'] == i]['name'].values[0]
print name
t = name.decode('utf-8')
print
print type(t)
# print chardet.detect(t)
self.df.ix[self.df[u'代码'] == i, u'当前日期'] = self.today
# t=ts.get_k_data(i)
pchange = df_t.ix[df_t['code'] == i, 'changepercent'].values[0]
print pchange
self.df.ix[self.df[u'代码'] == i, u'今日涨幅'] = pchange
current = df_t[df_t['code'] == i]['trade'].values[0]
self.df.ix[self.df[u'代码'] == i, u'当前价格'] = current
current_profit = (current - self.df[self.df[u'代码'] == i][u'买入价格'].values[0]) / \
self.df[self.df[u'代码'] == i][u'买入价格'].values[0]
self.df.ix[self.df[u'代码'] == i,
u'目前盈亏'] = round(current_profit * 100, 2)
print current_profit
print self.df
self.df.to_excel(self.name, encoding='utf-8')
def get_new_stock(self, start='2010', end='2011'):
'''
:param start: 开始年份 如 '2010'
:param end: 结束年份 如 '2011'
:return:
'''
df = pd.read_sql('tb_basic_info',
get_engine('db_stock'),
index_col='index')
df = df[df['timeToMarket'] != 0]
df['timeToMarket'] = pd.to_datetime(df['timeToMarket'],
format='%Y%m%d')
df = df.set_index('timeToMarket', drop=True)
# print(len(df['2010']))
years = OrderedDict()
values = []
# for year in range(1994, 2019):
# years[year] = len(df[str(year)])
# values.append(len(df[str(year)]))
# x = np.arange(1994, 2019)
# plt.figure(figsize=(10, 9))
# rect = plt.bar(x, values)
# self.rect_show(rect)
# plt.xticks(x[::2])
# plt.show()
new_stock = df[start:end]
return new_stock
def _xiayingxian(self, row, ratio):
'''
下影线的逻辑 ratio 下影线的长度比例，数字越大，下影线越长
row: series类型
'''
open_p = float(row['open'])
# print(open_p)
closed = float(row['close'])
# print(closed)
low = float(row['low'])
# print(low)
high = float(row['high'])
p = min(closed,open_p)
try:
diff = (p - low) * 1.00 / (high - low)
diff=round(diff,3)
except ZeroDivisionError:
diff = 0
if diff > ratio:
xiayinxian_engine = get_engine('db_selection')
date,code,name,ocupy_ration ,standards = row['datetime'],row['code'],row['name'],diff,ratio
df = pd.DataFrame(
{'datetime': [date], 'code': [code], 'name': [name], 'ocupy_ration': [ocupy_ration],
'standards': [standards]})
try:
df1=pd.read_sql_table('xiayingxian',xiayinxian_engine,index_col='index')
df = pd.concat([df1, df])
except Exception as e:
print(e)
#return None
df = df.reset_index(drop=True)
df.to_sql('xiayingxian',xiayinxian_engine,if_exists='replace')
return row
def save_position(self):
self.engine = get_engine('db_position', True)
df = self.get_position_df()
try:
df.to_sql('tb_position_{}'.format(self.today), con=self.engine)
except Exception as e:
self.logger.error(e)
def __init__(self):
self.engine = get_engine('db_stock', local=True)
self.conn = get_mysql_conn('db_stock', local='local')
self.info = pd.read_sql('tb_basic_info', con=self.engine, index_col='code')
self.db = pymongo.MongoClient(MONGO_HOST, MONGO_PORT)
self.doc = self.db['db_stock']['break_low_high']
ts.set_token(token)
self.pro = ts.pro_api()
self.count = 0
def bond(self):
engine = get_engine('db_bond')
bond_table = 'tb_bond_jisilu'
try:
jsl_df = pd.read_sql(bond_table, engine, index_col='index')
return list(jsl_df[u'正股代码'].values),list(jsl_df[u'正股名称'].values),list(jsl_df[u'溢价率'].values)
except Exception,e:
logger.log(e)
return None
def yesterday_zt_location(date='20180404'):
engine_zdt = get_engine('db_zdt')
engine_basic = get_engine('db_stock')
df = pd.read_sql(date + 'zdt', engine_zdt, index_col='index')
df_basic = pd.read_sql('basic_info', engine_basic, index_col='index')
result = {}
for code in df[u'代码'].values:
try:
area = df_basic[df_basic['code'] == code]['area'].values[0]
result.setdefault(area, 0)
result[area] += 1
except Exception as e:
print(e)
new_result = sorted(result.items(), key=lambda x: x[1], reverse=True)
for k, v in new_result:
print(k, v)
def plot_yesterday_zt():
engine = get_engine('db_zdt')
table_name = 'zrzt'
table = '{}{}'.format(datetime.datetime.now().strftime('%Y%m%d'),
table_name)
try:
df = pd.read_sql(table, engine)
except Exception, e:
print e
return
def basic_info(self):
engine = get_engine('db_stock')
df = ts.get_stock_basics()
if df is not None:
try:
df=df.reset_index()
df[u'更新日期']=datetime.datetime.now().strftime('%Y-%m-%d')
df.to_sql('tb_basic_info',engine,if_exists='replace')
except Exception,e:
print e
def yesterday_zt_location(date='20180404'):
engine_zdt = get_engine('db_zdt')
engine_basic = get_engine('db_stock')
df = pd.read_sql(date+'zdt',engine_zdt,index_col='index')
df_basic = pd.read_sql('basic_info',engine_basic,index_col='index')
result={}
for code in df['代码'].values:
try:
area=df_basic[df_basic['code']==code]['area'].values[0]
result.setdefault(area,0)
result[area]+=1
except Exception as e:
print(e)
new_result = sorted(result.items(),key=lambda x:x[1],reverse=True)
for k,v in new_result:
print(k,v)
def __init__(self):
# self.df = self.get_tick()
self.logger = self.llogger('log/'+'big_deal')
today = datetime.datetime.now().strftime('%Y-%m-%d')
if ts.is_holiday(today):
self.logger.info('{}假期 >>>>>'.format(today))
self.db_stock_engine = get_engine('db_stock', True)
self.jisilu_df = self.get_code()
self.code_name_dict=dict(zip(list(self.jisilu_df['可转债代码'].values),list(self.jisilu_df['可转债名称'].values)))
self.db = pymongo.MongoClient(config.mongodb_host, config.mongodb_port)
def save_position(self):
self.engine = get_engine('db_position', True)
df = self.get_position_df()
# print(df)
try:
df.to_sql('tb_position_{}'.format(self.today),
con=self.engine,
if_exists='replace')
except Exception as e:
self.logger.error(e)
def __init__(self):
self.engine = get_engine('db_stock')
self.kzz_code, self.kzz_name, self.zg_code, self.name, self.yjl = self.zg_bond()
self.kzz_stocks = dict(zip(self.kzz_code, self.kzz_name))
self.zg_stocks = dict(zip(self.zg_code, self.name))
self.kzz_stocks_yjl = dict(zip(self.kzz_code, self.yjl))
self.zg_stocks_yjl = dict(zip(self.zg_code, self.yjl))
self.api = ts.get_apis()
def __init__(self):
self.engine = get_engine('db_stock')
self.kzz_code, self.kzz_name, self.zg_code, self.name, self.yjl = self.zg_bond()
self.kzz_stocks = dict(zip(self.kzz_code, self.kzz_name))
self.zg_stocks = dict(zip(self.zg_code, self.name))
self.kzz_stocks_yjl = dict(zip(self.kzz_code, self.yjl))
self.zg_stocks_yjl = dict(zip(self.zg_code, self.yjl))
self.api = ts.get_apis()
def get_zhenggu():
df = pd.read_sql('tb_bond_jisilu',get_engine('db_bond'))
code_list = df[u'正股代码'].values
obj = Filter_Stock()
ns_df = obj.get_new_stock('2015','2018')
zg_code = ns_df[ns_df['code'].isin(code_list)]['code'].values
ret_df = df[df[u'正股代码'].isin(zg_code)][[u'可转债代码',u'可转债名称',u'正股名称',u'溢价率',u'可转债价格']]
print(ret_df)
with open('new_stock_zzk.txt','w') as f:
s = '\n'.join(list(ret_df[u'可转债代码'].values))
f.write(s)
class SaveData():
today = datetime.datetime.now().strftime("%Y-%m-%d")
daily_engine = get_engine('daily')
def __init__(self):
current = os.getcwd()
work_space = os.path.join(current, 'data')
if os.path.exists(work_space) == False:
os.mkdir(work_space)
os.chdir(work_space)
@staticmethod
def daily_market():
df = ts.get_today_all()
try:
df.to_sql(SaveData.today,
SaveData.daily_engine,
if_exists='replace')
except Exception as e:
print(e)
print("Save {} data to MySQL".format(SaveData.today))
#获取解禁股
def get_classified_stock(self, year=None, month=None):
df = ts.xsg_data(year, month)
filename = '{}-{}-classified_stock.xls'.format(year, month)
self.save_to_excel(df, filename)
def basic_info(self):
engine = get_engine('db_stock')
df = ts.get_stock_basics()
if df is not None:
try:
df = df.reset_index()
df[u'更新日期'] = datetime.datetime.now().strftime('%Y-%m-%d')
df.to_sql('tb_basic_info', engine, if_exists='replace')
except Exception as e:
print(e)
def save_to_excel(self, df, filename, encoding='gbk'):
try:
df.to_csv('temp.csv', encoding=encoding, index=False)
df = pd.read_csv('temp.csv',
encoding=encoding,
dtype={'code': str})
df.to_excel(filename, encoding=encoding)
return True
except Exception as e:
print("Save to excel faile")
print(e)
return None
def plot_yesterday_zt(type_name='zrzt', current=datetime.datetime.now().strftime('%Y%m%d')):
engine = get_engine('db_zdt')
table_name = type_name
table = '{}{}'.format(current, table_name)
try:
df = pd.read_sql(table, engine)
except Exception as e:
logger.error(e)
return
for i in range(len(df)):
code = df.iloc[i]['代码']
name = df.iloc[i]['名称']
plot_stock_line(api,code, name, table_name=table_name, current=current, start='2018-07-01', save=True)
def dataframe_create1():
l = 10
today = 1
code = 110
name = 'hello'
opens = 1
close = 10
high = 0
low = 0
vol = 99
amount = 99
df = pd.DataFrame(columns=['datetime', 'code', 'name', 'open', 'close', 'high', 'low', 'vol', 'amount'])
df.loc[99] = [today, code, name, opens, close, high, low, vol, amount]
print(df)
engine = get_engine('test')
def __init__(self):
self.today = datetime.date.today().strftime('%Y-%m-%d')
self.engine = get_engine('db_stock', True)
self.stock_candidates = self.get_candidates()
# self.stock_candidates = self.get_candidates()
self.logger = self.llogger('auto_trader_{}'.format(self.today))
self.logger.info('程序启动')
input('请运行下单程序，按Enter\n')
self.user = easytrader.use('ths')
# self.user.prepare('user.json')
self.user.connect(PROGRAM_PATH)
self.position = self.get_position()
self.blacklist_bond = self.get_blacklist()
self.q = easyquotation.use('qq')
def __init__(self):
self.today = datetime.date.today().strftime('%Y-%m-%d')
self.engine = get_engine('db_stock', True)
self.stock_candidates = self.get_candidates()
# self.stock_candidates = self.get_candidates()
self.logger = self.llogger('auto_trader_{}'.format(self.today))
self.logger.info('程序启动')
input('请运行下单程序，按Enter\n')
self.user = easytrader.use('ths')
# self.user.prepare('user.json')
self.user.connect(PROGRAM_PATH)
# self.position = self.get_position()
self.blacklist_bond = self.get_blacklist()
self.q=easyquotation.use('qq')
def plot_yesterday_zt(type_name='zrzt', current=datetime.datetime.now().strftime('%Y%m%d')):
engine = get_engine('db_zdt')
table_name = type_name
table = '{}{}'.format(current, table_name)
try:
df = pd.read_sql(table, engine)
except Exception as e:
logger.error('table_name >>> {}{}'.format(current,table_name))
logger.error(e)
return
for i in range(len(df)):
code = df.iloc[i]['代码']
name = df.iloc[i]['名称']
plot_stock_line(api,code, name, table_name=table_name, current=current, start='2018-07-01', save=True)
def merge_database():
local_db =get_mysql_conn('db_zdt',local=True)
cur = local_db.cursor()
cur.execute('show tables')
tables=cur.fetchall()
local_engine = get_engine('db_zdt',local=True)
dfs=[]
for table in tables:
try:
result =re.findall('(\d+)zdt$', table[0])
if len(result)>0:
print(table[0])
df =pd.read_sql(table[0],local_engine,index_col='index')
dfs.append(df)
except Exception as e:
print(e)
print(table[0])
dfx= pd.concat(dfs)
print(dfx.head())
# ali_engine = get_engine(None,local=False)
local_engine_stock=get_engine('db_stock',local=True)
dfx.to_sql('tb_zdt',local_engine_stock,if_exists='replace')
def today_tendency(today):
engine=get_engine('db_daily')
today=datetime.datetime.strptime(today,'%Y%m%d').strftime('%Y-%m-%d')
df = pd.read_sql(today,engine,index_col='index')
# 去除停牌的 成交量=0
df = df[df['volume']!=0]
median = df['changepercent'].median()
mean = df['changepercent'].mean()
std = df['changepercent'].std()
p_25=stats.scoreatpercentile(df['changepercent'],25)
p_50=stats.scoreatpercentile(df['changepercent'],50)
p_75=stats.scoreatpercentile(df['changepercent'],75)
print('中位数: {}'.format(median))
print('平均数: {}'.format(mean))
print('方差: {}'.format(std))
print('25%: {}'.format(p_25))
print('50%: {}'.format(p_50))
print('75%: {}'.format(p_75))
def save_to_dataframe(self, data, indexx, choice, post_fix):
engine = setting.get_engine('db_zdt')
if not data:
exit()
data_len = len(data)
if choice == 1:
for i in range(data_len):
data[i][choice] = data[i][choice]
df = pd.DataFrame(data, columns=indexx)
filename = os.path.join(
self.path, self.today + "_" + post_fix + ".xls")
# 今日涨停
if choice == 1:
df['今天的日期'] = self.today
df.to_excel(filename, encoding='gbk')
try:
df.to_sql(self.today + post_fix, engine, if_exists='fail')
except Exception as e:
logger.info(e)
# 昨日涨停
if choice == 2:
df = df.set_index('序号')
df['最大涨幅'] = df['最大涨幅'].map(lambda x: round(x * 100, 3))
df['最大跌幅'] = df['最大跌幅'].map(lambda x: round(x * 100, 3))
df['今日开盘涨幅'] = df['今日开盘涨幅'].map(lambda x: round(x * 100, 3))
df['昨日涨停强度'] = df['昨日涨停强度'].map(lambda x: round(x, 0))
df['今日涨停强度'] = df['今日涨停强度'].map(lambda x: round(x, 0))
try:
df.to_sql(self.today + post_fix, engine, if_exists='fail')
except Exception as e:
logger.info(e)
avg = round(df['今日涨幅'].mean(), 2)
current = datetime.datetime.now().strftime('%Y-%m-%d')
title = '昨天涨停个股今天{}\n的平均涨幅{}\n'.format(current, avg)
try:
sender_139(title, title)
except Exception as e:
print(e)
def monitor():
engine = get_engine('db_zdt')
table = '20180409zdt'
api = ts.get_apis()
df = pd.read_sql(table, engine, index_col='index')
price_list = []
percent_list = []
amplitude_list = []
start = datetime.datetime.now()
for i in df['代码'].values:
try:
curr = ts.quotes(i, conn=api)
last_close = curr['last_close'].values[0]
curr_price = curr['price'].values[0]
amplitude = round(((curr['high'].values[0] - curr['low'].values[0]) * 1.00 / last_close) * 100, 2)
# if last_close>=curr_price:
# print(i,)
# print(df[df['代码']==i]['名称'].values[0],)
# print( percent)
except Exception as e:
print('this point')
print(e)
api=ts.get_apis()
curr_price = 0
if last_close == 0:
percent = np.nan
percent = round((curr_price - last_close) * 100.00 / last_close, 2)
percent_list.append(percent)
price_list.append(curr_price)
amplitude_list.append(amplitude)
df['今日价格'] = price_list
df['今日涨幅'] = percent_list
df['今日振幅'] = amplitude_list
df['更新时间'] = datetime.datetime.now().strftime('%Y %m %d %H:%M%S')
end = datetime.datetime.now()
print('time use {}'.format(end - start))
df.to_sql(table + 'monitor', engine, if_exists='replace')
ts.close_apis(api)
def get_hist_data(code, name, start_data):
try:
# start_data = datetime.datetime.strptime(str(start_data), '%Y%m%d').strftime('%Y-%m-%d')
df = ts.bar(code, conn=conn, start_date=start_data, adj='qfq')
except Exception as e:
print(e)
return
hist_con = get_engine('history')
df.insert(1, 'name', name)
df = df.reset_index()
#print(df)
df2=pd.read_sql_table(code,hist_con,index_col='index')
try:
new_df = pd.concat([df,df2])
new_df = new_df.reset_index(drop=True)
new_df.to_sql(code, engine, if_exists='replace')
except Exception as e:
print(e)
return
def main():
obj = Filter_Stock()
now =datetime.datetime.now()
today = now.strftime("%Y%m%d")
tb_name = today+'zdt'
end = str(now.year)+'-'+str(now.month - 1)
df = obj.get_new_stock('2015',end)
code_list =df['code'].values
engine = get_engine('db_zdt')
zt_df = pd.read_sql(tb_name,engine,index_col='index')
zt_df['涨停强度']=map(lambda x:round(x,0),zt_df['涨停强度'])
ret_df = zt_df[zt_df['代码'].isin(code_list)]
if not ret_df.empty:
tb_name_save = today+'_cx'
excel_name = today+'_cx.xls'
ret_df.to_excel(excel_name,encoding='gbk')
ret_df.to_sql(tb_name_save,engine)
s= ret_df[['代码','名称','涨停强度','打开次数','第一次涨停时间','最后一次涨停时间']].to_string()
sendmail(s,today+'次新涨停')
def find_pattern(code):
pattern = ''
engine = get_engine('history')
try:
df = pd.read_sql(code, engine, index_col='index',)
except Exception as e:
print(e)
return
# print(df)
# for i in df['open'].astype('float').values:
# print(type(i))
# print(df['high'].values)
# print(df['low'].values)
# print(df['close'].values)
result = talib.CDLDARKCLOUDCOVER(df['open'].astype('float').values,
df['high'].astype('float').values,
df['low'].astype('float').values,
df['close'].astype('float').values)
df['CDL2CROWS']=result
if len(df[df['CDL2CROWS']!=0])!=0:
print(df[df['CDL2CROWS']!=0])
def current_data(self, adjust_no_use=True):
post_data = {
'btype': 'C',
'listed': 'Y',
'rp': '50',
'is_search': 'N',
}
js = self.download(self.url, data=post_data)
if not js:
return None
ret = js.json()
bond_list = ret.get('rows', {})
cell_list = []
for item in bond_list:
cell_list.append(pd.Series(item.get('cell')))
df = pd.DataFrame(cell_list)
# 下面的数据暂时不需要
if adjust_no_use:
# del df['active_fl']
# del df['adq_rating']
# del df['list_dt']
# del df['left_put_year']
# del df['owned']
# del df['put_dt']
# del df['real_force_redeem_price']
# del df['redeem_dt']
# del df['apply_cd']
# del df['force_redeem']
# del df['stock_id']
# del df['full_price']
# del df['pre_bond_id']
# del df['ytm_rt']
# del df['ytm_rt_tax']
# del df['repo_cd']
# del df['last_time']
# del df['pinyin']
# del df['put_real_days']
# del df['price_tips']
# del df['btype']
# del df['repo_valid']
# del df['repo_valid_to']
# del df['repo_valid_from']
# del df['repo_discount_rt']
# del df['adjust_tc']
# del df['cpn_desc']
# del df['market']
# del df['stock_net_value']
# 类型转换 部分含有%
df['premium_rt'] = df['premium_rt'].map(lambda x: float(x.replace('%', '')))
df['price'] = df['price'].astype('float64')
df['convert_price'] = df['convert_price'].astype('float64')
df['premium_rt'] = df['premium_rt'].astype('float64')
df['redeem_price'] = df['redeem_price'].astype('float64')
def convert_float(x):
try:
ret_float = float(x)
except:
ret_float = None
return ret_float
def convert_percent(x):
try:
ret = float(x) * 100
except:
ret = None
return ret
def remove_percent(x):
try:
ret = x.replace(r'%', '')
ret = float(ret)
except Exception as e:
ret = None
return ret
df['put_convert_price'] = df['put_convert_price'].map(convert_float)
df['sprice'] = df['sprice'].map(convert_float)
df['ration'] = df['ration'].map(convert_percent)
df['volume'] = df['volume'].map(convert_float)
df['convert_amt_ratio'] = df['convert_amt_ratio'].map(remove_percent)
df['ration_rt'] = df['ration_rt'].map(convert_float)
df['increase_rt']=df['increase_rt'].map(remove_percent)
df['sincrease_rt']=df['sincrease_rt'].map(remove_percent)
rename_columns = {'bond_id': '可转债代码', 'bond_nm': '可转债名称', 'price': '可转债价格', 'stock_nm': '正股名称',
'stock_cd': '正股代码',
'sprice': '正股现价',
'sincrease_rt': '正股涨跌幅',
'convert_price': '最新转股价', 'premium_rt': '溢价率', 'increase_rt': '可转债涨幅',
'put_convert_price': '回售触发价', 'convert_dt': '转股起始日',
'short_maturity_dt': '到期时间', 'volume': '成交额(万元)',
'redeem_price': '强赎价格', 'year_left': '剩余时间',
'next_put_dt': '回售起始日', 'rating_cd': '评级', 'issue_dt': '发行时间', 'redeem_tc': '强制赎回条款',
'adjust_tc': '下修条件', 'adjust_tip': '下修提示', 'put_tc': '回售', 'adj_cnt': '下调次数',
# 'ration':'已转股比例'
'convert_amt_ratio': '转债剩余占总市值比',
'curr_iss_amt': '剩余规模', 'orig_iss_amt': '发行规模',
'ration_rt': '股东配售率',
}
df = df.rename(columns=rename_columns)
df = df[list(rename_columns.values())]
df['更新日期'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
# dfx = df[['可转债代码', '可转债名称', '可转债涨幅', '可转债价格', '正股名称', '正股代码',
# '正股涨跌幅', '正股现价', '最新转股价', '溢价率', '评级',
# '转股起始日', '回售起始日', '回售触发价', '剩余时间',
# '更新日期']]
df = df.set_index('可转债代码', drop=True)
try:
df.to_sql('tb_jsl_{}'.format(datetime.datetime.now().strftime('%Y-%m-%d')), engine, if_exists='replace', dtype={'可转债代码': VARCHAR(10)})
engine2=get_engine('db_stock')
df.to_sql('tb_bond_jisilu'.format(datetime.datetime.now().strftime('%Y-%m-%d')), engine2, if_exists='replace', dtype={'可转债代码': VARCHAR(10)})
except Exception as e:
logger.info(e)
# -*-coding=utf-8-*- __author__ = 'Rocky' ''' email: [email protected] ''' import datetime import tushare as ts import os from setting import get_engine, get_mysql_conn import pandas as pd # pandas.set_option('display.max_rows',None) daily_engine = get_engine('daily') history_engine = get_engine('history') class Filter_Stock(): def __init__(self): current = os.getcwd() work_space = os.path.join(current, 'data') if os.path.exists(work_space) == False: os.mkdir(work_space) os.chdir(work_space) print os.getcwd() self.today = datetime.datetime.now().strftime("%Y-%m-%d") def get_location(self): df = ts.get_area_classified() print df # df.to_excel('location.xls') self.save_to_excel(df,'location.xls') def get_ST(self):
import pandas as pd
import talib
import tushare as ts
import matplotlib as mpl
from mpl_finance import candlestick2_ochl, volume_overlay
import matplotlib.pyplot as plt
from setting import get_engine
mpl.rcParams['font.sans-serif'] = ['simhei']
mpl.rcParams['axes.unicode_minus'] = False
from setting import llogger
filename=os.path.basename(__file__)
logger = llogger('log/'+filename)
engine = get_engine('db_stock', local=True)
base_info = pd.read_sql('tb_basic_info', engine, index_col='index')
def plot_stock_line(api,code, name, table_name, current, start='2017-10-01', save=False):
title = '{} {} {} {}'.format(current, code, name, table_name)
title = title.replace('*', '_')
if os.path.exists(title + '.png'):
return
if code is None and name is not None:
code = base_info[base_info['name'] == name]['code'].values[0]
# -*-coding=utf-8-*-
import re
import time
import datetime
import requests
import pandas as pd
from setting import get_engine, llogger, is_holiday,get_mysql_conn
import six
from send_mail import sender_139
from sqlalchemy import VARCHAR
import os
engine = get_engine('db_jisilu')
logger = llogger('log/'+'jisilu')
# 爬取集思录 可转债的数据
class Jisilu(object):
def __init__(self):
self.check_holiday()
# py2
if six.PY2:
self.timestamp = long(time.time() * 1000)
else:
self.timestamp = int(time.time() * 1000)
self.headers = {
'User-Agent': 'User-Agent:Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36',
'X-Requested-With': 'XMLHttpRequest'}
self.url = 'https://www.jisilu.cn/data/cbnew/cb_list/?___jsl=LST___t={}'.format(self.timestamp)
# -*-coding=utf-8-*-
# 获取 不同形态的k线
import random
import time
import tushare as ts
import pandas as pd
import os, datetime, math
import numpy as np
import logging
from setting import get_engine, MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, REDIS_HOST, get_mysql_conn
import redis
from threading import Thread
import MySQLdb
from collect_data import SaveData
engine = get_engine('history')
conn = ts.get_apis()
MYSQL_DB = 'history'
cursor = get_mysql_conn(MYSQL_DB).cursor()
# pd.set_option('display.max_rows', None)
class Kline():
def __init__(self):
logging.info('tushare version: {}'.format(ts.__version__))
path = os.path.join(os.getcwd(), 'data')
self.today_date = datetime.datetime.now().strftime('%Y-%m-%d')
logging.info(self.today_date)
if not os.path.exists(path):
os.mkdir(path)
os.chdir(path)
#-*-coding=utf-8-*-
import json
import tushare as ts
import pandas as pd
from setting import get_engine
import matplotlib.pyplot as plt
with open('codes.txt','r') as f:
# s= f.read()
codes=json.load(f)
stocks=codes.get('example1')
engine=get_engine('db_stock')
# for key in codes.get('example1').keys():
# print(key, codes.get('example1').get(key))
# df1=pd.DataFrame()
def pledge_info():
df=ts.stock_pledged()
df.to_sql('tb_pledged_base',engine,if_exists='replace')
df_list=[]
for stock in stocks:
df_list.append(df[df['code']==stock])
df=pd.concat(df_list)
# print(df)
df=df.reset_index(drop=True)
# print(df)
df= df.sort_values('p_ratio',ascending=False)
df['code']=df['code'].astype('str')
def __init__(self):
self.engine = get_engine('db_stock','local')
# -*-coding=utf-8-*-
import pandas as pd
import json
import pymongo
# from sqlalchemy import create_engine
import config
# 将mongo数据转移到mysql
from sqlalchemy import VARCHAR
from setting import get_engine
client = pymongo.MongoClient('10.18.6.46',port=27018)
# engine = create_engine('mysql+pymysql://root:{}@127.0.0.1:3306/db_rocky?charset=utf8'.format(config.localpassword))
engine = get_engine('spider',local='XGD')
def classic_method():
temp = []
start = 0
# 数据太大还是会爆内存,或者游标丢失
for i in doc.find().batch_size(500):
start += 1
del i['_id']
temp.append(i)
print(start)
print('start to save to mysql')
df = pd.read_json(json.dumps(temp))
df = df.set_index('poiid', drop=True)
df.to_sql('meituan', con=engine, if_exists='replace')
print('done')
def get_all_code():
engine = get_engine('db_stock')
df = pd.read_sql('basic_info',engine,index_col='index')
for code in df['code'].values:
find_pattern(code)
#-*-coding=utf-8
'''
可转债监控 python2
'''
# from __future__ import division
import tushare as ts
from setting import get_engine
import pandas as pd
import datetime,time
from numpy import inf
engine = get_engine('db_bond')
class ConvertBond():
def __init__(self):
self.conn=ts.get_apis()
self.available_bonds = pd.read_sql('tb_bond_jisilu', engine, index_col='index')['可转债代码'].values
self.allBonds=ts.new_cbonds(default=0,pause=2)
self.onSellBond=self.allBonds.dropna(subset=['marketprice'])
self.today=datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
self.total = self.onSellBond[self.onSellBond['bcode'].isin(self.available_bonds)]
def stockPrice(self,code):
stock_df = ts.quotes(code,conn=self.conn)
price = float(stock_df['price'].values[0])
print(code,price)
return price
def dataframe(self):
# -*-coding=utf-8-*-
# 获取 不同形态的k线
import random
import time
import tushare as ts
import pandas as pd
import os, datetime, math
import numpy as np
import logging
from setting import get_engine, MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, REDIS_HOST, get_mysql_conn
import redis
from threading import Thread
import MySQLdb
from collect_data import SaveData
engine = get_engine('history')
conn = ts.get_apis()
MYSQL_DB = 'history'
cursor = get_mysql_conn(MYSQL_DB).cursor()
# pd.set_option('display.max_rows', None)
class Kline():
def __init__(self):
logging.info('tushare version: {}'.format(ts.__version__))
path = os.path.join(os.getcwd(), 'data')
self.today_date = datetime.datetime.now().strftime('%Y-%m-%d')
logging.info(self.today_date)
if not os.path.exists(path):
os.mkdir(path)
os.chdir(path)
