def filter_stock_by_average_pe(min, max):
    path=os.path.join(current_folder,'3年平均利润.csv')
    if not os.path.exists(path): #没有就生成3年平均利润列表
        calcu_all_stocks_3year_average_profit(calcu_average_profit_end_year)

    gplb=pd.read_csv(path,encoding = "gbk",index_col=0)

    now=datetime.now()
    today=str(now)[:11]
    current_sec=str(now)[:18].replace('-','_').replace(':','_')
    price_path=os.path.join(current_folder,today+'股票价格.csv')
    if not os.path.exists(price_path):
        ts.get_today_all().set_index('code').to_csv(price_path)

    current_price=pd.read_csv(price_path,encoding = "gbk",index_col=0)
    current_price= current_price[['trade']]
    current_price.columns=['价格']
    gplb=gplb[['名字','行业','地区','流通股本','总股本(万)','总资产(万)','流动资产','固定资产','每股净资','市净率','上市日期','平均利润']]
    data=pd.merge(gplb,current_price,left_index=True, right_index=True)
    data['平均市盈率']=data['总股本(万)']*data['价格']/data['平均利润']
    data = data[data['平均市盈率'] < max]
    data = data[data['平均市盈率'] > min]
    data['平均市盈率']=data['平均市盈率'].round()
    data['平均利润']=data['平均利润'].round()
    data['市净率']=data['市净率'].round(1)
    data['固定资产']=data['固定资产'].round()
    data['流动资产']=data['流动资产'].round()
    data['总股本(万)']=data['总股本(万)'].round()
    data['流通股本']=data['流通股本'].round()
    average_pe_file = os.path.join(current_folder, today + '3年平均市盈率在%s和%s之间的公司.xlsx' % (min, max))
    data.to_excel(average_pe_file)

def FindGoodStock():
    pdReportToday = ts.get_today_all()
#     print(type(pdReportToday), pdReportToday)

    for index,row in pdReportToday.iterrows():
        delta = row["open"] - row["trade"]
        stockId = row["code"]
#         print(stockId, "--------------", delta)
        try:
            try:
                retVal = PrintOpenLow([stockId])
                if retVal == None:
                    print("Network data 1")
                    retVal = CheckLowDiffByTushare( stockId )
            except:
                print("Network data 2")
                retVal = CheckLowDiffByTushare( stockId )
                
            if delta > retVal[0]:
                print(stockId, "delta:", delta, "now:", row["trade"], retVal)
                if delta > retVal[0] +  0.5*retVal[1]:
                    print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
                elif delta > retVal[0] +  retVal[1]:
                    print("--------------------------------------------------------------------------------------------------")
#                 CheckLowDiffByTushare( stockId, bShow = False)
        except:
            print(stockId, "Exception")            

    def get_url_data_(self):

        # 从 tushare.org 获取股票市场的代码列表
        code_list_ = pandas.DataFrame((tushare.get_today_all())['code'])

        # 排序
        code_list_ = code_list_.sort(columns='code', ascending=True)

        # 增加一自然数列做为 index
        code_list_['index'] = pandas.DataFrame([i for i in range(0, len(code_list_))], code_list_.index)
        code_list_.reindex(index=code_list_['code'])

        # 写库
        code_list_.to_sql('code_list_', self.engine_, if_exists='replace', index=True, index_label='index')

        # 把 index 设为主键
        self.engine_.connect().execute('alter table testdb.code_list_ add primary key(`index`)')

        # 根据上面股票列表逐个获取个股数据
        for i in range(0, len(code_list_)):
            # 取的是已经复权的数据
            stock_data_ = tushare.get_h_data(code_list_['code'][i])

            # 因为获取的数据以 date 为 index，但是写库时不能把 date 当 index 写入，所以复制该列
            stock_data_['date'] = pandas.Series(stock_data_.index, stock_data_.index)
            stock_data_ = stock_data_.sort_values(by='date', ascending=True)
            stock_data_['index'] = pandas.DataFrame([i for i in range(0, len(stock_data_))], stock_data_.index)
            stock_data_.to_sql(code_list_['code'][i], self.engine_, if_exists='replace', index=True,
                               index_label='index')
            self.engine_.connect().execute('alter table testdb.' + code_list_['code'][i] + ' add primary key(`index`)')

