class BoxTrend(object):
def __init__(self,
trend_start_day,
pre_start_day,
trend_percent,
up_percent,
trend_end_day=None):
self.runner = SqlRunner()
self.trend_start_day = trend_start_day
self.trend_end_day = trend_end_day if trend_end_day else stock_latest_day(
self.runner)
self.pre_start_day = pre_start_day
self.pre_end_day = trend_start_day - datetime.timedelta(days=1)
self.trend_percent = trend_percent
self.up_percent = up_percent
def prerequsite(self):
aux_tables.refresh_stock_min_max_day(self.runner)
aux_tables.refresh_stock_max_close_day(self.runner, self.pre_start_day,
self.pre_end_day)
aux_tables.refresh_peak_close(self.runner, self.trend_start_day,
self.trend_end_day)
aux_tables.create_recommend_table(self.runner)
def recommend(self):
sql = """
DELETE FROM {name} WHERE update_time = now()::DATE AND reason = '{reason}';
INSERT INTO {name}
WITH stock_percent AS(
SELECT t.code, (close_list[1] - close_list[2] + 0.0) / close_list[2] as percent
FROM (
SELECT
m.code,
array_agg(close ORDER BY date DESC) AS close_list
FROM {stock_peak} m
WHERE rank <= 2
GROUP BY m.code
) t
WHERE (close_list[1] - close_list[2] + 0.0) / close_list[2] <= %(trend_percent)s
)
SELECT DISTINCT m.code, i.name, i.industry, i.esp::FLOAT8, i.pe, p.percent,
now()::DATE as update_time, '{reason}' as reason
FROM {stock_peak} m, {stock_min_max} d, {stock_info} i, stock_percent p, {stock_max} h
WHERE m.code = d.code AND d.min_date <= %(trend_start)s AND m.code = p.code AND
m.rank = 1 AND m.date = %(trend_end_day)s AND m.code = i.code AND i.name NOT LIKE '%%ST%%' AND
m.code = h.code and (h.close - m.close + 0.0) / h.close >= %(up_percent)s
ORDER BY percent DESC ;
""".format(stock_max=STOCK_MAX_CLOSE_DAY,
stock_min_max=STOCK_MIN_MAX_DAY,
reason='up_trend',
stock_info=STOCK_INFO,
stock_peak=STOCK_PEAK_CLOSE,
name=STOCK_RECOMMEND)
params = {
'trend_percent': self.trend_percent,
'up_percent': self.up_percent,
'trend_end_day': self.trend_end_day,
'trend_start': self.trend_start_day
}
self.runner.execute(sql, params)
class UpMA(object):
def __init__(self,
end_day=None,
push_days=7,
up_days=3,
volume_increase=2):
self.runner = SqlRunner()
self.end_day = end_day if end_day else stock_latest_day(self.runner)
self.push_days = push_days
self.up_days = up_days
self.volume_increase = volume_increase
def recommend(self):
sql = """
DELETE FROM {name} WHERE update_time = now()::DATE and reason = '{reason}';
INSERT INTO {name}
WITH ma_close AS (
select distinct s.code
from (
SELECT code, max_close
FROM (
select code,
sum(volume) filter (where p_change < 0) as cons,
sum(volume) filter (where p_change >= 0) as pos,
max(close) as max_close,
min(close) as min_close
from {stock_daily_more}
where
date >= %(end_day)s - %(push_days)s
group by code
having count(*) >= %(up_days)s
order by code
) t
WHERE t.min_close > 0 AND t.pos >= %(volume_increase)s * t.cons AND
(t.max_close - t.min_close) / t.min_close >= 0.04
) s,
{stock_daily_more} t
WHERE s.code = t.code AND
t.date = %(end_day)s AND
t.open > 0 AND
t.close >= ma5 AND t.close >= ma10 AND t.close >= ma20 AND
t.close >= ma30 AND t.close >= ma60 AND t.close >= s.max_close AND
t.p_change <= 5.0 AND (t.high - t.close) / t.open <= 0.01
)
SELECT DISTINCT c.code, i.name, i.industry, i.esp, i.pe, 0.0,
now()::DATE as update_time, '{reason}' as reason
FROM {stock_info} i, ma_close c
WHERE i.code = c.code
ORDER BY industry DESC ;
""".format(name=STOCK_RECOMMEND,
reason='up_ma',
stock_daily_more=STOCK_DAILY_MORE_MA,
stock_info=STOCK_INFO)
params = {
'end_day': self.end_day,
'push_days': self.push_days,
'up_days': self.up_days,
'volume_increase': self.volume_increase
}
self.runner.execute(sql, params)
def __init__(self, trend_start_day, pre_start_day, trend_percent,
up_percent):
self.trend_start_day = trend_start_day
self.pre_start_day = pre_start_day
self.pre_end_day = trend_start_day - datetime.timedelta(days=1)
self.runner = SqlRunner()
self.trend_percent = trend_percent
self.up_percent = up_percent
class AscendIncrease(object):
def __init__(self, trend_start_day, trend_end_day=None):
self.runner = SqlRunner()
self.trend_start_day = trend_start_day
self.trend_end_day = trend_end_day if trend_end_day else stock_latest_day(
self.runner)
def prerequsite(self):
aux_tables.refresh_stock_days()
aux_tables.refresh_peak_close(self.runner, self.trend_start_day,
self.trend_end_day)
aux_tables.refresh_low_close(self.runner, self.trend_start_day,
self.trend_end_day)
aux_tables.create_recommend_table(self.runner)
def recommend(self):
sql = """
DELETE FROM {name} WHERE update_time = now()::DATE AND reason = '{reason}';
INSERT INTO {name}
SELECT DISTINCT i.code, i.name, i.industry, i.esp, i.pe, 0.0,
now()::DATE as update_time, '{reason}' as reason
FROM (
SELECT DISTINCT s.code
FROM
(
SELECT l.code, count(*) filter (where l.rank = l.date_rank) as l_cnt,
max(l.rank) as l_t_cnt
FROM {stock_low} l
GROUP BY l.code
) s,
(
SELECT p.code, count(*) filter (where p.rank = p.date_rank) as p_cnt,
max(p.rank) as p_t_cnt
FROM {stock_peak} p
GROUP BY p.code
) t
where s.code = t.code and s.l_cnt = s.l_t_cnt and t.p_cnt = t.p_t_cnt
) s,
{stock_info} i,
{stock_daily} d,
{stock_days} t
WHERE s.code = i.code and s.code = d.code and d.date = %(trend_end)s AND
d.close >= d.ma5 and d.close >= d.ma10 and d.close >= d.ma20 AND
d.p_change <= 9.0 and s.code = t.code and t.num_days > 30 and d.open > 0
""".format(stock_max=STOCK_MAX_CLOSE_DAY,
stock_min_max=STOCK_MIN_MAX_DAY,
reason='asc',
stock_info=STOCK_INFO,
stock_peak=STOCK_PEAK_CLOSE,
name=STOCK_RECOMMEND,
stock_low=STOCK_LOW_CLOSE,
stock_daily=STOCK_DAILY,
stock_days=STOCK_DAYS)
params = {
'trend_end': self.trend_end_day,
'trend_start': self.trend_start_day
}
self.runner.execute(sql, params)
def __init__(self,
end_day=None,
push_days=7,
up_days=3,
volume_increase=2):
self.runner = SqlRunner()
self.end_day = end_day if end_day else stock_latest_day(self.runner)
self.push_days = push_days
self.up_days = up_days
self.volume_increase = volume_increase
def __init__(self,
trend_start_day,
pre_start_day,
trend_percent,
up_percent,
trend_end_day=None):
self.runner = SqlRunner()
self.trend_start_day = trend_start_day
self.trend_end_day = trend_end_day if trend_end_day else stock_latest_day(
self.runner)
self.pre_start_day = pre_start_day
self.pre_end_day = trend_start_day - datetime.timedelta(days=1)
self.trend_percent = trend_percent
self.up_percent = up_percent
def convert_tick_to_daily():
sql = """
INSERT INTO {stock_daily}(
code, date, open, high, low, close, volume,
price_change, p_change, ma5, ma10, ma20,
v_ma5, v_ma10, v_ma20, turnover)
SELECT s.code, s.date, s.open, s.high, s.low, s.price, s.volume / 100,
s.price - s.pre_close,
((s.price - s.pre_close) / s.pre_close) * 100,
(t.ma5 + (s.price - t.price5) / 5),
(t.ma10 + (s.price - t.price10) / 10),
(t.ma20 + (s.price - t.price20) / 20),
0, 0, 0, 0
FROM {stock_tick} s,
(
SELECT
code,
max(close)
FILTER (WHERE rank = 5) AS price5,
max(close)
FILTER (WHERE rank = 10) AS price10,
max(close)
FILTER (WHERE rank = 20) AS price20,
max(ma5)
FILTER (WHERE rank = 1) AS ma5,
max(ma10)
FILTER (WHERE rank = 1) AS ma10,
max(ma20)
FILTER (WHERE rank = 1) AS ma20
FROM (
SELECT
code,
close,
date,
ma5,
ma10,
ma20,
row_number()
OVER (PARTITION BY code
ORDER BY date DESC) AS rank
FROM {stock_daily}
WHERE date >= now()::DATE - 90
) t
GROUP BY code
) t
WHERE s.code = t.code
""".format(stock_daily=STOCK_DAILY, stock_tick=STOCK_TICK_DATA)
runner = SqlRunner()
runner.execute(sql)
class UpAllMAs(object):
def __init__(self):
self.runner = SqlRunner()
def create_table(self):
stmt = '''
DROP TABLE IF EXISTS up_all_mas;
CREATE TABLE up_all_mas (
industry TEXT,
code TEXT,
name TEXT,
p_change FLOAT,
n_days INT,
interval INT,
update_date DATE DEFAULT NOW()::DATE
);
'''
self.runner.execute(stmt)
def get_top_n(self, n_days, interval):
stmt = '''
DELETE FROM up_all_mas WHERE update_date = NOW()::DATE and n_days = %(n_days)s and interval = %(interval)s;
INSERT INTO up_all_mas (industry, code, name, p_change, n_days, interval)
SELECT i.industry, i.code, i.name, t.p_change, %(n_days)s, %(interval)s
FROM
(
SELECT code
FROM stock_daily
WHERE date >= %(start_date)s and p_change between -3 and 0
group by code
having count(*) >= %(n_days)s
) s,
stock_info i,
stock_daily_more t
where s.code = t.code and t.date = %(latest_date)s and s.code = i.code and
t.p_change between 0.1 and 5 and t.close > t.ma5 and t.close > t.ma10 and
t.close > t.ma20 and t.close > t.ma30 and t.close > t.ma60
and t.close > t.ma120 and t.close > t.ma250;
'''
latest_date = stock_day.stock_latest_day(self.runner)
params = {
'latest_date': latest_date,
'n_days': n_days,
'interval': interval,
'start_date': stock_day.stock_open_day(self.runner, latest_date, n_days + interval)
}
print stmt, params
self.runner.execute(stmt, params)
class ContinuousIncrease(object):
def __init__(self):
self.runner = SqlRunner()
def create_table(self):
stmt = '''
DROP TABLE IF EXISTS continuous_increase;
CREATE TABLE continuous_increase (
industry TEXT,
code TEXT,
name TEXT,
p_change FLOAT8[],
n_days INT,
update_date DATE DEFAULT NOW()::DATE
);
'''
self.runner.execute(stmt)
def get_top_n(self, n_days):
stmt = '''
DELETE FROM continuous_increase WHERE update_date = NOW()::DATE AND n_days = %(n_days)s;
INSERT INTO continuous_increase (industry, code, name, p_change, n_days)
SELECT t.industry, s.code, t.name,
array_agg(round(s.p_change::numeric, 2) order by s.date), %(n_days)s
FROM stock_daily s, stock_info t, stock_daily sd
WHERE s.code = t.code AND
s.p_change between -3.0 and 5.0 AND
s.date >= %(n_plus_one_days_ago)s AND
sd.code = s.code AND sd.date = %(latest_day)s AND
(sd.high - sd.close) / sd.close <= 0.02 AND
sd.close > sd.open AND sd.low <= (sd.close - sd.price_change)
group by t.name, s.code, t.industry
having sum((s.p_change >= 1.0)::INT) >= %(n_days)s AND
(ARRAY_AGG(s.p_change ORDER BY s.date ASC))[1]BETWEEN -3.0 AND 0 AND
(ARRAY_AGG(s.p_change ORDER BY s.date DESC))[1] > 0.0
'''
latest_day = stock_day.stock_latest_day(self.runner)
params = {
'latest_day': latest_day,
'n_days': n_days,
'n_days_ago': stock_day.stock_open_day(self.runner, latest_day, n_days),
'n_days': n_days,
'n_plus_one_days_ago': stock_day.stock_open_day(self.runner, latest_day, n_days + 1)
}
print stmt, params
self.runner.execute(stmt, params)
def refresh_stock_concept():
engine = get_pg_engine()
code_df = ts.get_concept_classified()
code_df.to_sql(constants.STOCK_CONCEPT, engine, if_exists='replace')
engine.dispose()
runner = SqlRunner()
runner.execute("CREATE INDEX on %s(code)" % constants.STOCK_CONCEPT)
runner.dispose()
def refresh_stock_list():
engine = get_pg_engine()
code_df = ts.get_stock_basics()
code_df.to_sql(constants.STOCK_INFO, engine, if_exists='replace')
engine.dispose()
runner = SqlRunner()
runner.execute("CREATE INDEX on %s(code)" % constants.STOCK_INFO)
runner.dispose()
def execute_stmt(stmt):
runner = SqlRunner()
rows, _ = runner.select(stmt)
return rows
class UpTrendVolumeAnalysis(object):
def __init__(self, start_day, end_day, volume_cnt, neg_volume_percent):
self.start_day = start_day
self.end_day = end_day
self.runner = SqlRunner()
self.volume_cnt = volume_cnt
self.neg_volume_percent = neg_volume_percent
def latest_day(self):
sql = 'SELECT max(date) FROM stock_daily'
rows, _ = self.runner.select(sql)
return rows[0][0]
def check_percent(self, a_list):
result = []
for x, y in zip(a_list, a_list[1:]):
result.append(abs((y - x + 0.0) / x))
return self.neg_volume_percent >= any(result)
def check_volume(self, neg_list, pos_list):
greater_cnt = 0
for item in pos_list:
if item < any(neg_list):
greater_cnt += 1
return greater_cnt <= self.volume_cnt
def recommend(self):
sql = """
SELECT code, date, p_change, volume
FROM stock_daily
WHERE date BETWEEN %(start_day)s AND %(end_day)s
ORDER BY code, date
"""
params = {'start_day': self.start_day, 'end_day': self.end_day}
rows, cnt = self.runner.select(sql, params)
rec_codes = []
available_days = dbutil.get_available_days(self.runner, self.start_day,
self.end_day)
for code, info in itertools.groupby(rows, key=operator.itemgetter(0)):
volume = list(info)
if len(volume) < len(available_days) - 3:
continue
neg_volume = [v[3] for v in volume if v[2] < 0]
pos_volume = [v[3] for v in volume if v[2] >= 0]
if self.check_volume(neg_volume, pos_volume) and \
self.check_percent(neg_volume):
rec_codes.append(code)
print code, neg_volume, pos_volume, [v[2] for v in volume]
sql = """
SELECT t.code, i.name, (max_close - min_close) / min_close FROM (
SELECT code, max(close) FILTER (WHERE date = %(min_day)s) as min_close,
max(high) FILTER (WHERE date = %(max_day)s) as max_close,
max(p_change) FILTER (where date = %(min_day)s) as p_change
FROM stock_daily
WHERE date in (%(min_day)s, %(max_day)s) and code in %(code_tuple)s
GROUP by code
) t, stock_info i
where p_change < 9.9 and t.code = i.code
ORDER BY (max_close - min_close) / min_close DESC
"""
params = {
'min_day': self.end_day + datetime.timedelta(days=1),
'max_day': self.latest_day(),
'code_tuple': tuple(rec_codes)
}
rows, _ = self.runner.select(sql, params)
import pprint
pprint.pprint(rows)
def __init__(self, start_day, end_day, volume_cnt, neg_volume_percent):
self.start_day = start_day
self.end_day = end_day
self.runner = SqlRunner()
self.volume_cnt = volume_cnt
self.neg_volume_percent = neg_volume_percent
class UpTrendAnalysis(object):
def __init__(self, trend_start_day, pre_start_day, trend_percent,
up_percent):
self.trend_start_day = trend_start_day
self.pre_start_day = pre_start_day
self.pre_end_day = trend_start_day - datetime.timedelta(days=1)
self.runner = SqlRunner()
self.trend_percent = trend_percent
self.up_percent = up_percent
def stock_min_close(self):
sql = """
DROP TABLE IF EXISTS stock_min_close;
CREATE TABLE stock_min_close AS
select code, date, close, min_close,
row_number() OVER (PARTITION BY code ORDER BY close DESC) as rank
from (
select code, date, close,
min(close) over ten_window as min_close
from stock_daily
where date >= %(trend_start)s
WINDOW ten_window AS (partition by code ORDER BY date ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING)
) t
where (close = min_close);
"""
self.runner.execute(sql, {"trend_start": self.trend_start_day})
def prerequsite(self):
self.stock_min_close()
def latest_day(self):
sql = 'SELECT max(date) FROM stock_daily'
rows, _ = self.runner.select(sql)
return rows[0][0]
def recommend(self):
sql = """
DELETE FROM stock_recommend WHERE update_time = now()::DATE and reason = 'up_trend_low';
INSERT INTO stock_recommend
WITH stock_percent AS(
SELECT t.code, (close_list[1] - close_list[2] + 0.0) / close_list[2] as percent
FROM (
SELECT
m.code,
array_agg(close ORDER BY date DESC) AS close_list
FROM stock_min_close m
WHERE rank <= 2
GROUP BY m.code
) t
WHERE (close_list[1] - close_list[2] + 0.0) / close_list[2] <= %(trend_percent)s
)
SELECT DISTINCT m.code, i.name, i.industry, i.esp, i.pe, p.percent,
now()::DATE as update_time, 'up_trend_low' as reason
FROM stock_min_close m, stock_min_max_date d, stock_info i, stock_percent p, stock_highest h
WHERE m.code = d.code AND d.min_date <= %(trend_start)s AND m.code = p.code AND
m.rank = 1 AND (m.date BETWEEN %(latest_day)s - 10 AND %(latest_day)s - 1) AND m.code = i.code AND i.name NOT LIKE '%%ST%%' AND
m.code = h.code and (h.close - m.close + 0.0) / h.close >= %(up_percent)s
ORDER BY percent DESC ;
"""
params = {
'trend_percent': self.trend_percent,
'up_percent': self.up_percent,
'latest_day': self.latest_day(),
'trend_start': self.trend_start_day
}
print self.latest_day()
self.runner.execute(sql, params)
def __init__(self, trend_start_day, trend_end_day=None):
self.runner = SqlRunner()
self.trend_start_day = trend_start_day
self.trend_end_day = trend_end_day if trend_end_day else stock_latest_day(
self.runner)
class TopConcept(object):
def __init__(self):
self.runner = SqlRunner()
def create_table(self):
stmt = '''
DROP TABLE IF EXISTS top_concept;
CREATE TABLE top_concept (
concept TEXT,
date DATE,
p_change FLOAT8,
positive_stock_ratio FLOAT8,
top_n_stock TEXT[],
top_n_change FLOAT8[],
update_date DATE DEFAULT NOW()::DATE
);
DROP TABLE IF EXISTS top_n_concept;
CREATE TABLE top_n_concept (
concept TEXT,
p_change FLOAT8[],
positive_stock_ratio FLOAT8[],
top_n_stock TEXT[],
update_date DATE DEFAULT NOW()::DATE
);
'''
self.runner.execute(stmt)
def get_top_n(self, top_n, n_days):
stmt = '''
DELETE FROM top_concept WHERE update_date = NOW()::DATE;
INSERT INTO top_concept (concept, date, p_change, positive_stock_ratio, top_n_stock, top_n_change)
SELECT concept, date, round(p_change::NUMERIC, 2) as p_change,
round(((positive_cnt + 0.0) / (positive_cnt + negative_cnt))::NUMERIC, 2),
top_n_stock, top_n_change
FROM (
SELECT date, concept, avg(p_change) as p_change,
(array_agg(name || '(' || code || ')' ORDER BY p_change DESC))[1:5] as top_n_stock,
(array_agg(p_change ORDER BY p_change DESC))[1:5] as top_n_change,
count(*) FILTER (WHERE p_change > 0) as positive_cnt,
count(*) FILTER (WHERE p_change <=0) as negative_cnt
FROM (
SELECT d.date, i.c_name as concept, d.p_change, i.name, i.code
FROM stock_concept i, stock_daily d
WHERE i.code = d.code AND
d.date BETWEEN %(start_day)s AND %(latest_day)s AND
i.code not in (
SELECT code
FROM stock_daily
WHERE date BETWEEN %(start_day)s AND %(latest_day)s AND p_change > 9.5
GROUP BY code
HAVING count(*) >= 2
)
) t
GROUP BY date, concept
) s;
DELETE FROM top_n_concept WHERE update_date = NOW()::DATE;
INSERT INTO top_n_concept (concept, p_change, positive_stock_ratio, top_n_stock)
SELECT i.concept, i.p_change, i.positive_stock_ratio,
t.top_n_stock
FROM
(
SELECT concept, array_agg(p_change order by date desc) as p_change,
array_agg(positive_stock_ratio order by date desc) as positive_stock_ratio
FROM top_concept
GROUP BY concept
) i,
(
SELECT concept,
(array_agg(top_n_stock::TEXT || '+' || stock_cnt::TEXT ORDER BY stock_cnt DESC))[1:5] as top_n_stock
FROM (
SELECT concept, top_n_stock, count(*) as stock_cnt
FROM (
SELECT concept, unnest(top_n_stock) as top_n_stock
FROM top_concept
) s
GROUP BY concept, top_n_stock
) m
GROUP BY concept
) t
WHERE i.concept = t.concept
'''
latest_day = stock_day.stock_latest_day(self.runner)
params = {
'top_n': top_n,
'n_days': n_days,
'latest_day': latest_day,
'start_day': stock_day.stock_open_day(self.runner, latest_day, n_days)
}
self.runner.execute(stmt, params)
def __init__(self, interval, end_day=None):
self.runner = SqlRunner()
self.interval = interval
self.end_day = end_day if end_day else stock_latest_day(self.runner)
self.start_day = stock_open_day(self.runner, self.end_day, self.interval)
class StockIncreaseRatio(object):
def __init__(self, interval, end_day=None):
self.runner = SqlRunner()
self.interval = interval
self.end_day = end_day if end_day else stock_latest_day(self.runner)
self.start_day = stock_open_day(self.runner, self.end_day, self.interval)
def create_table(self):
if not dbutil.table_exists(self.runner, constants.STOCK_INCREASE):
dbutil.create_table(self.runner, constants.STOCK_INCREASE, """
CREATE TABLE {name}
(
code text,
start_day date,
end_day date,
percent float8,
high_percent float8,
name TEXT,
c_name TEXT
);
CREATE INDEX on {name} (code);
CREATE INDEX on {name} (start_day, end_day);
""".format(name=constants.STOCK_INCREASE))
def get_ratio(self):
self.create_table()
print self.start_day, self.end_day
sql = """
DELETE FROM {name}
WHERE start_day = %(start_day)s and end_day = %(end_day)s;
INSERT INTO {name}
SELECT s.code, t.date as start_day, s.date as end_day,
(s.close - t.close) / t.close as percent, r.high_percent,
c.name, c.c_name
FROM
(
SELECT code, date, close
FROM {stock_daily}
WHERE date = %(end_day)s
) s,
(
SELECT code, date, close
FROM {stock_daily}
WHERE date = %(start_day)s
) t,
(
SELECT code, (max(high) - min(low)) / min(low) as high_percent
FROM {stock_daily}
WHERE date BETWEEN %(start_day)s and %(end_day)s
GROUP BY code
) r,
{stock_concept} c
WHERE s.code = t.code and s.code = r.code and r.code = c.code
""".format(stock_daily=constants.STOCK_DAILY, name=constants.STOCK_INCREASE,
stock_concept=constants.STOCK_CONCEPT)
params = {
'start_day': self.start_day,
'end_day': self.end_day
}
print sql, params
self.runner.execute(sql, params)
self.dispose()
def dispose(self):
self.runner.dispose()
def delete_stock_data():
runner = SqlRunner()
runner.execute("DELETE FROM {name} WHERE date = %(day)s".format(name=STOCK_DAILY),
{"day": stock_latest_day(runner)})
runner.dispose()
class TickData(object):
def __init__(self):
self.runner = SqlRunner()
def drop_stock_tick_table(self, name):
self.runner.execute("DROP TABLE IF EXISTS %s" % name)
def create_stock_tick_table(self, name):
dbutil.create_table(
self.runner, name, """
CREATE TABLE {name}
(
index BIGINT,
name TEXT,
open FLOAT8,
pre_close FLOAT8,
price FLOAT8,
high FLOAT8,
low FLOAT8,
bid FLOAT8,
ask FLOAT8,
volume FLOAT8,
amount FLOAT8,
b1_v TEXT,
b1_p TEXT,
b2_v TEXT,
b2_p TEXT,
b3_v TEXT,
b3_p TEXT,
b4_v TEXT,
b4_p TEXT,
b5_v TEXT,
b5_p TEXT,
a1_v TEXT,
a1_p TEXT,
a2_v TEXT,
a2_p TEXT,
a3_v TEXT,
a3_p TEXT,
a4_v TEXT,
a4_p TEXT,
a5_v TEXT,
a5_p TEXT,
date DATE,
time TIME,
code TEXT
);
CREATE INDEX on {name} (code)
""".format(name=name))
def get_stock_tick_by_code(self, table_name, stock_code):
try:
df = ts.get_realtime_quotes(stock_code)
if df is None or df.empty:
print "No need refresh %s" % stock_code
else:
df["code"] = stock_code
df.to_sql(table_name, self.runner.engine, if_exists='append')
df.to_sql(STOCK_TICK_HIST_DATA,
self.runner.engine,
if_exists='append')
print "generating,", stock_code
except Exception as e:
print "wrong code", stock_code
print e
def dispose(self):
self.runner.dispose()
class LatestAsc(object):
def __init__(self, trend_start_day, trend_end_day=None):
self.runner = SqlRunner()
self.trend_start_day = trend_start_day
self.trend_end_day = trend_end_day if trend_end_day else stock_latest_day(
self.runner)
def prerequsite(self):
aux_tables.refresh_stock_days()
aux_tables.refresh_peak_close(self.runner, self.trend_start_day,
self.trend_end_day)
aux_tables.refresh_low_close(self.runner, self.trend_start_day,
self.trend_end_day)
insert_more_ma_stock_daily()
aux_tables.create_recommend_table(self.runner)
def recommend(self):
sql = """
DELETE FROM {name} WHERE update_time = now()::DATE AND reason = '{reason}';
INSERT INTO {name}
SELECT DISTINCT i.code, i.name, i.industry, i.esp, i.pe, 0.0,
now()::DATE as update_time, '{reason}' as reason
FROM (
SELECT DISTINCT s.code
FROM
(
SELECT l.code, count(*) filter (where l.rank = l.date_rank) as l_cnt,
max(l.rank) as l_t_cnt
FROM {stock_low} l
GROUP BY l.code
) s,
(
SELECT p.code, count(*) filter (where p.rank = p.date_rank) as p_cnt,
max(p.rank) as p_t_cnt
FROM {stock_peak} p
GROUP BY p.code
) t
where s.code = t.code and s.l_cnt = s.l_t_cnt and t.p_cnt = t.p_t_cnt
) s,
{stock_info} i,
{stock_daily} d,
{stock_days} t,
(
SELECT code
FROM {stock_daily_more}
WHERE close >= ma5 and close >= ma10 and close >= ma20 AND
close >= ma30 and close >= ma60 and close >= ma120 AND
date >= %(pre_day)s
GROUP BY code
HAVING count(*) = %(pre_days)s
) q
WHERE s.code = i.code and s.code = d.code and s.code = q.code AND d.date = %(trend_end)s AND
d.close >= d.ma5 and d.close >= d.ma10 and d.close >= d.ma20 AND
d.p_change between 0.0 AND 9.0 and s.code = t.code and t.num_days > 25 and d.open > 0 AND
i.name not like '%%ST%%'
""".format(stock_max=STOCK_MAX_CLOSE_DAY,
stock_min_max=STOCK_MIN_MAX_DAY,
reason='latest_asc',
stock_info=STOCK_INFO,
stock_peak=STOCK_PEAK_CLOSE,
name=STOCK_RECOMMEND,
stock_low=STOCK_LOW_CLOSE,
stock_daily=STOCK_DAILY,
stock_days=STOCK_DAYS,
stock_daily_more=STOCK_DAILY_MORE_MA)
params = {
'trend_end': self.trend_end_day,
'trend_start': self.trend_start_day,
'pre_day': stock_open_day(self.runner, self.trend_end_day, 3),
'pre_days': 3
}
self.runner.execute(sql, params)
def __init__(self):
self.runner = SqlRunner()