def getBars(self,
instrument,
frequency,
timezone=None,
fromDateTime=None,
toDateTime=None):
instrument = normalize_instrument(instrument)
sql = "select bar.timestamp, bar.open, bar.high, bar.low, bar.close, bar.volume, bar.adj_close, bar.frequency" \
" from bar join instrument on (bar.instrument_id = instrument.instrument_id)" \
" where instrument.name = ? and bar.frequency = ?"
args = [instrument, frequency]
if fromDateTime is not None:
sql += " and bar.timestamp >= ?"
args.append(dt.datetime_to_timestamp(fromDateTime))
if toDateTime is not None:
sql += " and bar.timestamp <= ?"
args.append(dt.datetime_to_timestamp(toDateTime))
sql += " order by bar.timestamp asc"
cursor = self.__connection.cursor()
cursor.execute(sql, args)
ret = []
for row in cursor:
dateTime = dt.timestamp_to_datetime(row[0])
if timezone:
dateTime = dt.localize(dateTime, timezone)
ret.append(
bar.BasicBar(dateTime, row[1], row[2], row[3], row[4], row[5],
row[6], row[7]))
cursor.close()
return ret
def addBar(self, instrument, bar, frequency):
instrument = normalize_instrument(instrument)
instrumentId = self.__getOrCreateInstrument(instrument)
timeStamp = dt.datetime_to_timestamp(bar.getDateTime())
try:
sql = "insert into bar (instrument_id, frequency, timestamp, open, high, low, close, volume, adj_close) values (?, ?, ?, ?, ?, ?, ?, ?, ?)"
params = [
instrumentId, frequency, timeStamp,
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
self.__connection.execute(sql, params)
except sqlite3.IntegrityError:
sql = "update bar set open = ?, high = ?, low = ?, close = ?, volume = ?, adj_close = ?" \
" where instrument_id = ? and frequency = ? and timestamp = ?"
params = [
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose(), instrumentId, frequency, timeStamp
]
self.__connection.execute(sql, params)
def onNewValue(self, dateTime, value):
technical.EventWindow.onNewValue(self, dateTime, value)
if value is not None:
timestamp = dt.datetime_to_timestamp(dateTime)
if len(self.__timestamps):
assert (timestamp > self.__timestamps[-1])
self.__timestamps.append(timestamp)
def importEquityEvents(self, instruments, fromdate, todate):
w.start()
for i in range(len(instruments)):
data = w.wsd(
instruments[i],
"div_exdate,div_capitalization,div_stock,div_cashaftertax",
fromdate, todate, "")
instrument = normalize_instrument(instruments[i])
instrument_id = self.__getOrCreateInstrument(instrument)
for iday in range(len(data.Times)):
if data.Data[0][iday] is None:
continue
else:
div_exdate = dt.datetime_to_timestamp(data.Data[0][iday])
div_capitalization = data.Data[1][iday]
div_stock = data.Data[2][iday]
div_cashaftertax = data.Data[3][iday]
sql = """insert into equityevent (instrument_id, div_exdate,div_capitalization,div_stock,div_cashaftertax) values (%s, %s, %s, %s, %s)
"""
try:
self.__cursor.execute(sql, [
instrument_id, div_exdate, div_capitalization,
div_stock, div_cashaftertax
])
self.__connection.commit()
except psycopg2.IntegrityError:
self.__connection.rollback()
w.stop()
def getBars(self,
instrument,
frequency,
timezone=None,
fromDateTime=None,
toDateTime=None,
extra=None):
instrument = normalize_instrument(instrument)
sql = "select bar.timestamp, bar.open, bar.high, bar.low, bar.close, bar.volume, bar.adj_close, bar.frequency{extra}" \
" from bar join instrument on (bar.instrument_id = instrument.instrument_id)" \
" where instrument.name = %s and bar.frequency = %s"
extraFieldsString = ''
if extra is None:
extra = []
for i in range(0, len(extra)):
extraFieldsString = extraFieldsString + ", " + "bar." + extra[i]
args = [instrument, frequency]
if fromDateTime is not None:
sql += " and bar.timestamp >= %s"
args.append(dt.datetime_to_timestamp(fromDateTime))
if toDateTime is not None:
sql += " and bar.timestamp <= %s"
args.append(dt.datetime_to_timestamp(toDateTime))
sql += " order by bar.timestamp asc"
cursor = self.__connection.cursor()
cursor.execute(sql.format(extra=extraFieldsString), args)
self.__connection.commit()
ret = []
for row in cursor:
dateTime = dt.timestamp_to_datetime(row[0])
if timezone:
dateTime = dt.localize(dateTime, timezone)
extraRow = {}
for i in range(0, len(extra)):
extraRow[extra[i]] = row[8 + i]
ret.append(
bar.BasicBar(dateTime,
row[1],
row[2],
row[3],
row[4],
row[5],
row[6],
row[7],
extra=extraRow))
cursor.close()
return ret
def getFundamental(self, instrument, field, fromdate, todate=None):
if todate is None:
todate = fromdate
sql = """select fundamental.timestamp, instrument.name, fundamental.field, fundamental.value
from fundamental join instrument on (fundamental.instrument_id = instrument.instrument_id)
where instrument.name = {instrument}
and field = {field}
and fundamental.timestamp >= {fd_timestamp}
and fundamental.timestamp <= {td_timestamp}
""".format(instrument=instrument,
field=field,
fd_timestamp=datetime_to_timestamp(
datetime.strptime(fromdate, 'Y%-m%-d%')),
td_timestamp=datetime_to_timestamp(
datetime.strptime(todate, 'Y%-m%-d%')))
cursor = self.__connection.cursor()
cursor.execute(sql)
self.__connection.commit()
columns = ['date', 'instrument', 'field', 'value']
ret = pd.DataFrame(data=list(cursor), columns=columns).apply(
func=timestamp_to_datetime, axis='date').set_index('date')
return ret
def __init__(self, dateTime, frequency):
super(IntraDayRange, self).__init__()
assert isinstance(frequency, int)
assert frequency > 1
assert frequency < bar.Frequency.DAY
ts = int(dt.datetime_to_timestamp(dateTime))
slot = int(ts / frequency)
slotTs = slot * frequency
self.__begin = dt.timestamp_to_datetime(
slotTs, not dt.datetime_is_naive(dateTime))
if not dt.datetime_is_naive(dateTime):
self.__begin = dt.localize(self.__begin, dateTime.tzinfo)
self.__end = self.__begin + datetime.timedelta(seconds=frequency)
def importBarsFromCSVByPandas(self, filename, instrument):
def tradeStatusConverter(tradeStatus):
if tradeStatus == u'交易':
ret = 1
else:
ret = 0
return ret
dateConverter = lambda date: datetime_to_timestamp(
datetime.strptime(date[0:19], "%Y-%m-%d %H:%M:%S"))
converters = {'TRADE_STATUS': tradeStatusConverter}
data = pd.read_csv(filename, encoding='gbk', converters=converters)
data['adj_close'] = data['ADJFACTOR'] * data['CLOSE']
data['instrument_id'] = self.__getOrCreateInstrument(instrument)
data['timestamp'] = data['DATE'].apply(dateConverter)
data['frequency'] = bar.Frequency.DAY
data = data.drop(['DATE', 'ADJFACTOR'], axis=1)
data = data.dropna()
data.columns = data.columns.map(lambda x: x.lower())
engine = self.__engine
data.to_sql(name='bar', con=engine, if_exists='append', index=False)
def getValueAt(self, dateTime):
return self.__getValueAtImpl(dt.datetime_to_timestamp(dateTime))
def addBar(self, instrument, bar, frequency):
"""将bar中数据插入数据库"""
instrument = normalize_instrument(instrument)
instrumentId = self.__getOrCreateInstrument(instrument)
timeStamp = int(dt.datetime_to_timestamp(bar.getDateTime()))
try:
# extra为空时
if not bar.getExtraColumns():
sql = "insert into bar (instrument_id, frequency, timestamp, open, high, low, close, volume, adj_close) values (%s, %s, %s, %s, %s, %s, %s,%s, %s)"
params = [
instrumentId, frequency, timeStamp,
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
self.__cursor.execute(sql, params)
self.__connection.commit()
else:
# extra非空时，将extra中字段处理为逗号分隔的连续字符串形式，用于执行sql语句
extraColumns = bar.getExtraColumns()
keys = extraColumns.keys()
delimiter = ','
keysString = delimiter.join(keys)
for x in range(0, len(keys)):
if keys[x] not in self.getBarFields():
# 字段不存在则先添加字段再插入数据
sqlAlter = '''ALTER TABLE bar ADD COLUMN {field} numeric '''
self.__cursor.execute(sqlAlter.format(field=keys[x]))
self.__connection.commit()
sql1 = "insert into bar (instrument_id, frequency, timestamp, open, high, low, close, volume, adj_close," + keysString + ")"
# %s"*len(keys)表示需要额外传入的参数个数
sql2 = " values (%s, %s, %s, %s, %s, %s, %s, %s, %s" + " ,%s" * len(
keys) + ")"
sql = sql1 + sql2
params1 = [
instrumentId, frequency, timeStamp,
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
params2 = extraColumns.values()
params = params1 + params2
self.__cursor.execute(sql, params)
self.__connection.commit()
except psycopg2.IntegrityError:
self.__connection.rollback()
keys = bar.getExtraColumns().keys()
sql = "update bar set open = %s, high = %s, low = %s, close = %s, volume = %s, adj_close = %s{extra}" \
" where instrument_id = %s and frequency = %s and timestamp = %s"
extraUpdate = ''
for i in range(0, len(keys)):
extraUpdate = extraUpdate + ', ' + keys[i] + ' = %s'
params1 = [
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
params2 = [instrumentId, frequency, timeStamp]
extraParams = []
for i in range(0, len(keys)):
extraParams.append(bar.getExtraColumns()[keys[i]])
params = params1 + extraParams + params2
self.__cursor.execute(sql.format(extra=extraUpdate), params)
self.__connection.commit()