def SetExtensionInfoIntoSql(self, msg):
try:
infoDict = OrderedDict()
idDict = OrderedDict()
if (msg['DB'] == 'DAY'):
timeOffset = datetime2days(msg['ANA']['TRADE_DATE'])
idDict['IC'] = int(msg['ANA']['INNER_CODE'])
idDict['TO'] = timeOffset
infoDict['_id'] = idDict
infoDict['TO'] = timeOffset
elif (msg['DB'] == 'WEEK'):
idDict['IC'] = int(msg['ANA']['INNER_CODE'])
idDict['YR'] = msg['ANA']['TRADE_YEAR']
idDict['WK'] = msg['ANA']['TRADE_WEEK']
infoDict['_id'] = idDict
infoDict['TO'] = datetime2days(msg['ANA']['LAST_TRADE_DATE'])
elif (msg['DB'] == 'MONTH'):
idDict['IC'] = int(msg['ANA']['INNER_CODE'])
idDict['YR'] = msg['ANA']['TRADE_YEAR']
idDict['MN'] = msg['ANA']['TRADE_MONTH']
infoDict['_id'] = idDict
infoDict['TO'] = datetime2days(msg['ANA']['LAST_TRADE_DATE'])
infoDict['OP'] = float(msg['ANA']['TOPEN'])
infoDict['CP'] = float(msg['ANA']['TCLOSE'])
infoDict['HP'] = float(msg['ANA']['THIGH'])
infoDict['LP'] = float(msg['ANA']['TLOW'])
infoDict['VL'] = long(msg['ANA']['TVOLUME'])
infoDict['AM'] = float(msg['ANA']['TVALUE'])
if ((msg['ANA']['EXCHR'] == None) or (msg['ANA']['EXCHR'] == 0)):
infoDict['TR'] = 0
else:
infoDict['TR'] = float(msg['ANA']['EXCHR'])
infoDict['CA'] = float(msg['ANA']['CHNG'])
infoDict['AA'] = [float(msg['EXT']['MA5']), float(msg['EXT']['MA10']), float(msg['EXT']['MA13']),
float(msg['EXT']['MA14']), float(msg['EXT']['MA20']),float(msg['EXT']['MA25']),
float(msg['EXT']['MA43'])]
infoDict['AV'] = [long(msg['EXT']['VOL5']), long(msg['EXT']['VOL10']), long(msg['EXT']['VOL30']),
long(msg['EXT']['VOL60']), long(msg['EXT']['VOL135'])]
infoDict['KV'] = [float(msg['EXT']['K9']), float(msg['EXT']['K34'])]
infoDict['DV'] = [float(msg['EXT']['D3']), float(msg['EXT']['D9'])]
infoDict['JV'] = [float(msg['EXT']['J3']), float(msg['EXT']['J9'])]
infoDict['DIF'] = [float(msg['EXT']['DIF1']), float(msg['EXT']['DIF2']), float(msg['EXT']['DIF3'])]
infoDict['DEA'] = [float(msg['EXT']['DEA1']), float(msg['EXT']['DEA2']), float(msg['EXT']['DEA3'])]
infoDict['MACD'] = [float(msg['EXT']['MACD1']), float(msg['EXT']['MACD2']), float(msg['EXT']['MACD3'])]
infoDict['ER'] = int(msg['XRXD'])
infoDict['RSV'] = [float(msg['EXT']['RSV9']), float(msg['EXT']['RSV34'])]
infoDict['EMA'] = [float(msg['EXT']['EMA5']), float(msg['EXT']['EMA6']), float(msg['EXT']['EMA12']),
float(msg['EXT']['EMA13']), float(msg['EXT']['EMA26']), float(msg['EXT']['EMA35'])]
for mongo in self.mongoList:
mongo.update(dbDict[msg['DB']]['TABLE']['MONGO'], 'data_'+self.colNameDict[msg['DB']][msg['EXT']['INNER_CODE']], {'_id':idDict}, infoDict)
#并表数据写入
if (msg['WMT']):
mongo.update(dbDict[msg['DB']]['TABLE']['MERGE'], 'data', {'_id':idDict}, infoDict)
#print dbDict[msg['DB']]['TABLE']['MONGO'],'data_'+self.colNameDict[msg['DB']][msg['EXT']['INNER_CODE']]
#print idDict
#print infoDict
except Exception,e:
print 'Set extension info into MSSQL failed,',e
print traceback.format_exc()
def updateMongo(self, updateData):
try:
infoDict = collections.OrderedDict()
idDict = collections.OrderedDict()
if (updateData['DB'] == 'DAY'):
timeOffset = common.datetime2days(updateData['ANA']['TRADE_DATE'])
idDict['IC'] = int(updateData['ANA']['INNER_CODE'])
idDict['TO'] = timeOffset
infoDict['_id'] = idDict
infoDict['TO'] = timeOffset
elif (updateData['DB'] == 'WEEK'):
idDict['IC'] = int(updateData['ANA']['INNER_CODE'])
idDict['YR'] = updateData['ANA']['TRADE_YEAR']
idDict['WK'] = updateData['ANA']['TRADE_WEEK']
infoDict['_id'] = idDict
infoDict['TO'] = common.datetime2days(updateData['ANA']['LAST_TRADE_DATE'])
elif (updateData['DB'] == 'MONTH'):
idDict['IC'] = int(updateData['ANA']['INNER_CODE'])
idDict['YR'] = updateData['ANA']['TRADE_YEAR']
idDict['MN'] = updateData['ANA']['TRADE_MONTH']
infoDict['_id'] = idDict
infoDict['TO'] = common.datetime2days(updateData['ANA']['LAST_TRADE_DATE'])
infoDict['OP'] = float(updateData['ANA']['TOPEN'])
infoDict['CP'] = float(updateData['ANA']['TCLOSE'])
infoDict['HP'] = float(updateData['ANA']['THIGH'])
infoDict['LP'] = float(updateData['ANA']['TLOW'])
infoDict['VL'] = long(updateData['ANA']['TVOLUME'])
infoDict['AM'] = float(updateData['ANA']['TVALUE'])
infoDict['TR'] = float(updateData['ANA']['EXCHR']) if (updateData['ANA']['EXCHR'] is not None) else float(0)
infoDict['CA'] = float(updateData['ANA']['CHNG']) if (updateData['ANA']['CHNG'] is not None) else float(0)
infoDict['AA'] = [float(updateData['EXT']['MA5']), float(updateData['EXT']['MA10']), float(updateData['EXT']['MA13']),
float(updateData['EXT']['MA14']), float(updateData['EXT']['MA20']),float(updateData['EXT']['MA25']),
float(updateData['EXT']['MA43'])]
infoDict['AV'] = [long(updateData['EXT']['VOL5']), long(updateData['EXT']['VOL10']), long(updateData['EXT']['VOL30']),
long(updateData['EXT']['VOL60']), long(updateData['EXT']['VOL135'])]
infoDict['KV'] = [float(updateData['EXT']['K9']), float(updateData['EXT']['K34'])]
infoDict['DV'] = [float(updateData['EXT']['D3']), float(updateData['EXT']['D9'])]
infoDict['JV'] = [float(updateData['EXT']['J3']), float(updateData['EXT']['J9'])]
infoDict['DIF'] = [float(updateData['EXT']['DIF1']), float(updateData['EXT']['DIF2']), float(updateData['EXT']['DIF3'])]
infoDict['DEA'] = [float(updateData['EXT']['DEA1']), float(updateData['EXT']['DEA2']), float(updateData['EXT']['DEA3'])]
infoDict['MACD'] = [float(updateData['EXT']['MACD1']), float(updateData['EXT']['MACD2']), float(updateData['EXT']['MACD3'])]
infoDict['ER'] = int(updateData['XRXD'])
infoDict['RSV'] = [float(updateData['EXT']['RSV9']), float(updateData['EXT']['RSV34'])]
infoDict['EMA'] = [float(updateData['EXT']['EMA5']), float(updateData['EXT']['EMA6']), float(updateData['EXT']['EMA12']),
float(updateData['EXT']['EMA13']), float(updateData['EXT']['EMA26']), float(updateData['EXT']['EMA35'])]
for mongoHandle in self.mongoHandleList:
mongoHandle.update(QN_constant.dbDict[updateData['DB']]['TABLE']['MONGO'], 'data', {'_id':idDict}, infoDict)
#并表数据写入
if (updateData['WMT']):
mongoHandle.update(QN_constant.dbDict[updateData['DB']]['TABLE']['MERGE'], 'data', {'_id':idDict}, infoDict)
except:
self.logHandle.logInfo(str(traceback.format_exc()))
print traceback.format_exc()
return None
def InsertRecentIntoMongo(mongoHandle, db, recentInfoDict, splitSecurityDict):
print '>>', sys._getframe().f_code.co_name
for innerCode in recentInfoDict:
infoDict = OrderedDict()
idDict = OrderedDict()
if (db == 'DAY'):
timeOffset = datetime2days(recentInfoDict[innerCode]['TRADE_DATE'])
idDict['IC'] = int(recentInfoDict[innerCode]['INNER_CODE'])
idDict['TO'] = timeOffset
infoDict['_id'] = idDict
infoDict['TO'] = timeOffset
elif (db == 'WEEK'):
idDict['IC'] = int(recentInfoDict[innerCode]['INNER_CODE'])
idDict['YR'] = recentInfoDict[innerCode]['TRADE_YEAR']
idDict['WK'] = recentInfoDict[innerCode]['TRADE_WEEK']
infoDict['_id'] = idDict
infoDict['TO'] = datetime2days(recentInfoDict[innerCode]['LAST_TRADE_DATE'])
elif (db == 'MONTH'):
idDict['IC'] = int(recentInfoDict[innerCode]['INNER_CODE'])
idDict['YR'] = recentInfoDict[innerCode]['TRADE_YEAR']
idDict['MN'] = recentInfoDict[innerCode]['TRADE_MONTH']
infoDict['_id'] = idDict
infoDict['TO'] = datetime2days(recentInfoDict[innerCode]['LAST_TRADE_DATE'])
infoDict['OP'] = float(recentInfoDict[innerCode]['TOPEN'])
infoDict['CP'] = float(recentInfoDict[innerCode]['TCLOSE'])
infoDict['HP'] = float(recentInfoDict[innerCode]['THIGH'])
infoDict['LP'] = float(recentInfoDict[innerCode]['TLOW'])
infoDict['VL'] = long(recentInfoDict[innerCode]['TVOLUME'])
infoDict['AM'] = float(recentInfoDict[innerCode]['TVALUE'])
infoDict['TR'] = float(0.0)
infoDict['CA'] = float(recentInfoDict[innerCode]['CHNG'])
infoDict['AA'] = [float(recentInfoDict[innerCode]['MA5']), float(recentInfoDict[innerCode]['MA10']), float(recentInfoDict[innerCode]['MA13']),
float(recentInfoDict[innerCode]['MA14']), float(recentInfoDict[innerCode]['MA20']),float(recentInfoDict[innerCode]['MA25']),
float(recentInfoDict[innerCode]['MA43'])]
infoDict['AV'] = [long(recentInfoDict[innerCode]['VOL5']), long(recentInfoDict[innerCode]['VOL10']), long(recentInfoDict[innerCode]['VOL30']),
long(recentInfoDict[innerCode]['VOL60']), long(recentInfoDict[innerCode]['VOL135'])]
infoDict['KV'] = [float(recentInfoDict[innerCode]['K9']), float(recentInfoDict[innerCode]['K34'])]
infoDict['DV'] = [float(recentInfoDict[innerCode]['D3']), float(recentInfoDict[innerCode]['D9'])]
infoDict['JV'] = [float(recentInfoDict[innerCode]['J3']), float(recentInfoDict[innerCode]['J9'])]
infoDict['DIF'] = [float(recentInfoDict[innerCode]['DIF1']), float(recentInfoDict[innerCode]['DIF2']), float(recentInfoDict[innerCode]['DIF3'])]
infoDict['DEA'] = [float(recentInfoDict[innerCode]['DEA1']), float(recentInfoDict[innerCode]['DEA2']), float(recentInfoDict[innerCode]['DEA3'])]
infoDict['MACD'] = [float(recentInfoDict[innerCode]['MACD1']), float(recentInfoDict[innerCode]['MACD2']), float(recentInfoDict[innerCode]['MACD3'])]
infoDict['ER'] = int(recentInfoDict[innerCode]['XRXD'])
infoDict['RSV'] = [float(recentInfoDict[innerCode]['RSV9']), float(recentInfoDict[innerCode]['RSV34'])]
infoDict['EMA'] = [float(recentInfoDict[innerCode]['EMA5']), float(recentInfoDict[innerCode]['EMA6']), float(recentInfoDict[innerCode]['EMA12']),
float(recentInfoDict[innerCode]['EMA13']), float(recentInfoDict[innerCode]['EMA26']), float(recentInfoDict[innerCode]['EMA35'])]
mongoHandle.update(dbDict[db]['TABLE']['MONGO'], 'data_'+ splitSecurityDict[db][innerCode], {'_id':idDict}, infoDict)
print '<<', sys._getframe().f_code.co_name
return None
def getRecentTradeTime(self):
'''
从中间库获取所有股票最后更新记录时间
'''
try:
sqlCmd = '''
SELECT
[INNER_CODE],
[TRADE_DATE]
FROM(
SELECT
[INNER_CODE],
[TRADE_DATE],
ROW_NUMBER() OVER(PARTITION BY INNER_CODE ORDER BY TRADE_DATE DESC) AS DESCENT
FROM
[QN_Quotation].[dbo].[SECURITY_ANALYSIS_DAY]
) t
WHERE
DESCENT = '1'
'''
recentDict = dict()
records = self.mssqlHandle.sqlQuery(sqlCmd)
for record in records:
recentDict[str(record['INNER_CODE'])] = common.datetime2days(record['TRADE_DATE'])
return recentDict
except IndexError:
self.logHandle.logInfo(str(traceback.format_exc()))
print 'Get stock lately update time failed.'
return None
def setData(self, records):
'''
Insert stock info into mongo.
'''
try:
#for mongoHandle in self.mongoHandleList:
# mongoHandle.remove()
recordNum = 0
recentDict = self.getRecentTradeTime()
for record in records:
#Get inward code
inwardId = record['INNER_CODE']
keyDoc = collections.OrderedDict()
keyDoc['IC'] = int(inwardId)
keyDoc['TO'] = int(common.datetime2days(record['EX_DIVI_DATE']))
#spec
spec = collections.OrderedDict()
spec['_id'] = keyDoc
#document
document = collections.OrderedDict()
document['_id'] = keyDoc
document['BS'] = float(0) if (record['CASH_BT'] is None) else float(record['CASH_BT'])
document['PR'] = float(0) if (record['ALLOT_PRC'] is None) else float(record['ALLOT_PRC'])
CirculationStockChangeRatio = float(0)
CirculationStockChangeRatio += float(0) if (record['CAP_SHR'] is None) else float(record['CAP_SHR'])
CirculationStockChangeRatio += float(0) if (record['ALLOT_PCT'] is None) else float(record['ALLOT_PCT'])
CirculationStockChangeRatio += float(0) if (record['BONUS_SHR'] is None) else float(record['BONUS_SHR'])
document['FR'] = CirculationStockChangeRatio / float(10)
for mongoHandle in self.mongoHandleList:
if (recentDict.get(inwardId) is None):
document['EF'] = int(0)
elif (recentDict.get(inwardId) >= keyDoc['TO']):
document['EF'] = int(1)
else:
document['EF'] = int(0)
mongoHandle.update('INFO', 'RIGHT', spec, document)
recordNum += 1
self.logHandle.logInfo('Update ration succeed, update records: ' + str(recordNum))
except:
self.logHandle.logInfo('Update ration failed, update records: ' + str(recordNum) + '/' + str(len(records)) + ', exception: ' + str(traceback.format_exc()))
print traceback.format_exc()
return None
