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)