def get_day_list():
    print "tushare version:" + ts.__version__
    '''
    获取当天的涨幅排行榜
    code
    name
    changepercent 涨跌幅
    trade:现价
    open:
    high:
    low:
    settlement:昨日收盘价
    volume:
    turnoverratio:
    '''
    all_data = ts.get_today_all()
    if all_data is None:
        print 'None data fetched'
        return

    trade_date = datetime.datetime.today()
    if int(trade_date.strftime("%w")) == 0:
        # sunday
        trade_date = trade_date + datetime.timedelta(days=-2)
    elif int(trade_date.strftime("%w")) == 6:
        # saturday
        trade_date = trade_date + datetime.timedelta(days=-1)

    all_data_file_name = "allData" + trade_date.strftime("%Y-%m-%d") + ".csv"
    output_all_data_file_dir = "e:/data/" + all_data_file_name
    all_data.to_csv(output_all_data_file_dir, encoding='gbk')
    # output_all_data_file_dir = "e:/data/" + all_data_file_name
    # up_data = pd.read_csv(output_all_data_file_dir, encoding='gbk', index_col=0, dtype={'code': str})
    print all_data['code']

def get_stock_price(code, include_realtime_price):
    """
    获取个股股价
    :param code: 股票代码
    :param include_realtime_price: 是否含实时股价
    :return:
    """

    # 获取历史股价
    df = ts.get_hist_data(code)
    df = df[['close']]
    df['date'] = df.index

    if include_realtime_price:
        df_today = ts.get_today_all()
        df_code = df_today[df_today['code']==code]
        df_code = df_code[['trade']]
        df_code['date'] = GetNowDate()
        df_code.rename(columns={'trade': 'close'}, inplace=True)
        df = pd.concat([df, df_code], ignore_index=True)

    df.sort(columns='date', inplace=True)
    df = df.drop_duplicates(['date'])
    df.index = range(len(df))
    print '\n'
    # print df.head()
    print df.tail()
    return df

 def daily_market():
     df = ts.get_today_all()
     try:
         df.to_sql(SaveData.today,SaveData.daily_engine,if_exists='replace')
     except Exception as e:
         logger.info(e)
     logger.info("Save {} data to MySQL".format(SaveData.today))

