def get_adj_price_data(stocks_index, timeToMarket):
	'''
	stocks_index ::  a string of the stock number like '002292'
	timeToMarket ::  a string of the IPO date like '20120229'

	return :: a DataFrame containing the adj. price data
	'''
	year = int( timeToMarket[0:4] )
	month = int( timeToMarket[4:6] )
	date = int( timeToMarket[6:8] )
	d0 = datetime.datetime(year, month, date)
	d1 = d0 + datetime.timedelta(days = 730)    # getting data of 2 years each time
	today = datetime.datetime.today()
	if (today - d1).days > 0:
		final = ts.get_h_data(stocks_index, autype='hfq', start=d0.strftime('%Y-%m-%d'), end=d1.strftime('%Y-%m-%d'))
	else:
		final = ts.get_h_data(stocks_index, autype='hfq', start=d0.strftime('%Y-%m-%d'), end=today.strftime('%Y-%m-%d'))
		return final

	while (today - d1).days > 0:
		d0 = d1 + datetime.timedelta(days = 1)
		d1 = d0 + datetime.timedelta(days = 730)
		if (today - d1).days > 0:
			tmp = ts.get_h_data(stocks_index, autype='hfq', start=d0.strftime('%Y-%m-%d'), end=d1.strftime('%Y-%m-%d'))
			try:
				final = tmp.append(final)
			except:
				pass
		else:
			tmp = ts.get_h_data(stocks_index, autype='hfq', start=d0.strftime('%Y-%m-%d'), end=today.strftime('%Y-%m-%d'))
			try:
				final = tmp.append(final)
			except:
				pass
			return final

def plotstock(name,realname=u'代码'):
    sdatestring=getstartdate()
    quotes = ts.get_h_data(name,start=sdatestring)
    quotes.index = quotes.index.astype('datetime64[D]')
    sh = ts.get_h_data('sh',start=sdatestring)
    sh.index = sh.index.astype('datetime64[D]')
    quotes.sort_index(inplace=True)
    sh.sort_index(inplace=True)
    opens = quotes["close"]
    opensh=sh["close"]
    means=sum(opens)/len(opens)
    meansh=sum(opensh)/len(opensh)
    scale=meansh/means
    plt.figure()
    plt.subplot(211)
    quotes["close"].plot()
    sh["close"]=sh["close"]/scale
    sh["close"].plot()
    plt.legend([realname+':'+name,u'上证综指'])
    plt.subplot(212)
    qs=quotes["close"][-10:]
    qs.plot()
    shs=sh["close"][-10:]
    means=sum(qs)/len(qs)
    meansh=sum(shs)/len(shs)
    shs=shs/meansh*means
    shs.plot()
    plt.show()

def load_data_from_tushare(stock_number, start_date, end_date):

    #stock_number = stock_number[2:]
    try:
        print "stock number for tushare {}".format(stock_number)
        raw_data = ts.get_h_data(stock_number, start = start_date, end = end_date, autype=None)
        raw_data = raw_data.sort()
        open_price = raw_data["open"]
        high_price = raw_data["high"]
        low_price = raw_data["low"]
        close_price = raw_data["close"]
        vol = raw_data["volume"]
        amount = raw_data["amount"]
        
        fuquan_data = ts.get_h_data(stock_number, start = start_date, end = end_date)
        fuquan_data = fuquan_data.sort()
        fuquan_close_price = fuquan_data["close"]
        fuquan_open_price = fuquan_data["open"]
        data = {"open price":open_price, "high price":high_price, "low price":low_price, "close price":close_price, "vol":vol, "amount": amount, "fuquan close price": fuquan_close_price, "fuquan open price": fuquan_open_price}
        dfdata = pd.DataFrame(data)
        return dfdata 
    except:
        data = {"open price":[], "high price":[], "low price":[], "close price":[], "vol":[], "amount": []}
        dfdata = pd.DataFrame(data)
        return dfdata

def initData(code,Index=False):
    """
    code 证券代码，LD  最后交易日，Index   指数 --> ds 数据集，LASTDATE 最后交易日
    检查最新数据，如果mysql DB没有，就下载，并写库。
    如果不是没有最新交易日数据（例如：没数据或停牌），返回None
    """
    b2.log('获取%s数据..........'%code)
    print('获取%s数据..........'%code)    
    if Index :      
        ds=ts.get_h_data(code,index=Index)
        #LASTDATE=ds.index[0].strftime('%Y-%m-%d')
        ds.to_sql('zs'+code,engine,if_exists='replace') 
    else:
        tbstatus=readydata('qfq%s'%code)
        if  tbstatus==1:
            b2.log('qfq%s......Data Ready'%code)
            sqlcmd='select * from qfq%s order by date'%code
            ds=sql.read_sql(sqlcmd,engine,index_col='date')                
        elif tbstatus==0:
            ds=ts.get_h_data(code).sort()
            ds.to_sql('qfq'+code,engine,if_exists='replace') 
            engine.execute('''insert into tb_stamp values ('qfq%s',curdate())'''%code)
            sqlcmd='''select count(*) from qfq%s where date=(select max(date) from zs159915)'''%(code)
            result=engine.execute(sqlcmd)
            if list(result)[0][0]==0:
                ds=None            
        else:
            ds=None
    return ds

