def init_trading_info(self):
for code_index, code_id in self.stock_info['code'].iteritems():
mysql = 'create table if not exists `%s`(date varchar(10),\
open float,\
high float,\
close float,\
low float,\
volume float)' % code_id
mysql_5 = 'create table if not exists %s_5(date varchar(10),\
open float,\
high float,\
close float,\
low float,\
volume float)' % code_id
print code_index, code_id
if code_id not in self.tables:
if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME,
mysql):
raise Exception("create table %s failed" % code_id)
data = ts.get_k_data(code_id, retry_count=10)
if data is not None:
set(self.engine, data.reset_index(), str(code_id))
data_5 = ts.get_k_data(code_id, retry_count=10, ktype='5')
if data_5 is not None:
set(self.engine, data_5.reset_index(), "%s_5" % code_id)
def init_concept_from_ts(self):
table = 'concept'
sql = 'create table if not exists %s(c_name varchar(50),code varchar(5000))' % table
if table not in self.tables:
if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME, sql):
raise Exception("create table %s failed" % table)
df_concept = ts.get_concept_classified()
df_concept = df_concept[['code', 'c_name']]
df_concept_dict = {}
for index, code_id in df_concept['code'].iteritems():
concept_name = df_concept['c_name'][index]
if concept_name in df_concept_dict:
if not code_id in df_concept_dict:
df_concept_dict[concept_name].append(code_id)
else:
df_concept_dict[concept_name] = []
df_concept_dict[concept_name].append(code_id)
concept_obj = {}
with open("concepts/concepts.json") as f:
concept_obj = json.load(f)
for key, value in concept_obj.items():
if key in df_concept_dict:
if value not in df_concept_dict[key]:
df_concept_dict[key].extend(value)
else:
df_concept_dict[key] = value
for key in df_concept_dict:
df_concept_dict[key] = json.dumps(df_concept_dict[key],
ensure_ascii=False)
df_concept = DataFrame({
'c_name': df_concept_dict.keys(),
'code': df_concept_dict.values()
})
set(self.engine, df_concept, table)
return get(self.engine, SQL % table)
def init_forward_adjusted_price(self):
_today = datetime.now().strftime('%Y-%m-%d')
start_date = str(datetime.datetime.today().date() +
datetime.timedelta(-1))
end_date = str(datetime.datetime.today().date())
stock_infos = get_classified_stocks(market)
total_data = None
table = 'forward_adjusted_price'
sql = 'create table if not exists `%s`(date varchar(10),\
open float,\
high float,\
close float,\
low float,\
volume float,\
code varchar(8))' % table
if is_trading_day(_today):
for _index, code_id in stock_infos['code'].iteritems():
tmp_data = ts.get_k_data(code_id,
start=start_date,
end=end_date,
autype='qfq')
tmp_data['code'] = code_id
tmp_data = tmp_data.reset_index(drop=False)
total_data = tmp_data if _index == 0 else pd.concat(
[total_data, tmp_data],
ignore_index=True).drop_duplicates()
set(self.engine, total_data, table)
return get(self.engine, SQL % table)
def init_stock_basic_info(self):
table = 'info'
text = 'create table if not exists %s(code int,\
name varchar(20),\
industry varchar(20),\
c_name varchar(100),\
area varchar(20),\
pe float,\
outstanding float,\
totals float,\
totalAssets float,\
fixedAssets float,\
reserved float,\
reservedPerShare float,\
esp float,\
bvps float,\
pb float,\
timeToMarket int,\
undp float,\
perundp float,\
rev float,\
profit float,\
gpr float,\
npr float,\
limit-up-num int,\
continuous-limit-up-num int,\
holders int)' % table
if table not in self.tables:
if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME,
text):
raise Exception("create table %s failed" % table)
df = ts.get_stock_basics()
df = df.reset_index().rename_axis({'index': 'code'}, axis="columns")
df_concept = ts.get_concept_classified()
df_concept = df_concept[['code', 'c_name']]
df_concept_dict = {}
for index, code_id in df_concept['code'].iteritems():
concept_name = df_concept['c_name'][index]
if code_id in df_concept_dict:
if not concept_name in df_concept_dict:
df_concept_dict[code_id].append(concept_name)
else:
df_concept_dict[code_id] = []
df_concept_dict[code_id].append(concept_name)
for key in df_concept_dict:
df_concept_dict[key] = json.dumps(df_concept_dict[key],
ensure_ascii=False)
df_concept = DataFrame({
'code': df_concept_dict.keys(),
'c_name': df_concept_dict.values(),
'limit-up-num': 0,
'continuous-limit-up-num': 0
})
df_all = pd.merge(df, df_concept, how='left', on=['code'])
df_all.reset_index(drop=True)
set(self.engine, df_all, table)
return get(self.engine, SQL % table)
def get_stock_volumes(market, data_times):
stock_id_frame = get_classified_stocks(market)
stock_ids = list()
stock_names = list()
stock_turnover = list()
stock_volume = list()
stock_concepts = list()
stock_dates = list()
stock_pchanges = list()
pydate_array = data_times.to_pydatetime()
date_only_array = np.vectorize(lambda s: s.strftime('%Y-%m-%d'))(
pydate_array)
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
for _date in date_only_array:
if is_trading_day(_date):
for code_index, code_id in stock_id_frame['code'].iteritems():
if not is_sub_new_stock(
str(stock_id_frame['timeToMarket'][code_index])):
turnover = 0
volume = 0
stock_name = stock_id_frame['name'][code_index]
stock_concept = stock_id_frame['c_name'][code_index]
hist_data = get_hist_data(engine, code_id, _date)
if hist_data is not None:
hist_data = hist_data[hist_data['volume'] > 0]
hist_data = hist_data[[
'turnover', 'volume', 'p_change'
]]
if not hist_data.empty:
turnover = hist_data['turnover'][0]
p_change = hist_data['p_change'][0]
if turnover > 5 and p_change > 5:
stock_dates.append(_date)
stock_ids.append(code_id)
stock_names.append(stock_name)
volume = hist_data['volume'][0]
stock_volume.append(volume)
stock_turnover.append(turnover)
stock_concepts.append(stock_concept)
stock_pchanges.append(p_change)
df = DataFrame({
'date': stock_dates,
'code': stock_ids,
'name': stock_names,
'turnover': stock_turnover,
'volume': stock_volume,
'c_name': stock_concepts,
'p_change': stock_pchanges
})
table = 'turnover'
sql = 'create table if not exists %s(date varchar(10),code varchar(10),name varchar(20),volume float,p_change float,c_name varchar(5000))' % table
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
set(engine, df, table)
return get(engine, SQL % table)
def init_trading_day(self):
table = 'calendar'
sql = 'create table if not exists `%s`(calendarDate varchar(10),isOpen int)' % table
if table not in self.tables:
if not create_table(DB_USER, DB_PASSWD, DB_NAME, DB_HOSTNAME, sql):
raise Exception("create table %s failed" % table)
trading_day = ts.trade_cal()
if trading_day is not None:
set(self.engine, trading_day, str(table))
return get(self.engine, SQL % table)
def get_daily_stock_info():
_date = datetime.now().strftime('%Y-%m-%d')
if is_trading_day(_date):
table = 'today'
static_table = 'today'
engine = create_engine('mysql://%s:%s@%s/%s?charset=utf8' %
(DB_USER, DB_PASSWD, DB_HOSTNAME, DB_NAME))
y, m, d = time.strptime(_date, "%Y-%m-%d")[0:3]
mor_open_hour, mor_open_minute, mor_open_second = (9, 24, 0)
mor_open_time = datetime(y, m, d, mor_open_hour, mor_open_minute,
mor_open_second)
mor_close_hour, mor_close_minute, mor_close_second = (11, 31, 0)
mor_close_time = datetime(y, m, d, mor_close_hour, mor_close_minute,
mor_close_second)
aft_open_hour, aft_open_minute, aft_open_second = (13, 0, 0)
aft_open_time = datetime(y, m, d, aft_open_hour, aft_open_minute,
aft_open_second)
aft_close_hour, aft_close_minute, aft_close_second = (15, 0, 0)
aft_close_time = datetime(y, m, d, aft_close_hour, aft_close_minute,
aft_close_second)
now_time = datetime(y, m, d, 14, 0, 0)
p_changes = []
p_changes_data = DataFrame(
columns=[str(index - 10) for index in range(20)])
p_changes_data['date'] = now_time
while (mor_open_time < now_time <
mor_close_time) or (aft_open_time < now_time < aft_close_time):
now_time = datetime.now()
data = get_real_time_info()
if data is not None:
new_data = data[[
'date', 'name', 'code', 'turnover', 'p_change', 'price',
'limit-up-time', 'limit-down-time'
]]
for index, code_id in new_data['code'].iteritems():
p_change = new_data.query('code=="%s"' %
code_id).p_change.values[0]
if p_change > 9.9:
new_data['limit-up-time'][index] = now_time
if p_change < -9.9:
new_data['limit-down-time'][index] = now_time
for i in range(20):
if i - 10 < p_change <= i - 9:
p_changes[i] += 1
new_data = new_data[new_data['limit-up-time'] != 0
or new_data['limit-down-time'] != 0]
p_changes.append(now_time)
p_changes_data.loc[0] = p_changes
set(engine, new_data, table)
set(engine, p_changes_data, static_table)
time.sleep(60)
def dt(conn, msg):
if len(msg.text.split()) > 2:
naam = msg.text.split()[1]
error = ' '.join(msg.text.split()[2:])
rowid = mysql.set('INSERT INTO irc_dt (name, error) VALUES (%s, %s)', (naam, error))
conn.send('PRIVMSG %s :DT-fout %i added!\r\n' % (msg.channel, rowid))
else:
parts = msg.text.split()
if len(parts) == 1:
rows, count = mysql.get('SELECT * FROM irc_dt ORDER BY RAND()', '')
if count != 0:
conn.send('PRIVMSG %s :DT-fout %i: [%s] %s\r\n' % (msg.channel, rows[0][0], rows[0][1], rows[0][2]))
elif parts[1].isdigit():
rows, count = mysql.get('SELECT * FROM irc_dt WHERE id=%s', parts[1])
if count != 0:
conn.send('PRIVMSG %s :DT-fout %i: [%s] %s\r\n' % (msg.channel, rows[0][0], rows[0][1], rows[0][2]))
else:
rows, count = mysql.get('SELECT * FROM irc_dt WHERE name=%s ORDER BY RAND()', (parts[1]))
if count != 0:
conn.send('PRIVMSG %s :DT-fout: %s heeft in totaal al %i DT-fouten gemaakt\r\n' % (msg.channel, rows[0][1], count))
else:
conn.send('PRIVMSG %s :DT-fout: %s heeft nog geen DT-fouten gemaakt\r\n' % (msg.channel, parts[1]))
def __init__(self, buf_data, int_idx, dict_cfg):
options = dict_cfg["set_mapping"]
try:
data, new_idx = mysql.set(buf_data, int_idx, {"options": options})
except ValueError as xx:
raise FieldImpossibleException(xx)
self.d = data
self.raw_len = new_idx - int_idx
def addquote(conn, msg):
if len(msg.text.split()) > 1:
quote = ' '.join(msg.text.split()[1:])
rowid = mysql.set('INSERT INTO irc_quote (quote) VALUES (%s)', (quote))
conn.send('PRIVMSG %s :Quote %i added!\r\n' % (msg.channel, rowid))
else:
usage = 'Gebruik: !addquote quote'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def delquote(conn, msg):
if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg):
if len(msg.text.split()) > 1 and msg.text.split()[1].isdigit():
rowid = mysql.set('DELETE FROM irc_quote WHERE id=%s', msg.text.split()[1])
conn.send('PRIVMSG %s :Quote %i deleted!\r\n' % (msg.channel, int(msg.text.split()[1])))
else:
usage = 'Gebruik: !delquote id'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
else:
usage = 'Je moet een operator zijn om dit commando te kunnen gebruiken.'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def tell(conn, msg):
if len(msg.text.split()) > 2:
message = ' '.join(msg.text.split()[2:])
to = msg.text.split()[1]
frm = msg.user
rowid = mysql.set("INSERT INTO irc_tell (`from`, `to`, `message`) VALUES (%s, %s, %s)", (frm, to, message))
conn.send('PRIVMSG %s :I will tell %s that when %s is here.\r\n' % (msg.channel, to, to))
else:
usage = 'Gebruik: !tell naam tekst'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def unassign(conn, msg):
if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg):
if len(msg.text.split()) > 1:
word = msg.text.split()[1]
rowid = mysql.set('DELETE FROM irc_assign WHERE word=%s', (word))
conn.send('PRIVMSG %s :%s unassigned.\r\n' % (msg.channel, word))
else:
usage = 'Gebruik: !unassign woord'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
else:
usage = 'Je moet een operator zijn om dit commando te kunnen gebruiken.'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def assign(conn, msg):
if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg):
if len(msg.text.split()) > 2:
word = msg.text.split()[1]
defin = ' '.join(msg.text.split()[2:])
rows, count = mysql.get('SELECT * FROM irc_assign WHERE word=%s', (word))
if count > 0:
conn.send('PRIVMSG %s :%s is already defined: %s\r\n' % (msg.channel, rows[0][1], rows[0][2]))
else:
rowid = mysql.set('INSERT INTO irc_assign (word, def) VALUES (%s, %s)', (word, defin))
conn.send('PRIVMSG %s :%s added to assign list.\r\n' % (msg.channel, word))
else:
usage = 'Gebruik: !assign woord definitie'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
else:
usage = 'Je moet een administrator zijn om dit commando te kunnen uitvoeren.'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def reassign(conn, msg):
if msg.user == settings.irc_OWNER or utils.isadmin(conn, msg):
if len(msg.text.split()) > 2:
word = msg.text.split()[1]
defin = ' '.join(msg.text.split()[2:])
rows, count = mysql.get('SELECT * FROM irc_assign WHERE word=%s', (word))
if count == 0:
conn.send('PRIVMSG %s :%s is not defined yet. Use !assign word def to assign it.\r\n' % (msg.channel, word))
else:
rowid = mysql.set('UPDATE irc_assign SET def=%s WHERE word=%s', (defin, word))
conn.send('PRIVMSG %s :%s reassigned to: %s\r\n' % (msg.channel, word, defin))
else:
usage = 'Gebruik: !reassign woord definitie'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
else:
usage = 'Je moet een administrator zijn om dit commando te kunnen uitvoeren.'
conn.send('PRIVMSG %s :%s\r\n' % (msg.user, usage))
def active(conn, msg):
rows, count = mysql.get('SELECT * FROM irc_tell WHERE `to`=%s', (msg.user))
for row in rows:
conn.send('PRIVMSG %s :%s: [%s] %s: %s\r\n' % (msg.channel, row[3], row[1], row[2], row[4]))
count = mysql.set('DELETE FROM irc_tell WHERE `id`=%s', row[0])
