def WriteToPortfolioDataBase(portfolioName, symbols, factorValues,
rebalanceDate, database):
dataObjects = DataSeries(portfolioName + "_Portfolio")
#---Portfolio Basic Info---
portfolio = {}
portfolio["Rebalance"] = True
portfolio["DataSeries"] = dataObjects.name
portfolio["Name"] = portfolioName
portfolio["Account"] = portfolioName
#---Datatime---
portfolio["DateTime"] = ToDateTimeString(rebalanceDate)
portfolio["StdDateTime"] = ToUTCDateTime(rebalanceDate)
#---Stock Account---
weight = 1 / symbols.__len__()
positions = []
for i in range(symbols.__len__()):
positions.append({
"Instrument": symbols[i],
"Factor": factorValues[i],
"Weight": weight
})
portfolio["Positions"] = positions
#---Total---
portfolio["Value"] = 0.0
portfolio["Cash"] = 0.0
portfolio["PositionValue"] = 0.0
portfolio["PositionEquity"] = 0.0
portfolio["ProfitLoss"] = 0.0
portfolio["Deposit"] = 0.0
#添加“Portfolio”
dataObjects.add(portfolio)
database.saveDataSeries(dataObjects, isUpSert=True)
def WriteToDataBase_ArbiPositionFile(portfolioName, datetime1, database,
pathFilename):
instruments = database.findAll("Instruments", "Instruments")
dataObjects = DataSeries(portfolioName + "_Portfolio")
dataObject = {}
dataObject["DataSeries"] = dataObjects.name
#---Load Text File---
positions = []
file = open(pathFilename, 'r')
i = 0
headerIndexByName = {}
while True:
s = file.readline()
i = i + 1
if s == '':
break
content = s.split('\t')
#Process Headers
if i == 1:
headerCount = 0
for header in content:
headerIndexByName[header] = headerCount
headerCount = headerCount + 1
continue
#Process Header Done
# Position 的必填字段
position = {}
position["DateTime"] = ToDateTimeString(datetime1)
position["StdDateTime"] = ToUTCDateTime(datetime1)
position["Portfolio"] = portfolioName
position["Instrument"] = ""
position["Side"] = ""
position["Amount"] = 0.0 # 总持仓 可以是负数
position["Qty"] = 0.0 #总持仓 只能为正
position["Avaliable"] = 0.0 #可平仓
position["CostPrice"] = 0.0
position["LastTrade"] = 0.0
position["PositionValue"] = 0.0
position["PositionEquity"] = 0.0
position["ProfitLoss"] = 0.0
symbol1 = content[headerIndexByName[
"证券代码"]] #symbol read from Text File 600000, without Exchange info
name = content[headerIndexByName["证券名称"]]
#---Find Instrument in DataBase---
curInstrument = None
for instrument in instruments:
symbol2 = instrument[
"Symbol"] #symbol in Database 600000.SH, with Exchange info
symbol2 = symbol2[:6] #取前六位
if (symbol2 == "600988"):
kkwood = 1
if symbol1 == symbol2:
position["Instrument"] = instrument["Symbol"]
position["Key"] = instrument["Symbol"]
curInstrument = instrument
break
if curInstrument == None:
print("Can't Found Instrument Symbol: " + symbol1 + " " + name)
continue
position["Qty"] = float(content[headerIndexByName["股票余额"]])
position["Side"] = "Long"
position["Amount"] = position["Qty"]
position["Avaliable"] = float(content[headerIndexByName["可用余额"]])
position["CostPrice"] = float(content[headerIndexByName["成本价"]])
position["LastTrade"] = float(content[headerIndexByName["市价"]])
position["ProfitLoss"] = float(content[headerIndexByName["盈亏"]])
position["PositionValue"] = float(content[headerIndexByName["市值"]])
position["PositionEquity"] = position["PositionValue"]
#添加“一个Position”到“Positions列表”
positions.append(position)
dataObject["DateTime"] = ToDateTimeString(datetime1)
dataObject["StdDateTime"] = ToUTCDateTime(datetime1)
dataObject["Account"] = portfolioName
dataObject["Cash"] = 0.0
dataObject["Value"] = 0.0
dataObject["Positions"] = positions
positionEquity = 0
positionValue = 0
pofitLoss = 0
for position in positions:
positionEquity = positionEquity + position["PositionEquity"]
positionValue = positionValue + position["PositionValue"]
pofitLoss = pofitLoss + position["ProfitLoss"]
dataObject["PositionEquity"] = positionEquity
dataObject["PositionValue"] = positionValue
dataObject["PofitLoss"] = pofitLoss
dataObjects.add(dataObject)
database.saveDataSeries(dataObjects, isUpSert=True)
#---Loop Finished---
file.close()
def WriteToDataBase_WindPortfolioFile(portfolioName, datetime1, database,
excelPathfilename):
instruments = database.findAll("Instruments", "Instruments")
dataObjects = DataSeries(portfolioName + "_Portfolio")
dataObject = {}
dataObject["DataSeries"] = dataObjects.name
#---Load Text File---
book = xlrd.open_workbook(excelPathfilename)
sheet = book.sheet_by_name("Wind资讯")
nrows = sheet.nrows
ncols = sheet.ncols
positions = []
i = 0
headerIndexByName = {}
for i in range(0, nrows):
row_data = sheet.row_values(i)
content = row_data
if content[2] == "":
continue
#Process Headers
if i == 0:
headerCount = 0
for header in content:
headerIndexByName[header] = headerCount
headerCount = headerCount + 1
continue
#Process Header Done
# Position 的必填字段
position = {}
position["DateTime"] = ToDateTimeString(datetime1)
position["StdDateTime"] = ToUTCDateTime(datetime1)
position["Portfolio"] = portfolioName
position["Instrument"] = ""
position["Side"] = ""
position["Amount"] = 0.0 # 总持仓 可以是负数
position["Qty"] = 0.0 #总持仓 只能为正
position["Avaliable"] = 0.0 #可平仓
position["CostPrice"] = 0.0
position["LastTrade"] = 0.0
position["PositionValue"] = 0.0
position["PositionEquity"] = 0.0
position["ProfitLoss"] = 0.0
symbol1 = content[headerIndexByName[
"证券代码"]] #symbol read from Text File 600000, without Exchange info
name = content[headerIndexByName["证券简称"]]
#---Find Instrument in DataBase---
curInstrument = None
for instrument in instruments:
symbol2 = instrument[
"Symbol"] #symbol in Database 600000.SH, with Exchange info
if symbol1 == symbol2:
position["Instrument"] = instrument["Symbol"]
position["Key"] = instrument["Symbol"]
curInstrument = instrument
break
if curInstrument == None:
print("Can't Found Instrument Symbol: " + symbol1 + " " + name)
continue
index = headerIndexByName["持仓数量"]
value = content[index]
position["Amount"] = float(content[headerIndexByName["持仓数量"]])
amount = position["Amount"]
position["Qty"] = np.absolute(amount)
if amount >= 0:
position["Side"] = "Long"
elif amount < 0:
position["Side"] = "Short"
position["Avaliable"] = position["Qty"]
position["CostPrice"] = float(content[headerIndexByName["成本价格"]])
position["LastTrade"] = float(content[headerIndexByName["最新价"]])
position["ProfitLoss"] = float(content[headerIndexByName["浮动盈亏(元)"]])
position["PositionValue"] = float(
content[headerIndexByName["持仓市值(元)"]])
if curInstrument["InstrumentType"] == "Future" or curInstrument[
"InstrumentType"] == "Option":
position["PositionEquity"] = 0 # Wind文件没法计算保证金
else:
position["PositionEquity"] = position["PositionValue"]
#添加“一个Position”到“Positions列表”
positions.append(position)
dataObject["DateTime"] = ToDateTimeString(datetime1)
dataObject["StdDateTime"] = ToUTCDateTime(datetime1)
dataObject["Account"] = portfolioName
dataObject["Cash"] = 0.0
dataObject["Value"] = 0.0
dataObject["Positions"] = positions
positionEquity = 0
positionValue = 0
pofitLoss = 0
for position in positions:
positionEquity = positionEquity + position["PositionEquity"]
positionValue = positionValue + position["PositionValue"]
pofitLoss = pofitLoss + position["ProfitLoss"]
dataObject["PositionEquity"] = positionEquity
dataObject["PositionValue"] = positionValue
dataObject["PofitLoss"] = pofitLoss
dataObjects.add(dataObject)
database.saveDataSeries(dataObjects, isUpSert=True)
def WriteToDataBase_CTPPositionFile(portfolioName, datetime1, database,
pathFilename):
instruments = database.findAll("Instruments", "Future")
dataObjects = DataSeries(portfolioName + "_Portfolio")
dataObject = {}
dataObject["DataSeries"] = dataObjects.name
#---Load Text File---
positions = []
file = open(pathFilename, 'r')
i = 0
headerIndexByName = {}
while True:
s = file.readline()
i = i + 1
if s == '':
break
content = s.split(',')
tmpList = []
tmp = ""
for s in content:
if s[0] == '"':
tmp = s #Begin
elif s[-1] == '"':
tmp = tmp + s
tmpList.append(tmp)
tmp = "" #End
else:
if tmp != "": # Maight Middle
tmp = tmp + s
else:
tmpList.append(s)
content = tmpList
#Process Headers
if i == 1:
headerCount = 0
for header in content:
headerIndexByName[header] = headerCount
headerCount = headerCount + 1
continue
#Process Header Done
# Position 的必填字段
position = {}
position["DateTime"] = ToDateTimeString(datetime1)
position["StdDateTime"] = ToUTCDateTime(datetime1)
position["Portfolio"] = portfolioName
position["Instrument"] = ""
position["Side"] = ""
position["Amount"] = 0.0 # 总持仓 可以是负数
position["Qty"] = 0.0 #总持仓 只能为正
position["Avaliable"] = 0.0 #可平仓
position["CostPrice"] = 0.0
position["LastTrade"] = 0.0
position["PositionValue"] = 0.0
position["PositionEquity"] = 0.0
position["ProfitLoss"] = 0.0
symbol1 = content[headerIndexByName[
"合约"]] #symbol read from Text File 600000, without Exchange info
symbol1 = symbol1.upper()
#name = content[headerIndexByName["证券名称"]]
#---Find Instrument in DataBase---
curInstrument = None
for instrument in instruments:
symbol2 = instrument[
"Symbol"] #symbol in Database 600000.SH, with Exchange info
symbol2 = symbol2[:-4] #舍弃后四位
if (symbol2 == "IF1612"):
kkwood = 1
if symbol1 == symbol2:
position["Instrument"] = instrument["Symbol"]
position["Key"] = instrument["Symbol"]
curInstrument = instrument
break
if curInstrument == None:
print("Can't Found Instrument Symbol: " + symbol1)
continue
position["Qty"] = float(content[headerIndexByName["总持仓"]])
side = content[headerIndexByName["买卖"]]
side = side.strip()
if side == "买":
position["Side"] = "Long"
position["Amount"] = position["Qty"]
elif side == "卖":
position["Side"] = "Short"
position["Amount"] = -1.0 * position["Qty"]
else:
print("Can't Parse 方向: " + side)
continue
position["Avaliable"] = float(content[headerIndexByName["可平量"]])
position["CostPrice"] = float(content[headerIndexByName["持仓均价"]])
#position["LastTrade"] = float(content[headerIndexByName["最新价"]])#没有最新价字段
position["ProfitLoss"] = float(
content[headerIndexByName["持仓盈亏"]].strip('"'))
#position["PositionValue"] = float(content[headerIndexByName["市值"]])#自然算不出市值
if curInstrument["InstrumentType"] == "Future" or curInstrument[
"InstrumentType"] == "Option":
s = content[headerIndexByName["占用保证金"]] # 原文带着分号和逗号 "49,719.60"
s = s.strip('"')
position["PositionEquity"] = float(s)
else:
position["PositionEquity"] = position["PositionValue"]
#添加“一个Position”到“Positions列表”
positions.append(position)
dataObject["DateTime"] = ToDateTimeString(datetime1)
dataObject["StdDateTime"] = ToUTCDateTime(datetime1)
dataObject["Account"] = portfolioName
dataObject["Cash"] = 0.0
dataObject["Value"] = 0.0
dataObject["Positions"] = positions
positionEquity = 0
positionValue = 0
pofitLoss = 0
for position in positions:
positionEquity = positionEquity + position["PositionEquity"]
positionValue = positionValue + position["PositionValue"]
pofitLoss = pofitLoss + position["ProfitLoss"]
dataObject["PositionEquity"] = positionEquity
dataObject["PositionValue"] = positionValue
dataObject["PofitLoss"] = pofitLoss
dataObjects.add(dataObject)
database.saveDataSeries(dataObjects, isUpSert=True)
#---Loop Finished---
file.close()
def ReadTDXTextFile_WriteToDataBase2(symbol, pathFilename, database,
minutePeriod, startDatetime):
size = int(minutePeriod * 60)
dataObjects = DataSeries.DataSeries(symbol + "_Time_" + str(size) + "_Bar")
#---Load Text File---
positions = []
file = open(pathFilename, 'r')
i = 0
headerIndexByName = {}
while True:
s = file.readline()
i = i + 1
if s == '':
break
if i == 1:
continue
#Process Headers
if i == 2:
content = s.split('\t')
headerCount = 0
for header in content:
header = header.strip(" ")
header = header.strip("\n")
headerIndexByName[header] = headerCount
headerCount = headerCount + 1
continue
#Process Header Done
content = s.split(',')
if content.__len__() < 2:
break
dataObject = {}
sDate = content[headerIndexByName["日期"]]
sTime = content[headerIndexByName["时间"]]
sDateTime = sDate + " " + sTime[0:2] + ":" + sTime[2:4] + ":00.000"
datetime1 = ParseDateTime(sDateTime)
if datetime1 < startDatetime: #不计入那些太早的时间，加快效率
continue
datetime0 = datetime1 + timedelta(minutes=-minutePeriod)
datetime2 = ToUTCDateTime(datetime1)
#dataObject["DataSeries"] = name
dataObject["OpenDateTime"] = ToDateTimeString(datetime0)
dataObject["DateTime"] = ToDateTimeString(datetime1)
dataObject["StdDateTime"] = datetime2
dataObject["DataSeries"] = dataObjects.name
dataObject["Key"] = dataObjects.name + "_" + dataObject["DateTime"]
dataObject["Symbol"] = symbol
dataObject["Size"] = size
dataObject["BarType"] = "Time"
dataObject["Open"] = float(content[headerIndexByName["开盘"]])
dataObject["High"] = float(content[headerIndexByName["最高"]])
dataObject["Low"] = float(content[headerIndexByName["最低"]])
dataObject["Close"] = float(content[headerIndexByName["收盘"]])
dataObject["Volume"] = float(content[headerIndexByName["成交量"]])
dataObject["Money"] = float(content[headerIndexByName["成交额"]])
if "持仓量" in headerIndexByName:
dataObject["OpenInt"] = float(content[headerIndexByName["持仓量"]])
dataObjects.add(dataObject)
#---把DataSeries储存到数据库---
database.saveDataSeries(dataObjects, isUpSert=True)
kkwood = 1
def WriteToDataBase_JingChaoExcelFile(portfolioName, datetime1, datetime2,
database, excelPathfilename):
dataObjects = DataSeries(portfolioName + "_Portfolio")
#---Load Text File---
book = xlrd.open_workbook(excelPathfilename)
sheet = book.sheet_by_name("资产")
nrows = sheet.nrows
ncols = sheet.ncols
positions = []
i = 0
headerIndexByName = {}
for i in range(0, nrows):
row_data = sheet.row_values(i)
content = row_data
if content[0] == "":
continue
#Process Headers
if i == 0:
headerCount = 0
for header in content:
headerIndexByName[header] = headerCount
headerCount = headerCount + 1
continue
#Process Header Done
#---Portfolio Basic Info---
portfolio = {}
portfolio["DataSeries"] = dataObjects.name
portfolio["Name"] = portfolioName
portfolio["Account"] = portfolioName
#---Datatime---
daysAfter1900 = content[headerIndexByName["日期"]]
datetime0 = datetime(1900, 1, 1) + timedelta(days=daysAfter1900 - 2)
#datetime0 = ParseDateTime(sDate)
datetime0 = datetime0 + timedelta(hours=15)
portfolio["DateTime"] = ToDateTimeString(datetime0)
portfolio["StdDateTime"] = ToUTCDateTime(datetime0)
#---Stock Account---
portfolio["StockAccountValue"] = float(
content[headerIndexByName["总资产"]])
f0 = 0
f1 = 0
f2 = 0
f3 = 0
f4 = 0
f5 = 0
s0 = content[headerIndexByName["现金"]]
s1 = content[headerIndexByName["证券持仓资金"]]
s2 = content[headerIndexByName["拆借资金"]]
s3 = content[headerIndexByName["新股申购/冻结款"]]
s4 = content[headerIndexByName["证券出入金"]]
s5 = content[headerIndexByName["期货出入金"]]
if s0 != "":
f0 = float(s0)
if s1 != "":
f1 = float(s1)
if s2 != "":
f2 = float(s2)
if s3 != "":
f3 = float(s3)
if s4 != "":
f4 = float(s4)
if s5 != "":
f5 = float(s5)
portfolio["StockAccountCash"] = f0 + f2
portfolio["StockAccountPositionValue"] = f1 + f3
portfolio["StockAccountPositionEquity"] = portfolio[
"StockAccountPositionValue"]
portfolio["StockAccountDeposit"] = f4
#---Future Account---
portfolio["FutureAccountValue"] = float(
content[headerIndexByName["期货权益"]])
portfolio["FutureAccountCash"] = 0
portfolio["FutureAccountPositionValue"] = 0
portfolio["FutureAccountPositionEquity"] = 0
portfolio["FurureAccountDeposit"] = f5
#---Total---
portfolio["Value"] = float(content[headerIndexByName["计划总市值"]])
portfolio["Cash"] = 0.0
portfolio["PositionValue"] = 0.0
portfolio["PositionEquity"] = 0.0
portfolio["ProfitLoss"] = 0.0
portfolio["Deposit"] = f4 + f5
children = []
children.append(portfolioName + "-Stock")
children.append(portfolioName + "-Future")
portfolio["Chidren"] = children
#添加“Portfolio”
dataObjects.add(portfolio)
database.saveDataSeries(dataObjects, isUpSert=True)