def DownloadData(local, st, q):
	'''
	code is stockcode in string type
	st is time to market in string 'YYYY-MM-DD' type
	'''
	t = local.split('/')
	kind = t[1]
	code = t[2]

	if kind == 'cq':
		kind = None
	try:
		if len(st) > 8:
			df = ts.get_h_data(code, start=st, autype=kind)
		else:
			df = ts.get_h_data(code, autype=kind)

		if len(df) > 0:
			df = df.sort_index()
			df = df.sort_index(axis=1)
			filename = q.get()
			try:
				df.to_hdf(filename, local)
			except:
				store = pd.HDFStore(filename, mode='a')
				store[local] = df
				store.flush()
				store.close()

			q.put(filename)
			return [True, code]
	except:
		return [False, code]

 def download_stock_hist_price(self, code, start, end=None):
     print "downloading " + code
     if end is None:
         price = ts.get_h_data(code, start)
     else:
         price = ts.get_h_data(code, start, end)
     return price

def getdata(code):
	global lock
	dayhistory = ts.get_h_data(code, stock_timetomarket[code])
	if not dayhistory:
		dayhistory = ts.get_h_data(code, stock_timetomarket[code], index=True)
	dayhistory['code'] = code
	dayhistory.index = dayhistory.index.date
	print '\n code %s insert......\n' % code
	dayhistory.to_sql('h_data', engine, if_exists='append')

	def __init__(self, industry, benchmark, datatype, dic, start,end):
		global ms
		ms = ts.Master()
		self.stock = dic['stock']
		self.interest = dic['interest']
		self.industry = industry
		self.date = pd.date_range(start, end)
		self.days = len(self.date)
		self.beta, self.alpha, self.ir = None, None, None
		first_prev = ms.TradeCal(exchangeCD='XSHG',
			beginDate=start.replace("-",""),
			endDate=start.replace("-",""),
			field='calendarDate,prevTradeDate').prevTradeDate.iloc[0]

		#Build Benchmark
		self.benchmark = ts.get_hist_data(benchmark, first_prev, end)
		self.benchreturn = returns(
			self.benchmark[self.benchmark.index >= start].close,
			self.benchmark.close[:-1])
		self.benchreturn.index = pd.DatetimeIndex([pd.to_datetime(i) for i in self.benchreturn.keys()])
		
		#Datatype Effect
		if datatype == 'cap':
			#import pdb; pdb.set_trace()
			weight = self.stock.totalAssets[self.stock.industry == industry]
			weight = 1.0 * weight / weight.sum() #A series of cap weights
			d = 0
			for i in weight.index:
				a = ts.get_h_data(i, first_prev, end)
				a_close = a.close
				if len(a.index) != len(self.benchmark.index):
					a_close = equal_len(a.close, self.benchmark)
				return_of_i = returns(
					a_close[a_close.index >= start],
					a_close[a_close.index < end]
					) * weight.loc[i] #A series of cap-weighted return of the industry (index is date)
				d = d + return_of_i
		elif datatype == 'beta':
			index_list = self.stock.index[self.stock.industry == industry]
			total_beta, d = 0, 0
			for i in index_list:
				a = ts.get_h_data(i, first_prev, end)
				return_of_i = returns(
					a[a.index >= start].close,
					a.close[:-1])
				beta_i = 1.0 / beta(return_of_i, self.benchreturn)
				d += return_of_i * beta_i
				total_beta += beta_is
			d = 1.0 * d / total_beta
		else:
			raise KeyError('datatype can only be cap or beta')

		self.returns = d

def request_dayk(table, code, engine, start_date = '1990-01-01', end_date = '2050-01-01'):
    try:
        dayK_bfq = ts.get_h_data(code, start_date, end_date, None, retry_count=500)
        dayK_hfq = ts.get_h_data(code, start_date, end_date, 'hfq', retry_count=500)
        dayK_bfq['open_hfq'] = dayK_hfq['open']
        dayK_bfq['high_hfq'] = dayK_hfq['high']
        dayK_bfq['low_hfq'] = dayK_hfq['low']
        dayK_bfq['close_hfq'] = dayK_hfq['close']
        dayK_bfq['code'] = code
        dayK_bfq.to_sql(table, engine, if_exists='append', dtype={'date': Date})
        logging.info(str(code) + ', request_dayk success')
    except Exception:
        logging.error(str(code) + ' request_dayk failed on ' + str(threading.currentThread()))

