def saveExercises():
try:
filePath = ".\\ExerciseDataEnglish.xlsx"
if os.path.exists(filePath):
dataFrame = pd.read_excel(filePath, encoding='utf-8')
print(dataFrame)
if not dataFrame.empty:
BaseTools.save(dataFrame, DataTable.Exercises)
except Exception as e:
print(e)
def get_factor_data_by_stocklist(self, trade_date_str, codelist,
factorenname, tracetype):
# todo 待完成
pass
conn = bt.getConnection()
factorIdSql = "select factor_id from ST_FACTOR where factor_enname = '" + factorenname + "'"
factorIdDf = pd.read_sql(factorIdSql, conn)
factorIdList = factorIdDf.factor_id.tolist()
# print factorIdDf
factorIdStr = ''
for index in range(len(factorIdList)):
id = factorIdList[index]
factorIdStr += str(id)
if index != len(factorIdList) - 1:
factorIdStr += ','
# factorSql = "select * from st_factor where factor_id in ("+factorIdStr+") and "
factorSql = "SELECT FACTOR_ID, STOCKCODE, FACTOR_DATE, FACTOR_VALUE, REPORT_DATE from ST_FACTOR_VALUE where " \
"factor_date>=str_to_date('"+trade_date_str+"', '%Y-%m-%d') \
and FACTOR_ID = "
stockSql = ""
stockDf = ___getStockDF___(conn)
if codelist and len(codelist) > 0:
# 获取股票代码Sql
stockDf = stockDf[stockDf.STOCKCODE.isin(codelist)]
if len(codelist) < 50:
stockSql = ___getSecuritySql___(codelist)
#print stockDf
#print stockSql
factorSql += stockSql
factorDf = pd.read_sql(factorSql, conn)
def get_history_index_data_by_date(self, code, start_date_str,
end_date_str, frequency):
if code == None or start_date_str == None or end_date_str == None:
print 'param error'
return None
conn = bt.getConnection()
sql = "SELECT tradedate, CLOSEPRICE current_price from ST_INDEXTRADE_DATA where INDEXCODE = '" + code + "' and TRADEDATE>=DATE_FORMAT('" + start_date_str + "', '%Y-%m-%d') and TRADEDATE<=DATE_FORMAT('" + end_date_str + "', '%Y-%m-%d') ORDER BY TRADEDATE DESC"
df = pd.read_sql(sql, conn)
df['tradedate'] = df['tradedate'].map(lambda x: x.date())
return df
def get_history_data_by_stocklist(self, trade_date_str, codelist,
frequency, fq):
# todo 待完成
sql = "SELECT STOCKCODE, FACTOR_VALUE from ST_FACTOR_VALUE where FACTOR_ID=6 and FACTOR_DATE = str_to_date('" + trade_date_str + "', '%Y-%m-%d')"
conn = bt.getConnection()
df = pd.read_sql(sql, conn)
dfNew = df.set_index('STOCKCODE')
if codelist != None:
dfNew = dfNew[dfNew.index.isin(codelist)]
dfNew['FACTOR_VALUE'] = dfNew['FACTOR_VALUE'].map(lambda x: float(x))
return dfNew
def get_stocklist_by_type(self, trade_date, type):
trade_date_str = datetime.strftime(trade_date, '%Y-%m-%d')
# todo 待完成
stockcodeSql = ""
if type == '50':
stockcodeSql = "select STOCKCODE from W_CONSTITUTE_50 where tradedate = str_to_date('" + trade_date_str + "', '%Y-%m-%d')"
elif type == '300':
stockcodeSql = "select STOCKCODE from W_CONSTITUTE_300 where tradedate = str_to_date('" + trade_date_str + "', '%Y-%m-%d')"
elif type == '500':
stockcodeSql = "select STOCKCODE from W_CONSTITUTE_500 where tradedate = str_to_date('" + trade_date_str + "', '%Y-%m-%d')"
conn = bt.getConnection()
df = pd.read_sql(stockcodeSql, conn)
stockcodeList = df.STOCKCODE.tolist()
return stockcodeList
def updatePosition(positionList):
positionListNew = []
try:
for position in positionList:
# print order
v = (position.strategyId, position.stockcode, position.tradedate,
position.current_price, position.price, position.volume)
# print v
positionListNew.append(v)
conn = bt.getConnection()
cur = conn.cursor()
cur.executemany(
'insert into r_position(strategy_id, stockcode, tradedate, current_price, price, volume) values(%s, %s, %s, %s, %s, %s)',
positionListNew)
conn.commit()
except Exception, e:
traceback.print_exc()
indexDf = pd.read_sql(sql, conn)
return indexDf
'''
获取因子列表
'''
def getFactorList(conn):
sql = "select FACTOR_ID, FACTOR_NAME, FACTOR_ENNAME from ST_FACTOR ORDER BY factor_id"
factorDf = pd.read_sql(sql, conn)
return factorDf
conn = bt.getConnection()
#print getPositionList(1, conn)
#print getOrderList(1, conn)
#getLastAccountDate(None, conn)
'''
orderList = []
d = datetime.strptime('2017-4-8', '%Y-%m-%d')
o = StockOrder.StockOrder(1, '000001.SZ', d, 10.2, 100)
orderList.append(o)
#order(orderList)
positionList=[]
#p = StockPosition.StockPosition(4, '000001.SZ', d, 10.2, 100)
#positionList.append(p)
#updatePosition(positionList)
#savePosition(1, orderList)
def get_factor_data_by_date(self, code, start_date_str, end_date_str,
factorenname, tracetype):
factorDf = None
conn = bt.getConnection()
code = code[0:6]
df = bt.getTradeDay(conn, start_date_str, end_date_str, type=1)
#df = pd.DataFrame({'date': pd.Series(datelist)})
df['fv'] = np.NaN
df['reportdate'] = np.NaN
#print df
# 通过trade_date与ann_date比较，小于trade_date，返回因子值
start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
quarterlist = bt.getQuarterlistByDate(start_date, end_date)
#print quarterlist
for q in quarterlist:
try:
# 业绩报告（主表）
df1 = ts.get_report_data(q[0], q[1])
# 盈利能力--因子值数据
df2 = ts.get_profit_data(q[0], q[1])
df1 = df1[df1.code == code]
df2 = df2[df2.code == code]
# 去重
df1 = df1.sort_values(by=["code", "report_date"],
ascending=False)
df1 = df1.drop_duplicates(['code'])
# print df1
df1 = df1.reset_index(drop=True)
df2 = df2.drop_duplicates(['code'])
df2 = df2.reset_index(drop=True)
df1New = df1.set_index('code')
df2New = df2.set_index('code')
df2New['ann_date'] = df1New['report_date']
#print len(df2New)
df2New = df2New[df2New.ann_date == df2New.ann_date]
df2New = df2New[df2New[factorenname] == df2New[factorenname]]
df2New['ann_date'] = df2New['ann_date'].map(
lambda x: str(q[0]) + '-' + x)
df2New['ann_date_new'] = df2New['ann_date'].map(
lambda x: datetime.strptime(x, '%Y-%m-%d'))
#print len(df2New)
# df2New['date'] = df2New.index
# df2New = df2New[df2New.index.isin(codelistNew)]
if q[1] == 1:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '3-31', '%Y-%m-%d')
elif q[1] == 2:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '6-30', '%Y-%m-%d')
elif q[1] == 3:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '9-30', '%Y-%m-%d')
elif q[1] == 4:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '12-30', '%Y-%m-%d')
factorDfTemp = df2New.reset_index(drop=True)
factorDf = pd.concat([factorDf, factorDfTemp])
except Exception, e:
traceback.print_exc()
continue
def get_factor_data_by_stocklist(self, trade_date_str, codelist,
factorenname, tracetype):
factorDf = None
codelistNew = []
if len(codelist) > 0:
for code in codelist:
codelistNew.append(code[0:6])
# 通过时间得到查询的年份、季度，往前顺延两个季度
# 通过因子名称找出调用的api---业绩报告（主表）、如：偿债能力
# 分别查询获取财务因子，年份、季度、接口
df = pd.DataFrame({'code': pd.Series(codelist)})
df['fv'] = np.NaN
df['reportdate'] = np.NaN
#print df
# 通过trade_date与ann_date比较，小于trade_date，返回因子值
trade_date = datetime.strptime(trade_date_str, '%Y-%m-%d')
quarterlist = bt.getLastestQuarterlistByDate(trade_date)
#print quarterlist
for q in quarterlist:
try:
# 业绩报告（主表）
df1 = ts.get_report_data(q[0], q[1])
# 盈利能力--因子值数据
df2 = ts.get_profit_data(q[0], q[1])
# 去重
df1 = df1.sort_values(by=["code", "report_date"],
ascending=False)
#print df1
df1 = df1.drop_duplicates(['code'])
#print df1
df1 = df1.reset_index(drop=True)
df2 = df2.drop_duplicates(['code'])
df2 = df2.reset_index(drop=True)
df1New = df1.set_index('code')
df2New = df2.set_index('code')
df2New['ann_date'] = df1New['report_date']
#print df2New
#print len(df2New)
df2New = df2New[df2New.ann_date == df2New.ann_date]
#print df2New
df2New = df2New[df2New[factorenname] == df2New[factorenname]]
#print df2New
#print df2New
if len(df2New) == 0:
continue
df2New['ann_date'] = df2New['ann_date'].map(
lambda x: str(q[0]) + '-' + str(x))
#print df2New['ann_date']
df2New['ann_date_new'] = df2New['ann_date'].map(
lambda x: transfer(x))
#print len(df2New)
df2New['code'] = df2New.index
df2New = df2New[df2New.index.isin(codelistNew)]
if q[1] == 1:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '3-31', '%Y-%m-%d')
elif q[1] == 2:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '6-30', '%Y-%m-%d')
elif q[1] == 3:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '9-30', '%Y-%m-%d')
elif q[1] == 4:
df2New['reportdate'] = datetime.strptime(
str(q[0]) + '-' + '12-30', '%Y-%m-%d')
factorDfTemp = df2New.reset_index(drop=True)
factorDf = pd.concat([factorDf, factorDfTemp])
#print factorDf
#break
except Exception, e:
traceback.print_exc()
continue
def get_factor_data_by_datecode(self, codelist, start_date_str,
end_date_str, factorenname, tracetype):
start = time.time()
startdate = None
end_date = None
start_date_str_new = None
end_date_str_new = None
factorDateSql = ""
stockSql = ""
factorId = ""
conn = bt.getConnection()
c = conn.cursor()
factorIdSql = "select factor_id from ST_FACTOR where factor_enname = '" + factorenname + "'"
c.execute(factorIdSql)
factorIdResult = c.fetchone()
factorId = factorIdResult[0]
tradeDayDf = bt.getTradeDay(conn, start_date_str, end_date_str)
# tradeDayDfNew = tradeDayDf.set_index('TRADEDATE')
tradeDayDf['FACTOR_ID'] = factorId
tradeDayDf['STOCKCODE'] = np.NaN
tradeDayDf['FACTOR_VALUE'] = np.NaN
# 获取股票代码Sql
if codelist and len(codelist) > 0 and len(codelist) < 50:
stockSql = ___getSecuritySql___(codelist)
#print stockSql
df = pd.DataFrame()
if tracetype == 0: # 不追溯
if start_date_str != None and end_date_str != None:
start_date_str_new = start_date_str
end_date_str_new = end_date_str
factorDateSql = "factor_date>=str_to_date('" + start_date_str_new + "', '%Y-%m-%d') and factor_date<=str_to_date('" + end_date_str_new + "', '%Y-%m-%d')"
factorSql = "SELECT FACTOR_ID, STOCKCODE, FACTOR_DATE, FACTOR_VALUE, REPORT_DATE from ST_FACTOR_VALUE where " \
+ factorDateSql + " and FACTOR_ID = "+str(factorId) + stockSql
factorDf = pd.read_sql(factorSql, conn)
elif tracetype == -1: # 追溯到底
if start_date_str != None and end_date_str != None:
start_date_str_new = start_date_str
end_date_str_new = end_date_str
factorDateSql = "factor_date>=str_to_date('" + start_date_str_new + "', '%Y-%m-%d') and factor_date<=str_to_date('" + end_date_str_new + "', '%Y-%m-%d')"
#
factorSql = "SELECT FACTOR_ID, STOCKCODE, FACTOR_DATE, FACTOR_VALUE, REPORT_DATE from ST_FACTOR_VALUE where " \
+ factorDateSql + " and FACTOR_ID = " + str(factorId) + stockSql
factorSql = factorSql + " union all " + " SELECT FACTOR_ID, STOCKCODE, FACTOR_DATE, FACTOR_VALUE, REPORT_DATE from (SELECT FACTOR_ID, STOCKCODE, FACTOR_DATE, FACTOR_VALUE, REPORT_DATE from ST_FACTOR_VALUE where \
FACTOR_ID = " + str(
factorId
) + stockSql + " order by FACTOR_DATE desc) new group by STOCKCODE, FACTOR_DATE"
#print factorSql
factorDf = pd.read_sql(factorSql, conn)
elif tracetype > 0: # 追溯某段时长
if start_date_str != None and end_date_str != None:
startdate = datetime.strptime(
start_date_str,
"%Y-%m-%d") + timedelta(days=-365 * tracetype)
start_date_str_new = startdate.strftime('%Y-%m-%d')
end_date_str_new = end_date_str
factorDateSql = "factor_date>=str_to_date('" + start_date_str_new + "', '%Y-%m-%d') and factor_date<=str_to_date('" + end_date_str_new + "', '%Y-%m-%d')"
factorSql = "SELECT FACTOR_ID, STOCKCODE, FACTOR_DATE, FACTOR_VALUE, REPORT_DATE from ST_FACTOR_VALUE where " \
+ factorDateSql + " and FACTOR_ID = " + str(factorId) + stockSql
factorDf = pd.read_sql(factorSql, conn)
factorDf = factorDf.sort_values(by='FACTOR_DATE')
factorDfGroup = factorDf.groupby('STOCKCODE')
for (key, value) in factorDfGroup:
# print key
#print value
tradeDayDfCopy = tradeDayDf.copy()
tradeDayDfCopy['STOCKCODE'] = key
# 排序
# 按日期分别追溯查询因子值
tradeDayDfCopy = tradeDayDfCopy.apply(___updateTradeDayDfRow___,
args=(value, ),
axis=1)
if len(df) == 0:
df = tradeDayDfCopy
else:
df = pd.concat([df, tradeDayDfCopy])
#print df
# 过滤获取股票
if codelist and len(codelist) >= 50:
df = df[df.STOCKCODE.isin(codelist)]
finish = time.time()
print(finish - start)
return df