def today_df_filter0(today_df):
    #"""
    today_df = ts.get_today_all()
    today_df = today_df[today_df.amount>0]
    today_df_high_open = today_df[today_df.open>today_df.settlement*1.005]
    all_trade_code = today_df['code'].values.tolist()
    all_a_code = ps.get_all_code(hist_dir="C:/中国银河证券海王星/T0002/export/")
    all_stop_code = list(set(all_a_code).difference(set(all_trade_code)))
    print('\n')
    print('all_stop_code=%s' % all_stop_code)
    print(len(all_stop_code))
    high_open_code_str = today_df_high_open['code'].values.tolist()
    print('all_trade_code = %s'%all_trade_code)
    print(len(all_trade_code))
    print('today_df_high_open = %s'%high_open_code_str)
    today_df['star'] = ((today_df['trade']-today_df['open'])/(today_df['high']-today_df['low'])).round(3)
    today_df['star_h'] = np.where(today_df['star']>=0, ((today_df['high']-today_df['trade'])/(today_df['high']-today_df['low'])).round(3),
                                  ((today_df['high']-today_df['open'])/(today_df['high']-today_df['low'])).round(3))
    today_df['atr'] = np.where((today_df['high']-today_df['low'])<(today_df['high']-today_df['settlement']),
                                today_df['high']-today_df['settlement'],today_df['high']-today_df['low']) #temp_df['close'].shift(1)-temp_df['low'])
    today_df['atr'] = np.where(today_df['atr']<(today_df['settlement']-today_df['low']),
                             (today_df['settlement']-today_df['low']),today_df['atr'])
    today_df['atr_r'] = ((today_df['atr']/today_df['settlement']).round(3))*100.0
    today_df['star_chg'] = today_df['star'] * today_df['changepercent']
    #del today_df['atr']
    describe_df = today_df.describe().round(3)
    #print(type(describe_df))
    lt_describe = describe_df.loc['25%']#.iloc[3].values
    mean_chg = describe_df.loc['mean','changepercent']
    most_chg = describe_df.loc['75%','changepercent']
    least_chg = describe_df.loc['25%','changepercent']
    most_atr_r = describe_df.loc['75%','atr_r']
    least_atr_r = describe_df.loc['25%','atr_r']
    print(mean_chg,most_chg,least_chg,most_atr_r,least_atr_r)
    print(describe_df)
    great_rate = 2.0
    gt_today_df = today_df[today_df.changepercent> great_rate]
    great_atd_df = today_df[today_df['atr_r']>11]
    stock_basic_df=ts.get_stock_basics()
    #stock_basic_df['outstanding'] = stock_basic_df['outstanding'] * 0.0001
    #stock_basic_df['totals'] = stock_basic_df['totals'] * 0.0001
    lt_outstanding_df = stock_basic_df[stock_basic_df.outstanding<100000] #流通股本小于10亿
    print(lt_outstanding_df)
    
    today_df['real'] = 0.00000001 *today_df['amount']/today_df['turnoverratio']
    min_atr_df = today_df[today_df.real<10.0]
    min_atr_df = min_atr_df[min_atr_df.atr_r<least_atr_r]
    lt_outstanding_df_list = lt_outstanding_df.index.values.tolist()
    print(lt_outstanding_df_list)
    min_atr_df = min_atr_df.set_index('code')
    min_atr_df_list = min_atr_df.index.values.tolist()
    print(min_atr_df_list)
    inter_list = list(set(min_atr_df_list).intersection(set(lt_outstanding_df_list)))
    print(len(inter_list))
    filter_df = min_atr_df[min_atr_df.index.isin(inter_list)]
    #print(filter_df)
    #print(type(filter_df))
    min_atr_df = filter_df.sort_values(axis=0, by='atr_r', ascending=True)
    min_atr_df = filter_df.sort_values(axis=0, by='star', ascending=False)
    print(min_atr_df)

    def GetAllTodayData(self):
        #存储每天 涨幅排行  榜,避免每次读取耗时过长
        filename=self.today+'_all_.xls'
        #放在data文件夹下
        filename=os.path.join(self.path,filename)
        if not os.path.exists(filename):
            re_try=5
            while re_try>0:
                try:
                    self.df_today_all=ts.get_today_all()
                #过滤停牌的
                # self.df_today_all.drop(self.df_today_all[self.df_today_all['turnoverratio']==0].index,inplace=True)
                #实测可用，删除的方法
                #n1=self.df_today_all[self.df_today_all['turnoverratio']==0]
                #n2=self.df_today_all.drop(n1.index)
                #print n2
                # print self.df_today_all
                    break
                except Exception,e:
                    re_try=re_try-1
                    time.sleep(5)
            if len(self.df_today_all)!=0:

                self.df_today_all.to_excel(filename,sheet_name='All')
            else:
                self.df_today_all=None

	def fetch_classification(self):
		# 数据来源自新浪财经的行业分类/概念分类/地域分类
		print( "Trying: get_today_all" )
		today_all = ts.get_today_all() #一次性获取今日全部股价
		set_today_all = set(today_all.T.values[0])

		print( "Trying: get_industry_classified" )
		industry_classified = ts.get_industry_classified()
		set_industry_classified = set(industry_classified.T.values[0])

		print( "Trying: get_area_classified" )
		area_classified = ts.get_area_classified()
		set_area_classified = set(area_classified.T.values[0])

		print( "Trying: get_concept_classified" )
		concept_classified = ts.get_concept_classified()
		set_concept_classified = set(concept_classified.T.values[0])

		print( "Trying: get_sme_classified" )
		sme_classified = ts.get_sme_classified()
		set_sme_classified = set(sme_classified.T.values[0])

		return [
					today_all
				,	set_today_all
				,	industry_classified
				,	set_industry_classified
				,	area_classified
				,	set_area_classified
				,	concept_classified
				,	set_concept_classified
				,	sme_classified
				,	set_sme_classified
				]

