def census_the_trend(m):
msg("start census_the_trend")
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
if tables == None or retNum == 0:
msg("no tables like")
return
regex=u"^census_.*$"
#manual "cffe_pxspread_if1208_if1207_s" do not exist
tables = ["shfe_pxspread_ru1301_ru1305_s", "shfe_pxspread_ru1205_ru1201_s", "shfe_pxspread_rb1301_rb1305_s", "shfe_pxspread_rb1205_rb1201_s", \
"cffe_pxspread_if1201_if1202_s", "cffe_pxspread_if1202_if1201_s", "cffe_pxspread_if1202_if1203_s", \
"cffe_pxspread_if1203_if1202_s", "cffe_pxspread_if1203_if1204_s", "cffe_pxspread_if1204_if1203_s", \
"cffe_pxspread_if1204_if1205_s", "cffe_pxspread_if1205_if1204_s", "cffe_pxspread_if1205_if1206_s", \
"cffe_pxspread_if1206_if1205_s", "cffe_pxspread_if1206_if1207_s", "cffe_pxspread_if1207_if1206_s", \
"cffe_pxspread_if1207_if1208_s", "cffe_pxspread_if1208_if1209_s", \
"cffe_pxspread_if1209_if1208_s", "cffe_pxspread_if1209_if1210_s", "cffe_pxspread_if1210_if1209_s", \
"cffe_pxspread_if1210_if1211_s", "cffe_pxspread_if1211_if1210_s", "cffe_pxspread_if1211_if1212_s"]
for t in tables:
# if re.match(regex, t[0]) == None:
# traverse_table_get_trend(m, t[0])
traverse_table_get_trend(m, t)
def clean_by_table(m, tableName):
m.cur_s.execute("SELECT Date FROM %s WHERE id = (SELECT MAX(ID) FROM %s)" % (tableName, tableName))
try:
maxDate = str(m.cur_s.fetchone()[0])
except TypeError, e:
msg("no result in " + tableName)
return
def clean_by_table(m, tableName):
m.cur_s.execute("SELECT Date FROM %s WHERE id = (SELECT MAX(ID) FROM %s)" %
(tableName, tableName))
try:
maxDate = str(m.cur_s.fetchone()[0])
except TypeError, e:
msg("no result in " + tableName)
return
def rectify_the_data(m, date):
msg("start rectify the data")
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
if tables == None or retNum == 0:
msg("no tables like")
return
regex = u"^census_.*$"
for t in tables:
if re.match(regex, t[0]) == None:
rectify_by_date(m, date, t[0])
def data_clean(m, date):
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
if tables == None or retNum == 0:
msg("no tables like")
return
regex=u"^census_.*$"
for t in tables:
if re.match(regex, t[0]) != None:
continue
clean_by_date(m, date, t[0])
def rectify_the_data(m, date):
msg("start rectify the data")
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
if tables == None or retNum == 0:
msg("no tables like")
return
regex=u"^census_.*$"
for t in tables:
if re.match(regex, t[0]) == None:
rectify_by_date(m, date, t[0])
def data_clean(m, date):
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
if tables == None or retNum == 0:
msg("no tables like")
return
regex = u"^census_.*$"
for t in tables:
if re.match(regex, t[0]) != None:
continue
clean_by_date(m, date, t[0])
def insertDailyView(m):
for s in Constant.preContractID:
tableNameLike = "%" + s + "%"
sqlCmd = "show tables like " + "'" + tableNameLike + "'"
regex = '^.*_daily_view$'
regex2 = '^.*_main_contracts$'
retNum = m.cur.execute(sqlCmd)
tables = m.cur.fetchall()
if tables == None or retNum == 0:
msg("no tables like %s "%tableNameLike)
continue
for tmpT in tables:
tableName = tmpT[0]
if re.match(regex, tableName) == None and re.match(regex2, tableName) == None:
m.dealWithTheDay(getYestoday() + " 23:00:00", getToday() + " 23:00:00", tableName, s.lower())
def insertDailyView(m):
for s in Constant.preContractID:
tableNameLike = "%" + s + "%"
sqlCmd = "show tables like " + "'" + tableNameLike + "'"
regex = '^.*_daily_view$'
regex2 = '^.*_main_contracts$'
retNum = m.cur.execute(sqlCmd)
tables = m.cur.fetchall()
if tables == None or retNum == 0:
msg("no tables like %s " % tableNameLike)
continue
for tmpT in tables:
tableName = tmpT[0]
if re.match(regex, tableName) == None and re.match(
regex2, tableName) == None:
m.dealWithTheDay(getYestoday() + " 23:00:00",
getToday() + " 23:00:00", tableName,
s.lower())
def add_reverse_data(m):
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
if tables == None or retNum == 0:
msg("no tables like")
return
regex=u"^census_.*$"
for t in tables:
tableName = t[0]
if re.match(regex, t[0]) != None:
continue
splitName = tableName.split("_")
name1 = splitName[2]
name2 = splitName[3]
if name1 > name2:
reverseName = splitName[0] + "_" + splitName[1] + "_" + splitName[3] + "_" \
+ splitName[2] + "_" + splitName[4]
if tables.count((reverseName, )) > 0:
do_the_data_adding_job(m, tableName, reverseName)
def calculate_the_trend_avg(m):
contraList = ["cffe_if", "shfe_ru", "shfe_rb"]
startTm = "2012-08-01 00:00:00"
endTm = "2012-10-27 23:59:59"
for conName in contraList:
for typeStr in Constant.dictSpreadSType.keys():
for trendStr in Constant.kLineTrendType.keys():
selectSql = "SELECT MAX(LineNum), AVG(LineNum), MIN(LineNum) FROM census_pxspread_regression \
WHERE InsID = '%s' AND TYPE = %s AND RegType = %s AND BeginTM > '%s' AND EndTm < '%s'"\
%( conName, Constant.dictSpreadSType[typeStr], Constant.kLineTrendType[trendStr], startTm, endTm)
retNum = m.cur_s.execute(selectSql)
retRec = m.cur_s.fetchone()
if None == retRec or retNum ==0:
msg("no data in census_pxspread_regression of insId %s type %s regType %s beginTm %s, endTm %s"\
%(conName, typeStr, trendStr, startTm, endTm))
continue
print "insId %s type %s regType %s (max, avg, min)"%\
(conName, typeStr, trendStr) + str(retRec)
def add_reverse_data(m):
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
if tables == None or retNum == 0:
msg("no tables like")
return
regex = u"^census_.*$"
for t in tables:
tableName = t[0]
if re.match(regex, t[0]) != None:
continue
splitName = tableName.split("_")
name1 = splitName[2]
name2 = splitName[3]
if name1 > name2:
reverseName = splitName[0] + "_" + splitName[1] + "_" + splitName[3] + "_" \
+ splitName[2] + "_" + splitName[4]
if tables.count((reverseName, )) > 0:
do_the_data_adding_job(m, tableName, reverseName)
def do_the_data_adding_job(m, tableName, reverseName):
msg("do_the_data_adding_job from table %s to %s" %
(reverseName, tableName))
for typeStr in Constant.dictSpreadSType.keys():
selectSql = "SELECT Tm, Px FROM %s WHERE Type = %s ORDER BY Tm limit %s"\
%( tableName, Constant.dictSpreadSType[typeStr], str(Constant.MANum - 1))
retNum = m.cur_s.execute(selectSql)
retRec = m.cur_s.fetchall()
if None == retRec or retNum == 0:
msg("no manum data in table %s" % (tableName))
return
hisSql = "SELECT Tm, Px FROM %s WHERE TYPE = %s ORDER BY Tm DESC \
LIMIT %s" % (reverseName, Constant.dictSpreadSType[typeStr],
str(Constant.MANum - 1))
histNum = m.cur_s.execute(hisSql)
hisRec = m.cur_s.fetchall()
list(hisRec).reverse()
globalList = hisRec + retRec
if len(globalList) < Constant.MANum:
msg("no valid ma10 typeStr:%s, table :%s: " % (typeStr, tableName))
continue
pxList = [0] * Constant.MANum
for i in range(0, Constant.MANum):
if i < len(hisRec):
pxList[i] = globalList[i][1] * -1
else:
pxList[i] = globalList[i][1]
pList = Constant.MANum - 1
while pList < len(globalList):
stdDif = util.stdDeviation(pxList)
ma10 = util.MA_10(pxList)
updateSql = "UPDATE %s SET MA10 = %s, BollUp = %s, BollBo = %s \
WHERE Tm = '%s' AND TYPE=%s"\
%(tableName, str(ma10), str(ma10 + Constant.stdDeCount * stdDif), str(ma10 - Constant.stdDeCount * stdDif), str(globalList[pList][0]), Constant.dictSpreadSType[typeStr] )
m.cur_s.execute(updateSql)
if pList + 1 >= len(globalList):
break
tmpList = pxList[1:] + [globalList[pList + 1][1]]
pxList = tmpList
pList = pList + 1
def calculate_the_profit(m):
contraList = ["cffe_if", "shfe_ru", "shfe_rb"]
startTm = "2012-08-01 00:00:00"
endTm = "2012-10-27 23:59:59"
for conName in contraList:
for typeStr in Constant.dictSpreadSType.keys():
# upToDown
selectSql = "SELECT sum(BeginPx - EndPx - %s) FROM census_pxspread_regression \
WHERE InsID = '%s' AND TYPE = %s AND RegType = 2 AND BeginTM > '%s' AND EndTm < '%s'"\
%(str(Constant.tradeCost[conName]), conName, Constant.dictSpreadSType[typeStr], startTm, endTm)
retNum = m.cur_s.execute(selectSql)
upToDown = m.cur_s.fetchone()
if None == upToDown or retNum ==0:
msg("no data in census_pxspread_regression of insId %s type %s regType %s beginTm %s, endTm %s"\
%(conName, typeStr, "upToDown", startTm, endTm))
continue
print "insId %s type %s (upToDown)"%\
(conName, typeStr) + str(upToDown)
# downToUp
selectSql = "SELECT sum(EndPx - BeginPx - %s) FROM census_pxspread_regression \
WHERE InsID = '%s' AND TYPE = %s AND RegType = 4 AND BeginTM > '%s' AND EndTm < '%s'"\
%(str(Constant.tradeCost[conName]), conName, Constant.dictSpreadSType[typeStr], startTm, endTm)
retNum = m.cur_s.execute(selectSql)
downToUp = m.cur_s.fetchone()
if None == downToUp or retNum ==0:
msg("no data in census_pxspread_regression of insId %s type %s regType %s beginTm %s, endTm %s"\
%(conName, typeStr, "downToUp", startTm, endTm))
continue
print "insId %s type %s (downToUp)"%\
(conName, typeStr) + str(downToUp)
def do_the_data_adding_job(m, tableName, reverseName):
msg("do_the_data_adding_job from table %s to %s"%(reverseName, tableName))
for typeStr in Constant.dictSpreadSType.keys():
selectSql = "SELECT Tm, Px FROM %s WHERE Type = %s ORDER BY Tm limit %s"\
%( tableName, Constant.dictSpreadSType[typeStr], str(Constant.MANum - 1))
retNum = m.cur_s.execute(selectSql)
retRec = m.cur_s.fetchall()
if None == retRec or retNum ==0:
msg("no manum data in table %s"%(tableName))
return
hisSql = "SELECT Tm, Px FROM %s WHERE TYPE = %s ORDER BY Tm DESC \
LIMIT %s"%(reverseName, Constant.dictSpreadSType[typeStr], str(Constant.MANum - 1))
histNum = m.cur_s.execute(hisSql)
hisRec = m.cur_s.fetchall()
list(hisRec).reverse()
globalList = hisRec + retRec
if len(globalList) < Constant.MANum:
msg("no valid ma10 typeStr:%s, table :%s: "%(typeStr, tableName))
continue
pxList = [0] * Constant.MANum
for i in range(0, Constant.MANum):
if i < len(hisRec):
pxList[i] = globalList[i][1] * -1
else:
pxList[i] = globalList[i][1]
pList = Constant.MANum - 1
while pList < len(globalList):
stdDif = util.stdDeviation(pxList)
ma10 = util.MA_10(pxList)
updateSql = "UPDATE %s SET MA10 = %s, BollUp = %s, BollBo = %s \
WHERE Tm = '%s' AND TYPE=%s"\
%(tableName, str(ma10), str(ma10 + Constant.stdDeCount * stdDif), str(ma10 - Constant.stdDeCount * stdDif), str(globalList[pList][0]), Constant.dictSpreadSType[typeStr] )
m.cur_s.execute(updateSql)
if pList + 1 >= len(globalList):
break;
tmpList = pxList[1:] + [globalList[pList + 1][1]]
pxList = tmpList
pList = pList + 1
from matplotlib.dates import DayLocator, HourLocator, DateFormatter, drange
pl.plot(c, px, 'ro')
pl.plot(c, px, 'r')
pl.plot(c, ma, 'g:')
pl.plot(c, up, 'b--')
pl.plot(c, down, 'y--')
axes = pl.gca()
axes.set_xticks(range(1, count + 1))
axes.set_xticklabels(tm, rotation=90, size=6)
pl.grid(True)
pl.show()
if __name__ == "__main__":
msg(" census start work~")
m = PreProcess()
m.connectToMysql()
# census_the_trend(m)
# traverse_table_get_trend(m, "cffe_pxspread_if1210_if1303_s")
# calculate_the_trend_avg(m)
##
# calculate_the_profit(m)
draw_lines(m)
m.close()
msg("end of work of census")
def rectify_by_date(m, date, tableName):
msg("rectify the table %s of date %s" % (tableName, date))
contractName = tableName.split("_")[0] + "_" + tableName.split("_")[2][:2]
#rectify type
rectifyType = Constant.normalRectify
if Constant.rectifyRule.has_key(contractName):
rectifyType = Constant.rectifyRule[contractName]
# step 1 rectify the last 1hour k-line with the last 1min k-line
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt FROM %s WHERE Tm = '%s' and Type = 1"%\
(tableName, date + " " + rectifyType[-1])
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
bMin = m.cur_s.fetchone()
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt, Tm FROM %s WHERE Date = '%s' AND TYPE = 5 order by Tm desc limit 1"%\
(tableName, date)
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
bHour = m.cur_s.fetchone()
newPx = (bMin[1] * bMin[4] +
bHour[1] * bHour[4]) * 1.0 / (bMin[4] + bHour[4])
if bMin[2] > bHour[2]:
newHPx = bMin[2]
else:
newHPx = bHour[2]
if bMin[3] < bHour[3]:
newLPx = bMin[3]
else:
newLPx = bHour[3]
updateSql = "UPDATE %s SET CPx = %s, Px = %s, HPx = %s, LPx = %s, Cnt = %s \
WHERE Tm = '%s' AND TYPE=5" %(tableName, str(bMin[0]), str(newPx), str(newHPx), \
str(newLPx), str((bMin[4] + bHour[4])), str(bHour[5]) )
m.cur_s.execute(updateSql)
for boundary in rectifyType:
bTm = date + " " + boundary
#step 2 rectify the 1min, 3min, 5min, 15min
for i in xrange(1, 5):
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt FROM %s WHERE Tm = '%s' AND TYPE = %s"%\
(tableName, bTm, str(i))
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
bRec = m.cur_s.fetchone()
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt, Tm FROM %s WHERE Tm < '%s' AND \
TYPE = %s ORDER BY tm DESC LIMIT 1" % (tableName, bTm, str(i))
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
lRec = m.cur_s.fetchone()
newPx = (bRec[1] * bRec[4] +
lRec[1] * lRec[4]) * 1.0 / (bRec[4] + lRec[4])
if bRec[2] > lRec[2]:
newHPx = bRec[2]
else:
newHPx = lRec[2]
if bRec[3] < lRec[3]:
newLPx = bRec[3]
else:
newLPx = lRec[3]
updateSql = "UPDATE %s SET CPx = %s, Px = %s, HPx = %s, LPx = %s, Cnt = %s \
WHERE Tm = '%s' AND TYPE=%s" %(tableName, str(bRec[0]), str(newPx), str(newHPx), \
str(newLPx), str((bRec[4] + lRec[4])), str(lRec[5]), str(i) )
m.cur_s.execute(updateSql)
#step 3 delete the boundary record
deleteSql = "DELETE FROM %s WHERE Tm = '%s'" % (tableName, bTm)
m.cur_s.execute(deleteSql)
m.cur_s.execute(delSql)
def clean_by_table(m, tableName):
m.cur_s.execute("SELECT Date FROM %s WHERE id = (SELECT MAX(ID) FROM %s)" % (tableName, tableName))
try:
maxDate = str(m.cur_s.fetchone()[0])
except TypeError, e:
msg("no result in " + tableName)
return
m.cur_s.execute("SELECT Date FROM %s WHERE id = (SELECT MIN(ID) FROM %s)" % (tableName, tableName))
try:
minDate = str(m.cur_s.fetchone()[0])
except TypeError, e:
msg("no result in " + tableName)
return
# print minDate, maxDate
minDateList = minDate.split("-")
maxDateList = maxDate.split("-")
digitMinYear = int(minDateList[0])
digitMaxYear = int(maxDateList[0])
#debug
# print tableName, digitMinYear, digitMaxYear, minDateList, digitMaxYear
for year in range(digitMinYear, digitMaxYear + 1):
if (year == digitMinYear):
for month in range(int(minDateList[1]), 13):
def doTheDataJob(m):
today = getToday()
#step 1
#prepare the daily view
msg("start step 1 prepare the daily view ")
insertDailyView(m)
msg("finish step 1 prepare the daily view ")
#step 2
#calculate the max V
msg("start step 2 calculate the max V ")
for s in Constant.preContractID:
msg("begin generate main contract " + s)
m.findTheMainContractOfDay(s.lower(), today)
msg("finish step 2 calculate the max V ")
#step 3
#generate the Px spread
msg("start step 3 generate the Px spread ")
for s in Constant.preContractID:
msg("begin generate generateSpreadOfProduct " + s)
generateSpreadOfDay(m, s, today)
msg("finish step 3 generate the Px spread ")
#step 4
#calculate the spreadPx statistics k-line
msg("start step 4 calculate the spreadPx statistics k-line")
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.spreadcur.execute(sqlCmd)
tables = m.spreadcur.fetchall()
if tables == None or retNum == 0:
msg("no tables like ")
return
for t in tables:
m.calculate_the_k_by_date(getToday(), t[0])
msg("finish step 4 calculate the spreadPx statistics k-line")
#step 5 rectify the last k-line
rectify_the_data(m, today)
#step 6 clean dirty data
msg("start step 6 clean dirty data")
data_clean(m, today)
#step 7
#calculate the 10ma data
msg("start step 7 calculate the 10ma data")
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
regex = u"^census_.*$"
if tables == None or retNum == 0:
msg("no tables like ")
return
for t in tables:
if re.match(regex, t[0]) != None:
continue
m.calculate_10MA_by_date(getToday(), t[0])
msg("finish step 6 calculate the 10ma data")
#step 8 add reverse data to boll & ma
msg("start step 8 add reverse data to boll & ma ")
add_reverse_data(m)
#step 8 add reverse data to boll & ma
msg("start step 8 add reverse data to boll & ma ")
add_reverse_data(m)
if __name__ == "__main__":
msg("daily data process start work~")
m = PreProcess()
m.connectToMysql()
td = ['2012-10-26', '2012-10-29', '2012-10-30']
yd = ['2012-10-25', '2012-10-28', '2012-10-29']
#can define definedToday and definedYestoday to calculate andy date you want
# for i in range(0, 5):
# definedToday = td[i];definedYestoday = yd[i]
# doTheDataJob(m)
# definedToday = '2012-10-18'; definedYestoday = '2012-10-17'
definedToday = defaultDate; definedYestoday = defaultDate
# print getToday(), getYestoday()
def doTheDataJob(m):
today = getToday()
#step 1
#prepare the daily view
msg("start step 1 prepare the daily view ")
insertDailyView(m)
msg("finish step 1 prepare the daily view ")
#step 2
#calculate the max V
msg("start step 2 calculate the max V ")
for s in Constant.preContractID:
msg("begin generate main contract " + s)
m.findTheMainContractOfDay(s.lower(), today)
msg("finish step 2 calculate the max V ")
#step 3
#generate the Px spread
msg("start step 3 generate the Px spread ")
for s in Constant.preContractID:
msg("begin generate generateSpreadOfProduct " + s)
generateSpreadOfDay(m, s, today)
msg("finish step 3 generate the Px spread ")
#step 4
#calculate the spreadPx statistics k-line
msg("start step 4 calculate the spreadPx statistics k-line")
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.spreadcur.execute(sqlCmd)
tables = m.spreadcur.fetchall()
if tables == None or retNum == 0:
msg("no tables like ")
return
for t in tables:
m.calculate_the_k_by_date(getToday(), t[0])
msg("finish step 4 calculate the spreadPx statistics k-line")
#step 5 rectify the last k-line
rectify_the_data(m, today)
#step 6 clean dirty data
msg("start step 6 clean dirty data")
data_clean(m, today)
#step 7
#calculate the 10ma data
msg("start step 7 calculate the 10ma data")
sqlCmd = "SHOW TABLES LIKE '%pxspread%'"
retNum = m.cur_s.execute(sqlCmd)
tables = m.cur_s.fetchall()
regex=u"^census_.*$"
if tables == None or retNum == 0:
msg("no tables like ")
return
for t in tables:
if re.match(regex, t[0]) != None:
continue
m.calculate_10MA_by_date(getToday(), t[0])
msg("finish step 6 calculate the 10ma data")
#step 8 add reverse data to boll & ma
msg("start step 8 add reverse data to boll & ma ")
add_reverse_data(m)
def rectify_by_date(m, date, tableName):
msg("rectify the table %s of date %s"%(tableName, date))
contractName = tableName.split("_")[0] + "_" + tableName.split("_")[2][:2]
#rectify type
rectifyType = Constant.normalRectify
if Constant.rectifyRule.has_key(contractName):
rectifyType = Constant.rectifyRule[contractName]
# step 1 rectify the last 1hour k-line with the last 1min k-line
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt FROM %s WHERE Tm = '%s' and Type = 1"%\
(tableName, date + " " + rectifyType[-1])
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
bMin = m.cur_s.fetchone()
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt, Tm FROM %s WHERE Date = '%s' AND TYPE = 5 order by Tm desc limit 1"%\
(tableName, date)
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
bHour = m.cur_s.fetchone()
newPx = (bMin[1] * bMin[4] + bHour[1] * bHour[4]) * 1.0/(bMin[4] + bHour[4])
if bMin[2] > bHour[2]:
newHPx = bMin[2]
else:
newHPx = bHour[2]
if bMin[3] < bHour[3]:
newLPx = bMin[3]
else:
newLPx = bHour[3]
updateSql = "UPDATE %s SET CPx = %s, Px = %s, HPx = %s, LPx = %s, Cnt = %s \
WHERE Tm = '%s' AND TYPE=5"%(tableName, str(bMin[0]), str(newPx), str(newHPx), \
str(newLPx), str((bMin[4] + bHour[4])), str(bHour[5]) )
m.cur_s.execute(updateSql)
for boundary in rectifyType:
bTm = date + " " + boundary
#step 2 rectify the 1min, 3min, 5min, 15min
for i in xrange(1, 5):
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt FROM %s WHERE Tm = '%s' AND TYPE = %s"%\
(tableName, bTm, str(i))
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
bRec = m.cur_s.fetchone()
selectSql = "SELECT CPx, Px, HPx, LPx, Cnt, Tm FROM %s WHERE Tm < '%s' AND \
TYPE = %s ORDER BY tm DESC LIMIT 1"%(tableName, bTm, str(i))
recNum = m.cur_s.execute(selectSql)
if recNum > 0:
lRec = m.cur_s.fetchone()
newPx = (bRec[1] * bRec[4] + lRec[1] * lRec[4]) * 1.0/(bRec[4] + lRec[4])
if bRec[2] > lRec[2]:
newHPx = bRec[2]
else:
newHPx = lRec[2]
if bRec[3] < lRec[3]:
newLPx = bRec[3]
else:
newLPx = lRec[3]
updateSql = "UPDATE %s SET CPx = %s, Px = %s, HPx = %s, LPx = %s, Cnt = %s \
WHERE Tm = '%s' AND TYPE=%s"%(tableName, str(bRec[0]), str(newPx), str(newHPx), \
str(newLPx), str((bRec[4] + lRec[4])), str(lRec[5]), str(i) )
m.cur_s.execute(updateSql)
#step 3 delete the boundary record
deleteSql = "DELETE FROM %s WHERE Tm = '%s'"%(tableName, bTm)
m.cur_s.execute(deleteSql)
return
for t in tables:
if re.match(regex, t[0]) != None:
continue
m.calculate_10MA_by_date(getToday(), t[0])
msg("finish step 6 calculate the 10ma data")
#step 8 add reverse data to boll & ma
msg("start step 8 add reverse data to boll & ma ")
add_reverse_data(m)
if __name__ == "__main__":
msg("daily data process start work~")
m = PreProcess()
m.connectToMysql()
td = ['2012-10-26', '2012-10-29', '2012-10-30']
yd = ['2012-10-25', '2012-10-28', '2012-10-29']
#can define definedToday and definedYestoday to calculate andy date you want
# for i in range(0, 5):
# definedToday = td[i];definedYestoday = yd[i]
# doTheDataJob(m)
# definedToday = '2012-10-18'; definedYestoday = '2012-10-17'
definedToday = defaultDate
definedYestoday = defaultDate
# print getToday(), getYestoday()
def traverse_table_get_trend(m, tableName):
msg("traverse_table_get_trend of table: %s"%tableName)
contractName = tableName.split("_")[0] + "_" + tableName.split("_")[2][:2]
for typeStr in Constant.dictSpreadSType.keys():
selectSql = "SELECT Px, BollUp, MA10, BollBo, Tm FROM %s WHERE Type = %s AND MA10 IS NOT NULL"\
%( tableName, Constant.dictSpreadSType[typeStr])
retNum = m.cur_s.execute(selectSql)
retRec = m.cur_s.fetchall()
if None == retRec or retNum ==0:
msg("no data in table %s of type %s"%(tableName, typeStr))
continue
countFlag = False; lineCount = 0
#status 1 shang->zhong
#init the status machine
lastStatus = 0; newStatus = 0; beginTm = 'cannotbe'; beginPx = 0
lastStatus = get_px_status(retRec[0][0], retRec[0][1:-1])
if lastStatus == 1:
countFlag = True
beginTm = str(retRec[0][4])
beginPx = retRec[0][0]
beginMa = retRec[0][2]
beginStd = retRec[0][1] - retRec[0][2]
lineCount = 0
for i in xrange(1, len(retRec)):
newStatus = get_px_status(retRec[i][0], retRec[i][1:-1])
if countFlag:
if newStatus == 1 or newStatus == 2:
lineCount = lineCount + 1
continue
elif newStatus == 3 or newStatus == 4:
lineCount = lineCount + 1
myvalue = "'" + tableName + "', '" + contractName + "', " + Constant.dictSpreadSType[typeStr]\
+ ", 1, '" + beginTm + "', '" + str(retRec[i][4]) + "', " + str(beginPx) + ", " + \
str(retRec[i][0]) + ", " + str(lineCount) + ", " + str(beginMa) + ", " + str(beginStd)
insertSql = "INSERT INTO `census_pxspread_regression`(`TableName`,`InsID`,`Type`,\
`RegType`,`BeginTm`,`EndTm`,`BeginPx`,`EndPx`,`LineNum`, `BeginMA`, `BeginStd`) VALUES ( \
%s) on duplicate key update LineNum = %s ;"%(myvalue, str(lineCount))
m.cur_s.execute(insertSql)
countFlag = False; lineCount = 0
else:
if newStatus == 1:
countFlag = True
beginTm = str(retRec[i][4])
beginPx = retRec[i][0]
lineCount = 0
beginMa = retRec[i][2]
beginStd = retRec[i][1] - retRec[i][2]
countFlag = False; lineCount = 0
#status 2 shang->xia
#init the status machine
lastStatus = 0; newStatus = 0; beginTm = 'cannotbe'; beginPx = 0
lastStatus = get_px_status(retRec[0][0], retRec[0][1:-1])
if lastStatus == 1:
countFlag = True
beginTm = str(retRec[0][4])
beginPx = retRec[0][0]
lineCount = 0
beginMa = retRec[0][2]
beginStd = retRec[0][1] - retRec[0][2]
for i in xrange(1, len(retRec)):
newStatus = get_px_status(retRec[i][0], retRec[i][1:-1])
if countFlag:
if newStatus == 1 or newStatus == 2 or newStatus == 3:
lineCount = lineCount + 1
continue
elif newStatus == 4:
lineCount = lineCount + 1
myvalue = "'" + tableName + "', '" + contractName + "', " + Constant.dictSpreadSType[typeStr]\
+ ", 2, '" + beginTm + "', '" + str(retRec[i][4]) + "', " + str(beginPx) + ", " + \
str(retRec[i][0]) + ", " + str(lineCount) + ", " + str(beginMa) + ", " + str(beginStd)
insertSql = "INSERT INTO `census_pxspread_regression`(`TableName`,`InsID`,`Type`,\
`RegType`,`BeginTm`,`EndTm`,`BeginPx`,`EndPx`,`LineNum`, `BeginMA`, `BeginStd`) VALUES ( \
%s) on duplicate key update LineNum = %s ;"%(myvalue, str(lineCount))
m.cur_s.execute(insertSql)
countFlag = False; lineCount = 0
else:
if newStatus == 1:
countFlag = True
beginTm = str(retRec[i][4])
beginPx = retRec[i][0]
lineCount = 0
beginMa = retRec[i][2]
beginStd = retRec[i][1] - retRec[i][2]
countFlag = False; lineCount = 0
#status 3 xia->zhong
#init the status machine
lastStatus = 0; newStatus = 0; beginTm = 'cannotbe'; beginPx = 0
lastStatus = get_px_status(retRec[0][0], retRec[0][1:-1])
if lastStatus == 4:
countFlag = True
beginTm = str(retRec[0][4])
beginPx = retRec[0][0]
lineCount = 0
beginMa = retRec[0][2]
beginStd = retRec[0][1] - retRec[0][2]
for i in xrange(1, len(retRec)):
newStatus = get_px_status(retRec[i][0], retRec[i][1:-1])
if countFlag:
if newStatus == 3 or newStatus == 4:
lineCount = lineCount + 1
continue
elif newStatus == 1 or newStatus == 2:
lineCount = lineCount + 1
myvalue = "'" + tableName + "', '" + contractName + "', " + Constant.dictSpreadSType[typeStr]\
+ ", 3, '" + beginTm + "', '" + str(retRec[i][4]) + "', " + str(beginPx) + ", " + \
str(retRec[i][0]) + ", " + str(lineCount) + ", " + str(beginMa) + ", " + str(beginStd)
insertSql = "INSERT INTO `census_pxspread_regression`(`TableName`,`InsID`,`Type`,\
`RegType`,`BeginTm`,`EndTm`,`BeginPx`,`EndPx`,`LineNum`, `BeginMA`, `BeginStd`) VALUES ( \
%s) on duplicate key update LineNum = %s ;"%(myvalue, str(lineCount))
m.cur_s.execute(insertSql)
countFlag = False; lineCount = 0
else:
if newStatus == 4:
countFlag = True
beginTm = str(retRec[i][4])
beginPx = retRec[i][0]
lineCount = 0
beginMa = retRec[i][2]
beginStd = retRec[i][1] - retRec[i][2]
countFlag = False; lineCount = 0
#status 4 xia->shang
#init the status machine
lastStatus = 0; newStatus = 0; beginTm = 'cannotbe'; beginPx = 0
lastStatus = get_px_status(retRec[0][0], retRec[0][1:-1])
if lastStatus == 4:
countFlag = True
beginTm = str(retRec[0][4])
beginPx = retRec[0][0]
lineCount = 0
beginMa = retRec[0][2]
beginStd = retRec[0][1] - retRec[0][2]
for i in xrange(1, len(retRec)):
newStatus = get_px_status(retRec[i][0], retRec[i][1:-1])
if countFlag:
if newStatus == 4 or newStatus == 2 or newStatus == 3:
lineCount = lineCount + 1
continue
elif newStatus == 1:
lineCount = lineCount + 1
myvalue = "'" + tableName + "', '" + contractName + "', " + Constant.dictSpreadSType[typeStr]\
+ ", 4, '" + beginTm + "', '" + str(retRec[i][4]) + "', " + str(beginPx) + ", " + \
str(retRec[i][0]) + ", " + str(lineCount) + ", " + str(beginMa) + ", " + str(beginStd)
insertSql = "INSERT INTO `census_pxspread_regression`(`TableName`,`InsID`,`Type`,\
`RegType`,`BeginTm`,`EndTm`,`BeginPx`,`EndPx`,`LineNum`, `BeginMA`, `BeginStd`) VALUES ( \
%s) on duplicate key update LineNum = %s ;"%(myvalue, str(lineCount))
m.cur_s.execute(insertSql)
countFlag = False; lineCount = 0
else:
if newStatus == 4:
countFlag = True
beginTm = str(retRec[i][4])
beginPx = retRec[i][0]
lineCount = 0
beginMa = retRec[i][2]
beginStd = retRec[i][1] - retRec[i][2]
def clean_by_table(m, tableName):
m.cur_s.execute("SELECT Date FROM %s WHERE id = (SELECT MAX(ID) FROM %s)" %
(tableName, tableName))
try:
maxDate = str(m.cur_s.fetchone()[0])
except TypeError, e:
msg("no result in " + tableName)
return
m.cur_s.execute("SELECT Date FROM %s WHERE id = (SELECT MIN(ID) FROM %s)" %
(tableName, tableName))
try:
minDate = str(m.cur_s.fetchone()[0])
except TypeError, e:
msg("no result in " + tableName)
return
# print minDate, maxDate
minDateList = minDate.split("-")
maxDateList = maxDate.split("-")
digitMinYear = int(minDateList[0])
digitMaxYear = int(maxDateList[0])
#debug
# print tableName, digitMinYear, digitMaxYear, minDateList, digitMaxYear
for year in range(digitMinYear, digitMaxYear + 1):