import requests
from jqdatasdk import *
import tushare as ts
from bs4 import BeautifulSoup
from lxml import etree
from multiprocessing import Pool
import multiprocessing
import time
import datetime
import random
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import json
import re
import warnings
warnings.filterwarnings("ignore")
###################################################################################################################################
# Mysql常规操作
class MysqlHandler:
def __init__(self):
self.conn = pymysql.connect("127.0.0.1", "root", "test", "quant_db", charset='utf8')
def delete(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
self.conn.commit()
cursor.close()
self.conn.close()
###################################################################################################################################
# Tushare接口数据请求
# 1、股票列表
# 2、日线行情
# 3、资金流向
# 4、沪深股通
# 5、涨停跌停
# 6、shibor
# 7、所属概念
# 8、涨跌停价格
class TuShare:
def __init__(self):
self.date = time.strftime("%Y%m%d", time.localtime())
self.pro = ts.pro_api('ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7')
self.engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
def stock_basic(self):
data1 = self.pro.stock_basic(list_status='L', fields='ts_code,name,area,industry,market,exchange,list_date,is_hs')
data1['area'] = data1['area'].apply(lambda x: "广东" if (x == '深圳') == True else x)
stock_csv = pd.read_csv('C:\Users\Red-tea-1919\Desktop\Stock\行业概念.csv', encoding='gbk')[['股票代码', '股票简称', '所属概念', '所属同花顺行业']]
dict = {}
data2 = pd.DataFrame()
for index, row in stock_csv.iterrows():
stk_code = row['股票代码']
yjhy = (row['所属同花顺行业'].split('-'))[0]
ejhy = (row['所属同花顺行业'].split('-'))[1]
sjhy = (row['所属同花顺行业'].split('-'))[2]
dict = {'ts_code': stk_code, 'yjhy': yjhy, 'ejhy': ejhy, 'sjhy': sjhy}
data2 = data2.append(dict, ignore_index=True)
data2 = data2[['ts_code', 'yjhy', 'ejhy', 'sjhy']]
data = pd.merge(data1, data2, how='left', on='ts_code')
data.to_sql('stock_basic', con=self.engine, if_exists='append', index=False)
print(data.head(5))
print('--------------------程序执行完毕！--------------------')
def stock_daily(self):
data = self.pro.daily(trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
print(data.head(5))
data.to_sql('stock_daily', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def index_daily(self):
data1 = self.pro.index_daily(ts_code='000001.SH', trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
data2 = self.pro.index_daily(ts_code='399001.SZ', trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
data3 = self.pro.index_daily(ts_code='399006.SZ', trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
data = pd.concat([data1, data2, data3], ignore_index=True)
print(data)
data.to_sql('index_daily', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def stock_limit_price(self):
data = self.pro.stk_limit(trade_date=self.date, fields='trade_date,ts_code,pre_close,up_limit,down_limit')
print(data.head(5))
data.to_sql('stock_limit_price', con=self.engine, if_exists='replace', index=False)
print('--------------------程序执行完毕！--------------------')
def stock_daily_basic(self):
data = self.pro.daily_basic(trade_date=self.date, fields='ts_code,trade_date,turnover_rate,turnover_rate_f,volume_ratio,total_share,float_share,free_share')
print(data.head(5))
data.to_sql('stock_daily_basic', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def stock_moneyflow(self):
data = self.pro.moneyflow(trade_date=self.date, fields='ts_code,trade_date,buy_md_amount,sell_md_amount,buy_lg_amount,sell_lg_amount,buy_elg_amount,sell_elg_amount,net_mf_amount')
print(data.head(5))
data.to_sql('stock_moneyflow', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def hsgt_moneyflow(self):
data = self.pro.moneyflow_hsgt(trade_date=self.date, fields='trade_date,hgt,sgt,north_money')
print(data.head(5))
data.to_sql('hsgt_moneyflow', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def limit_list(self):
data = self.pro.limit_list(trade_date=self.date, fields='trade_date,ts_code,close,pct_chg,fc_ratio,fd_amount,first_time,last_time,open_times,strth,limit')
print(data.head(5))
data.to_sql('limit_stock_list', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def shibor(self):
data = self.pro.shibor(date=self.date, fields='date,on,1m')
print(data.head(5))
data.to_sql('shibor', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def pre_cal_date(self):
t1 = datetime.date.today()
t2 = t1 - datetime.timedelta(days=90)
start_date = t2.strftime('%Y%m%d')
end_date = t1.strftime('%Y%m%d')
data = self.pro.trade_cal(exchange='SSE', start_date=start_date, end_date=end_date, fields='exchange,cal_date,is_open,pretrade_date')
print(data.head(5))
data.to_sql('trade_cal_date', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
###################################################################################################################################
if __name__ == "__main__":
t1 = time.time()
conn = pymysql.connect("127.0.0.1", "root", "test", "quant_db", charset='utf8')
cursor = conn.cursor()
cursor.execute('delete from stock_basic')
cursor.execute('delete from trade_cal_date')
conn.commit()
cursor.close()
conn.close()
obj1 = TuShare()
obj1.stock_daily()
obj1.index_daily()
obj1.stock_daily_basic()
obj1.stock_moneyflow()
obj1.hsgt_moneyflow()
obj1.limit_list()
obj1.shibor()
obj1.pre_cal_date()
obj1.stock_basic()
t2 = time.time()
print('本次程序运行时间为%s秒' % (t2 - t1))
import requests
from jqdatasdk import *
import tushare as ts
from bs4 import BeautifulSoup
from lxml import etree
from multiprocessing import Pool
import multiprocessing
import time
import datetime
import random
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import json
import re
import warnings
warnings.filterwarnings("ignore")
###################################################################################################################################
# Mysql常规操作
class MysqlHandler:
def __init__(self):
self.conn = pymysql.connect("127.0.0.1", "root", "test", "quant_db", charset='utf8')
def delete(self, sql):
cursor = self.conn.cursor()
cursor.execute(sql)
self.conn.commit()
cursor.close()
self.conn.close()
###################################################################################################################################
# Tushare接口数据请求
# 1、股票列表
# 2、日线行情
# 3、资金流向
# 4、沪深股通
# 5、涨停跌停
# 6、shibor
# 7、所属概念
# 8、涨跌停价格
class TuShare:
def __init__(self):
self.date = time.strftime("%Y%m%d", time.localtime())
self.pro = ts.pro_api('ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7')
self.engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
def stock_basic(self):
data1 = self.pro.stock_basic(list_status='L', fields='ts_code,name,area,industry,market,exchange,list_date,is_hs')
data1['area'] = data1['area'].apply(lambda x: "广东" if (x == '深圳') == True else x)
stock_csv = pd.read_csv('C:\Users\Red-tea-1919\Desktop\Stock\行业概念.csv', encoding='gbk')[['股票代码', '股票简称', '所属概念', '所属同花顺行业']]
dict = {}
data2 = pd.DataFrame()
for index, row in stock_csv.iterrows():
stk_code = row['股票代码']
yjhy = (row['所属同花顺行业'].split('-'))[0]
ejhy = (row['所属同花顺行业'].split('-'))[1]
sjhy = (row['所属同花顺行业'].split('-'))[2]
dict = {'ts_code': stk_code, 'yjhy': yjhy, 'ejhy': ejhy, 'sjhy': sjhy}
data2 = data2.append(dict, ignore_index=True)
data2 = data2[['ts_code', 'yjhy', 'ejhy', 'sjhy']]
data = pd.merge(data1, data2, how='left', on='ts_code')
data.to_sql('stock_basic', con=self.engine, if_exists='append', index=False)
print(data.head(5))
print('--------------------程序执行完毕！--------------------')
def stock_daily(self):
data = self.pro.daily(trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
print(data.head(5))
data.to_sql('stock_daily', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def index_daily(self):
data1 = self.pro.index_daily(ts_code='000001.SH', trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
data2 = self.pro.index_daily(ts_code='399001.SZ', trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
data3 = self.pro.index_daily(ts_code='399006.SZ', trade_date=self.date, fields='ts_code,trade_date,open,high,low,close,pre_close,pct_chg,amount')
data = pd.concat([data1, data2, data3], ignore_index=True)
print(data)
data.to_sql('index_daily', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def stock_limit_price(self):
data = self.pro.stk_limit(trade_date=self.date, fields='trade_date,ts_code,pre_close,up_limit,down_limit')
print(data.head(5))
data.to_sql('stock_limit_price', con=self.engine, if_exists='replace', index=False)
print('--------------------程序执行完毕！--------------------')
def stock_daily_basic(self):
data = self.pro.daily_basic(trade_date=self.date, fields='ts_code,trade_date,turnover_rate,turnover_rate_f,volume_ratio,total_share,float_share,free_share')
print(data.head(5))
data.to_sql('stock_daily_basic', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def stock_moneyflow(self):
data = self.pro.moneyflow(trade_date=self.date, fields='ts_code,trade_date,buy_md_amount,sell_md_amount,buy_lg_amount,sell_lg_amount,buy_elg_amount,sell_elg_amount,net_mf_amount')
print(data.head(5))
data.to_sql('stock_moneyflow', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def hsgt_moneyflow(self):
data = self.pro.moneyflow_hsgt(trade_date=self.date, fields='trade_date,hgt,sgt,north_money')
print(data.head(5))
data.to_sql('hsgt_moneyflow', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def limit_list(self):
data = self.pro.limit_list(trade_date=self.date, fields='trade_date,ts_code,close,pct_chg,fc_ratio,fd_amount,first_time,last_time,open_times,strth,limit')
print(data.head(5))
data.to_sql('limit_stock_list', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def shibor(self):
data = self.pro.shibor(date=self.date, fields='date,on,1m')
print(data.head(5))
data.to_sql('shibor', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def pre_cal_date(self):
t1 = datetime.date.today()
t2 = t1 - datetime.timedelta(days=90)
start_date = t2.strftime('%Y%m%d')
end_date = t1.strftime('%Y%m%d')
data = self.pro.trade_cal(exchange='SSE', start_date=start_date, end_date=end_date, fields='exchange,cal_date,is_open,pretrade_date')
print(data.head(5))
data.to_sql('trade_cal_date', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
###################################################################################################################################
if __name__ == "__main__":
t1 = time.time()
conn = pymysql.connect("127.0.0.1", "root", "test", "quant_db", charset='utf8')
cursor = conn.cursor()
cursor.execute('delete from stock_basic')
cursor.execute('delete from trade_cal_date')
conn.commit()
cursor.close()
conn.close()
obj1 = TuShare()
obj1.stock_daily()
obj1.index_daily()
obj1.stock_daily_basic()
obj1.stock_moneyflow()
obj1.hsgt_moneyflow()
obj1.limit_list()
obj1.shibor()
obj1.pre_cal_date()
obj1.stock_basic()
t2 = time.time()
print('本次程序运行时间为%s秒' % (t2 - t1))
import requests
from jqdatasdk import *
import tushare as ts
from bs4 import BeautifulSoup
from lxml import etree
from multiprocessing import Pool
import multiprocessing
import time
import datetime
import random
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import json
import re
import math
import warnings
warnings.filterwarnings("ignore")
###################################################################################################################################
# 东方财富爬虫
# 1、股票异动
# 2、沪深股通
class Eastmoney:
def __init__(self):
self.session = requests.Session()
self.date = time.strftime("%Y%m%d", time.localtime())
self.engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
self.timestamp = int(round(time.time() * 1000))
self.url1 = 'http://push2ex.eastmoney.com/getStockCountChanges?type=4,8,16,32,64,128,8193,8194,8201,8202,8203,8204,8207,8208,8209,8210,8211,8212,8213,8214,8215,8216'
self.url2 = "http://push2ex.eastmoney.com/getAllStockChanges?type=8201,8202,8193,4,32,64,8207,8209,8211,8213,8215,8204,8203,8194,8,16,128,8208,8210,8212,8214,8216"
self.url3 = 'http://push2ex.eastmoney.com/getTopicZTPool?'
self.ua_list = [
'Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0',
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 UBrowser/4.0.3214.0 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3314.0 Safari/537.36 SE 2.X MetaSr 1.0'
]
def changes_max_page(self):
# 1、抓取网页数据
param = {"_": self.timestamp, "pagesize": '64', "ut": '7eea3edcaed734bea9cbfc24409ed989', "dpt": 'wzchanges'}
html = json.loads(self.session.get(url=self.url1, params=param, headers={'User-Agent': random.choice(self.ua_list)}).text)
# 2、网页数据解析
self.changes_max_page = html['rt']
def changeSpider(self):
self.changes_max_page()
# 1、定义数据存储容器
dict = {}
data = pd.DataFrame()
# 2、爬取网页数据
for page in range(0, int(self.changes_max_page)):
param = {"pageindex": page, "pagesize": '64', "ut": '7eea3edcaed734bea9cbfc24409ed989', "dpt": 'wzchanges'}
html = json.loads(self.session.get(url=self.url2, params=param, headers={'User-Agent': random.choice(self.ua_list)}).text)
# 3、网页数据解析
if html['data'] is None:
break
else:
allstock = html['data']['allstock']
for stock in allstock:
code = stock['c']
name = stock['n']
chg_type = stock['t']
if len(str(stock['tm'])) == 5:
chg_time = str(self.date) + '0' + str(stock['tm'])
else:
chg_time = str(self.date) + str(stock['tm'])
if chg_type in ('8201', '8202', '8207', '8209', '8211', '8215', '8204', '8203', '8208', '8210', '8212', '8216'):
chg_value = stock['i'] * 100
chg_index = '%'
elif chg_type in ('8193', '8194', '128', '64'):
chg_value = stock['i']
chg_index = '股'
else:
chg_value = stock['i']
chg_index = '元'
# 4、数据容器存储
dict = {'chg_time': chg_time, 'code': code, 'name': name, 'chg_type': chg_type, 'chg_value': chg_value, 'chg_index': chg_index}
data = data.append(dict, ignore_index=True)
print('--------------------程序执行完毕！共抓取%s页数据--------------------' % (page + 1))
data = data[['chg_time', 'code', 'name', 'chg_type', 'chg_value', 'chg_index']]
# 5、数据清洗
data = data[~ (data['code'].str.startswith('1') | data['code'].str.startswith('2') | data['code'].str.startswith('5') | data['code'].str.startswith('9'))]
data['code'] = data['code'].apply(lambda x: (x + ".SH") if x.startswith('6') == True else (x + ".SZ"))
print(data.head(5))
# 6、mysql持久化存储
data.to_sql('stock_change', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def limit(self):
i = math.floor(random.random() * 10000000 + 1)
t = int(time.time() * 1000)
p = 'callbackdata' + str(i + 1) + '('
q = ');'
param = {'cb': 'callbackdata' + str(i + 1), 'ut': '7eea3edcaed734bea9cbfc24409ed989', 'dpt': 'wz.ztzt', 'Pageindex': '0', 'pagesize': 920, 'sort': 'fbt:asc', 'date': self.date, '_': t}
html = self.session.get(url=self.url3, params=param, headers={'User-Agent': random.choice(self.ua_list)}).text
html = json.loads(html.replace(p, '').replace(q, ''))
list = html['data']['pool']
dict = {}
data = pd.DataFrame()
for x in list:
date = self.date
code = x['c']
lbc = x['lbc']
hybk = x['hybk']
dict = {'date': date, 'code': code, 'lbc': lbc, 'hybk': hybk}
data = data.append(dict, ignore_index=True)
data = data[['date', 'code', 'lbc', 'hybk']]
data['code'] = data['code'].apply(lambda x: (x + ".SH") if x.startswith('6') == True else (x + ".SZ"))
print(data.head(5))
data.to_sql('limit_stock_lbc', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
###################################################################################################################################
# 主运行程序
class Main:
def main_Eastmoney(self):
obj1 = Eastmoney()
obj1.changeSpider()
obj1.limit()
###################################################################################################################################
if __name__ == "__main__":
t1 = time.time()
Main().main_Eastmoney()
t2 = time.time()
print('本次程序运行时间为%s秒' % (t2 - t1))
import requests
from jqdatasdk import *
import tushare as ts
from bs4 import BeautifulSoup
from lxml import etree
from multiprocessing import Pool
import multiprocessing
import time
import datetime
import random
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import json
import re
import warnings
warnings.filterwarnings("ignore")
###################################################################################################################################
# 开盘啦爬虫
# 1、风口概念
# 2、龙虎榜
class Kpl:
def __init__(self):
self.date = time.strftime("%Y-%m-%d", time.localtime())
self.session = requests.Session()
self.engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
self.url = 'https://pclhb.kaipanla.com/w1/api/index.php'
self.url1 = 'https://pchq.kaipanla.com/w1/api/index.php'
self.ua_list = [
'Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0',
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 UBrowser/4.0.3214.0 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3314.0 Safari/537.36 SE 2.X MetaSr 1.0'
]
def hotConceptSpider(self):
# 1、定义数据容器
code_list = []
dict = {}
data = pd.DataFrame()
# 2、参数构造
param = {'c': 'LongHuBang', 'a': 'GetStockList', 'st': 300, 'Time': str(self.date), 'UserID': 399083, 'Token': '71aef0e806e61ad3169ddc9473e37886'}
# 3、网页数据抓取
html = json.loads(self.session.post(url=self.url, headers={'User-Agent': random.choice(self.ua_list)}, data=param).text)['list']
# 4、网页数据解析
for j in html:
code = j['ID']
name = j['Name']
code_list.append(code)
if len(j['FengKou']) == 0:
hot_cept = '无'
else:
p = j['FengKou'].split(',')
for i in p:
hot_cept = i
dict = {'date': self.date, 'code': code, 'name': name, 'hot_cept': hot_cept}
data = data.append(dict, ignore_index=True)
# 5、重命名及顺序调整
data = data[['date', 'code', 'name', 'hot_cept']]
data = data[data['code'].str.startswith('0') | data['code'].str.startswith('3') | data['code'].str.startswith('6')]
data['code'] = data['code'].apply(lambda x: (x + ".SH") if x.startswith('6') == True else (x + ".SZ"))
data['date'] = data['date'].apply(lambda x: str(x).replace('-', ''))
print(data.head(5))
# 6、数据存储
print('--------------------程序执行完毕！--------------------')
data.to_sql('hot_concept', con=self.engine, if_exists='append', index=False)
return code_list
def lhbSpider(self, code_list):
for code in code_list:
# 2、参数构造
param = {'c': 'Stock', 'a': 'GetNewOneStockInfo', 'StockID': code, 'Time': self.date, 'UserID': '399083', 'Token': '71aef0e806e61ad3169ddc9473e37886'}
# 3、网页数据抓取
html = json.loads(self.session.post(url=self.url, headers={'User-Agent': random.choice(self.ua_list)}, data=param).text)['List']
dict1 = {}
dict2 = {}
data1 = pd.DataFrame()
data2 = pd.DataFrame()
data = pd.DataFrame()
# 4、网页数据解析
for j in html:
for b in j['BuyList']:
date = self.date
code = code
direction = 'buy'
dname = b['Name']
buy = b['Buy']
sell = b['Sell']
try:
if b['GroupIcon'][0] == None:
groupicon = '空'
else:
groupicon = b['GroupIcon'][0]
except:
groupicon = '空'
youziicon = b['YouZiIcon']
dict1 = {'date': date, 'code': code, 'direction': direction, 'dname': dname, 'buy': buy, 'sell': sell, 'groupicon': groupicon, 'youziicon': youziicon}
data1 = data1.append(dict1, ignore_index=True)
for b in j['SellList']:
date = self.date
code = code
direction = 'sell'
dname = b['Name']
buy = b['Buy']
sell = b['Sell']
try:
if b['GroupIcon'][0] == None:
groupicon = '空'
else:
groupicon = b['GroupIcon'][0]
except:
groupicon = '空'
youziicon = b['YouZiIcon']
dict2 = {'date': date, 'code': code, 'direction': direction, 'dname': dname, 'buy': buy, 'sell': sell, 'groupicon': groupicon, 'youziicon': youziicon}
data2 = data2.append(dict2, ignore_index=True)
# 5、数据处理
data = pd.concat([data1, data2], axis=0)
data = data[['date', 'code', 'direction', 'dname', 'buy', 'sell', 'groupicon', 'youziicon']]
data = data[data['code'].str.startswith('0') | data['code'].str.startswith('3') | data['code'].str.startswith('6')]
data['code'] = data['code'].apply(lambda x: (x + ".SH") if x.startswith('6') == True else (x + ".SZ"))
data['date'] = data['date'].apply(lambda x: str(x).replace('-', ''))
data.to_sql('lhb_list', con=self.engine, if_exists='append', index=False)
print(data.head(5))
# mysql数据存储
print('--------------------程序执行完毕！--------------------')
def scqxSpider(self):
# 1、参数构造
param = {'c': 'PCArrangeData', 'a': 'GetIndexPlate', 'st': 3, 'StockID': 'SH000001', 'UserID': 399083, 'Token': '2292739880d01bd81e169e90a1898ebe'}
# 2、网页数据抓取
html = json.loads(self.session.post(url=self.url1, headers={'User-Agent': random.choice(self.ua_list)}, data=param).text)['Mood']
dict = {
'zrzt': html['ZRZT'], # 自然涨停
'ztzb': html['ZTZB'], # 涨停炸板
'szjs': html['SZJS'], # 上涨家数
'xdjs': html['XDJS'], # 下跌家数
'zt': html['ZT'], # 涨停家数
'dt': html['DT'], # 跌停家数
'zbl': html['ZBL'], # 炸板率
'cgl': html['CGL'], # 昨日打板成功率
'yll': html['YLL'], # 昨日打板盈利率
'day': html['day'] # 交易日期
}
data = pd.DataFrame(dict, index=[0])
data = data[['day', 'zrzt', 'ztzb', 'szjs', 'xdjs', 'zt', 'dt', 'zbl', 'cgl', 'yll']]
data.to_sql('scqx', con=self.engine, if_exists='append', index=False)
print(data)
print('--------------------程序执行完毕！--------------------')
###################################################################################################################################
# 主运行程序
class Main:
def main_Kpl(self):
obj1 = Kpl()
code_list = obj1.hotConceptSpider()
obj1.lhbSpider(code_list)
obj1.scqxSpider()
###################################################################################################################################
if __name__ == "__main__":
t1 = time.time()
Main().main_Kpl()
t2 = time.time()
print('本次程序运行时间为%s秒' % (t2 - t1))
import requests
from jqdatasdk import *
import tushare as ts
from bs4 import BeautifulSoup
from lxml import etree
from multiprocessing import Pool
import multiprocessing
import time
import datetime
import random
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import json
import re
import warnings
warnings.filterwarnings("ignore")
###################################################################################################################################
# code标准化
class CodeHandler:
def __init__(self):
self.pro = ts.pro_api('ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7')
def get_all_securities(self):
self.stocks = self.pro.stock_basic(list_status='L', fields='ts_code,exchange')
def jq_code_normalize(self):
self.get_all_securities()
stocks = self.stocks['ts_code'].apply(lambda x: (x[:7] + "XSHG") if x.endswith('SH') == True else (x[:7] + "XSHE")).tolist()
return stocks
def ts_code_normalize(self):
self.get_all_securities()
stocks = self.stocks['ts_code'].tolist()
return stocks
def no_code_normalize(self):
self.get_all_securities()
stocks = self.stocks['ts_code'].apply(lambda x: x[:6])
return stocks
def else_code_normalize(self):
self.get_all_securities()
stocks = self.stocks['ts_code'].apply(lambda x: ('SH' + x[:6]) if x.endswith('SH') == True else ('SZ' + x[:6])).tolist()
return stocks
###################################################################################################################################
# 新浪财经爬虫
# 1、个股成交分价表
def divCostSpider(code):
# 1、定义数据容器
try:
dict = {}
data = pd.DataFrame()
engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
# 2、构造URL
date = time.strftime("%Y%m%d", time.localtime())
ua_list = [
'Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0',
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 UBrowser/4.0.3214.0 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3314.0 Safari/537.36 SE 2.X MetaSr 1.0'
]
url = 'http://vip.stock.finance.sina.com.cn/quotes_service/view/cn_price.php?symbol=' + code.lower()
# 3、抓取网页数据
html = requests.get(url=url, headers={'User-Agent': random.choice(ua_list)}).text
# 4、网页数据解析
tree = etree.HTML(html)
tr_list = tree.xpath('//*[@id="divListTemplate"]/table/tbody/tr')
for tr in tr_list:
price = tr.xpath('.//td[1]/text()')[0].strip()
volume = tr.xpath('.//td[2]/text()')[0].strip()
buyrate = str(tr.xpath('.//td[3]/text()')[0].strip()).replace('%', '')
# 5、容器存储
dict = {'code': code, 'date': date, 'price': price, 'volume': volume, 'buyrate': buyrate}
data = data.append(dict, ignore_index=True)
# print('--------------------程序执行完毕！%s数据已抓取--------------------' % code)
data = data[['code', 'date', 'price', 'volume', 'buyrate']]
data['code'] = data['code'].apply(lambda x: (x[2:] + ".SH") if x.startswith('SH') == True else (x[2:] + ".SZ"))
# print(data.head(5))
# 6、mysql持久化存储
data.to_sql('stock_divcost', con=engine, if_exists='append', index=False)
except:
print('%s数据抓取出现问题' % code)
###################################################################################################################################
# 主运行程序
class Main:
def main_Sina(self):
obj1 = CodeHandler()
code_list = obj1.else_code_normalize()
pool = Pool()
pool.map(divCostSpider, code_list)
pool.close()
pool.join()
###################################################################################################################################
if __name__ == "__main__":
t1 = time.time()
Main().main_Sina()
t2 = time.time()
print('本次程序运行时间为%s秒' % (t2 - t1))
import requests
from jqdatasdk import *
import tushare as ts
from bs4 import BeautifulSoup
from lxml import etree
from multiprocessing import Pool
import multiprocessing
import time
import datetime
import random
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import json
import re
import math
import warnings
warnings.filterwarnings("ignore")
###################################################################################################################################
# 东方财富爬虫
# 1、股票异动
# 2、沪深股通
class Eastmoney:
def __init__(self):
self.session = requests.Session()
self.engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
self.pro = ts.pro_api('ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7')
self.url1 = 'http://dcfm.eastmoney.com/EM_MutiSvcExpandInterface/api/js/get?'
self.ua_list = [
'Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0',
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 UBrowser/4.0.3214.0 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3314.0 Safari/537.36 SE 2.X MetaSr 1.0'
]
self.sql = '''SELECT pretrade_date FROM trade_cal_date WHERE exchange = 'SSE' AND is_open = 1 ORDER BY cal_date DESC LIMIT 1'''
self.dataframe = pd.read_sql(self.sql,self.engine)['pretrade_date'].tolist()
print(self.dataframe)
#self.pretrade_date = str(self.dataframe[0][:4]) + '-' + str(self.dataframe[0][4:6]) + '-' + str(self.dataframe[0][6:])
self.pretrade_date = '2020-07-22'
print(self.pretrade_date)
def hsgt_max_page(self):
param = {'type': 'HSGT20_GGTJ_SUM',
'token': '894050c76af8597a853f5b408b759f5d',
'st': 'ShareSZ_Chg_One',
'sr': '-1',
'p': '1',
'ps': '50',
'js': 'var eAEIQxxY={pages:(tp),data:(x)}',
'filter': "(DateType='1' and HdDate='{}')".format(self.pretrade_date),
'rt': '53077355'}
html = self.session.get(url=self.url1, params=param, headers={'User-Agent': random.choice(self.ua_list)}).text
m = re.compile('var.*?{pages:(d+),data:.*?')
p = re.search(m, html)
self.hsgt_max_page = p.group(1)
def hsgtMoneySpider(self):
self.hsgt_max_page()
dict = {}
data = pd.DataFrame()
for page in range(0, int(self.hsgt_max_page)):
param = {'type': 'HSGT20_GGTJ_SUM',
'token': '894050c76af8597a853f5b408b759f5d',
'st': 'ShareSZ_Chg_One',
'sr': '-1',
'p': page,
'ps': '50',
'js': 'var eAEIQxxY={pages:(tp),data:(x)}',
'filter': "(DateType='1' and HdDate='{}')".format(self.pretrade_date),
'rt': '53077355'}
html = self.session.get(url=self.url1, params=param, headers={'User-Agent': random.choice(self.ua_list)}).text
# 提取出list，可以使用json.dumps和json.loads
q = re.compile('var.*?data:(.*)}', re.S)
i = re.search(q, html)
for p in json.loads(i.group(1)):
date = p['HdDate'] # 日期
code = p['SCode'] # 代码
name = p['SName'] # 名称
hyname = p['HYName'] # 所属板块
dqname = p['DQName'] # 地区板块
newprice = p['NewPrice'] # 今日收盘价
zdf = p['Zdf'] # 今日涨跌幅
sharehold = p['ShareHold'] # 今日持股股数，无单位
sharesz = p['ShareSZ'] # 今日持股市值，无单位
ltzb = p['LTZB'] # 今日持股占流通股比，无单位
sharesrate = p['SharesRate'] # 今日持股占总股本比，单位%
sharehold_chg_one = p['ShareHold_Chg_One'] # 今日增持股数，无单位
sharesz_chg_one = p['ShareSZ_Chg_One'] # 今日增持市值，无单位
ltzb_one = p['LTZB_One'] # 今日增持占流通股比，无单位
zzb_one = p['ZZB_One'] # 今日增持占总股本比,无单位
dict = {
'date': self.pretrade_date,
'code': code,
'hyname': hyname,
'dqname': dqname,
'newprice': newprice,
'zdf': zdf,
'sharehold': sharehold,
'sharesz': sharesz,
'ltzb': ltzb,
'sharesrate': sharesrate,
'sharehold_chg_one': sharehold_chg_one,
'sharesz_chg_one': sharesz_chg_one,
'ltzb_one': ltzb_one,
'zzb_one': zzb_one
}
data = data.append(dict, ignore_index=True)
data = data[['date', 'code', 'hyname', 'dqname', 'newprice', 'zdf', 'sharehold', 'sharesz', 'ltzb', 'sharesrate', 'sharehold_chg_one', 'sharesz_chg_one', 'ltzb_one', 'zzb_one']]
data['code'] = data['code'].apply(lambda x: (x + ".SH") if x.startswith('6') == True else (x + ".SZ"))
data['date'] = data['date'].apply(lambda x: str(x).replace('-', ''))
print(data.head(5))
data.to_sql('hsgt_detail', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
def pre_cal_date(self):
t1 = datetime.date.today()
t2 = t1 - datetime.timedelta(days=90)
start_date = t2.strftime('%Y%m%d')
end_date = t1.strftime('%Y%m%d')
data = self.pro.trade_cal(exchange='SSE', start_date=start_date, end_date=end_date, fields='exchange,cal_date,is_open,pretrade_date')
print(data.head(5))
data.to_sql('trade_cal_date', con=self.engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
###################################################################################################################################
# 主运行程序
class Main:
def main_Eastmoney(self):
obj1 = Eastmoney()
obj1.pre_cal_date()
obj1.hsgtMoneySpider()
###################################################################################################################################
if __name__ == "__main__":
t1 = time.time()
Main().main_Eastmoney()
t2 = time.time()
print('本次程序运行时间为%s秒' % (t2 - t1))
import requests
from jqdatasdk import *
import tushare as ts
from bs4 import BeautifulSoup
from lxml import etree
from multiprocessing import Pool
import multiprocessing
import time
import datetime
import random
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
import pymysql
import json
import re
import warnings
warnings.filterwarnings("ignore")
###################################################################################################################################
# Mysql常规操作
def DeleteHandler(sql):
con = pymysql.connect("127.0.0.1", "root", "test", "quant_db", charset='utf8')
cursor = con.cursor()
cursor.execute(sql)
cursor.close()
###################################################################################################################################
# Tushare所属概念
def TuShareConcept():
concept_id = (pro.concept(fields='code,name'))['code'].tolist()
visit_count = 0
data = pd.DataFrame()
for id in concept_id:
if (visit_count + 1) % 199 == 0:
time.sleep(10)
else:
data = data.append(pro.concept_detail(id=id, fields='concept_name,ts_code'), ignore_index=True)
visit_count += 1
data = data[['concept_name', 'ts_code']]
print('--------------------程序执行完毕！--------------------')
return data
###################################################################################################################################
# 开盘啦所属概念
def KplConcept():
try:
date = time.strftime("%Y-%m-%d", time.localtime())
code_list = pro.stock_basic(list_status='L', fields='ts_code,name')['ts_code'].tolist()
# 1、网页数据抓取
url = 'https://pchq.kaipanla.com/w1/api/index.php'
engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
ua_list = [
'Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 Firefox/52.0',
'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/68.0.3440.106 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/534.57.2 (KHTML, like Gecko) Version/5.1.7 Safari/534.57.2',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.122 UBrowser/4.0.3214.0 Safari/537.36',
'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3314.0 Safari/537.36 SE 2.X MetaSr 1.0'
]
dict1 = {}
data1 = pd.DataFrame()
visit_count = 0
for code in code_list:
if (visit_count + 1) % 400 == 0:
time.sleep(60)
else:
data = {'c': 'PCArrangeData',
'a': 'GetHQPlate',
'StockID': code[:6],
'Day': date,
'time': '15:00',
'SelType': '1, 2, 3, 8, 9, 5, 6, 7',
'UserID': 399083,
'Token': '71aef0e806e61ad3169ddc9473e37886'
}
html = requests.post(url=url, data=data, headers={'User-Agent': random.choice(ua_list)}).text
# 2、网页数据解析
ts_code = json.loads(html)['trend']['code']
cp_list = json.loads(html)['stockplate']
for cp in cp_list:
concept = cp[0]
dict1 = {'concept_name': concept, 'ts_code': code}
data1 = data1.append(dict1, ignore_index=True)
data1 = data1[['concept_name', 'ts_code']]
data1 = data1[~ (data1['ts_code'].str.startswith('1') | data1['ts_code'].str.startswith('2') | data1['ts_code'].str.startswith('5') | data1['ts_code'].str.startswith('9'))]
except Exception as error:
print(error)
print('--------------------程序执行完毕！--------------------')
return data1
###################################################################################################################################
# 问财所属概念
def IwencaiConcept():
# 读取下载转存的csv文件（原始下载文件为xml格式）
stock_csv = pd.read_csv('C:\Users\Red-tea-1919\Desktop\Stock\行业概念.csv', encoding='gbk')[['股票代码', '股票简称', '所属概念', '所属同花顺行业']]
# 1、本地文本文件解析
dict = {}
data = pd.DataFrame()
for index, row in stock_csv.iterrows():
ts_code = row['股票代码']
concept = row['所属概念'].split(';')
for cp in concept:
dict = {'concept_name': cp, 'ts_code': ts_code}
data = data.append(dict, ignore_index=True)
data = data[['concept_name', 'ts_code']]
print('--------------------程序执行完毕！--------------------')
return data
###################################################################################################################################
# 聚宽所属概念
def JoinQuantConcept():
auth('18829345691', '345691')
data1 = get_concepts()
data1 = data1.reset_index(inplace=False)
data1 = data1.rename(columns={'index': 'concept_code', 'name': 'concept_name'})
dict2 = {}
data2 = pd.DataFrame()
for concept_code in data1['concept_code'].tolist():
for ts_code in get_concept_stocks(concept_code, date=None):
dict2 = {'concept_code': concept_code, 'ts_code': ts_code}
data2 = data2.append(dict2, ignore_index=True)
data = pd.merge(data1, data2)
data = data[['concept_name', 'ts_code']]
data['ts_code'] = data['ts_code'].apply(lambda x: (x[:6] + ".SH") if x.endswith('XSHG') == True else (x[:6] + ".SZ"))
print('--------------------程序执行完毕！--------------------')
return data
###################################################################################################################################
# 所属概念去重
def drop_duplicates():
engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
data1 = KplConcept()
data2 = IwencaiConcept()
#data3 = TuShareConcept()
#data4 = JoinQuantConcept()
#data = pd.concat([data1, data2, data3, data4], ignore_index=True).drop_duplicates()
data = pd.concat([data1, data2], ignore_index=True).drop_duplicates(inplace=False)
DeleteHandler('delete from concept_detail')
data.to_sql('concept_detail', con=engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
###################################################################################################################################
# 问财流通股东
def IwencaiHodler():
engine = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8')
# 1、本地文本文件解析
# 统计实际流通股，持股比例低于5%
data = pd.read_csv('C:\Users\Red-tea-1919\Desktop\Stock\流通股东明细.csv', encoding='gbk')[['股票代码', '股票简称', '流通股东持股数量(股)', '流通股东持股比例(%)']]
data = data.rename(columns={'股票代码': 'stk_code', '股票简称': 'stk_name', '流通股东持股数量(股)': 'cir_num', '流通股东持股比例(%)': 'cir_rate'})
data = data.drop(data[data.cir_rate < 5].index)
data = data.groupby(['stk_code', 'stk_name']).sum()
data = data.reset_index()
DeleteHandler('delete from flow_holder')
data.to_sql('flow_holder', con=engine, if_exists='append', index=False)
print('--------------------程序执行完毕！--------------------')
###################################################################################################################################
# 开盘啦：精选概念
if __name__ == '__main__':
t1 = time.time()
pro = ts.pro_api('ac16b470869c5d82db5033ae9288f77b282d2b5519507d6d2c72fdd7')
IwencaiHodler()
drop_duplicates()
t2 = time.time()
print('本次程序运行时间为%s秒' % (t2 - t1))
from pyecharts import options as opts # 全局、系列配置
from pyecharts.globals import ThemeType # 主题风格
from pyecharts.charts import Kline, Line, Bar, Gauge, Pie, Scatter, TreeMap, Grid, Tab, Page # K线图、线形图、柱状图、仪表盘、饼图、散点图、矩形树图、组合组件、分页组件
from pyecharts.components import Table # 表格组件
from pyecharts.options import ComponentTitleOpts
from pyecharts.commons.utils import JsCode
import time # 时间模块
import pandas as pd # Pandas模块
import numpy as np # Numpy模块
from sqlalchemy import create_engine # 数据引擎模块
import warnings
warnings.filterwarnings('ignore')
#############################################################################################################################################################
# f1函数，c1、c2、c3分别表示炸板率、打板成功率、打板盈利率
def f1():
sql1 = '''SELECT `day`,zbl,cgl,yll FROM scqx WHERE `day`=(SELECT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) '''
data1 = pd.read_sql(sql1, conn)
v1 = round((data1['zbl'].values)[0], 1)
v2 = round((data1['cgl'].values)[0], 1)
v3 = round((data1['yll'].values)[0], 1)
c1 = (
Gauge(init_opts=opts.InitOpts(theme=ThemeType.DARK))
.add(series_name='', data_pair=[['炸板率', v1]], title_label_opts=opts.LabelOpts(font_size=20, color='white', font_family='Microsoft YaHei'))
.set_global_opts(
title_opts=opts.TitleOpts(title='炸板率', title_textstyle_opts=opts.TextStyleOpts(color='#fff', font_size=16)),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal')
)
)
c2 = (
Gauge(init_opts=opts.InitOpts(theme=ThemeType.DARK))
.add(series_name='', data_pair=[['打板成功率', v2]], title_label_opts=opts.LabelOpts(font_size=20, color='white', font_family='Microsoft YaHei'))
.set_global_opts(
title_opts=opts.TitleOpts(title='打板成功率', title_textstyle_opts=opts.TextStyleOpts(color='#fff', font_size=16)),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal')
)
)
c3 = (
Gauge(init_opts=opts.InitOpts(theme=ThemeType.DARK))
.add(series_name='', data_pair=[['打板盈利率', v3]], title_label_opts=opts.LabelOpts(font_size=20, color='white', font_family='Microsoft YaHei'))
.set_global_opts(
title_opts=opts.TitleOpts(title='打板盈利率', title_textstyle_opts=opts.TextStyleOpts(color='#fff', font_size=16)),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal')
)
)
return c1, c2, c3
#############################################################################################################################################################
# f2函数，c4分板块成交额占比
def f2():
sql1 = '''
SELECT
t2.market,
SUM(t1.amount * 1000 ) AS 'amount'
FROM
stock_daily t1
LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code
WHERE
t1.trade_date = (SELECT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) AND t2.market IS NOT NULL AND t1.ts_code NOT IN ('000001.SH', '399001.SZ', '399006.SZ')
GROUP BY
t2.market
'''
data1 = pd.read_sql(sql1, conn)
k1 = data1['market'].tolist()
v1 = data1['amount'].tolist()
v2 = 0
for i in v1:
v2 += i
v3 = []
for j in v1:
v3.append(round(j / v2 * 100, 1))
c4 = (
Pie()
.add(series_name='', data_pair=[list(z) for z in zip(k1, v3)], radius=['40%', '75%'])
.set_global_opts(
title_opts=opts.TitleOpts(title='交易板块成交占比'),
legend_opts=opts.LegendOpts(orient='vertical', pos_top='15%', pos_left='2%'),
)
.set_series_opts(label_opts=opts.LabelOpts(formatter='{b}: {c}'))
)
return c4
#############################################################################################################################################################
# f3函数，c5分行业成交额占比
def f3():
sql1 = '''
SELECT
industry,
sum(amount) AS 'amount'
FROM
(SELECT
(CASE
WHEN yjhy IN ( '金融服务' ) THEN '金融'
WHEN yjhy IN ( '信息服务', '信息设备', '电子' ) THEN 'TMT'
WHEN yjhy IN ( '医药生物', '食品饮料' ) THEN '喝酒吃药'
ELSE '其他' END ) AS 'industry',
SUM( t1.amount * 1000 ) AS 'amount'
FROM
stock_daily t1
LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code
WHERE
t1.trade_date = ( SELECT cal_date FROM trade_cal_date WHERE is_open = 1 ORDER BY cal_date DESC LIMIT 1 ) AND t2.market IS NOT NULL AND t1.ts_code NOT IN ( '000001.SH', '399001.SZ', '399006.SZ' )
GROUP BY industry ) t3
GROUP BY industry
'''
data1 = pd.read_sql(sql1, conn)
k1 = data1['industry'].tolist()
v1 = data1['amount'].tolist()
v2 = 0
for i in v1:
v2 += i
v3 = []
for j in v1:
v3.append(round(j / v2 * 100, 1))
c5 = (
Pie()
.add(series_name='', data_pair=[list(z) for z in zip(k1, v3)], radius=['40%', '75%'])
.set_global_opts(
title_opts=opts.TitleOpts(title='行业成交占比'),
legend_opts=opts.LegendOpts(orient='vertical', pos_top='15%', pos_left='2%'),
)
.set_series_opts(label_opts=opts.LabelOpts(formatter='{b}: {c}'))
)
return c5
#############################################################################################################################################################
# f4函数，l1涨跌停统计、l2涨跌统计、l3吃肉吃面、l4赚钱效应、l5日内情绪、l6低位涨停表现、l7大幅涨跌统计、l8高度板涨停表现
def f4():
sql1 = '''
SELECT
trade_date,zrzt,szjs,xdjs,zt,dt,zbl,cgl,yll,szd5,xdd5,rndr,rndm,yzb,elb,slb,f4b,gdb
FROM
-- 日内肉、日内面、日内大涨、日内大跌
(SELECT trade_date,
COUNT(DISTINCT CASE WHEN pct_chg >= 5 THEN ts_code ELSE 0 END) AS 'szd5',
COUNT(DISTINCT CASE WHEN pct_chg <=- 5 THEN ts_code ELSE 0 END) AS 'xdd5',
COUNT(DISTINCT CASE WHEN (`close` - low)/low >= 0.1 THEN ts_code ELSE 0 END) AS 'rndr',
COUNT(DISTINCT CASE WHEN (`close` - high)/high <=- 0.1 THEN ts_code ELSE 0 END) AS 'rndm'
FROM stock_daily WHERE ts_code NOT IN ( '000001.SH', '399001.SZ', '399006.SZ' ) GROUP BY trade_date) t1
LEFT JOIN
scqx t2 ON t1.trade_date = t2.`day`
LEFT JOIN
-- 一字板
(SELECT date,
COUNT(DISTINCT CASE WHEN lbc = 1 THEN `code` ELSE 0 END) AS 'yzb',
COUNT(DISTINCT CASE WHEN lbc = 2 THEN `code` ELSE 0 END) AS 'elb',
COUNT(DISTINCT CASE WHEN lbc = 3 THEN `code` ELSE 0 END) AS 'slb',
COUNT(DISTINCT CASE WHEN lbc = 4 THEN `code` ELSE 0 END) AS 'f4b',
COUNT(DISTINCT CASE WHEN lbc > 4 THEN `code` ELSE 0 END) AS 'gdb'
FROM limit_stock_lbc GROUP BY date) t3 ON t1.trade_date = t3.date
ORDER BY trade_date ASC
'''
data1 = pd.read_sql(sql1, conn)
x = data1['trade_date'].tolist()
y1 = data1['zrzt'].tolist()
y2 = data1['szjs'].tolist()
y3 = data1['xdjs'].tolist()
y4 = data1['zt'].tolist()
y5 = data1['dt'].tolist()
y6 = data1['zbl'].tolist()
y7 = data1['cgl'].tolist()
y8 = data1['yll'].tolist()
y9 = data1['szd5'].tolist()
y10 = data1['xdd5'].tolist()
y11 = data1['rndr'].tolist()
y12 = data1['rndm'].tolist()
y13 = data1['yzb'].tolist()
y14 = data1['elb'].tolist()
y15 = data1['slb'].tolist()
y16 = data1['f4b'].tolist()
y17 = data1['gdb'].tolist()
l1 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('涨停', y4, is_smooth=True,is_connect_nones=False)
.add_yaxis('跌停', y5, is_smooth=True,is_connect_nones=False)
.add_yaxis('自然涨停', y1, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='涨跌停家数'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
l2 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('上涨', y2, is_smooth=True,is_connect_nones=False)
.add_yaxis('下跌', y3, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='涨跌家数'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
l3 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('肉', y11, is_smooth=True,is_connect_nones=False)
.add_yaxis('面', y12, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='日内大肉及大面'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
l4 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('打板成功率', y7, is_smooth=True,is_connect_nones=False)
.add_yaxis('打板盈利率', y8, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='赚钱效应'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
l5 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('炸板率', y6, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='炸板率'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
l6 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('一板', y13, is_smooth=True,is_connect_nones=False)
.add_yaxis('二板', y14, is_smooth=True,is_connect_nones=False)
.add_yaxis('三板', y15, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='低位连板家数'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
l7 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('大幅上涨', y9, is_smooth=True,is_connect_nones=False)
.add_yaxis('大幅下跌', y10, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='大幅涨跌家数'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
l8 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('四板', y16, is_smooth=True,is_connect_nones=False)
.add_yaxis('高度板', y17, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='高位连板停家数'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
return l1, l2, l3, l4, l5, l6, l7, l8
#############################################################################################################################################################
# f5函数，l9市场量能、l10竞价量能、l11涨跌停封单、l12一线游资、l13顶级游资、l14知名游资、l15机构、l16外资、l17北向资金、l18中单、l19大单、l20超大单、b1大单净额
def f5():
sql1 = '''
SELECT
t1.trade_date,
round(qsccj/100000000,2) AS 'qsccj',
round(jjcj/100000000,2) AS 'jjcj',
round(ztfd/100000000,2) AS 'ztfd',
round(dtfd/100000000,2) AS 'dtfd',
round(yxb/100000000,2) AS 'yxb',
round(djb/100000000,2) AS 'djb',
round(zmb/100000000,2) AS 'zmb',
round(jgb/100000000,2) AS 'jgb',
round(wzb/100000000,2) AS 'wzb',
round(yxs/100000000,2) AS 'yxs',
round(djs/100000000,2) AS 'djs',
round(zms/100000000,2) AS 'zms',
round(jgs/100000000,2) AS 'jgs',
round(wzs/100000000,2) AS 'wzs',
round(hgt/100000000,2) AS 'hgt',
round(sgt/100000000,2) AS 'sgt',
round(north/100000000,2) AS 'north',
round(zdb/100000000,2) AS 'zdb',
round(ddb/100000000,2) AS 'ddb',
round(cddb/100000000,2) AS 'cddb',
-round(zds/100000000,2) AS 'zds',
-round(dds/100000000,2) AS 'dds',
-round(cdds/100000000,2) AS 'cdds',
round(ddje/100000000,2) AS 'zddje',
round((yxb+yxs)/100000000,2) AS 'yxje',
round((djb+djs)/100000000,2) AS 'djje',
round((zmb+zms)/100000000,2) AS 'zmje',
round((jgb+jgs)/100000000,2) AS 'jgje',
round((wzb+wzs)/100000000,2) AS 'wzje',
round((zdb-zds)/100000000,2) AS 'zdje',
round((ddb-dds)/100000000,2) AS 'ddje',
round((cddb-cdds)/100000000,2) AS 'cddje'
FROM
-- 全市场总成交金额
(SELECT trade_date,SUM(amount)*1000 AS 'qsccj' FROM stock_daily WHERE ts_code NOT IN ('000001.SH','399001.SZ','399006.SZ') GROUP BY trade_date) t1
LEFT JOIN
-- 竞价成交金额
(SELECT date,sum(money) AS 'jjcj' FROM call_auction GROUP BY date) t2 ON t1.trade_date=t2.date
LEFT JOIN
-- 涨停封单金额
(SELECT trade_date,sum(fd_amount) AS 'ztfd' FROM limit_stock_list WHERE `limit` = 'U' GROUP BY trade_date) t3 ON t1.trade_date=t3.trade_date
LEFT JOIN
-- 跌停封单金额
(SELECT trade_date,sum(fd_amount) AS 'dtfd' FROM limit_stock_list WHERE `limit` = 'D' GROUP BY trade_date) t4 ON t1.trade_date=t4.trade_date
LEFT JOIN
(SELECT date,
SUM(CASE WHEN (youziicon=1 AND direction='buy') THEN buy ELSE 0 END) AS 'yxb',
SUM(CASE WHEN (youziicon=2 AND direction='buy') THEN buy ELSE 0 END) AS 'djb',
SUM(CASE WHEN (youziicon=1 AND direction='buy') THEN buy ELSE 0 END) AS 'zmb',
SUM(CASE WHEN (dname='机构专用' AND direction='buy') THEN buy ELSE 0 END) AS 'jgb',
SUM(CASE WHEN (dname LIKE '_股通专用' AND direction='buy') THEN buy ELSE 0 END) AS 'wzb',
SUM(CASE WHEN (youziicon=1 AND direction='sell') THEN sell ELSE 0 END) AS 'yxs',
SUM(CASE WHEN (youziicon=2 AND direction='sell') THEN sell ELSE 0 END) AS 'djs',
SUM(CASE WHEN (youziicon=1 AND direction='sell') THEN sell ELSE 0 END) AS 'zms',
SUM(CASE WHEN (dname='机构专用' AND direction='sell') THEN sell ELSE 0 END) AS 'jgs',
SUM(CASE WHEN (dname LIKE '_股通专用' AND direction='sell') THEN sell ELSE 0 END) AS 'wzs'
FROM lhb_list GROUP BY date)t5 ON t1.trade_date=t5.date
LEFT JOIN
-- 北向资金
(SELECT trade_date,hgt*1000000 AS 'hgt',sgt*1000000 AS 'sgt',north_money*1000000 AS 'north' FROM hsgt_moneyflow) t6 ON t1.trade_date=t6.trade_date
LEFT JOIN
-- 大单资金
(SELECT trade_date,
SUM(buy_md_amount)*10000 AS 'zdb',
SUM(buy_lg_amount)*10000 AS 'ddb',
SUM(buy_elg_amount)*10000 AS 'cddb',
SUM(sell_md_amount)*10000 AS 'zds',
SUM(sell_lg_amount)*10000 AS 'dds',
SUM(sell_elg_amount)*10000 AS 'cdds',
SUM(net_mf_amount)*10000 AS 'ddje'
FROM stock_moneyflow GROUP BY trade_date) t7 ON t1.trade_date=t7.trade_date
ORDER BY t1.trade_date ASC
'''
data1 = pd.read_sql(sql1, conn)
x = data1['trade_date'].tolist()
y1 = data1['qsccj'].tolist() # 全市场量能
y2 = data1['jjcj'].tolist() # 竞价量能
y3 = data1['ztfd'].tolist() # 涨停封单
y4 = data1['dtfd'].tolist() # 跌停封单
y5 = data1['yxb'].tolist() # 一线游资买入
y6 = data1['djb'].tolist() # 顶级游资买入
y7 = data1['zmb'].tolist() # 知名游资买入
y8 = data1['jgb'].tolist() # 机构买入
y9 = data1['wzb'].tolist() # 外资买入
y10 = data1['yxs'].tolist() # 一线游资卖出
y11 = data1['djs'].tolist() # 顶级游资卖出
y12 = data1['zms'].tolist() # 知名游资卖出
y13 = data1['jgs'].tolist() # 机构卖出
y14 = data1['wzs'].tolist() # 外资卖出
y15 = data1['hgt'].tolist() # 沪股通
y16 = data1['sgt'].tolist() # 深股通
y17 = data1['north'].tolist() # 北向资金
y18 = data1['zdb'].tolist() # 中单买入
y19 = data1['ddb'].tolist() # 大单买入
y20 = data1['cddb'].tolist() # 超大单买入
y21 = data1['zds'].tolist() # 中单卖出
y22 = data1['dds'].tolist() # 大单卖出
y23 = data1['cdds'].tolist() # 超大单卖出
y24 = data1['zddje'].tolist() # 总大单净额
y25 = data1['yxje'].tolist() # 一线游资净额
y26 = data1['djje'].tolist() # 顶级游资净额
y27 = data1['zmje'].tolist() # 知名游资净额
y28 = data1['jgje'].tolist() # 机构净额
y29 = data1['wzje'].tolist() # 外资净额
y30 = data1['zdje'].tolist() # 中单净额
y31 = data1['ddje'].tolist() # 大单净额
y32 = data1['cddje'].tolist() # 超大单净额
l9 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('全市场', y1, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='全市场市场量能'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l10 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('竞价', y2, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='全市场竞价量能'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l11 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('涨停', y3, is_smooth=True,is_connect_nones=False)
.add_yaxis('跌停', y4, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='涨跌停封单金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l12 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y5, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y10, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='龙虎榜一线游资买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l13 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y6, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y11, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='龙虎榜顶级游资买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l14 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y7, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y12, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='龙虎榜知名游资买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l15 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y8, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y13, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='龙虎榜机构买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l16 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y9, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y14, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='龙虎榜外资买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l17 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('沪股通', y15, is_smooth=True,is_connect_nones=False)
.add_yaxis('深股通', y16, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='沪深股通买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l18 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y18, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y21, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='中单买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l19 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y19, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y21, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='大单买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
l20 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('买入', y20, is_smooth=True,is_connect_nones=False)
.add_yaxis('卖出', y23, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='超大单买卖金额'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元'))
)
)
b1 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('大单', y24)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='全市场大单净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b5 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('北向', y17)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='北向资金净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b6 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('一线游资', y25)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='一线游资净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b7 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('顶级游资', y26)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='顶级游资净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b8 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('知名游资', y27)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='知名游资净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b9 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('机构', y28)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='机构净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b10 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('外资', y29)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='外资净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b11 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('中单', y30)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='中单净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b12 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('大单', y31)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='大单净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
b13 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('超大单', y32)
.set_global_opts(
yaxis_opts=opts.AxisOpts(is_scale=True, axislabel_opts=opts.LabelOpts(formatter='{value}亿元')),
title_opts=opts.TitleOpts(title='超大单净额'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
legend_opts=opts.LegendOpts(legend_icon='rect'),
)
)
return l9, l10, l11, l12, l13, l14, l15, l16, l17, l18, l19, l20, b1, b5, b6, b7, b8, b9, b10, b11, b12, b13
#############################################################################################################################################################
# f6函数，K1上证指数、k2深证指数、k3创业板指数、、b2上证涨跌幅、b3深证涨跌幅、b4创业板涨跌幅、
def f6():
sql1 = '''SELECT ts_code,trade_date,`open`,high,low,`close`,pre_close,pct_chg FROM stock_daily WHERE ts_code='000001.SH' ORDER BY trade_date ASC'''
sql2 = '''SELECT ts_code,trade_date,`open`,high,low,`close`,pre_close,pct_chg FROM stock_daily WHERE ts_code='399001.SZ' ORDER BY trade_date ASC'''
sql3 = '''SELECT ts_code,trade_date,`open`,high,low,`close`,pre_close,pct_chg FROM stock_daily WHERE ts_code='399006.SZ' ORDER BY trade_date ASC'''
data1 = pd.read_sql(sql1, conn)
data2 = pd.read_sql(sql2, conn)
data3 = pd.read_sql(sql3, conn)
x = data1['trade_date'].tolist()
y1 = []
for index, row in data1.iterrows():
list = [row['open'], row['close'], row['low'], row['high']]
y1.append(list)
y2 = []
for index, row in data2.iterrows():
list = [row['open'], row['close'], row['low'], row['high']]
y2.append(list)
y3 = []
for index, row in data3.iterrows():
list = [row['open'], row['close'], row['low'], row['high']]
y3.append(list)
y4 = data1['pct_chg'].tolist()
y5 = data2['pct_chg'].tolist()
y6 = data3['pct_chg'].tolist()
k1 = (
Kline(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('上证', y1)
.set_global_opts(
xaxis_opts=opts.AxisOpts(is_scale=True),
yaxis_opts=opts.AxisOpts(is_scale=True, splitarea_opts=opts.SplitAreaOpts(is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1))),
title_opts=opts.TitleOpts(title='上证指数', title_link='http://q.10jqka.com.cn/'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100)
)
)
b2 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('上证', y4)
.set_global_opts(
yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter='{value}%')),
title_opts=opts.TitleOpts(title='上证涨跌幅'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
)
)
k2 = (
Kline(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('深证', y2)
.set_global_opts(
xaxis_opts=opts.AxisOpts(is_scale=True),
yaxis_opts=opts.AxisOpts(is_scale=True, splitarea_opts=opts.SplitAreaOpts(is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1))),
title_opts=opts.TitleOpts(title='深证指数'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
)
)
b3 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('深证', y5)
.set_global_opts(
yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter='{value}%')),
title_opts=opts.TitleOpts(title='深证涨跌幅'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
)
)
k3 = (
Kline(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('创业板', y3)
.set_global_opts(
xaxis_opts=opts.AxisOpts(is_scale=True),
yaxis_opts=opts.AxisOpts(is_scale=True, splitarea_opts=opts.SplitAreaOpts(is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1))),
title_opts=opts.TitleOpts(title='创业指数'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
)
)
b4 = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('创业板', y6)
.set_global_opts(
yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter='{value}%')),
title_opts=opts.TitleOpts(title='创业板涨跌幅'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
)
)
return k1, k2, k3, b2, b3, b4
#############################################################################################################################################################
# f7函数，l21shibor利率
def f7():
sql = '''SELECT date,`on` FROM shibor ORDER BY date ASC'''
data = pd.read_sql(sql, conn)
x = data['date'].tolist()
y = data['on'].tolist()
print(x)
l21 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK, width='1660px', height='560px'))
.add_xaxis(x)
.add_yaxis('隔夜', y, is_smooth=True,is_connect_nones=False)
.set_global_opts(
title_opts=opts.TitleOpts(title='shibor'),
toolbox_opts=opts.ToolboxOpts(is_show=True, orient='horizontal'),
datazoom_opts=opts.DataZoomOpts(range_start=20,range_end=100),
legend_opts=opts.LegendOpts(legend_icon='rect'),
yaxis_opts=opts.AxisOpts(is_scale=True)
)
)
return l21
#############################################################################################################################################################
# f8函数，table1日内异动明细
def f8():
sql1 = '''
SELECT
t3.trade_date,t2.ts_code,t1.`name`,COUNT(chg_time) AS 'ydcs',round((free_share*`close`)/10000,2) AS 'zyltsz',ejhy
FROM
stock_change t1
LEFT JOIN stock_basic t2 ON t1.`code`=t2.ts_code
LEFT JOIN stock_daily t3 ON t1.`code`=t3.ts_code AND date(t1.chg_time)=t3.trade_date
LEFT JOIN stock_daily_basic t4 ON t1.`code`=t4.ts_code AND date(t1.chg_time)=t4.trade_date
WHERE ejhy IS NOT NULL AND date(chg_time)=(SELECT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1)
GROUP BY t2.ts_code ORDER BY ydcs DESC
'''
data1 = pd.read_sql(sql1, conn)
r1 = ['交易日期', '所属行业', '股票代码', '股票名称', '日内异动次数', '自由流通市值']
y1 = []
for index, row in data1.iterrows():
list = [row['trade_date'], row['ejhy'], row['ts_code'], row['name'], row['ydcs'], row['zyltsz']]
y1.append(list)
table1 = (
Table()
.add(headers=r1, rows=y1)
.set_global_opts(
title_opts=ComponentTitleOpts(title='异动个股明细'),
)
)
return table1
#############################################################################################################################################################
# f9函数，table2 60日新高
def f9():
sql2 = '''
SELECT
t3.trade_date,
(CASE WHEN chg_type='8213' THEN '60日新高' ELSE '60日大幅上涨' END) AS 'ydlx',
t2.ts_code,t1.`name`,round((free_share*`close`)/10000,2) AS 'zyltsz',ejhy
FROM
stock_change t1
LEFT JOIN stock_basic t2 ON t1.`code`=t2.ts_code
LEFT JOIN stock_daily t3 ON t1.`code`=t3.ts_code AND date(t1.chg_time)=t3.trade_date
LEFT JOIN stock_daily_basic t4 ON t1.`code`=t4.ts_code AND date(t1.chg_time)=t4.trade_date
WHERE ejhy IS NOT NULL AND date(chg_time)=(SELECT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1)
AND chg_type IN ('8213','8215') GROUP BY t2.ts_code ORDER BY ydlx DESC
'''
data2 = pd.read_sql(sql2, conn)
r2 = ['交易日期', '异动类型', '所属行业', '股票代码', '股票名称', '自由流通市值']
y2 = []
for index, row in data2.iterrows():
list = [row['trade_date'], row['ydlx'], row['ejhy'], row['ts_code'], row['name'], row['zyltsz']]
y2.append(list)
table2 = (
Table()
.add(headers=r2, rows=y2)
.set_global_opts(
title_opts=ComponentTitleOpts(title='60日新高及大幅上涨个股明细'),
)
)
return table2
#############################################################################################################################################################
# f10函数、table3沪深股通明细
def f10():
sql3 = '''
SELECT
t1.date,`code`,`name`,newprice,zdf,
round(sharesz/10000,1) AS 'cgsz',
round(ltzb*100,2) AS 'cgzb',
round(sharesz_chg_one/10000,1) AS 'zcsz',
ejhy
FROM
hsgt_detail t1
LEFT JOIN stock_basic t2 ON t1.`code`=t2.ts_code
WHERE
date = ( SELECT DISTINCT date FROM hsgt_detail ORDER BY date DESC LIMIT 1, 1 )
ORDER BY zcsz DESC
'''
data3 = pd.read_sql(sql3, conn)
r3 = ['交易日期', '所属行业', '股票代码', '股票名称', '股价', '涨跌幅', '增持市值(万元)', '持股市值(万元)', '持股占流通股比例(%)']
y3 = []
for index, row in data3.iterrows():
list = [row['date'], row['ejhy'], row['code'], row['name'], row['newprice'], row['zdf'], row['zcsz'], row['cgsz'], row['cgzb']]
y3.append(list)
table3 = (
Table()
.add(headers=r3, rows=y3)
.set_global_opts(
title_opts=ComponentTitleOpts(title='港资个股买卖明细'),
)
)
return table3
#############################################################################################################################################################
# f11函数，table4游资明细
def f11():
sql4 = '''
SELECT
t1.date,
`code`,
`name`,
ejhy,
round(buy/10000,1) AS 'buy',
round(sell/10000,1) AS 'sell',
round((buy+sell)/10000,1) AS 'net',
groupicon,
(CASE
WHEN youziicon = 1 THEN '一线游资'
WHEN youziicon = 2 THEN '顶级游资'
WHEN youziicon = 3 THEN '知名游资'
WHEN dname = '机构专用' THEN '机构'
WHEN dname LIKE '_股通专用' THEN '外资' ELSE '其他' END
) AS 'fund_type'
FROM
lhb_list t1
LEFT JOIN stock_basic t2 ON t1.`code` = t2.ts_code
WHERE
buy >= 50000000 AND t1.date=( SELECT DISTINCT date FROM hsgt_detail ORDER BY date DESC LIMIT 1, 1 )
ORDER BY `code`, net DESC
'''
data4 = pd.read_sql(sql4, conn)
r4 = ['交易日期', '所属行业', '股票代码', '股票名称', '买入金额(万元)', '卖出金额(万元)', '净买入额(万元)', '营业部标签', '资金分类']
y4 = []
for index, row in data4.iterrows():
list = [row['date'], row['ejhy'], row['code'], row['name'], row['buy'], row['sell'], row['net'], row['groupicon'], row['fund_type']]
y4.append(list)
table4 = (
Table()
.add(headers=r4, rows=y4)
.set_global_opts(
title_opts=ComponentTitleOpts(title='龙虎榜买入超5000万明细'),
)
)
return table4
#############################################################################################################################################################
# f12函数，table5个股大资金明细
def f12():
sql5 = '''
SELECT
v1.trade_date,
v1.ejhy,
v1.ts_code,
v1.`name`,
v1.net AS 'jr',
v2.net AS 'sr',
v3.net AS 'wr',
v4.net AS 'shr',
v5.net AS 'ehr',
v6.net AS 'thr'
FROM
(SELECT t1.trade_date,t2.ts_code,t2.`name`,SUM(net_mf_amount) AS 'net',t2.ejhy FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date = (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v1
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v2 ON v1.ts_code = v2.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 3, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v3 ON v1.ts_code = v3.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 8, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v4 ON v1.ts_code = v4.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 18, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v5 ON v1.ts_code = v5.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 28, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v6 ON v1.ts_code = v6.ts_code
WHERE v1.ejhy IS NOT NULL
ORDER BY jr DESC
'''
data5 = pd.read_sql(sql5, conn)
r5 = ['交易日期', '所属行业', '股票代码', '股票名称', '今日(万元)', '3日(万元)', '5日(万元)', '10日(万元)', '20日(万元)', '30日(万元)']
y5 = []
for index, row in data5.iterrows():
list = [row['trade_date'], row['ejhy'], row['ts_code'], row['name'], row['jr'], row['sr'], row['wr'], row['shr'], row['ehr'], row['thr']]
y5.append(list)
table5 = (
Table()
.add(headers=r5, rows=y5)
.set_global_opts(
title_opts=ComponentTitleOpts(title='个股大单明细'),
)
)
return table5
#############################################################################################################################################################
# f13函数，table6行业大资金明细
def f13():
sql6 = '''
SELECT
p.trade_date,
p.ejhy,
SUM(jr) AS 'jr',
SUM(sr) AS 'sr',
SUM(wr) AS 'wr',
SUM(shr) AS 'shr',
SUM(ehr) AS 'ehr',
SUM(thr) AS 'thr'
FROM
(SELECT
v1.trade_date,
v1.ts_code,
v1.`name`,
v1.net AS 'jr',
v2.net AS 'sr',
v3.net AS 'wr',
v4.net AS 'shr',
v5.net AS 'ehr',
v6.net AS 'thr',
v1.ejhy
FROM
(SELECT t1.trade_date,t2.ts_code,t2.`name`,SUM(net_mf_amount) AS 'net',t2.ejhy FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date = (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v1
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v2 ON v1.ts_code = v2.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 3, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v3 ON v1.ts_code = v3.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 8, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v4 ON v1.ts_code = v4.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 18, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v5 ON v1.ts_code = v5.ts_code
LEFT JOIN
(SELECT t2.ts_code,SUM(net_mf_amount) AS 'net' FROM stock_moneyflow t1 LEFT JOIN stock_basic t2 ON t1.ts_code = t2.ts_code WHERE t1.trade_date BETWEEN (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 28, 1) AND (SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open=1 ORDER BY cal_date DESC LIMIT 1) GROUP BY t2.ts_code) v6 ON v1.ts_code = v6.ts_code) p
WHERE p.ejhy IS NOT NULL
GROUP BY ejhy ORDER BY jr DESC
'''
data6 = pd.read_sql(sql6, conn)
r6 = ['交易日期', '所属行业', '今日(万元)', '3日(万元)', '5日(万元)', '10日(万元)', '20日(万元)', '30日(万元)']
y6 = []
for index, row in data6.iterrows():
list = [row['trade_date'], row['ejhy'], row['jr'], row['sr'], row['wr'], row['shr'], row['ehr'], row['thr']]
y6.append(list)
table6 = (
Table()
.add(headers=r6, rows=y6)
.set_global_opts(
title_opts=ComponentTitleOpts(title='行业大单明细'),
)
)
return table6
#############################################################################################################################################################
# f14函数，table7热门概念
def f14():
sql7 = '''
SELECT
date,`code`,`name`,GROUP_CONCAT( hot_cept ) AS 'hot_cept'
FROM
hot_concept
WHERE
date = ( SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open = 1 ORDER BY cal_date DESC LIMIT 1 )
GROUP BY
date,`code`
ORDER BY
hot_cept
'''
data7 = pd.read_sql(sql7, conn)
r7 = ['交易日期', '股票代码', '股票名称', '风口概念']
y7 = []
for index, row in data7.iterrows():
list = [row['date'], row['code'], row['name'], row['hot_cept']]
y7.append(list)
table7 = (
Table()
.add(headers=r7, rows=y7)
.set_global_opts(
title_opts=ComponentTitleOpts(title='龙虎榜风口概念'),
)
)
return table7
#############################################################################################################################################################
# f15函数，table8常用地址
def f15():
r8 = ['网站', 'url', '备注']
y8 = [
['财联社', 'https://www.cls.cn/', '即时消息'],
['牧童资讯', 'http://www.mutongzixun.com/', '四大报汇总'],
['蓝鲸', 'https://www.lanjinger.com/', '财经新闻'],
['界面', 'https://www.jiemian.com/', '财经新闻'],
['同花顺', 'http://data.10jqka.com.cn/', '数据中心'],
['东方财富', 'http://quote.eastmoney.com/center/hszs.html', '数据中心'],
['网易财经', 'http://quotes.money.163.com/stock/', '数据中心'],
['更多行业', 'https://zhuanlan.zhihu.com/p/136645566', '知乎整理']
]
table8 = (
Table()
.add(headers=r8, rows=y8)
.set_global_opts(
title_opts=ComponentTitleOpts(title='龙虎榜风口概念'),
)
)
return table8
#############################################################################################################################################################
# f16函数，table9阳线数量
def f16():
sql8 = '''
SELECT
t3.ts_code,`name`,ejhy,yang,yin,round(yang/yin,2) AS rate
FROM
(
SELECT
ts_code,SUM(CASE WHEN type='阳线' THEN syax ELSE 0 END) AS 'yang',SUM(CASE WHEN type='阴线' THEN syax ELSE 0 END) AS 'yin'
FROM
(
SELECT
ts_code,type,COUNT(CASE WHEN type='阳线' THEN 'Y' ELSE 'N' END) AS 'syax'
FROM
(
SELECT
trade_date,ts_code,(CASE WHEN (`close`-`open`) >= 0 THEN '阳线' ELSE '阴线' END) AS 'type'
FROM
stock_daily
WHERE
trade_date BETWEEN ( SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open = 1 ORDER BY cal_date DESC LIMIT 22, 1 )
AND ( SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open = 1 ORDER BY cal_date DESC LIMIT 1 )
AND ts_code NOT IN ( '000001.SH', '399001.SZ', '399006.SZ' )
) t1
GROUP BY
ts_code,type
) t2
GROUP BY
ts_code
ORDER BY
ts_code
) t3
LEFT JOIN stock_basic t4 ON t3.ts_code = t4.ts_code
ORDER BY
rate DESC
'''
data8 = pd.read_sql(sql8, conn)
r8 = ['股票代码', '股票名称', '所属行业', '阳线数量', '阴线数量', '阳/阴']
y8 = []
for index, row in data8.iterrows():
list = [row['ts_code'], row['name'], row['ejhy'], row['yang'], row['yin'], row['rate']]
y8.append(list)
table9 = (
Table()
.add(headers=r8, rows=y8)
.set_global_opts(
title_opts=ComponentTitleOpts(title='近期阳线及阴线占比'),
)
)
return table9
#############################################################################################################################################################
# f17函数，table10竞价排名
def f17():
sql8 = '''
SELECT
date,`code`,`name`,ejhy,round(money/10000,1) AS 'money'
FROM
call_auction t1
LEFT JOIN stock_basic t2 ON t1.`code`=t2.ts_code
WHERE
date=(SELECT DISTINCT cal_date FROM trade_cal_date WHERE is_open = 1 ORDER BY cal_date DESC LIMIT 1)
ORDER BY money DESC
'''
data8 = pd.read_sql(sql8, conn)
r8 = ['交易日期', '股票代码', '股票名称', '所属行业', '竞价成交额']
y8 = []
for index, row in data8.iterrows():
list = [row['date'], row['code'], row['name'], row['ejhy'], row['money']]
y8.append(list)
table10 = (
Table()
.add(headers=r8, rows=y8)
.set_global_opts(
title_opts=ComponentTitleOpts(title='竞价成交排行榜'),
)
)
return table10
#############################################################################################################################################################
if __name__ == '__main__':
conn = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8') # 创建引擎
# f1函数，c1、c2、c3分别表示炸板率、打板成功率、打板盈利率
# c1, c2, c3 = f1()
# f2函数，c4分板块成交额占比
c4 = f2()
# f3函数，c5分行业成交额占比
c5 = f3()
# f4函数，l1涨跌停统计、l2涨跌统计、l3吃肉吃面、l4赚钱效应、l5日内情绪、l6低位涨停表现、l7大幅涨跌统计、l8高度板涨停表现
l1, l2, l3, l4, l5, l6, l7, l8 = f4()
# f5函数，l9市场量能、l10竞价量能、l11涨跌停封单、l12一线游资、l13顶级游资、l14知名游资、l15机构、l16外资、l17北向资金、l18中单、l19大单、l20超大单、b1大单净额、b5北向净额
# b6一线净额,b7顶级定额,b8知名净额,b9机构净额,b10外资净额,b11中单净额,b12大单净额,b13超大单净额
l9, l10, l11, l12, l13, l14, l15, l16, l17, l18, l19, l20, b1, b5, b6, b7, b8, b9, b10, b11, b12, b13 = f5()
# f6函数，K1上证指数、k2深证指数、k3创业板指数、、b2上证涨跌幅、b3深证涨跌幅、b4创业板涨跌幅、
k1, k2, k3, b2, b3, b4 = f6()
# f7函数，l21shibor利率
l21 = f7()
# f8函数，table1日内异动明细
table1 = f8()
# f9函数，table2 60日新高
table2 = f9()
# f10函数、table3沪深股通明细
table3 = f10()
# f11函数，table4游资明细
table4 = f11()
# f12函数，table5个股大资金明细
table5 = f12()
# f13函数，table6行业大资金明细
table6 = f13()
# f14函数，table7热门概念
table7 = f14()
# f15函数，table8常用地址
table8 = f15()
# f16函数，table9阳线数量
table9 = f16()
# f17函数，table10竞价排名
table10 = f17()
tab = (
Tab()
.add(l1, '情绪-涨跌停家数')
.add(l2, '情绪-涨跌家数')
.add(l7, '情绪-大幅涨跌家数')
.add(l3, '情绪-日内吃肉吃面')
.add(l6, '情绪-低位涨停板')
.add(l8, '情绪-高位涨停板')
.add(l4, '情绪-赚钱效应')
.add(l5, '情绪-炸板率')
.add(l21, '情绪-shibor利率')
.add(l9, '资金-市场量能')
.add(l10, '资金-竞价量能')
.add(l11, '资金-涨跌停封单量能')
.add(l17, '资金-沪深股通')
.add(b5, '资金-北向净额')
.add(b11, '资金-中单净额')
.add(b12, '资金-大单净额')
.add(b13, '资金-超大单净额')
.add(b1, '资金-主力净额')
.add(l18, '资金-中单')
.add(l19, '资金-大单')
.add(l20, '资金-超大单')
.add(l12, '龙虎榜资金-一线游资')
.add(l13, '龙虎榜资金-顶级游资')
.add(l14, '龙虎榜资金-知名游资')
.add(l15, '龙虎榜资金-机构')
.add(l16, '龙虎榜资金-外资')
.add(b6, '龙虎榜资金-一线游资净额')
.add(b7, '龙虎榜资金-顶级游资净额')
.add(b8, '龙虎榜资金-知名游资净额')
.add(b9, '龙虎榜资金-机构净额')
.add(b10, '龙虎榜资金-外资净额')
.add(table1, '机会-个股异动明细')
.add(table2, '机会-60日新高明细')
.add(table3, '机会-沪深股通明细')
.add(table4, '机会-龙虎榜明细')
.add(table5, '机会-个股大单明细')
.add(table6, '机会-行业大单明细')
.add(table7, '机会-风口概念')
.add(table9, '机会-阳线阴线占比')
.add(table10, '机会-个股竞价明细')
.add(k1, '盘面-上证指数')
.add(b2, '盘面-上证涨跌幅')
.add(k2, '盘面-深证指数')
.add(b3, '盘面-深证涨跌幅')
.add(k3, '盘面-创业板指数')
.add(b4, '盘面-创业板涨跌幅')
.add(c4, '盘面-市场板块成交占比')
.add(c5, '盘面-行业成交占比')
.add(table8, '更多-拓展网站')
)
tab.render('20200722.html')
CREATE TABLE `call_auction` ( `code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `date` date DEFAULT NULL COMMENT '交易日期', `price` float(9,2) DEFAULT NULL COMMENT '股票价格（元）', `volume` float(18,2) DEFAULT NULL COMMENT '成交数量（股）', `money` float(18,2) DEFAULT NULL COMMENT '成交金额（元）', KEY `code+date` (`code`,`date`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `change_type` ( `chg_type` varchar(9) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '异动类型', `chg_name` varchar(18) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '异动名称', KEY `type` (`chg_type`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `concept_detail` ( `concept_name` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '所属概念', `ts_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', KEY `code` (`ts_code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `flow_holder` ( `stk_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `stk_name` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票名称', `cir_num` float(18,2) DEFAULT NULL COMMENT '流通股东持股数量(股)，持股比例大于5%', `cir_rate` float(18,2) DEFAULT NULL COMMENT '流通股东持股比例(%)，持股比例大于5%', KEY `code` (`stk_code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `hot_concept` ( `date` date DEFAULT NULL COMMENT '交易日期', `code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `name` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票名称', `hot_cept` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '风口概念', KEY `code+date` (`date`,`code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `hsgt_detail` ( `date` date DEFAULT NULL COMMENT '交易日期', `code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `hyname` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '行业板块', `dqname` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '地区板块', `newprice` float(18,2) DEFAULT NULL COMMENT '股票价格', `zdf` float(18,2) DEFAULT NULL COMMENT '涨跌幅', `sharehold` float(18,2) DEFAULT NULL COMMENT '今日持股股数，无单位', `sharesz` float(18,2) DEFAULT NULL COMMENT '今日持股市值，无单位', `ltzb` float(18,2) DEFAULT NULL COMMENT '今日持股占流通股比，无单位', `sharesrate` float(18,2) DEFAULT NULL COMMENT '今日持股占总股本比，单位%', `sharehold_chg_one` float(18,2) DEFAULT NULL COMMENT '今日增持股数，无单位', `sharesz_chg_one` float(18,2) DEFAULT NULL COMMENT '今日增持市值，无单位', `ltzb_one` float(18,2) DEFAULT NULL COMMENT '今日增持占流通股比，无单位', `zzb_one` float(18,2) DEFAULT NULL COMMENT '今日增持占总股本比,无单位', KEY `code+date` (`date`,`code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `hsgt_moneyflow` ( `trade_date` date DEFAULT NULL COMMENT '交易日期', `hgt` float(18,2) DEFAULT NULL COMMENT '沪股通（百万元）', `sgt` float(18,2) DEFAULT NULL COMMENT '深股通（百万元）', `north_money` float(18,2) DEFAULT NULL COMMENT '北向资金（百万元）', KEY `date` (`trade_date`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `lhb_list` ( `date` date DEFAULT NULL COMMENT '交易日期', `code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `direction` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '买卖方向', `dname` varchar(72) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '营业部名称', `buy` float(18,2) DEFAULT NULL COMMENT '买入金额（元）', `sell` float(18,2) DEFAULT NULL COMMENT '卖出金额（元）', `groupicon` varchar(27) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '营业部标签', `youziicon` int(9) DEFAULT NULL COMMENT '游资标签', KEY `code+date` (`date`,`code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `limit_stock_lbc` ( `date` date DEFAULT NULL COMMENT '交易日期', `code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `lbc` int(2) DEFAULT NULL COMMENT '连板数', `hybk` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '行业板块', KEY `code+date` (`date`,`code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `limit_stock_list` ( `trade_date` date DEFAULT NULL COMMENT '交易日期', `ts_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `close` float(9,2) DEFAULT NULL COMMENT '收盘价（元）', `pct_chg` float(8,2) DEFAULT NULL COMMENT '涨跌幅（%）', `fc_ratio` float(9,2) DEFAULT NULL COMMENT '封单金额/日成交金额', `fd_amount` float(18,2) DEFAULT NULL COMMENT '封单金额（元）', `first_time` time DEFAULT NULL COMMENT '首次涨停时间', `last_time` time DEFAULT NULL COMMENT '最后涨停时间', `open_times` int(3) DEFAULT NULL COMMENT '开板次数', `strth` float(9,2) DEFAULT NULL COMMENT '涨跌轻度', `limit` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '涨停跌停', KEY `code+date` (`trade_date`,`ts_code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `scqx` ( `day` text COLLATE utf8mb4_general_ci, `zrzt` bigint(20) DEFAULT NULL, `ztzb` bigint(20) DEFAULT NULL, `szjs` bigint(20) DEFAULT NULL, `xdjs` bigint(20) DEFAULT NULL, `zt` bigint(20) DEFAULT NULL, `dt` bigint(20) DEFAULT NULL, `zbl` double DEFAULT NULL, `cgl` double DEFAULT NULL, `yll` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `shibor` ( `date` date DEFAULT NULL COMMENT '交易日期', `on` float(9,2) DEFAULT NULL COMMENT '隔夜shibor', `1m` float(9,2) DEFAULT NULL COMMENT '一月shibor', KEY `date` (`date`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `stock_basic` ( `ts_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `name` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票名称', `area` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '所属省份', `industry` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '所属行业', `market` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '交易市场', `exchange` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '交易所', `list_date` date DEFAULT NULL COMMENT '上市日期', `is_hs` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '是否沪深港通', `yjhy` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '一级行业', `ejhy` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '二级行业', `sjhy` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '三级行业', KEY `code` (`ts_code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `stock_change` ( `chg_time` datetime DEFAULT NULL COMMENT '交易时间', `code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `name` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票名称', `chg_type` int(9) DEFAULT NULL COMMENT '异动类型', `chg_value` float(18,2) DEFAULT NULL COMMENT '异动数值', `chg_index` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '数据单位', KEY `code+date` (`chg_time`,`code`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `stock_daily` ( `ts_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `trade_date` date DEFAULT NULL COMMENT '交易日期', `open` float(9,2) DEFAULT NULL COMMENT '开盘价（元）', `high` float(9,2) DEFAULT NULL COMMENT '最高价（元）', `low` float(9,2) DEFAULT NULL COMMENT '最低价（元）', `close` float(9,2) DEFAULT NULL COMMENT '收盘价（元）', `pre_close` float(9,2) DEFAULT NULL COMMENT '昨収价（元）', `pct_chg` float(9,2) DEFAULT NULL COMMENT '涨跌幅（%）', `amount` float(18,2) DEFAULT NULL COMMENT '成交额（千元）', KEY `code+date` (`ts_code`,`trade_date`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `stock_daily_basic` ( `ts_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `trade_date` date DEFAULT NULL COMMENT '交易日期', `turnover_rate` float(9,2) DEFAULT NULL COMMENT '换手率（%）', `turnover_rate_f` float(9,2) DEFAULT NULL COMMENT '实际换手率（%）', `volume_ratio` float(9,2) DEFAULT NULL COMMENT '量比', `total_share` float(18,2) DEFAULT NULL COMMENT '总股本（万股）', `float_share` float(18,2) DEFAULT NULL COMMENT '流通股本（万股）', `free_share` float(18,2) DEFAULT NULL COMMENT '自由流通股本（万股）', KEY `code+date` (`ts_code`,`trade_date`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `stock_moneyflow` ( `ts_code` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '股票代码', `trade_date` date DEFAULT NULL COMMENT '交易日期', `buy_md_amount` float(18,2) DEFAULT NULL COMMENT '中单买入额（万元）', `sell_md_amount` float(18,2) DEFAULT NULL COMMENT '中单卖出额（万元）', `buy_lg_amount` float(18,2) DEFAULT NULL COMMENT '大单买入额（万元）', `sell_lg_amount` float(18,2) DEFAULT NULL COMMENT '大单卖出额（万元）', `buy_elg_amount` float(18,2) DEFAULT NULL COMMENT '超大单买入额（万元）', `sell_elg_amount` float(18,2) DEFAULT NULL COMMENT '超大单卖出额（万元）', `net_mf_amount` float(18,2) DEFAULT NULL COMMENT '净买入额（万元）', KEY `code+date` (`ts_code`,`trade_date`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CREATE TABLE `trade_cal_date` ( `exchange` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '交易所', `cal_date` date DEFAULT NULL, `is_open` int(1) DEFAULT NULL, `pretrade_date` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 8201 火箭发射 8202 快速反弹 8193 大笔买入 4 封涨停板 32 打开跌停板 64 有大买盘 8207 竞价上涨 8209 高开5日线 8211 向上缺口 8213 60日新高 8215 60日大幅上涨 8204 加速下跌 8203 高台跳水 8194 大笔卖出 8 封跌停板 16 打开涨停板 128 有大卖盘 8208 竞价下跌 8210 低开5日线 8212 向下缺口 8214 60日新低 8216 60日大幅下跌