def create_h5():
## ms = MSSQL(host="localhost",user="******",pwd="123456",db="PythonWeiboStatistics")
## #返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
## ms.ExecNonQuery("insert into WeiBoUser values('2','3')")
# ms = MSSQLHelper.getMSSQLInstance()
#
# #连接
# sqlserverCon = ms.getConnect()
# sql = 'SELECT NatureDay,WindCode,IfTradingDay FROM AsIndexComponentZZ500 order by NatureDay,WindCode'
#
# # resList = ms.ExecQuery(sql)
# # for (NatureDay,WindCode,IfTradingDay) in resList:
# # print(str(NatureDay) + ' ' + str(WindCode) + ' ' + str(IfTradingDay))
#
# #sqlserrver转成h5
# df = pd.read_sql(sql, con=sqlserverCon)
# print('df:')
# print(df)
# exportToHDFStore(df, StockConst.root + '/' + targetAddr + '.h5')
hs300df = SourceDataDao.load_h5(StockConst.ROOT + StockConst.HS300H5)
zz500df = SourceDataDao.load_h5(StockConst.ROOT + StockConst.ZZ500H5)
zz800df = hs300df.append(zz500df)
zz800df = zz800df.sort_values(by=['NatureDay', 'WindCode'], ascending=True)
SourceDataDao.export_to_hdfstore(zz800df,
StockConst.ROOT + StockConst.ZZ800H5)
def createFakeSignalData(addr):
signalData = SourceDataDao.load_signal_data(addr)
startDate = signalData.index[0][0]
endDate = signalData.index[-1][0]
startYear = DateUtil.datetime2_year_str(startDate)
endYear = DateUtil.datetime2_year_str(endDate)
FORMAT = "%d-%d-%d"
# c = time.strftime('%Y%m%d', time.strptime(y, '%Y-%m-%d'))
for year in range(int(startYear), int(endYear) + 1, 1):
for month in range(1, 13):
d = calendar.monthrange(year, month)
exportStartDate = FORMAT % (year, month, 1)
exportEndDate = FORMAT % (year, month, d[1])
# exportStartDate = str(year)+'-'+addZero(month)+'-'+'01'
# exportEndDate = str(year) + '-' + addZero(month) + '-' + getMonthEndDay(month)
subSignalData = SourceDataDao.select_by_date(
signalData, exportStartDate, exportEndDate)
print('exportStartDate:' + exportStartDate)
print('exportEndDate:' + exportEndDate)
print('subSignalData:' + str(len(subSignalData)))
if (len(subSignalData) > 0):
newSubSignalData = subSignalData.copy()
newSubSignalData['Mom2'] = subSignalData['Mom']
newSubSignalData['Mom3'] = subSignalData['Mom']
SourceDataDao.export_to_hdfstore(
newSubSignalData,
StockConst.ROOT + StockConst.FAKE_SIGNAL_DATA_H5)
def createSignalDataHill():
# dateList = ['2010-01-01','2010-01-02','2010-01-03','2010-01-04']
dateList1 = pd.date_range('2004-01-01', '2004-06-30')
dateList2 = pd.date_range('2004-07-01', '2004-12-31')
df1 = createSignalDataHillMain(dateList1, True)
df2 = createSignalDataHillMain(dateList2, False)
df = df1.append(df2)
# print(df)
SourceDataDao.export_to_hdfstore(
df, StockConst.ROOT + '/export/SignalDataHill.h5')
def createFakeIndexQuote2():
dateRange = pd.date_range(start='2005-01-01', end='2005-12-31')
codeList = [
StockConst.HS300_CODE, StockConst.ZZ500_CODE, StockConst.ZZ800_CODE
]
multi_index = pd.MultiIndex.from_product([dateRange, codeList],
names=['TradingDay', 'WindCode'])
indexDF = pd.DataFrame(index=multi_index, columns=['ChangePCT'])
indexDF['ChangePCT'] = 1 #百分之
# print(indexDF)
SourceDataDao.export_to_hdfstore(
indexDF, StockConst.ROOT + StockConst.FAKE_INDEX_QUOTE_H5_2)
indexDF.to_csv(StockConst.ROOT + '\\export\\fakeIndexQuoteH5_2.csv')
def mergeSignalWithIndexMain(signalDataDf, indexDf, addr):
# print(signalDataDf)
# print(indexDf)
# newIndexDf = indexDf.apply(convertDate, axis=1)
# # indexDf['NatureDay'] = DateUtil.str2Datetime(indexDf['NatureDay'])
# print(newIndexDf)
# 取交集
df = pd.merge(indexDf, signalDataDf, left_on=['NatureDay','WindCode'], right_index=True) # NatureDay,WindCode
# 取3列
df = df[['NatureDay','WindCode','Mom']]
# 重命名
df. rename(columns={'NatureDay': 'TradingDay'}, inplace=True)
# 设置索引
df = df.set_index(['TradingDay','WindCode'])
# 导出h5
SourceDataDao.export_to_hdfstore(df, StockConst.ROOT + addr)
def addBuySellFlgAndExport(addr):
dailyQuote = SourceDataDao.load_daily_quote(addr)
startDate = dailyQuote.index[0][0]
endDate = dailyQuote.index[-1][0]
startYear = DateUtil.datetime2_year_str(startDate)
endYear = DateUtil.datetime2_year_str(endDate)
FORMAT = "%d-%d-%d"
# c = time.strftime('%Y%m%d', time.strptime(y, '%Y-%m-%d'))
for year in range(int(startYear), int(endYear) + 1, 1):
for month in range(1 ,13):
d = calendar.monthrange(year, month)
exportStartDate = FORMAT % (year, month, 1)
exportEndDate = FORMAT % (year, month, d[1])
# exportStartDate = str(year)+'-'+addZero(month)+'-'+'01'
# exportEndDate = str(year) + '-' + addZero(month) + '-' + getMonthEndDay(month)
subDailyQuote = SourceDataDao.select_by_date(dailyQuote, exportStartDate, exportEndDate)
print('exportStartDate:' + exportStartDate)
print('exportEndDate:' + exportEndDate)
print( 'dailyQuote:' +str(len(subDailyQuote)))
if(len(subDailyQuote) > 0):
newSubDailyQuote = addBuySellFlg(subDailyQuote)
SourceDataDao.export_to_hdfstore(newSubDailyQuote, StockConst.ROOT + StockConst.NEW_DAILY_QUOTE_H5)
def addDelistFlgAndExport(sourceAddr,targetAddr):
dailyQuote = SourceDataDao.load_daily_quote(sourceAddr)
# print(dailyQuote)
lastTradingDay = max(list(dailyQuote.index.get_level_values(0).unique()))
# lastTradingDayTsp = pd.Timestamp(DateUtil.datetime2Str(lastTradingDay))
# print('lastTradingDay:'+lastTradingDayTsp)
# 全部退市股
delistDf = dailyQuote.groupby(level=StockConst.INNERCODE).apply(getDelist, lastTradingDay) # False True
# print(delistDf)
# 退市标记
dailyQuote.insert(len(dailyQuote.columns),'delistFlg', None) # 退市股delistFlg=1
for index, row in delistDf.iterrows():
tradingDay = index[1]
innerCode = index[2]
dailyQuote.ix[(tradingDay,innerCode), 'delistFlg'] = '1'
# print(dailyQuote.ix[(tradingDay,innerCode)])
# 导出到h5
SourceDataDao.export_to_hdfstore(dailyQuote, StockConst.ROOT + '/' + targetAddr + '.h5') # dailyQuote.to_csv(StockConst.root + '\export\'+targetAddr+'.csv')
def create_h5():
## ms = MSSQL(host="localhost",user="******",pwd="123456",db="PythonWeiboStatistics")
## #返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段
## ms.ExecNonQuery("insert into WeiBoUser values('2','3')")
ms = MSSQLHelper.get_mssql_instance_default()
#连接
sqlserverCon = ms.get_connect()
sql = 'SELECT NatureDay,WindCode,IfTradingDay FROM AsIndexComponentHS300 order by NatureDay,WindCode'
# resList = ms.ExecQuery(sql)
# for (NatureDay,WindCode,IfTradingDay) in resList:
# print(str(NatureDay) + ' ' + str(WindCode) + ' ' + str(IfTradingDay))
#sqlserrver转成h5
df = pd.read_sql(sql, con=sqlserverCon)
# print('df:')
# print(df)
# exportToHDFStore(df, StockConst.root + '/' + targetAddr + '.h5')
SourceDataDao.export_to_hdfstore(df, StockConst.ROOT + StockConst.HS300H5)
sqlserverCon.close()
