def doFetchTrade(client,sessHandle,symbols,date,starttime,endtime): execReq=TExecuteStatementReq() #swBegin=Time.time() execReq.sessionHandle=sessHandle q="select * from marketdata2.trades where day = "+date.strftime('%Y%m%d') p='' if len(symbols)!=0 and len(symbols) < 400: for s in symbols: p=p+"','"+s p="("+p[2:]+"')" q=q+ ' and symbol in '+p if starttime !='': q=q + " and time >= " + str(timeconvert2(starttime)) if endtime !='': q=q + " and time <= " + str(timeconvert2(endtime)) q = q + " order by seqno limit " + str(Maxrow); print "run the query, date: "+ date.strftime('%Y-%m-%d') + ". Please wait!" #print q execReq.statement=q execResp = client.ExecuteStatement(execReq) stmtHandle = execResp.operationHandle fetchReq=TFetchResultsReq() fetchReq.operationHandle=stmtHandle fetchReq.orietntion=TFetchOrientation.FETCH_FIRST fetchReq.maxRows=Maxrow count=0 bmore=True #swEnd=Time.time() while bmore: resultsResp = client.FetchResults(fetchReq) resultSet = resultsResp.results count=len(resultSet.rows) bmore=resultsResp.hasMoreRows; for row in resultSet.rows: symExi = False strSym = row.colVals[1].stringVal.value for sym in symbols: if sym == strSym or len(symbols) < 400: symExi = True break if (symExi): trd=DB.Trade() trd.Date(row.colVals[18].i32Val.value) trd.Time(timeFormat(row.colVals[15].i32Val.value)) trd.Open(row.colVals[8].doubleVal.value) trd.Symbol(row.colVals[1].stringVal.value) trd.Exg(row.colVals[17].i32Val.value) trd.High(row.colVals[9].doubleVal.value) trd.Last(row.colVals[11].doubleVal.value) trd.Low(row.colVals[10].doubleVal.value) trd.Price(row.colVals[2].doubleVal.value) trd.Size(row.colVals[6].i32Val.value) trd.Volume(row.colVals[13].i32Val.value) DB.OnTrade(trd) fetchReq.orietntion=TFetchOrientation.FETCH_NEXT #print '100%' #if(count!=1000): #break print 'done'
def doFetchQuote(client,sessHandle,symbols,date,starttime,endtime): execReq=TExecuteStatementReq() execReq.sessionHandle=sessHandle q="select * from marketdata.quotes where day = "+date.strftime('%Y%m%d') p='' if len(symbols)!=0 and len(symbols) < 400: for s in symbols: p=p+"','"+s p="("+p[2:]+"')" q=q+ ' and symbol in '+p if starttime !='': q=q + " and time >= " + str(timeconvert2(starttime)) if endtime !='': q=q + " and time <= " + str(timeconvert2(endtime)) q = q + " order by seqno limit " + str(Maxrow); print "run the query, date: "+ date.strftime('%Y-%m-%d') + ". Please wait!" execReq.statement=q execResp = client.ExecuteStatement(execReq) stmtHandle = execResp.operationHandle fetchReq=TFetchResultsReq() fetchReq.operationHandle=stmtHandle fetchReq.orietntion=TFetchOrientation.FETCH_FIRST fetchReq.maxRows=Maxrow count=0 bmore=True while bmore: resultsResp = client.FetchResults(fetchReq) resultSet = resultsResp.results count=len(resultSet.rows) bmore=resultsResp.hasMoreRows; for row in resultSet.rows: symExi = False strSym = row.colVals[2].stringVal.value for sym in symbols: if sym == strSym or len(symbols) < 400: symExi = True break if (symExi): quo=DB.Quote() quo.Date(row.colVals[19].i32Val.value) quo.Time(timeFormat(row.colVals[16].i32Val.value)) quo.Symbol(row.colVals[2].stringVal.value) quo.Ask(row.colVals[5].doubleVal.value) quo.Ask_size(row.colVals[3].i32Val.value) quo.Best_ask(row.colVals[8].doubleVal.value) quo.Best_ask_exg(row.colVals[12].i32Val.value) quo.Best_ask_size(row.colVals[11].i32Val.value) quo.Best_bid(row.colVals[9].doubleVal.value) quo.Best_bid_exg(row.colVals[13].i32Val.value) quo.Best_bid_size(row.colVals[12].i32Val.value) quo.Bid(row.colVals[6].doubleVal.value) quo.Bid_size(row.colVals[4].i32Val.value) DB.OnQuote(quo) fetchReq.orietntion=TFetchOrientation.FETCH_NEXT print 'done'
def doFetchNews(client,sessHandle,symbols,date,starttime,endtime): execReq=TExecuteStatementReq() #swBegin=Time.time() execReq.sessionHandle=sessHandle q="select * from marketdata.news where day = "+date.strftime('%Y%m%d') if starttime !='': q=q + " and msofday >= " + str(timeconvert2(starttime)) if endtime !='': q=q + " and msofday <= " + str(timeconvert2(endtime)) p='' if len(symbols)!=0 and len(symbols) < 400: for s in symbols: p=p+"','"+s p="("+p[2:]+"')" q=q+ ' and symbol in '+ p print "run the query, date: "+ date.strftime('%Y-%m-%d') + ". Please wait!" #print q execReq.statement=q execResp = client.ExecuteStatement(execReq) stmtHandle = execResp.operationHandle fetchReq=TFetchResultsReq() fetchReq.operationHandle=stmtHandle fetchReq.orietntion=TFetchOrientation.FETCH_FIRST fetchReq.maxRows=Maxrow count=0 bmore=True #swEnd=Time.time() while bmore: resultsResp = client.FetchResults(fetchReq) resultSet = resultsResp.results count=len(resultSet.rows) bmore=resultsResp.hasMoreRows for row in resultSet.rows: symExi = False strSym = row.colVals[1].stringVal.value for sym in symbols: if sym == strSym or len(symbols)<400: symExi = True break if (symExi): news=DB.News() news.Date(row.colVals[9].i32Val.value) news.Time(timeFormat(row.colVals[2].i32Val.value)) news.Symbol(row.colVals[1].stringVal.value) news.Seqno(row.colVals[0].i32Val.value) news.Category(row.colVals[3].stringVal.value) news.Source(row.colVals[4].stringVal.value) news.Headline(row.colVals[5].stringVal.value.replace("\"","")) news.Resourceid(row.colVals[6].stringVal.value) news.Story(row.colVals[7].stringVal.value.replace("\"","")) news.Tags(row.colVals[8].stringVal.value.replace("\"","")) DB.OnNews(news) fetchReq.orietntion=TFetchOrientation.FETCH_NEXT print 'done'
def doFetchDailyNo(client,sessHandle,symbols,startdate,enddate): execReq=TExecuteStatementReq() execReq.sessionHandle=sessHandle q="select * from marketdata.daily where day >= " + startdate.strftime('%Y%m%d') + " and day <= "+ enddate.strftime('%Y%m%d') p='' if len(symbols)!=0 and len(symbols) < 400: for s in symbols: p=p+"','"+s p="("+p[2:]+"')" q=q+ ' and symbol in '+p q = q + " order by day limit " + str(Maxrow); print "run the query, begindate: "+ startdate.strftime('%Y-%m-%d') + ", enddate: "+enddate.strftime('%Y-%m-%d')+". Please wait!" execReq.statement=q execResp = client.ExecuteStatement(execReq) stmtHandle = execResp.operationHandle fetchReq=TFetchResultsReq() fetchReq.operationHandle=stmtHandle fetchReq.orietntion=TFetchOrientation.FETCH_FIRST fetchReq.maxRows=Maxrow count=0 bmore=True while bmore: resultsResp = client.FetchResults(fetchReq) resultSet = resultsResp.results count=len(resultSet.rows) bmore=resultsResp.hasMoreRows; for row in resultSet.rows: symExi = False strSym = row.colVals[1].stringVal.value for sym in symbols: if sym == strSym or len(symbols) < 400: symExi = True break if (symExi): dai=DB.Daily() dai.Date(row.colVals[0].i32Val.value) dai.Symbol(row.colVals[1].stringVal.value) dai.High(row.colVals[3].doubleVal.value) dai.Low(row.colVals[4].doubleVal.value) dai.Close(row.colVals[5].doubleVal.value) dai.Open(row.colVals[2].doubleVal.value) dai.Volume(row.colVals[6].i32Val.value) DB.OnDaily(dai) fetchReq.orietntion=TFetchOrientation.FETCH_NEXT #print '100%' #if(count!=1000): #break print 'done'
def doFetchExgprints(client,sessHandle,symbols,date): execReq=TExecuteStatementReq() #swBegin=Time.time() execReq.sessionHandle=sessHandle q="select * from marketdata.exgprints where day = "+date.strftime('%Y%m%d') p='' if len(symbols)!=0 and len(symbols) < 400: for s in symbols: p=p+"','"+s p="("+p[2:]+"')" q=q+ ' and symbol in '+p print "run the query, date: "+ date.strftime('%Y-%m-%d') + ". Please wait!" #print q execReq.statement=q execResp = client.ExecuteStatement(execReq) stmtHandle = execResp.operationHandle fetchReq=TFetchResultsReq() fetchReq.operationHandle=stmtHandle fetchReq.orietntion=TFetchOrientation.FETCH_FIRST fetchReq.maxRows=Maxrow count=0 bmore=True #swEnd=Time.time() while bmore: resultsResp = client.FetchResults(fetchReq) resultSet = resultsResp.results count=len(resultSet.rows) bmore=resultsResp.hasMoreRows; for row in resultSet.rows: symExi = False strSym = row.colVals[0].stringVal.value for sym in symbols: if (strSym==sym) or len(symbols) < 400: symExi = True break; if(symExi): exgP=DB.Exgprints() exgP.Date(row.colVals[7].i32Val.value) exgP.Time(timeFormat(row.colVals[1].i32Val.value)) exgP.Symbol(row.colVals[0].stringVal.value) exgP.Price(row.colVals[5].doubleVal.value) exgP.Size(row.colVals[6].i32Val.value) exgP.Listedexchange(row.colVals[2].i32Val.value) exgP.Reportingexchange(row.colVals[3].i32Val.value) exgP.Printtype(row.colVals[4].i32Val.value) DB.OnExgprints(exgP) fetchReq.orietntion=TFetchOrientation.FETCH_NEXT print 'done'
def doFetchNYSEImbalance(client,sessHandle,symbols,date,starttime,endtime): execReq=TExecuteStatementReq() execReq.sessionHandle=sessHandle q="select * from marketdata.imb_nyse where day = "+date.strftime('%Y%m%d') p='' if len(symbols)!=0 and len(symbols) < 400: for s in symbols: p=p+"','"+s p="("+p[2:]+"')" q=q+ ' and symbol in '+p if starttime !='': q=q + " and msofday >= " + str(timeconvert2(starttime)) if endtime !='': q=q + " and msofday <= " + str(timeconvert2(endtime)) print "run the query, date: "+ date.strftime('%Y-%m-%d') + ". Please wait!" #print q execReq.statement=q execResp = client.ExecuteStatement(execReq) stmtHandle = execResp.operationHandle fetchReq=TFetchResultsReq() fetchReq.operationHandle=stmtHandle fetchReq.orietntion=TFetchOrientation.FETCH_FIRST fetchReq.maxRows=Maxrow count=0 bmore=True while bmore: resultsResp = client.FetchResults(fetchReq) resultSet = resultsResp.results count=len(resultSet.rows) bmore=resultsResp.hasMoreRows; for row in resultSet.rows: symExi = False strSym = row.colVals[0].stringVal.value for sym in symbols: if sym == strSym or len(symbols) < 400: symExi = True break if (symExi): Imb=DB.Imbalance() sidej = DB.Side.NONE typej = DB.ImbType.UNKNOWN if row.colVals[2].byteVal.value == 0: sidej = DB.Side.NONE elif row.colVals[2].byteVal.value == 1: sidej = DB.Side.BUY elif row.colVals[2].byteVal.value == 2: sidej = DB.Side.SELL if row.colVals[3].byteVal.value == 0: typej = DB.ImbType.UNKNOWN elif row.colVals[3].byteVal.value == 1: typej = DB.ImbType.OPEN elif row.colVals[3].byteVal.value == 4: typej = DB.ImbType.CLOSE elif row.colVals[3].byteVal.value == 5: typej = DB.ImbType.NO_IMBALANCE Imb.Date(row.colVals[13].i32Val.value) Imb.Time(timeFormat(row.colVals[1].i32Val.value)) Imb.Symbol(row.colVals[0].stringVal.value) Imb.Clearing_price(row.colVals[11].doubleVal.value) Imb.Far_price(row.colVals[9].doubleVal.value) Imb.Imbalance_type(typej) Imb.Market_imbalance(row.colVals[6].i32Val.value) Imb.Near_price(row.colVals[10].doubleVal.value) Imb.Paired_shares(row.colVals[5].i32Val.value) Imb.Ref_price(row.colVals[8].doubleVal.value) Imb.Side(sidej) Imb.Total_imbalance(row.colVals[7].i32Val.value) Imb.Tag(row.colVals[12].byteVal.value) DB.OnImbalance(Imb) fetchReq.orietntion=TFetchOrientation.FETCH_NEXT print 'done'