Ejemplo n.º 1
0
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
Ejemplo n.º 2
0
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.')
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
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)
Ejemplo n.º 5
0
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.')
Ejemplo n.º 6
0
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)
Ejemplo n.º 7
0
def execute(sql):
    db.conn().exec(sql)
    print('execute SQL successed.')
Ejemplo n.º 8
0
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)
Ejemplo n.º 9
0
#!/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
Ejemplo n.º 10
0
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.')
Ejemplo n.º 11
0
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')
Ejemplo n.º 12
0
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.')
Ejemplo n.º 13
0
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)
Ejemplo n.º 14
0
def show(ps):
    sql = "SELECT * FROM monitor WHERE user=?"
    for r in db.conn().all(sql, [ps.user]):
        print(r)
Ejemplo n.º 15
0
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.'
        )
Ejemplo n.º 16
0
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.')
Ejemplo n.º 17
0
def getByCode(code):
    sql = "SELECT * FROM trade WHERE code=?"
    rs = db.conn().all(sql, code)
    render(rs)
Ejemplo n.º 18
0
def getAll():
    sql = "SELECT * FROM trade"
    rs = db.conn().all(sql)
    render(rs)
Ejemplo n.º 19
0
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.')
Ejemplo n.º 20
0
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.")
Ejemplo n.º 21
0
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()
Ejemplo n.º 22
0
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))