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 _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 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):