예제 #1
0
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)
예제 #2
0
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
예제 #3
0
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
예제 #4
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])
예제 #5
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])
예제 #6
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])
예제 #7
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])
예제 #8
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())
예제 #9
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())
예제 #10
0
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)
예제 #11
0
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)
예제 #12
0
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)
예제 #13
0
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
예제 #14
0
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)
예제 #15
0
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
예제 #16
0
    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")
예제 #17
0
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)
예제 #18
0
    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):
예제 #19
0
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)
예제 #20
0
        
    

    #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()
예제 #21
0
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)
예제 #22
0
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)
예제 #23
0
        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()
예제 #24
0
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]
예제 #25
0
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):