def DownloadCqAll(code,st):
	'''
	code is stockcode in string type
	st is time to market in string 'YYYY-MM-DD' type
	'''
	if len(st)>2:
		df=ts.get_h_data(code,start=st,autype=None,retry_count=5,pause=1)
		df=df.sort_index(ascending=1)
	else:
		df=ts.get_h_data(code,autype=None,retry_count=5,pause=1)
	#print code+':'+st+' finished!'
	df=df.sort_index(axis=0)
	df=df.sort_index(axis=1)
	return [code,df]

def load_data_from_tushare_real_time(stock_number, start_date):

    try:
        print "stock number for tushare {}".format(stock_number)
        raw_data = ts.get_h_data(stock_number, start = start_date, autype=None)
        raw_data = raw_data.sort()
        open_price = list(raw_data["open"].values)
        high_price = list(raw_data["high"].values)
        low_price = list(raw_data["low"].values)
        close_price = list(raw_data["close"].values)
        vol = list(raw_data["volume"].values)
        amount = list(raw_data["amount"].values)
        f = lambda x:str(x).split(" ")[0]
        date = map(f,list(raw_data.index))

        fuquan_data = ts.get_h_data(stock_number, start = start_date)
        fuquan_data = fuquan_data.sort()
        fuquan_close_price = list(fuquan_data["close"].values)
        fuquan_open_price = list(fuquan_data["open"].values)

        o, h, l, c, v, a, d = get_sina_data(stock_number)
        open_price.append(o) 
        close_price.append(c) 
        fuquan_close_price.append(c) 
        fuquan_open_price.append(o) 
        high_price.append(h) 
        low_price.append(l)
        vol.append(v)
        amount.append(a)
        date.append(d) 

        ff = lambda x:float(x)
        open_price = map(ff,open_price)
        high_price = map(ff,high_price)
        low_price = map(ff,low_price)
        close_price = map(ff,close_price)
        fuquan_close_price = map(ff,fuquan_close_price)
        fuquan_open_price = map(ff,fuquan_open_price)
        vol = map(ff,vol)
        amount = map(ff,amount)
        

        data = {"open price":open_price, "high price":high_price, "low price":low_price, "close price":close_price, "vol":vol, "amount": amount, "date": date, "fuquan close price": fuquan_close_price, "fuquan open price": fuquan_open_price}
        dfdata = pd.DataFrame(data)
        return dfdata
    except:
        data = {"open price":[], "high price":[], "low price":[], "close price":[], "vol":[], "amount": []}
        dfdata = pd.DataFrame(data)
        return dfdata

 def load_data(self, pcontract, dt_start=None, dt_end=None):
     dt_start = _process_dt(dt_start)
     if not dt_start: dt_start = _VERY_EARLY_START
     dt_end = _process_dt(dt_end)
     data = ts.get_h_data(pcontract.contract.code,
                          start=dt_start, end=dt_end)
     return _process_tushare_data(data.iloc[::-1])

    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 append_days(self,stock, start, end):
     '''
     添加stock,指定时间范围内的数据
     '''
     data = ts.get_h_data(stock,start=start,end=end)
     data = data.sort_index(ascending=True)  
     data.to_sql('day_'+stock, self.engine,if_exists='append')

def get_stock_data(stock_list, start_date):
    stock_data_list = []
    for stockcode in stock_list:
        stock_data_list.append({})
        stock_data_list[-1]['code'] = stockcode
        stock_data_list[-1]['data']=ts.get_h_data(code = stockcode, start = start_date).sort(ascending = True)
    return stock_data_list

def getDayLine(from_num, to_num,startDay = 0, endDay = time.strftime('%Y-%m-%d', time.localtime())):

    global df_base, control_num, engine
    for row_index, row in df_base.iterrows():
        try:
            if control_num < from_num:
                pass
            elif control_num == to_num:
                break
            else:
        
                stocknum = row_index
                if startDay == 0: #download the day line data from the beginning
                    
                    timeToMarket = df_base.ix[stocknum]['timeToMarket']
                    
                    startDay = str(timeToMarket)
                    startDay = startDay[:4] + '-' + startDay[4:6] + '-' + startDay[6:8]
                    
                    
                qfq_history= ts.get_h_data(stocknum, start = startDay, end = endDay, retry_count=10)
                qfq_history.insert(0,'stocknum',stocknum)
                qfq_history.to_sql('qfq_day',engine,if_exists='append')
            control_num += 1
        
    
        except:
            s = stocknum +'\n'
            f = open('qfq_err' + endDay, 'a')
            f.write(s)
            f.close()
            pass

