def many(sqls, filt, disp, codes):
rate = parameter.extreme(sqls['rate'], ext=('0', '0.1'))
df = pd.DataFrame(columns=['code', 'ndays'])
conn = db.conn()
pbar = tqdm(total=len(codes))
for code in codes:
pbar.set_description('[{}]'.format(code))
pbar.update(1)
sql = "SELECT close, low FROM kdata\
WHERE code='{}' AND date>='{}' AND date<='{}'"\
.format()
res = db.conn().all(sql)
n = 0
for r in res:
_rate = (r['close']-r['low']) / r['low']
if rate[0] <= _rate <= rate[1]:
n += 1
if n>0:
df.append({
'code': code,
'ndays': n,
})
return df
def add(ps):
sql = "INSERT OR REPLACE INTO monitor(id, user, date, code, volume, amount, message) VALUES (\
(SELECT id FROM monitor WHERE user=? AND code=?), ?, ?, ?, ?, ?, ?)"
db.conn().exec(sql, [
ps.user, ps.code, ps.user,
date.today(), ps.code, ps.volume, ps.amount, ps.message
])
print('Add stock monitor info successed.')
def once(sqls, filt, disp):
rate = parameter.extreme(sqls['rate'], ext=('0', '0.1'), toFloat=False)
sql = "SELECT code, (close-low)/low AS rate FROM kdata WHERE date='{}' AND close<open AND (close-low)/low>={} AND (close-low)/low<={}"\
.format(sqls['date'], rate[0], rate[1])
df = db.conn().df(sql)
return df
def main(argString='600300'):
code = argString
sql = "SELECT code, price, change, volume, amount/10000 as amount, turnover, pe, \
name || ' ' || industry || ' ' || area as extrInfo\
FROM day_all WHERE code=?"
df = db.conn().df(sql, [code])
print(df)
print('近期价格:')
sql = "SELECT date, close, open, high, low, volume FROM kdata WHERE code=? ORDER BY date DESC LIMIT 5"
df = db.conn().df(sql, [code])
df.sort_values('date', inplace=True)
print(df)
exit()
sql = "SELECT max(date) FROM kdata"
date = db.conn().val(sql)
get_tick_data(code, date)
def fetch_k_data(codes):
print('Fetching data through function named get_k_data(code)... ')
sql = "SELECT code, max(date) FROM kdata GROUP BY code"
dates = dict(db.conn().all(sql))
lastTradeDay = tdate.lastTrade(tdate.today())
errorCodes = []
pbar = tqdm(total=len(codes))
for code in codes:
pbar.set_description('[{}]'.format(code))
sDate = dates.get(code, '2017-01-01')
if sDate < lastTradeDay:
try:
df = ts.get_k_data(code, start=sDate)
df = df.set_index('date')
df.to_sql('kdata', db.conn().connect, if_exists='append')
except Exception as e:
print('[{}] some error raised:'.format(code), e)
errorCodes.append(code)
pbar.update(1)
pbar.close()
print('The data fetched and saved to table [kdata] success.')
def main(argString=''):
ps = {'page': '1'}
# ps.update(paras)
offset = (int(ps.get('page', 1)) - 1) * 10
sql = "SELECT code, name from stock_basics LIMIT {offset}, 10"\
.format(offset=offset, **ps)
res = db.conn().all(sql)
for r in res:
print(r)
print()
print(ps)
def execute(sql):
db.conn().exec(sql)
print('execute SQL successed.')
def fetch_hist_data():
codes = ['600300', '300347', '0000001', '000881']
codes = ['300347']
today = datetime.date.today()
weekday = today.weekday()
if weekday == 5:
today -= datetime.timedelta(days=1)
elif weekday == 6:
today -= datetime.timedelta(days=2)
pbar = tqdm(total=len(codes))
for code in codes:
sql = "SELECT date, close, hfq FROM hist WHERE code=? ORDER BY date DESC LIMIT 1"
res = db.conn().one(sql, [code])
p_date, p_close, p_hfq = res if res else ('2017-01-01', 0, 1)
startdate = datetime.date(
*map(int, p_date.split('-'))) + datetime.timedelta(days=1)
if startdate <= today:
try:
df = ts.get_hist_data(code,
start=startdate.strftime('%Y-%m-%d'))
except Exception as e:
print(e)
else:
pass
if any(df):
df = df.sort_index()
hfqs, needFQ = [], False
if p_close == 0:
r0 = df.iloc[0]
p_close = round(r0['close'] - r0['price_change'], 2)
for idx in df.index:
r = df.loc[idx]
E = round(r['close'] - r['price_change'], 2)
if abs(E - p_close) < 0.02:
hfq = p_hfq
else:
hfq = round(p_hfq * p_close / E, 9)
needFQ = True
hfqs.append(hfq)
p_close, p_hfq = r['close'], hfq
df.insert(0, 'code', pd.Series([code], index=df.index))
df.insert(1, 'hfq', pd.Series(hfqs, index=df.index))
df.insert(2, 'qfq', pd.Series([1], index=df.index))
df.to_sql('hist', db.conn().connect, if_exists='append')
if needFQ:
sql = "UPDATE kdata SET qfq = hfq/? WHERE code=?"
db.conn().showsql().exec(sql, [hfq, code])
pbar.update(1)
pbar.close()
print('fetch data successed.')
sql = "SELECT code, date, hfq, qfq FROM kdata ORDER BY date"
res = db.conn().all(sql)
for r in res:
print(r)
#!/usr/bin/env python
# -*- coding:utf-8
# author: HuoHongJian
# date: 2018-01-05
import os, time
import pandas as pd
from libs.Sqlite import Sqlite as db
from libs import tdate, cache, parameter, dataFrame
sql = "SELECT max(date) FROM kdata"
maxdate = db.conn().val(sql)
sqls = {'ma':'5,10', 'date':maxdate}
filt = {}
disp = {}
def main(argString=''):
global sqls, filt, disp
sqls, filt, disp = parameter.parse(argString, sqls, filt, disp)
key = hash('ycx' + str(sqls))
if cache.dfs.has(key):
df = cache.dfs.get(key)
else:
date = sqls.get('date')
if not tdate.isTrade(date):
print('The date you input is not trade day, please reset date parameter')
return
def fetch_today_all():
print('Fetching today_all... ')
df = ts.get_today_all()
df.to_sql('today_all', db.conn().connect, if_exists='replace')
print('is done! AND saved data to table [today_all] success.')
def fetch_stock_basics():
print('Fetching stock_basics... ')
df = ts.get_stock_basics()
df.to_sql('stock_basics', db.conn().connect, if_exists='replace')
print('is done! And saved data to table [stock_basics] success')
def remove(ps):
sql = "DELETE FROM monitor WHERE user=? AND code=?"
db.conn().exec(sql, [ps.user, ps.code])
print('code=[{}] stock is deleted success.')
def fetchall(sql):
res = db.conn().all(sql)
print([r[0] for r in db.cursor.description])
print('-' * 30)
for r in res:
print(r)
def show(ps):
sql = "SELECT * FROM monitor WHERE user=?"
for r in db.conn().all(sql, [ps.user]):
print(r)
def fetch_day_all():
print('Fetching day_all... ')
# 判断是否为当天数据
for N in range(10):
df = ts.get_day_all()
size = df.shape[0]
loop = False
for i in range(3):
idx = random.randint(0, size - 1)
_df = df.ix[idx]
sql = "SELECT open, close, high, low FROM kdata WHERE code=? ORDER BY date DESC LIMIT 1"
res = db.conn().one(sql, [_df.code])
if not any(res):
continue
if res[0] == _df.open and res[1] == _df.price and res[
2] == _df.high and res[3] == _df.low:
loop = True
break
if not loop:
break
if N > 8:
exit()
print('网上数据不是最新的，1个小时后，再次下载.')
time.sleep(3600)
# 处理下载的数据
df = df.loc[df.open > 0]
df.to_sql('day_all', db.conn().connect, if_exists='replace')
codes = list(df.code) #np.array(df[['code']]).tolist()
print('is done! And saved data to table [day_all] success.')
# 判断是否append to table [kdata]
sql = "SELECT max(date) FROM kdata"
maxDate = db.conn().val(sql) or '2018-01-01'
today = tdate.today()
_time = tdate.time()
nextTradeDay = tdate.nextTrade(maxDate)
# 数据连续,可一次性下载当天全部交易数据
if (nextTradeDay == today and _time > '15:50:00') or 1:
df = df[['code', 'open', 'price', 'high', 'low', 'volume']]
df = df.set_index('code')
df.insert(0, 'date', pd.Series([nextTradeDay], index=df.index))
df.rename(columns={'price': 'close'}, inplace=True)
df.to_sql('kdata', db.conn().connect, if_exists='append')
print(
'At the same time, day_all data also saved to table [kdata] success.'
)
sql = "INSERT INTO log (operate, result, message) VALUES (?, ?, ?)"
db.conn().exec(sql, [
'fetch_day_all', 'success',
'fetch and saveed the data to tables [day_all, kdata], total=[{}]'.
format(len(codes))
])
print('Starting compute moving average... ')
pbar = tqdm(total=len(codes))
for code in codes:
pbar.set_description('[{}]'.format(code))
sql = "SELECT id, close, volume FROM kdata WHERE code=? ORDER BY date DESC LIMIT 60"
rdf = db.conn().df(sql, [code]).sort_index(axis=0, ascending=False)
rdf = fetch.compute_ma(rdf)
r = rdf.iloc[-1]
sql = '''UPDATE kdata SET
ma5=?, ma10=?, ma20=?, ma30=?, ma60=?,
va5=?, va10=?, va20=?, va30=?, va60=?
WHERE id=?'''
db.conn().exec(sql, [*r[3:].round(2), r.id])
pbar.update(1)
pbar.close()
else:
print(
'Save data to table [kdata] failed, Please execute the program at trade day and time > 16:00:00.'
)
def buystock(code, price, volume, user, date, remark):
sql = "INSERT INTO trade(user, date, deal, code, price, volume, amount, remark) VALUES(?,?,'b',?,?,?,?,?)"
db.conn().showsql(False).exec(
sql, [user, date, code, price, volume, price * volume, remark])
print('add the info of buy stock is successed.')
def getByCode(code):
sql = "SELECT * FROM trade WHERE code=?"
rs = db.conn().all(sql, code)
render(rs)
def getAll():
sql = "SELECT * FROM trade"
rs = db.conn().all(sql)
render(rs)
def fetch_trade_calendar():
print('Fetching trade_calendar... ', end='')
df = ts.trade_cal()
df.to_sql('trade_cal', db.conn().connect, if_exists='replace')
print('is done! And saved data to table [trade_cal] success.')
def fetch_concept_classified():
print("fetching concept classified data (概念分类)")
df = ts.get_concept_classified()
df.to_sql('concept', db.conn().connect, if_exists='replace')
print("Concept classified data is saved to [concept] table success.")
def monitor(ps):
import tushare as ts
from libs.Sqlite import Sqlite as db
from colorama import Fore, Back, Style
COLORS = ['YELLOW', 'MAGENTA', 'CYAN', 'RED', 'GREEN', 'BLUE'] * 3
header = '{:>3} {:^8} {:^6} {:>6} {:>6} {:>5} {:>6} {:>6} {:>6} {:>6}'.format(
'NO', 'time', 'code', 'price', 'pc ', 'pcr', 'valume', 'amount', 'bid',
'ask')
pattern = '{:>3} {:^8} {:^6} {:>6.2f} {:>6.2f} {:>5.1f}% {:>6.0f} {:>6.0f} {:>6.2f} {:>6.2f}'
sql = "SELECT code, volume, amount FROM monitor WHERE user=?"
res = db.conn().all(sql, [ps.user])
print('Monitoring:', str(res))
i, I, codes, stock = 0, 0, [], {}
for code, volume, amount in res:
codes.append(code)
stock[code] = {
'volume': volume,
'amount': amount,
'color': COLORS[I],
'lastVolume': 0,
'lastAmount': 0
}
I += 1
while True:
time.sleep(6)
t = time.strftime("%H:%M:%S")
if (t < '09:28:00' or (t > '11:32:00' and t < '12:58:00')
or t > '15:02:00'):
time.sleep(54)
continue
try:
df = ts.get_realtime_quotes(codes)
except:
continue
for r in df.values:
open, pre_close, price, high, low, bid, ask = float(r[1]), float(
r[2]), float(r[3]), float(r[4]), float(r[5]), float(
r[6]), float(r[7])
volume, amount = float(r[8]) / 100, float(r[9]) / 10000
# b1_v, b2_v, b3_v, b4_v, b5_v = r[10], r[12], r[14], r[16], r[18]
# b1_p, b2_p, b3_p, b4_p, b5_p = r[11], r[13], r[15], r[17], r[19]
# a1_v, a2_v, a3_v, a4_v, a5_v = r[20], r[22], r[24], r[26], r[28]
# a1_p, a2_p, a3_p, a4_p, a5_p = r[21], r[23], r[25], r[27], r[29]
date, times, code = str(r[30]), str(r[31]), str(r[32])
vc = volume - stock[code]['lastVolume']
ac = amount - stock[code]['lastAmount']
stock[code]['lastVolume'] = volume
stock[code]['lastAmount'] = amount
pc = price - pre_close
pcr = pc / pre_close * 100
if vc > stock[code]['volume'] and ac > stock[code]['amount']:
i += 1
if i % 10 == 1:
print(Back.BLUE, header, '', Style.RESET_ALL)
print(
getattr(Fore, stock[code]['color']),
pattern.format(i, times, code, price, pc, pcr, vc, ac, bid,
ask), Style.RESET_ALL)
# print('\a')
if i % 5 == 0:
print()
from libs.Sqlite import Sqlite as db
import tushare as ts
import threading
import time
from concurrent import futures
from queue import Queue
end = object()
print('start..........')
s = time.time()
q = Queue()
sql = "select code from day_all limit 100"
res = db.conn().all(sql)
codes = [r[0] for r in res]
def test():
print('this is a function')
for i in range(100):
print(i)
r = q.get()
print(r)
if r == 'hhj':
print('hhj')
break
pbar = tqdm(total=len(codes))
