def main(): Directory = "E:/options/" startdate = "20190516" startdate = datetime.datetime.strptime(startdate, '%Y%m%d') info = InfoApi() info.GetDbHistoryConnect() info.GetStaticDataconnect() t = TradingDay(info) enddate = datetime.datetime.now() # if datetime.datetime.now().hour<17: # enddate=datetime.datetime.now()-datetime.timedelta(days=1) # if not t.IsTradingDayFuture(enddate.strftime("%Y%m%d")): # enddate = t.NextTradingDayFuture(enddate.strftime("%Y%m%d"), False) # enddate = datetime.datetime.strptime(enddate, "%Y%m%d") enddate = datetime.datetime.strptime("20190516", "%Y%m%d") while startdate <= enddate: print startdate starttime = datetime.datetime.now() setattr(info, 'TradingDay', startdate) GetSourceData(info) startdate = t.NextTradingDayFuture(startdate.strftime("%Y%m%d"), True) startdate = datetime.datetime.strptime(startdate, '%Y%m%d') endtime = datetime.datetime.now() print endtime - starttime
def ComputerAvgVol(): """计算某一天交易的合约平均成交量""" TradingDay = datetime.datetime.now().strftime("%Y-%m-%d") TradingDay = '2019-01-15' info = InfoApi() mysql1 = info.GetStaticDataconnect() templist = IsExistData(Instrumentsql % TradingDay, info) sqllist = [] progress = ProgressBar() for i in progress(templist): #Instrumentlist.append(str(i[0]).encode("utf-8")) InstrumentID = str(i[0]).encode("utf-8").strip() """for each InstrumentID""" temp = ComputerAvgEachInstrumentID(info, InstrumentID) sqllist = sqllist + temp mysql1.ExecmanysNonQuery(InsertSql, sqllist)
def dealCsvfile(): print "str_file",str_file,type(str_file) temp = InfoApi().GetDbHistoryDataAccount() mysql = Mysql(temp[0], temp[1], temp[2], temp[3]) abspath=os.path.abspath(str_file) print abspath,os.getcwd() for filenames in os.walk(str_file): ### 返回一个元组 目录路径,子目录名字,非目录文件名字 print filenames,len(filenames[2]) ## 判断CleanedData是否存在 if not os.path.exists("./cleanedData"): os.mkdir("./cleanedData") for i in filenames[1]: path=filenames[0].replace(abspath,"")+"\\"+i+"\\" path=os.getcwd()+"\\cleanedData"+path if not os.path.exists(path): os.mkdir(path) fileDirectory=filenames[0] for filename in filenames[2]: if str(filename).endswith(".csv"): # 顺便抽取day的统计数据 累计成交量 TotalAmount 累计成交金额 price2 计算价 price3 昨日结算价 LastClose 昨日收盘价 Open ReadData(filename,fileDirectory,mysql) mysql.Disconnect()
# Add a header format. header_format = workbook.add_format({ 'bold': True, 'text_wrap': True, 'valign': 'vcenter', 'border': 1 }) header_format.set_align('center') header_format.set_align('vcenter') writer.save() if __name__ == '__main__': info = InfoApi() info.GetDbHistoryConnect() #初始化mysql t = TradingDay(info) productlist = ['i', 'ni'] #成交量持仓量的品种代码 startdate = '20190524' startdate = datetime.datetime.strptime(startdate, "%Y%m%d") enddate = datetime.datetime.now() startdate = t.NextTradingDayFuture(enddate.strftime("%Y%m%d"), False) startdate = datetime.datetime.strptime(startdate, "%Y%m%d") while startdate <= enddate: print info.mysql Prestartdate = t.NextTradingDayFuture(startdate.strftime("%Y%m%d"), False)
if not 'CZCE' in eixstlist: GetCZCEPosition(info, startdate, "CZCE") # 获取郑商所日成交top20 else: print 'CZCE', "持仓排名top20数据已经存在" # GetDCEPosition(info,startdate,"DCE") #大商所持仓信息 # # GetSHFEPosition(info, startdate, "SHFE") # 上期所 # # # GetCZCEPosition(info, startdate, "CZCE") # 郑商所阶段性前20名 # GetDCEStagedTurnover(info,startdate,"DCE") #阶段性成交 if __name__ == "__main__": info = InfoApi() info.GetDbHistoryConnect() info.Get_Msplider() t = NextTradingDay.TradingDay(info) startdate = datetime.datetime.now() - datetime.timedelta(days=7) startdate = t.NextTradingDay(startdate.strftime("%Y%m%d"), False) startdate = datetime.datetime.strptime(startdate, "%Y%m%d") enddate = datetime.datetime.now() mysplider = info.mysplider while startdate.strftime("%Y%m%d") <= enddate.strftime("%Y%m%d"): print startdate, main(startdate, mysplider, info) startdate = t.NextTradingDay(startdate.strftime("%Y%m%d"), True)
'specialInstrumentId'] tdate = date.replace("-", "") FutureInstrumentList = info.GetAllTradeInstrumentByTradingDay(tdate) for i in FutureInstrumentList: try: code = re.match('^[a-zA-Z]+', str(i)).group() m = templist[code] if i in info.tempdata['specialInstrumentId'].keys(): m = info.tempdata['specialInstrumentId'][i] col = [date, i, FutureInstrumentList[i], m] datalist.append(tuple(col)) except: print "文字解析异常,未知品种", i, code, raise Exception print '东航期货', len(datalist) info.mysql.UpdateMarginExample(datalist, '2') def GetInstrumentIdByCodeDate(code, ExchangeId, specialMonth): if ExchangeId == 'CZCE': return code + str(specialMonth)[1:].zfill(3) else: return code + specialMonth if __name__ == '__main__': info = InfoApi() info.Get_Msplider() info.Get_BasicApi() main(info)
for i in topnypz: InstrumentName = str(i.get_text().encode("utf-8")).strip()[:-6] print InstrumentName href = url + i['href'] if not basic.StrinList(InstrumentName, existlist): mysql.ExecNonQuery(insertsql % (ExchangeID, InstrumentName, href)) else: mysql.ExecNonQuery(updatesql % (href, InstrumentName)) if __name__ == '__main__': print """你确定要执行此脚本?执行完成后还要修改相应交易时间""" # raise Exception Instrument = {} temp = {} info = InfoApi() basic = BasicAPI() mysplider = MySplider() account = info.GetDbHistoryDataAccount() mysql = Mysql(account[0], account[1], account[2], account[3]) existsql = "select [InstrumentName] from [PreTrade].[dbo].[ContractCode]" insertsql = "insert into [PreTrade].[dbo].[ContractCode](ExchangeID,[InstrumentName],[Website]) values('%s','%s','%s')" updatesql = "update [PreTrade].[dbo].[ContractCode] set [Website]='%s' where InstrumentName='%s'" existlist = mysql.ExecQueryGetList(existsql) ExchangeList = info.GetAllExchange() for i in ExchangeList: url = info.GetExchangeWebsite(i) if i == 'CZCE': GetCZCEProduct(mysplider, info, i) # elif i=='DCE': # GetDCEProduct(mysplider, info, i)
else: print TradingDay, "中金所期货结算结算数据已经存在" def main(startdate, infoapi): CFFEXSettlementDetail(startdate, infoapi) # 上期所保证金手续费相关信息 CZCESettlementDetail(startdate, infoapi) # 郑商所保证金手续费信息 DCESettlementDetail(startdate, infoapi) # 大商所保证金手续费信息 SHFESettlementDetail(startdate, infoapi) #上期所保证金手续费相关信息 if __name__ == "__main__": infoapi = InfoApi() infoapi.Get_Msplider() t = TradingDay(infoapi) startdate = datetime.datetime.now() - datetime.timedelta(days=7) startdate = t.NextTradingDay(startdate.strftime("%Y%m%d"), False) startdate = datetime.datetime.strptime(startdate, "%Y%m%d") enddate = datetime.datetime.now() if enddate.hour < 16: enddate = t.NextTradingDayFuture(enddate.strftime("%Y%m%d"), False) enddate = datetime.datetime.strptime(enddate, "%Y%m%d") while startdate.strftime("%Y%m%d") <= enddate.strftime("%Y%m%d"): print startdate main(startdate, infoapi) startdate = t.NextTradingDay(startdate.strftime("%Y%m%d"), True) startdate = datetime.datetime.strptime(startdate, "%Y%m%d")
Level_5_clean(filename, directory, info) elif type==1: Level_1_Clean(filename, directory, info) if __name__=='__main__': ProductCodeList=['ni','au','ag','rb','i','m','TA'] # ProductCodeList = ['ni'] StartDay="20180801" # StartDay = "20190201" storeDirectory="D:/DATA/MainIstrument/some/" """Read SqlServer Get Main InstrumentID by ProductCode""" pass info = InfoApi() info.GetDbHistoryConnect() t = NextTradingDay.TradingDay(info) # enddate = datetime.datetime.now()-datetime.timedelta(days=1) enddate = datetime.datetime.strptime("20190201","%Y%m%d") for i in tqdm(ProductCodeList): StartDay = "20180801" startdate = datetime.datetime.strptime(StartDay, "%Y%m%d") while startdate.strftime("%Y%m%d") <= enddate.strftime("%Y%m%d"): print startdate IsExistdiretory(startdate.strftime("%Y%m%d"),storeDirectory) MainInstrument=info.GetMainInstrumentIdByProductCode(i,startdate.strftime("%Y-%m-%d")) info.cleanDatadict = [startdate.strftime("%Y%m%d"), i,MainInstrument,storeDirectory+startdate.strftime("%Y%m%d")+"/"] CleanData(info) startdate = t.NextTradingDay(startdate.strftime("%Y%m%d"), True)
sql = "INSERT INTO [StatisticData].[dbo].[Calender]([Date],IsTradingDay,IsEveningOpen,[PreTradingDay],[NextTradingDay]) VALUES('%s',%s,%s,'%s','%s')" existsql = "select top 1 [Date] from [StatisticData].[dbo].[Calender] order by Date desc" def datatolist(data): templist = [] for k in data: content = data[k].split(",") templist = templist + content[::-1] templist = sorted(templist, reverse=False) return templist if __name__ == "__main__": info = InfoApi() templist = [] holiday = info.setting.GetHoliday() temp = info.mysql.ExecQueryGetList(existsql) t = TradingDay(info) if len(temp): startdate = datetime.datetime.strptime( temp[0], "%Y-%m-%d") + datetime.timedelta(days=1) else: startdate = datetime.datetime.strptime("20170101", "%Y%m%d") enddate = datetime.datetime.now() enddate = t.NextTradingDay(enddate.strftime("%Y%m%d"), True) enddate = datetime.datetime.strptime(enddate, "%Y%m%d") while startdate.strftime("%Y%m%d") <= enddate.strftime("%Y%m%d"): col = [] tempstartday = startdate.strftime("%Y%m%d").strip()
Level_5_clean(filename, directory, info) elif type==1: Level_1_Clean(filename, directory, info) if __name__=='__main__': # ProductCodeList = ['ni'] StartDay="20190315" # StartDay = "20190201" storeDirectory="D:/DATA/CleanedData/" """Read SqlServer Get Main InstrumentID by ProductCode""" pass info = InfoApi() info.GetDbHistoryConnect() ProductCodeList =info.GetAllProductCode() t = NextTradingDay.TradingDay(info) # enddate = datetime.datetime.now()-datetime.timedelta(days=1) enddate = datetime.datetime.strptime("20190315","%Y%m%d") startdate = datetime.datetime.strptime(StartDay, "%Y%m%d") while startdate.strftime("%Y%m%d") <= enddate.strftime("%Y%m%d"): threadList=list() threadNum=4 for i in tqdm(ProductCodeList): IsExistdiretory(startdate.strftime("%Y%m%d"),storeDirectory) code = info.GetCodeByInstrumentID(i) MainInstrument=info.GetMainInstrumentIdByProductCode(code,startdate.strftime("%Y-%m-%d")) info.cleanDatadict = [startdate.strftime("%Y%m%d"), code,MainInstrument,storeDirectory+startdate.strftime("%Y%m%d")+"/"]
# -*- coding: utf-8 -*- # @Time : 2019/1/15 9:44 # @Author : ZouJunLin import os from utils.InfoApi import * cnfpath = os.path.abspath('../config.conf') print cnfpath info = InfoApi().GetDbHistoryConnect() print info
u'名次', u'会员编号', u'会员简称', u'成交量(手)', u'增减', u'名次1', u'会员编号1', u'会员简称1', u'持买单量1', u'增减1', u'名次2', u'会员编号2', u'会员简称2', u'持卖单量2', u'增减2' ] sql="SELECT [Rank],[ParticipantID1],[ParticipantABBR1],[CJ1],[CJ1_CHG],[Rank],[ParticipantID2],[ParticipantABBR2],[CJ2],[CJ2_CHG],[Rank] ,[ParticipantID3],[ParticipantABBR3],[CJ3],[CJ3_CHG] " \ "FROM [PreTrade].[dbo].[Position_Top20] where TradingDay='%s' and InstrumentID='%s'" for i in info.PositionTop20InstrumentID.keys(): data = info.Get2Listfromsql(sql % (startdate.strftime("%Y-%m-%d"), i)) if len(data): excelDataToExcel(data, info.PositionTop20InstrumentID[i], columns, startdate.strftime("%Y%m%d"), i) #压缩成zip格式 if __name__ == "__main__": info = InfoApi() info.GetDbHistoryConnect() info.Get_Msplider() info.GetPositionTop20InstrumentID() t = NextTradingDay.TradingDay(info) startdate = datetime.datetime.now() - datetime.timedelta(days=4) startdate = t.NextTradingDay(startdate.strftime("%Y%m%d"), False) startdate = datetime.datetime.strptime(startdate, "%Y%m%d") enddate = datetime.datetime.now() if enddate.hour < 16: enddate = t.NextTradingDayFuture(enddate.strftime("%Y%m%d"), False) enddate = datetime.datetime.strptime(enddate, "%Y%m%d") mysplider = info.mysplider while startdate.strftime("%Y%m%d") <= enddate.strftime("%Y%m%d"):
def geturldata(): """ 网上获取交易日历,有时候会出错 """ url = "http://www.shfe.com.cn/js/calendar-data.js" req = requests.get(url) data = req.text.encode("utf-8").replace("define(function (){ return ", "").replace(";});", "").strip() data = eval(data) templist = datatolist(data) return templist if __name__ == "__main__": info = InfoApi() daylist = geturldata() daylist.append('20180618') daylist = sorted(daylist) nowdate = datetime.datetime.now() nowdate = nowdate.strftime("%Y%m%d") #### 20180824之前的非交易日 for i in daylist: if i >= nowdate: length = daylist.index(i) break daylist = daylist[:length] mysql = info.GetDbHistoryConnect() sql1 = "SELECT [NoTradingDay] FROM [PreTrade].[dbo].[HistoryNoTdCalendar]" tlist = mysql.ExecQueryGetList(sql1) daylist = list(set(daylist) - set(tlist))
# @Author : ZouJunLin from utils.InfoApi import * import datetime from utils.TradingDay import NextTradingDay from Level1Clean import * from utils.BasicAPI import * import threading directory = os.getcwd() + "\data" if __name__ == '__main__': storeDirectory = "D:/GitData/PreTrade/src/optionDataClean/CleanedData/" dataDiretory = "" abspath = os.path.abspath(directory) info = InfoApi() info.GetDbHistoryConnect() t = NextTradingDay.TradingDay(info) for filenames in os.walk(abspath): print filenames if not os.path.exists("./CleanedData"): os.mkdir("./CleanedData") ## 新建清洗后的数据的存储路径 for i in filenames[1]: path = filenames[0].replace(abspath, "") + "\\" + i + "\\" path = os.getcwd() + "\\CleanedData" + path if not os.path.exists(path): os.mkdir(path) fileDirectory = filenames[0] for filename in filenames[2]: filename = filename.decode(encoding='gbk')
break lasthms = theorynextdate(lasthms, TradTime) while lasthms != "": csv_data.at[i, 'Time'] = lasthms Myappend(csv_data, i, templist, InstrumentId, TradingDay) lasthms = theorynextdate(lasthms, TradTime) writer.writerows(templist) # InsertFutureData(templist, tuple(daylist),insertFuturesql,insertDayFuturesql) if len(daylist) > 0: mysql.ExecmanyNonQuery(insertDayFuturesql, tuple(daylist)) if len(templist) > 0: mysql.ExecmanysNonQuery(insertFuturesql, templist) # writer.writerows(templist) def Myappend(csv_data, i, templist, InstrumentID, TradingDay): newcol = [] newcol.append(TradingDay) newcol.append(InstrumentID) for column in columnsNum1: newcol.append(csv_data.at[i, column]) # newcol.insert(4,'') # newcol.insert(5,'') # newcol.insert(35,'') templist.append(tuple(newcol)) if __name__ == '__main__': temp = InfoApi().GetDbHistoryDataAccount() mysql = Mysql(temp[0], temp[1], temp[2], temp[3]) dataclean(mysql)