def recoveDayline(startDay, endDay):
    global df_base, control_num, engine
    if os.path.exists('qfq_err' + endDay):
        f = open('qfq_err' + endDay)
        lines = f.readlines()
        lineNos = range(len(lines))
        
        for lineNo in lineNos:
            try:
                
                line = lines[0]
                stocknum = line[:6]
                if startDay == 0:
                    timeToMarket = df_base.ix[stocknum]['timeToMarket']
                    startDay = str(timeToMarket)
                    startDay = startDay[:4] + '-' + startDay[4:6] + '-' + startDay[6:8]
                qfq_history = ts.get_h_data(stocknum, start = startDay, end = endDay, retry_count=10)
                qfq_history.insert(0,'stocknum',stocknum)
                qfq_history.to_sql('qfq_day',engine,if_exists='append')
                del lines[0] #delect the recovered data
                print(lineNo)
                
            except:
                f.close()
                f = open('qfq_err' + endDay, 'w')
                f.writelines(lines)
                f.close()
        if os.path.getsize('qfq_err' + endDay) == 0: 
            os.remove('qfq_err' + endDay)

def download_kline_source_select(code, date_start, date_end):
    try:
        if len(code)==6:
            df_qfq = ts.get_h_data(str(code), start=date_start, end=date_end) # 前复权
        else:
            # import pandas.io.data as web
            # price = web.get_data_yahoo('000001.SS', '1991-07-15')

            df_qfq = ts.get_hist_data(str(code), start=date_start, end=date_end)
        if len(df_qfq)==0:
            return None
        #if df_qfq is None:
        #df_qfq = ts.get_hist_data(code, start=date_start, end=date_end)
        # df_qfq = df_qfq[::-1]
        df_qfq[KEY_CODE] = code
        df_qfq[KEY_DATE] = df_qfq.index


        columns = [KEY_CODE, KEY_DATE, KEY_OPEN, KEY_HIGH, KEY_CLOSE, KEY_LOW, KEY_VOLUME]
        df_qfq = df_qfq[columns]

        print df_qfq.head()

        return df_qfq
    except Exception as e:
        print str(e)

def getHistoryData(datapath, sid, start=None, end=None):
    '''

    :param datapath:
    :param sid:
    :param start:
    :param end:
    :return:
    '''
    DAYFORMAT = '%Y-%m-%d'
    MAXINTERVAL = 365 * 3
    totalData = []
    if end == None:
        end = datetime.strftime(datetime.today(), DAYFORMAT)

    endday = end

    if (datetime.strptime(end, DAYFORMAT) - datetime.strptime(start, DAYFORMAT)).days > MAXINTERVAL:
        startday = datetime.strftime(datetime.strptime(endday, DAYFORMAT) - timedelta(days=MAXINTERVAL), DAYFORMAT)
    else:
        startday = start
    while ( datetime.strptime(startday, DAYFORMAT) >= datetime.strptime(start, DAYFORMAT)):
        # 分成三年三年这种调用方式，然后汇总
        temp = tushare.get_h_data(sid, start=startday, end=endday)
        totalData.append(temp)
        endday = startday
        startday = datetime.strftime(datetime.strptime(endday, DAYFORMAT) - timedelta(days=MAXINTERVAL), DAYFORMAT)

    AllData = pandas.concat(totalData)
    filenameX = lambda x: '_'.join(x)
    filename = os.path.join(datapath, filenameX([sid, start, end])) + '.csv'
    AllData.to_csv(filename.lower())

def fetchStockData(code, output_csv=None):
    StockDf = ts.get_h_data(code)
    StockDf = StockDf.sort_index(axis=0, ascending=True)
    #adding EMA feature
    StockDf['ema'] = StockDf['close']
    StockDf['rise'] = StockDf['close']
    DfLen = len(StockDf.index)
    EMA = 0;
    RISE = 0;
    for n in range(0,DfLen):
        idx = n
        Close = StockDf.ix[idx, 'close']
        if(n==0):
            EMA = Close
            RISE = 0
        else:
            EMA = StockDf.ix[idx-11, 'ema']
            EMA = ((n-1)*EMA + 2*Close)/(n+1)
            CloseP = StockDf.ix[idx-1, 'close']
            RISE = (Close - CloseP)/CloseP
        
        StockDf.ix[idx,'ema'] = EMA
        StockDf.ix[idx,'rise'] = RISE

    if(output_csv != None):
        StockDf.to_csv(output_csv)
        
    return StockDf