def readPosition(strDB, strTB, dtStart):
con = MySQLDBAPI.connect(strDB)
sql = 'SELECT * FROM {0} where trade_date = "{1}"'.format(
strTB, dtStart.strftime('%Y%m%d'))
df = pd.read_sql(sql, con)
con.dispose()
return df
def clearTBFakeTomorrow(strDB, strTB):
con = MySQLDBAPI.connectMDB(strDB)
cur = con.cursor()
sql = 'delete from {0} where trade_date="{1}";'.format(strTB, dtTomorrow)
cur.execute(sql)
con.commit()
cur.close()
con.close()
return
def readContractCode(dtStart):
import CommodityDataBase as CDB
reload(CDB)
strTB = CDB.Utils.UtilsDB.DAILY_DOMINANT_DB_NAME
con = MySQLDBAPI.connect(CDB.Utils.UtilsDB.strMySQLDB)
sql = 'SELECT * FROM {0} where trade_date="{1}"'.format(
strTB, dtStart.strftime('%Y%m%d'))
df = pd.read_sql(sql, con)
con.dispose()
return df
def readPerformance(strDB, strTB, dtStart=None):
con = MySQLDBAPI.connect(strDB)
if dtStart is None:
sql = 'SELECT * FROM {0}'.format(strTB)
else:
sql = 'SELECT * FROM {0} where trade_date > "{1}"'.format(
strTB, dtStart.strftime('%Y%m%d'))
df = pd.read_sql(sql, con)
con.dispose()
return df
def readIndexConstituent(IndexCode, dtEnd=datetime.datetime.now()):
dtStart = dtEnd - datetime.timedelta(31 * 5 + 20, 1)
strTB = SDB.Utils.strTB_IndexConstituent
con = MySQLDBAPI.connect(DB_NAME)
dtEnd = max(dtEnd, datetime.datetime(2007, 1, 31))
sql = 'SELECT IndexCode,TradingDay,SecuCode,Weights FROM {0} where IndexCode="{1}" and TradingDay>="{2}" and TradingDay<="{3}"'.format(
strTB, IndexCode, dtStart.strftime('%Y%m%d'), dtEnd.strftime('%Y%m%d'))
df = pd.read_sql(sql, con)
con.dispose()
return df
def saveTB_DAILY(strDB, strTB, df, listColumnIndex=['code', 'trade_date']):
# save to temporary table
MySQLDBAPI.dropTable(strDB, strTB_TEMP)
MySQLDBAPI.save(strDB, strTB_TEMP, df)
strIndex = '_'.join(listColumnIndex)
strIndex = strIndex[:15]
sqlIndexExists = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE `TABLE_CATALOG` = 'def' AND `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = '{0}' AND `INDEX_NAME` = '{1}';".format(
strTB, strIndex)
con = MySQLDBAPI.connect(strDB)
df = pd.read_sql(sqlIndexExists, con)
con.dispose()
con = MySQLDBAPI.connectMDB(strDB)
cur = con.cursor()
if df.empty:
# insert 1 row
sqlCreate = "CREATE TABLE IF NOT EXISTS {0} SELECT * FROM {1} WHERE code IS NOT NULL LIMIT 1;".format(
strTB, strTB_TEMP)
cur.execute(sqlCreate)
# not null
sqlAlterColumnTradingDay = "ALTER TABLE {0} MODIFY trade_date datetime NOT NULL".format(
strTB)
cur.execute(sqlAlterColumnTradingDay)
sqlAlterColumnCode = "ALTER TABLE {0} MODIFY code varchar(30) NOT NULL".format(
strTB)
cur.execute(sqlAlterColumnCode)
for strColumn in set(listColumnIndex).difference(
set(['code', 'trade_date'])):
if strColumn.startswith('str') or strColumn in [
'freq', 'ContractRange'
]:
sql = "ALTER TABLE {0} MODIFY {1} varchar(20) NOT NULL".format(
strTB, strColumn)
else:
sql = "ALTER TABLE {0} MODIFY {1} double".format(
strTB, strColumn)
cur.execute(sql)
# add unique index if necessary
sqlAddIndexUnique = "CREATE UNIQUE INDEX {0} ON {1} ({2})".format(
strIndex, strTB, ','.join(listColumnIndex))
cur.execute(sqlAddIndexUnique)
# add non-unique index
for column in listColumnIndex:
sqlAddIndexOne = "CREATE INDEX {0} ON {1} ({0})".format(
column, strTB)
cur.execute(sqlAddIndexOne)
# temp -> formal table
print 'insert from TEMP to FORMAL'
cur.execute("INSERT IGNORE INTO {0} SELECT * FROM {1}".format(
strTB, strTB_TEMP))
con.commit()
cur.close()
con.close()
def getTradingData(dictOption):
d = {}
d['SecuCode'] = dictOption['SecuCode']
d['strDTStart'] = dictOption['dtStart'].strftime('%Y%m%d')
sql = 'select TradingDay,SecuCode,Open,High,Low,Close,TurnoverVolume,TurnoverRatio,ExeOpen,ExeClose,PB,PE,NonRestrictedCap from FactorDaily where TradingDay>="{d[strDTStart]}" and SecuCode="{d[SecuCode]}";'.format(
d=d)
con = MySQLDBAPI.connect(DB_NAME)
df = pd.read_sql(sql, con)
df = df[df['Close'] > 0.0]
df = df[df['TurnoverVolume'] > 0]
for strFactor in df.columns.intersection(['PE', 'PE3', 'PE5']):
df.ix[df[strFactor] < 0, strFactor] = 10000
con.dispose()
df['ExeOpen'] = df['ExeClose'] / df['Close'] * df['Open']
df = df[df['TradingDay'].isin(dfTradingDay.index)]
return df
def generateXY(df, NDayTest):
listFactor = []
boolMomentum = True
boolMomentumCarry = False
boolCarry = False
boolHF = True
# assign dfTS
if boolMomentumCarry or boolCarry:
dfTS = dictDataSpec['dfTS']
ixCommon = dfRaw.index.intersection(
dfTS.index.get_level_values('TradingDay'))
dfRaw.ix[ixCommon, 'TS2'] = dfTS.xs(dictDataSpec['Secu'],
level='SecuCode').ix[ixCommon,
'TS2']
# momentum
if boolMomentum:
listNDayMOM = [5, 10, 20, 60, 120]
strColumn = 'Close'
for nDayMOM in listNDayMOM:
strFactor = 'MOM%d' % nDayMOM
listFactor.append(strFactor)
df[strFactor] = df[strColumn] / df[strColumn].shift(nDayMOM) - 1
# carry rate Momentum
if boolMomentumCarry:
listNDayMOMCarry = [20, 40]
strColumn = 'TS2'
for nDayMOMCarry in listNDayMOMCarry:
strFactor = 'MOMCarry%d' % nDayMOMCarry
listFactor.append(strFactor)
df[strFactor] = df[strColumn] - df[strColumn].shift(nDayMOMCarry)
# carry rate
if boolCarry:
#listFactor.append('TS2')
dfRaw.ix[ixCommon, 'pRank'] = dfTS.xs(dictDataSpec['Secu'],
level='SecuCode').ix[ixCommon,
'pRank']
listFactor.append('pRank')
# read High Frequency Data
if boolHF:
NDayHFRolling = 20
import CommodityDataBase as CDB
reload(CDB)
import RoyalMountain.DataBase.MySQLDBAPI as MySQLDBAPI
reload(MySQLDBAPI)
con = MySQLDBAPI.connect(CDB.Utils.UtilsDB.strMySQLDB)
sql = "select * from CommodityDataBase.HFFactor_Daily where SecuCode='{0}'".format(
df.name)
dfHFFactor = pd.read_sql(sql, con)
dfHFFactor = dfHFFactor.set_index('TradingDay').sort_index()
listColumnHF = dfHFFactor.columns.difference(
['index', 'SecuCode', 'PVF_SplitStyle'])
dfHFFactor = dfHFFactor[listColumnHF]
dfHFFactor = dfHFFactor.rolling(NDayHFRolling).mean()
#dfHFFactor = dfHFFactor / dfHFFactor.shift(NDayHFRolling) - 1
con.dispose()
#raise Exception
# drop na
df = df[df[listFactor].isnull().sum(axis=1) == 0]
# Y
strColumnY = 'Close'
df['ReturnFuture'] = df[strColumnY] / df[strColumnY].shift(NDayTest) - 1
df['ReturnFuture'] = df['ReturnFuture'].shift(-NDayTest)
ixNotNull = df[df['ReturnFuture'].isnull() == False].index
df.ix[ixNotNull,
'Y'] = df.ix[ixNotNull,
'ReturnFuture'].apply(lambda x: -1 if x < 0 else 1)
return df, listFactor