def get_break_bvps():
    base_info = ts.get_stock_basics()
    current_prices = ts.get_today_all()


    current_prices[current_prices['code'] == '000625']['trade'].values[0]
    base_info.loc['000625']['bvps']

def get_all_top():
    gold = {}
    goldl = []
    df = ts.get_today_all()
    top = df[df['changepercent'] > 6]
    print "top:", len(top['code'])
    for code in top['code']:
        ave=sl.get_today_tick_ave(code)

    def real_time_data_all_stock(self):
        now_str = sutils.get_datetime_str()
        now_data = ts.get_today_all()
        now_data_len = len(now_data)
        logger.debug('The length of real time data is: %d' % now_data_len)

        now_data['time'] = pd.Series(np.array([now_str] * now_data_len))
        mutils.to_sql(now_data, table='real_time_data', engine)

def gsz():
    hq = ts.get_today_all()
    hq['trade'] = hq.apply(lambda x: x.settlement if x.trade == 0 else x.trade, axis=1)
    basedata = stock_info[['outstanding', 'totals', 'reservedPerShare', 'esp']]
    hqdata = hq[['code', 'name', 'trade', 'mktcap', 'nmc']]
    hqdata = hqdata.set_index('code')
    data = basedata.merge(hqdata, left_index=True, right_index=True)
    print(data.head(10))

def save_excel():
    df = ts.get_today_all()
    df.to_excel('1.xls', sheet_name='all_stock')
    df2 = ts.get_hist_data('300333')
    df2.to_excel('1.xls', sheet_name='basic_info')
    df.ExcelWriter
    out = pd.ExcelWriter("2.xls")
    df.to_excel()

def get_day_all():
    try:
        #print('Get Day All...')
        df = ts.get_today_all()
    except:
        print('ts.get_today_all failed.')
        sys.exit(1)
    return(df)

def get_stop_trade_symbol():
    today_df = ts.get_today_all()
    today_df = today_df[today_df.amount>0]
    today_df_high_open = today_df[today_df.open>today_df.settlement*1.005]
    all_trade_code = today_df['code'].values.tolist()
    all_a_code = pds.get_all_code(hist_dir="C:/中国银河证券海王星/T0002/export/")
    #all_a_code = pds.get_all_code(hist_dir="C:/hist/day/data/")
    all_stop_codes = list(set(all_a_code).difference(set(all_trade_code)))
    return all_stop_codes

def getOutStandingStock():
    df_stk = ts.get_stock_basics().reset_index()

    df_trans = ts.get_today_all()
    df_result = pd.merge(df_stk,df_trans,on = 'code')
    df_result = df_result.loc[:,['code','name_x','industry','outstanding','trade']]
    df_result['outstanding_amt'] =  df_result['outstanding'] * df_result['trade'] * 10000    
    df_result = df_result[df_result.outstanding_amt < 5000000000]
    return df_result

def update_stocks_data(state, all_stocks):
    if not state:
        try:
            all_stocks = ts.get_today_all()
            state = True
        except Exception, e:
            print e
            all_stocks = None
            state = False

def get_all_trading_codes():
    '''
        获取A股市场全部的交易代码和交易名称 
    '''
    all_data = ts.get_today_all()
    current_dir = os.path.dirname(__file__)
    ab_path = os.path.join( current_dir, "..\\", "..\\" )
    meta_data_path =os.path.abspath( ab_path ) + "\\meta_data\\"
    all_data[['code', 'name']].to_csv(meta_data_path+"meta_codes.csv", encoding='utf-8', index=False)

 def get_day_rise(self):
     if not os.path.isfile(self.appPath + '/' + self.lastTradeDate + '_Datas.csv'):
         try:
             codeList = ts.get_today_all()
         except Exception:
             return []
         codeList.to_csv(self.appPath + '/' + self.lastTradeDate + '_Datas.csv', encoding='utf8')
     else:
         codeList = pd.read_csv(self.appPath + '/' + self.lastTradeDate + '_Datas.csv', encoding='utf8',dtype={'code': str})
     return codeList