def computeVolume(records): count = len(records) for i in range(0, count): r10 = records[i:i + 10] l = [r.volume for r in r10] volume_avg_10 = reduce(lambda x, y: x + y, l) / len(l) volume_p = float(records[i].volume) / volume_avg_10 #对volume进行分类 volume_level = 0 if volume_p >= 3: volume_level = 31 elif volume_p < 3 and volume_p >= 2: volume_level = 21 elif volume_p < 2 and volume_p >= 0.5: volume_level = 19 elif volume_p < 0.5 and volume_p >= 0.33: volume_level = 12 elif volume_p < 0.33: volume_level = 13 else: volume_level = 0 sql = 'update stock_daily set volume_avg_10=%s,volume_level=%s where pk_id=%s' % ( volume_avg_10, volume_level, records[i].pk_id) dbw.query(sql)
def computeForecast(records, categories, allpp): count = len(records) for i in range(0, count): if not records[i].trend_3: continue if not records[i].trend_5: continue if not records[i].candle_sort: continue if not records[i].up_or_down: continue if not records[i].volume_level: continue if not records[i].jump_level: continue if not records[i].ma_5_10: continue fields = { 'trend_3': records[i].trend_3, 'trend_5': records[i].trend_5, 'candle_sort': records[i].candle_sort, #'up_or_down':records[i].up_or_down, 'volume_level': records[i].volume_level, 'jump_level': records[i].jump_level, 'ma_5_10': records[i].ma_5_10 } #print fields x = computeP.run(fields, categories, allpp) sql = 'update stock_daily set forecast=%s where pk_id=%s' % ( x[2] / x[1], records[i].pk_id) dbw.query(sql)
def computeForecast(records,categories,allpp): count = len(records) for i in range(0,count): if not records[i].trend_3: continue if not records[i].trend_5: continue if not records[i].candle_sort: continue if not records[i].up_or_down: continue if not records[i].volume_level: continue if not records[i].jump_level: continue if not records[i].ma_5_10: continue fields = {'trend_3':records[i].trend_3, 'trend_5':records[i].trend_5, 'candle_sort':records[i].candle_sort, #'up_or_down':records[i].up_or_down, 'volume_level':records[i].volume_level ,'jump_level':records[i].jump_level ,'ma_5_10':records[i].ma_5_10 } #print fields x = computeP.run(fields,categories,allpp) sql = 'update stock_daily set forecast=%s where pk_id=%s' % (x[2]/x[1],records[i].pk_id) dbw.query(sql)
def computeVolume(records): count = len(records) for i in range(0,count): r10 = records[i:i+10] l = [r.volume for r in r10] volume_avg_10 = reduce(lambda x, y: x + y, l) / len(l) volume_p = float(records[i].volume) / volume_avg_10 #对volume进行分类 volume_level = 0 if volume_p>=3: volume_level = 31 elif volume_p<3 and volume_p>=2: volume_level = 21 elif volume_p<2 and volume_p>=0.5: volume_level = 19 elif volume_p<0.5 and volume_p>=0.33: volume_level = 12 elif volume_p<0.33: volume_level = 13 else: volume_level = 0 sql = 'update stock_daily set volume_avg_10=%s,volume_level=%s where pk_id=%s' % (volume_avg_10,volume_level,records[i].pk_id) dbw.query(sql)
def tmp(): fields = [ 'open_price', 'high_price', 'low_price', 'close_price', 'volume', 'raise_drop', 'raise_drop_rate', 'volume_updown', 'volume_updown_rate' ] for f in fields: dbw.query('ALTER TABLE date_sum_infos ADD avg_%s decimal(8,2);' % (f)) return
def computeCandle(records): count = len(records) for i in range(0,count): r = records[i] result = comm.get_candle_2(r.open,r.close,r.high,r.low) up_or_down = 2 if result[1]>0 else 1 sql = 'update stock_daily set candle_sort=%s,up_or_down=%s where pk_id=%s' % (result[4],up_or_down,records[i].pk_id) dbw.query(sql)
def updateP(p,count,category,feature,featureField): cfKey = "%s|%s" % (feature,category) if featureField else (category if category else 'none') cfKey = "%s|%s" % (cfKey,featureField) row = list(dbr.select('category_feature_probability',where='cfKey=$cfKey',vars=locals())) sql = "insert into category_feature_probability set probability=%s,category='%s',feature='%s',cfKey='%s',count=%s,field='%s'" % (p,category,feature,cfKey,count,featureField) if row: sql = "update category_feature_probability set probability=%s,category='%s',feature='%s',count=%s,field='%s' where id=%s" % (p,category,feature,count,featureField,row[0].id) dbw.query(sql)
def update_last_high_low(): trade_dates = load_trade_dates() today = trade_dates[0].trade_date last_day = trade_dates[1].trade_date sql = """update stock_daily s, (SELECT stock_no,high_low FROM `stock_daily` where trade_date='%s' ) as last set s.last_high_low = last.high_low where s.trade_date='%s' and s.stock_no=last.stock_no""" % (last_day,today) dbw.query(sql)
def computeCandle(records): count = len(records) for i in range(0, count): r = records[i] result = comm.get_candle_2(r.open, r.close, r.high, r.low) up_or_down = 2 if result[1] > 0 else 1 sql = 'update stock_daily set candle_sort=%s,up_or_down=%s where pk_id=%s' % ( result[4], up_or_down, records[i].pk_id) dbw.query(sql)
def update_last_high_low(): trade_dates = load_trade_dates() today = trade_dates[0].trade_date last_day = trade_dates[1].trade_date sql = """update stock_daily s, (SELECT stock_no,high_low FROM `stock_daily` where trade_date='%s' ) as last set s.last_high_low = last.high_low where s.trade_date='%s' and s.stock_no=last.stock_no""" % (last_day, today) dbw.query(sql)
def update_v2(l): dbw.delete('stock_daily_records_tmp',where="pk_id>0",vars=locals()) dbw.supports_multiple_insert = True dbw.multiple_insert('stock_daily_records_tmp',l) dbw.query('''update stock_daily_records a,stock_daily_records_tmp t set a.raise_drop=t.raise_drop, a.raise_drop_rate=t.raise_drop_rate, a.volume_updown=t.volume_updown, a.volume_updown_rate=t.volume_updown_rate, a.last_update = t.last_update where a.pk_id=t.pk_id''')
def replace_z_records(record): # date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close, # raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update strfields = "date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close,raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update" fields = strfields.split(",") sql = "replace into forecast_backup.z_%s set %s" % ( record.stock_no, ",".join(["%s='%s'" % (k, v) for k, v in record.items() if k in fields and v is not None]), ) dbw.query(sql)
def update_v2(l): dbw.delete('stock_daily_records_tmp', where="pk_id>0", vars=locals()) dbw.supports_multiple_insert = True dbw.multiple_insert('stock_daily_records_tmp', l) dbw.query('''update stock_daily_records a,stock_daily_records_tmp t set a.raise_drop=t.raise_drop, a.raise_drop_rate=t.raise_drop_rate, a.volume_updown=t.volume_updown, a.volume_updown_rate=t.volume_updown_rate, a.last_update = t.last_update where a.pk_id=t.pk_id''')
def create_table_sql(): l = [] for fun in funs: for field,ftype in fields.items(): for day in days: field_final = '%s_%s_%s' % (fun,field,day) segment = "`%s` %s DEFAULT NULL" % (field_final,ftype) print segment l.append(segment) dbw.query(''' DROP TABLE IF EXISTS `stock_sums_info` ''') dbw.query(sql_t % (','.join(l)))
def computeTrend(records): count = len(records) for i in range(0,count): if count-i>2: t3 = comm.get_trend(records[i:i+3]) sql = 'update stock_daily set trend_3=%s where pk_id=%s' % (t3,records[i].pk_id) dbw.query(sql) if count-i>4: t5 = comm.get_trend(records[i:i+5]) sql = 'update stock_daily set trend_5=%s where pk_id=%s' % (t5,records[i].pk_id) dbw.query(sql)
def replace_z_records(record): #date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close, #raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update strfields = 'date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close,raise_drop,raise_drop_rate,is_traday,volume_updown,volume_updown_rate,create_date,last_update' fields = strfields.split(',') sql = "replace into forecast_backup.z_%s set %s" % ( record.stock_no, ','.join([ "%s='%s'" % (k, v) for k, v in record.items() if k in fields and v is not None ])) dbw.query(sql)
def update_avg_volume_10(): trade_dates = load_trade_dates() today = trade_dates[0].trade_date begin_day = trade_dates[-1].trade_date end_day = trade_dates[1].trade_date sql = """update stock_daily s, (SELECT stock_no,avg(volume) as avg_volume FROM `stock_daily` where trade_date BETWEEN '%s' and '%s' group by stock_no) as avg10 set s.volume_avg_10 = avg10.avg_volume where s.trade_date='%s' and s.stock_no=avg10.stock_no""" % (begin_day,end_day,today) dbw.query(sql)
def computeTrend(records): count = len(records) for i in range(0, count): if count - i > 2: t3 = comm.get_trend(records[i:i + 3]) sql = 'update stock_daily set trend_3=%s where pk_id=%s' % ( t3, records[i].pk_id) dbw.query(sql) if count - i > 4: t5 = comm.get_trend(records[i:i + 5]) sql = 'update stock_daily set trend_5=%s where pk_id=%s' % ( t5, records[i].pk_id) dbw.query(sql)
def update_avg_volume_10(): trade_dates = load_trade_dates() today = trade_dates[0].trade_date begin_day = trade_dates[-1].trade_date end_day = trade_dates[1].trade_date sql = """update stock_daily s, (SELECT stock_no,avg(volume) as avg_volume FROM `stock_daily` where trade_date BETWEEN '%s' and '%s' group by stock_no) as avg10 set s.volume_avg_10 = avg10.avg_volume where s.trade_date='%s' and s.stock_no=avg10.stock_no""" % ( begin_day, end_day, today) dbw.query(sql)
def updateP(p, count, category, feature, featureField): cfKey = "%s|%s" % (feature, category) if featureField else ( category if category else 'none') cfKey = "%s|%s" % (cfKey, featureField) row = list( dbr.select('category_feature_probability', where='cfKey=$cfKey', vars=locals())) sql = "insert into category_feature_probability set probability=%s,category='%s',feature='%s',cfKey='%s',count=%s,field='%s'" % ( p, category, feature, cfKey, count, featureField) if row: sql = "update category_feature_probability set probability=%s,category='%s',feature='%s',count=%s,field='%s' where id=%s" % ( p, category, feature, count, featureField, row[0].id) dbw.query(sql)
def run_strategy_sum(strategy_id): sql='''update trading_strategies s, (select strategy_id,max(earn_rate) as max_earn_rate, min(earn_rate) as min_earn_rate, sum(earnings)/sum(input_output)*100 as avg_earn_rate, count(*) as trade_count from `trading_records` where strategy_id=%s and buy_or_sell=1) as ss set s.max_earn_rate=ss.max_earn_rate, s.min_earn_rate = ss.min_earn_rate, s.avg_earn_rate = ss.avg_earn_rate, s.trade_count = ss.trade_count, s.last_update = now() where s.pk_id=ss.strategy_id; ''' % (strategy_id) dbw.query(sql)
def computeMA(records): count = len(records) for i in range(0,count): ma5 = ma10 = 0 if count-i>=5: l5 = [r.close for r in records[i:i+5]] ma5 = reduce(lambda x, y: x + y , l5) / 5 if count-i>=10: l10 = [r.close for r in records[i:i+10]] ma10 = reduce(lambda x, y: x + y , l10) / 10 ma_5_10 = 0 if ma5<>0 and ma10<>0: ma_5_10 = 2 if ma5>ma10 else 1 sql = 'update stock_daily set ma_5=%s,ma_10=%s,ma_5_10=%s where pk_id=%s' % (ma5,ma10,ma_5_10,records[i].pk_id) dbw.query(sql)
def run_avg_daily(startm, endm): fields = [ 'open_price', 'high_price', 'low_price', 'close_price', 'volume', 'raise_drop', 'raise_drop_rate', 'volume_updown', 'volume_updown_rate' ] str_what = ','.join( ['avg(%s) as avg_%s' % (field, field) for field in fields]) sql = "SELECT date,%s FROM `stock_daily_records` where date>='%s' and date<'%s' and volume>0 GROUP BY date order by date desc;" % ( str_what, startm, endm) l = list(dbr.query(sql)) for i in l: dbw.query( "update date_sum_infos set %s where date='%s'" % (','.join(['%s=%s' % (k, v) for k, v in i.items() if k != 'date']), i.date))
def computeMA(records): count = len(records) for i in range(0, count): ma5 = ma10 = 0 if count - i >= 5: l5 = [r.close for r in records[i:i + 5]] ma5 = reduce(lambda x, y: x + y, l5) / 5 if count - i >= 10: l10 = [r.close for r in records[i:i + 10]] ma10 = reduce(lambda x, y: x + y, l10) / 10 ma_5_10 = 0 if ma5 <> 0 and ma10 <> 0: ma_5_10 = 2 if ma5 > ma10 else 1 sql = 'update stock_daily set ma_5=%s,ma_10=%s,ma_5_10=%s where pk_id=%s' % ( ma5, ma10, ma_5_10, records[i].pk_id) dbw.query(sql)
def import_date_sums(str_date): sql = '''SELECT 'all' as stock_plate,count(*) as stock_count, avg(open_price) as open,avg(high_price) as high,avg(low_price) as low,avg(close_price) as close,avg(volume) as volume,avg(amount) as amount FROM `stock_daily_records` where date='%s' ''' % (str_date) #rows = dbr.query(sql) #insert_date_sum(str_date,rows) sql = '''SELECT stock_market_no as stock_plate,count(*) as stock_count, avg(open_price) as open,avg(high_price) as high,avg(low_price) as low,avg(close_price) as close,avg(volume) as volume,avg(amount) as amount FROM `stock_daily_records` where date='%s' and stock_market_no is not null group by stock_market_no''' % (str_date) rows = dbr.query(sql) insert_date_sum(str_date,rows) sql = '''SELECT stock_market_no as stock_plate,count(*) as stock_count FROM `stock_daily_records` where date='%s' and stock_market_no is not null and raise_drop>0 group by stock_market_no ''' % (str_date) rows = dbr.query(sql) for r in rows: stock_plate = r.stock_plate dbw.update('date_sums',price_up_count=r.stock_count, where="trade_date=$str_date and stock_plate=$stock_plate",vars=locals()) dbw.query("update date_sums set price_up_percent=price_up_count/stock_count where trade_date='%s'" % (str_date) )
def create_table(stock_no): dbw.query("DROP TABLE IF EXISTS forecast_backup.`z_%s`;" % (stock_no)) sql = """CREATE TABLE forecast_backup.`z_%s` ( `date` date NOT NULL DEFAULT '0000-00-00', `stock_no` varchar(10) DEFAULT NULL, `open_price` decimal(8,2) DEFAULT NULL, `high_price` decimal(8,2) DEFAULT NULL, `low_price` decimal(8,2) DEFAULT NULL, `close_price` decimal(8,2) DEFAULT NULL, `volume` bigint(11) DEFAULT NULL, `amount` bigint(11) DEFAULT NULL, `adj_close` decimal(8,2) DEFAULT NULL, `create_date` datetime DEFAULT NULL, `last_update` datetime DEFAULT NULL, `raise_drop` decimal(8,2) DEFAULT NULL, `raise_drop_rate` decimal(7,3) DEFAULT NULL, `is_traday` int(11) DEFAULT NULL, `volume_updown` bigint(11) DEFAULT NULL, `volume_updown_rate` decimal(30,2) DEFAULT NULL, PRIMARY KEY (`date`), KEY `volume_idx` (`volume`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;""" % (stock_no) dbw.query(sql)
def compute_tf_idf(): #数据量非常小的情况下可以这样用,数据量大就需要编写hadoop脚本 dbw.query("""update terms as t, (SELECT term_id, count(*) as count FROM term_doc group by term_id) as tmp set t.count_domain = tmp.count where t.term_id=tmp.term_id;""") #update term's idf r = dbw.select('subjects',what="count(*) as count") #select count(*) as count from subjects; doc_total_count = r[0].count dbw.query("update terms set idf_domain=LOG(%s/(count_domain+1))" % doc_total_count ) #update term's tf dbw.query("""update term_doc as t, (SELECT doc_id, sum(term_count) as doc_term_count FROM term_doc group by doc_id) as tmp set t.tf = t.term_count/tmp.doc_term_count where t.doc_id = tmp.doc_id""") #update term's tf-idf dbw.query("""update term_doc as td, terms as t set td.tf_idf = td.tf*t.idf_domain where td.term_id = t.term_id """)
def _______________________________update_date_sum_v2(plate=0): #速度太慢,淘汰 sql = '''update date_sum_infos a, (SELECT date,'%s' as plate, count(pk_id) as count FROM stock_daily_records WHERE raise_drop is not null and raise_drop>0 GROUP BY date) as b set a.price_up_count=b.count where a.date=b.date and a.plate=b.plate;''' % (plate) dbw.query(sql) sql = '''update date_sum_infos a, (SELECT date,'%s' as plate, count(pk_id) as count FROM stock_daily_records WHERE volume_updown_rate is not null and volume_updown_rate>0 GROUP BY date) as b set a.volumn_up_count=b.count where a.date=b.date and a.plate=b.plate;''' % (plate) dbw.query(sql) sql = '''update date_sum_infos set price_up_percent = price_up_count/total_count,volumn_up_percent=volumn_up_count/total_count;''' dbw.query(sql)
def compute_tf_idf(): #数据量非常小的情况下可以这样用,数据量大就需要编写hadoop脚本 dbw.query( """update terms as t, (SELECT term_id, count(*) as count FROM term_doc group by term_id) as tmp set t.count_domain = tmp.count where t.term_id=tmp.term_id;""") #update term's idf r = dbw.select( 'subjects', what="count(*) as count") #select count(*) as count from subjects; doc_total_count = r[0].count dbw.query("update terms set idf_domain=LOG(%s/(count_domain+1))" % doc_total_count) #update term's tf dbw.query("""update term_doc as t, (SELECT doc_id, sum(term_count) as doc_term_count FROM term_doc group by doc_id) as tmp set t.tf = t.term_count/tmp.doc_term_count where t.doc_id = tmp.doc_id""") #update term's tf-idf dbw.query("""update term_doc as td, terms as t set td.tf_idf = td.tf*t.idf_domain where td.term_id = t.term_id """)
def computeFuture(records): for r in records: i = records.index(r) if i>1: prate = int((records[i-2].close - records[i-1].close) *1000 / records[i-1].close) frange = getFutureRange(prate) sql = 'update stock_daily set future1_prate=%s,future1_range=%s where pk_id=%s' % (prate,frange,r.pk_id) dbw.query(sql) if i>2: prate = int((records[i-3].close - records[i-1].close) *1000 / records[i-1].close ) frange = getFutureRange(prate) sql = 'update stock_daily set future2_prate=%s,future2_range=%s where pk_id=%s' % (prate,frange,r.pk_id) dbw.query(sql) if i>3: prate = int((records[i-4].close - records[i-1].close) *1000 / records[i-1].close ) frange = getFutureRange(prate) sql = 'update stock_daily set future3_prate=%s,future3_range=%s where pk_id=%s' % (prate,frange,r.pk_id) dbw.query(sql)
def computeFuture(records): for r in records: i = records.index(r) if i > 1: prate = int((records[i - 2].close - records[i - 1].close) * 1000 / records[i - 1].close) frange = getFutureRange(prate) sql = 'update stock_daily set future1_prate=%s,future1_range=%s where pk_id=%s' % ( prate, frange, r.pk_id) dbw.query(sql) if i > 2: prate = int((records[i - 3].close - records[i - 1].close) * 1000 / records[i - 1].close) frange = getFutureRange(prate) sql = 'update stock_daily set future2_prate=%s,future2_range=%s where pk_id=%s' % ( prate, frange, r.pk_id) dbw.query(sql) if i > 3: prate = int((records[i - 4].close - records[i - 1].close) * 1000 / records[i - 1].close) frange = getFutureRange(prate) sql = 'update stock_daily set future3_prate=%s,future3_range=%s where pk_id=%s' % ( prate, frange, r.pk_id) dbw.query(sql)
def create_tables(): rows = da.stockbaseinfos.load_all_stocks() for r in rows: dbw.query("ALTER TABLE forecast_backup.`z_%s` ENGINE=MyISAM;" % (r.stock_no))
def update_high_low(stock_no,rows): rows['high_low_update_date'] = datetime.datetime.now() sql = 'update stock_base_infos set %s where stock_no=%s' %(','.join(["%s='%s'" % (k,v) for k,v in rows.items()]),stock_no) dbw.query( sql )
def insert_trend_data(pkid, stock_no, h5, h3, l5, l3): dbw.query( 'replace into trend_daily(pkid,stock_no,date,high5,high3,low5,low3)values' )
def import_trade_record(stock_no, rows): sql = "replace into forecast_backup.z_% (date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close)values()" % ( stock_no) dbw.query(sql)
def update_high_low(stock_no, rows): rows['high_low_update_date'] = datetime.datetime.now() sql = 'update stock_base_infos set %s where stock_no=%s' % (','.join( ["%s='%s'" % (k, v) for k, v in rows.items()]), stock_no) dbw.query(sql)
def import_history_rows(stock_no,rows): fields = ['date', 'open_price','high_price','low_price','close_price', 'volume','amount','adj_close'] for r in rows: str_field_value_list = ','.join(["%s='%s'" % (k,v) for k,v in r.items() if k in fields and v is not None]) dbw.query('replace into forecast_backup.z_%s set %s' % (stock_no,str_field_value_list))
def insertRealt(term_id, doc_id): dbw.query( 'replace into term_doc set term_id=%s,doc_id=%s,last_update=sysdate()' % (term_id, doc_id))
def update_marketcode(date): dbw.query("update `stock_daily_records` a, stock_base_infos b set a.stock_market_no=b.market_code where a.date='"+ date.strftime('%Y-%m-%d') +"' and a.stock_no = b.stock_no")
def import_trade_record(stock_no,rows): sql = "replace into forecast_backup.z_% (date,stock_no,open_price,high_price,low_price,close_price,volume,amount,adj_close)values()" % (stock_no) dbw.query(sql)
def insert_trend_data(pkid,stock_no,h5,h3,l5,l3): dbw.query('replace into trend_daily(pkid,stock_no,date,high5,high3,low5,low3)values')
def insertRealt(term_id,doc_id): dbw.query('replace into term_doc set term_id=%s,doc_id=%s,last_update=sysdate()'%(term_id,doc_id))
def remove_daily_records(date): results = dbr.select('stock_daily_records',where="date=$date",vars=locals()) fields = ['date','stock_no','open_price','high_price','low_price','close_price','create_date','last_update', 'volume','amount','adj_close','raise_drop','raise_drop_rate','is_traday','volume_updown','volume_updown_rate'] for r in results: str_field_value_list = ','.join(["%s='%s'" % (k,v) for k,v in r.items() if k in fields and v is not None]) dbw.query('replace into forecast_backup.z_%s set %s' % (r.stock_no,str_field_value_list))
def term_doc_insert(user_id,term_id,doc_id,term_count): dbw.query("replace into term_doc(user_id,term_id,doc_id,term_count)values(%s,%s,%s,%s)" % (user_id,term_id,doc_id,term_count))
def term_doc_insert(user_id, term_id, doc_id, term_count): dbw.query( "replace into term_doc(user_id,term_id,doc_id,term_count)values(%s,%s,%s,%s)" % (user_id, term_id, doc_id, term_count))