from pyecharts import options as opts # 全局、系列配置
from pyecharts.charts import Bar, Grid, Line # 柱形图
from pyecharts.globals import ThemeType # 主题风格
from pyecharts.charts import Kline, Line, Bar, Gauge, Pie, Scatter, TreeMap # K线图、线形图、柱状图、仪表盘、饼图、散点图、矩形树图
from pyecharts.charts import Grid, Tab # 组合组件、分页组件
from pyecharts.components import Table # 表格组件
import time # 时间模块
import pandas as pd # Pandas模块
import numpy as np # Numpy模块
from sqlalchemy import create_engine # 数据引擎模块
conn = create_engine('mysql+pymysql://root:test@127.0.0.1:3306/quant_db?charset=utf8') # 创建引擎
##########################################################################################################################################################
# 统计数据
# 上证指数
sql01 = '''
SELECT
trade_date, -- 交易日期
`open`, -- 开盘价
high, -- 最高价
low, -- 最低价
`close`, -- 收盘价
pct_chg -- 涨跌幅
FROM
stock_daily
WHERE
ts_code = '000001.SH'
'''
# 深证指数
sql02 = '''
SELECT
trade_date, -- 交易日期
`open`, -- 开盘价
high, -- 最高价
low, -- 最低价
`close`, -- 收盘价
pct_chg -- 涨跌幅
FROM
stock_daily
WHERE
ts_code = '399001.SZ'
'''
# 创业板指
sql03 = '''
SELECT
trade_date, -- 交易日期
`open`, -- 开盘价
high, -- 最高价
low, -- 最低价
`close`, -- 收盘价
pct_chg -- 涨跌幅
FROM
stock_daily
WHERE
ts_code = '399006.SZ'
'''
# shibor
sql04 = '''
SELECT
date, -- 交易日期
`on` -- shibor利率
FROM
shibor
'''
# 全市场成交额
sql05 = '''
SELECT
trade_date, -- 交易日期
SUM(amount)/100000000 AS 'amount' -- 成交总额，亿
FROM
stock_daily
WHERE
ts_code NOT IN ('000001.SH','399001.SZ','399006.SZ')
GROUP BY
trade_date
'''
# 全市场竞价总额
sql06 = '''
SELECT
date, -- 交易日期
sum(money)/100000000 AS money -- 竞价总额，亿
FROM
call_auction
GROUP BY
date
'''
# 北向资金
sql07 = '''
SELECT
trade_date, -- 交易日期
hgt/100, -- 沪股通，亿
sgt/100, -- 深股通，亿
north_money/100 -- 北向资金，亿
FROM
hsgt_moneyflow
'''
# 大单流向
sql08 = '''
SELECT
trade_date, -- 交易日期
SUM(buy_md_amount-sell_md_amount)/1000 AS 'md_amount', -- 中单净额/亿
SUM(buy_lg_amount-sell_lg_amount)/1000 AS 'lg_amount', -- 大单净额/亿
SUM(buy_elg_amount-sell_elg_amount)/1000 AS 'elg_amount', -- 超大单净额/亿
SUM(net_mf_amount)/1000 AS 'net_mf_amount' -- 资金净额/亿
FROM
stock_moneyflow
GROUP BY
trade_date
'''
# 游资资金流向
sql09 = '''
SELECT
date, -- 交易日期
SUM(CASE WHEN (youziicon=1 AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'yxb', -- 一线游资买入，亿
SUM(CASE WHEN (youziicon=2 AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'djb', -- 顶级游资买入，亿
SUM(CASE WHEN (youziicon=1 AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'zmb', -- 知名游资买入，亿
SUM(CASE WHEN (dname='机构专用' AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'jgb', -- 机构买入，亿
SUM(CASE WHEN (dname LIKE '%股通专用' AND direction='buy') THEN buy ELSE 0 END)/100000000 AS 'wzb', -- 外资买入，亿
SUM(CASE WHEN (youziicon=1 AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'yxs', -- 一线游资卖出，亿
SUM(CASE WHEN (youziicon=2 AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'djs', -- 顶级游资卖出，亿
SUM(CASE WHEN (youziicon=1 AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'zms', -- 知名游资卖出，亿
SUM(CASE WHEN (dname='机构专用' AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'jgs', -- 机构卖出，亿
SUM(CASE WHEN (dname LIKE '%股通专用' AND direction='sell') THEN sell ELSE 0 END)/100000000 AS 'wzs' -- 外资卖出，亿
FROM
lhb_list
GROUP BY
date
'''
# 市场情绪
sql10 = '''
SELECT
t1.trade_date, -- 交易日期
t1.zt_fd_amount/100000000, -- 涨停封单/亿
t2.dt_fd_amount/100000000, -- 跌停封单/亿
t3.1b, -- 1板
t3.2b, -- 2板
t3.3b, -- 3板
t3.4b, -- 4板
t3.gdb, -- 高度板
t4.dr, -- 大肉股
t4.dm, -- 大面股
t5.szd5, -- 涨幅大于5%
t5.xdd5, -- 跌幅大于5%
t6.zrzt, -- 自然涨停
t6.ztzb, -- 自然跌停
t6.szjs, -- 上涨家数
t6.xdjs, -- 下跌家数
t6.zt, -- 涨停家数
t6.dt, -- 跌停家数
t6.zbl, -- 炸板率
t6.cgl, -- 昨日打板成功率
t6.yll -- 昨日打板盈利率
FROM
-- 涨停封单金额
(SELECT trade_date,sum(fd_amount) AS 'zt_fd_amount' FROM limit_stock_list WHERE `limit` = 'U' GROUP BY trade_date) t1
LEFT JOIN
-- 跌停封单金额
(SELECT trade_date,sum(fd_amount) AS 'dt_fd_amount' FROM limit_stock_list WHERE `limit` = 'D' GROUP BY trade_date) t2 ON t1.trade_date=t2.trade_date
LEFT JOIN
-- 一字板,2板,3板以上,高度板
(SELECT date,COUNT(DISTINCT CASE WHEN lbc = 1 THEN `code` ELSE 0 END ) AS '1b',COUNT(DISTINCT CASE WHEN lbc = 2 THEN `code` ELSE 0 END ) AS '2b',COUNT(DISTINCT CASE WHEN lbc = 3 THEN `code` ELSE 0 END ) AS '3b',COUNT(DISTINCT CASE WHEN lbc = 4 THEN `code` ELSE 0 END ) AS '4b',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
LEFT JOIN
-- 大肉股,大面股
(SELECT trade_date,COUNT(DISTINCT CASE WHEN ((`close`-`open`)/`open`)>=0.12 THEN ts_code ELSE 0 END) AS 'dr',COUNT(DISTINCT CASE WHEN ((`close`-`open`)/`open`)<-0.12 THEN ts_code ELSE 0 END) AS 'dm' FROM stock_daily GROUP BY trade_date) t4 ON t1.trade_date=t4.trade_date
LEFT JOIN
-- 涨幅5%以上,跌幅5%以上
(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' FROM stock_daily GROUP BY trade_date) t5 ON t1.trade_date=t5.trade_date
LEFT JOIN
-- 自然涨停,涨停炸板,上涨家数,下跌家数,涨停家数,跌停家数,炸板率,昨日打板成功率,昨日打板盈利率
(SELECT `day`,zrzt, ztzb, szjs, xdjs, zt, dt, zbl, cgl, yll FROM scqx) t6 ON t1.trade_date=t6.`day`
ORDER BY t1.trade_date DESC
'''
# 异动统计
sql11 = '''
SELECT
t1.`code`, -- 股票代码
t1.`name`, -- 股票名称
COUNT(CASE WHEN (chg_type IN ('8201', '8202', '8193', '4', '32', '64', '8207', '8209', '8211', '8213', '8215')) THEN t1.`code` ELSE 0 END) AS 'now_df', -- 当天多方异动统计
COUNT(CASE WHEN (chg_type IN ('8204', '8203', '8194', '8', '16', '128', '8208', '8210', '8212', '8214', '8216')) THEN t1.`code` ELSE 0 END) AS 'now_kf', -- 当天空方异动统计
t2.thrity_df, -- 近30日多方异动统计
t2.thrity_kf -- 近30日空方异动统计
FROM
stock_change t1
LEFT JOIN (
SELECT
`code`,
-- 近30日多方异动统计
COUNT(CASE WHEN (chg_type IN ('8201', '8202', '8193', '4', '32', '64', '8207', '8209', '8211', '8213', '8215')) THEN `code` ELSE 0 END)AS 'thrity_df',
-- 近30日空方异动统计
COUNT(CASE WHEN (chg_type IN ('8204', '8203', '8194', '8', '16', '128', '8208', '8210', '8212', '8214', '8216')) THEN `code` ELSE 0 END)AS 'thrity_kf'
FROM
stock_change
WHERE
date(chg_time) BETWEEN date_SUB(date(NOW()), INTERVAL 30 DAY ) AND date(NOW())
GROUP BY
`code`
) t2 ON t1.`code` = t2.`code`
WHERE
date(chg_time) = date(now())
GROUP BY
`code`
'''
##########################################################################################################################################################
# 公共数据
# 所属板块
sql13 = '''
SELECT
ts_code, -- 股票代码
industry -- 所属行业
FROM
stock_basic
'''
# 所属概念
sql14 = '''
SELECT
ts_code, -- 股票代码
GROUP_CONCAT(concept_name) AS 'concept_name' -- 所属概念
FROM
concept_detail
GROUP BY
ts_code
'''
##########################################################################################################################################################
# 明细数据
# 主力净额
sql15 = '''
SELECT
t1.trade_date, -- 交易日期
t1.ts_code, -- 股票代码
t3.stk_name, -- 股票名称
t1.`close`, -- 股票价格
t1.pct_chg, -- 涨跌幅
t1.amount/100000 AS 'amount', -- 成交额,亿
t2.net_mf_amount/10000 AS 'net_mf_amount' -- 主力金额，亿
FROM
stock_daily t1
LEFT JOIN stock_moneyflow t2 ON t1.ts_code = t2.ts_code AND t1.trade_date = t2.trade_date
WHERE
t1.ts_code NOT IN ('000001.SH', '399001.SZ', '399006.SZ') AND t1.trade_date = date(NOW())
ORDER BY
net_mf_amount DESC
'''
# 持股低于5%所有流通股市值
sql16 = '''
SELECT
t1.trade_date, -- 交易日期
t1.ts_code, -- 股票代码
t2.stk_name, -- 股票名称
(t1.float_share * 10000- t2.cir_num)/100000000 AS 'flow_share' -- 持股低于5%自有流通股市值，亿
FROM
stock_daily_basic t1
LEFT JOIN flow_holder t2 ON t1.ts_code = t2.stk_code
WHERE
t1.trade_date = date(now())
'''
# 北向资金排行榜明细
sql17 = '''
SELECT
t1.date, -- 交易日期
t1.`code`, -- 股票代码
t1.sharesz / 100000000 AS sharesz, -- 今日持股市值，亿
t1.sharesz_chg_one / 100000000 AS sharesz_chg_one, -- 今日增持市值，亿
t2.thrity_sharesz_chg_one / 100000000 AS thrity_sharesz_chg_one -- 近30日增持市值，亿
FROM
hsgt_detail t1
LEFT JOIN (
SELECT
`code`,
SUM( sharesz_chg_one ) AS thrity_sharesz_chg_one
FROM
hsgt_detail
WHERE
date BETWEEN date_SUB(date(NOW()), INTERVAL 30 DAY ) AND date(NOW())
GROUP BY
`code`
) t2 ON t1.`code` = t2.`code`
WHERE
date = ( SELECT DISTINCT date FROM hsgt_detail ORDER BY date DESC LIMIT 1, 1 )
'''
# 异动明细
sql18 = '''
SELECT
(case when time(chg_time) between '09:00:00' and '10:00:00' then '09:00-10:00'
when time(chg_time) between '10:00:00' and '10:30:00' then '10:00-10:30'
when time(chg_time) between '10:30:00' and '11:00:00' then '10:30-11:00'
when time(chg_time) between '11:00:00' and '11:30:00' then '11:00-11:30'
when time(chg_time) between '13:00:00' and '13:30:00' then '13:00-13:30'
when time(chg_time) between '13:30:00' and '14:00:00' then '13:30-14:00'
when time(chg_time) between '14:00:00' and '14:30:00' then '14:00-14:30'
when time(chg_time) between '14:30:00' and '15:00:00' then '14:30-15:00'
else 0 end) as 'chg_time', -- 时间区间
t1.`code`, -- 股票代码
t1.`name`, -- 股票名称
COUNT(CASE WHEN (t1.chg_type IN ('8201', '8202', '8193', '4', '32', '64', '8207', '8209', '8211', '8213', '8215')) THEN t1.`code` ELSE 0 END) AS 'now_df', -- 当天多方异动统计
COUNT(CASE WHEN (t1.chg_type IN ('8204', '8203', '8194', '8', '16', '128', '8208', '8210', '8212', '8214', '8216')) THEN t1.`code` ELSE 0 END) AS 'now_kf' -- 当天空方异动统计
FROM
stock_change t1
LEFT JOIN change_type t2 ON t1.chg_type = t2.chg_type
WHERE
date(chg_time)='20200706'
GROUP BY
chg_time,`code`
'''
# 涨停板明细
sql19 = '''
SELECT
t1.ts_code, -- 股票代码
t1.`close`, -- 股票价格
t1.pct_chg, -- 涨跌幅
t1.fd_amount/10000, -- 封单金额，万
t1.last_time, -- 最后一次涨停时间
t1.open_times, -- 封单次数
t2.lbc -- 连板数
FROM
limit_stock_list t1
LEFT JOIN limit_stock_lbc t2 ON t1.trade_date = t2.date AND t1.ts_code = t2.CODE
WHERE
t1.trade_date = NOW() AND t1.`limit` = 'U'
'''
# 龙虎榜明细
sql19 = '''
SELECT
t1.`code`, -- 股票代码
t2.`name`, -- 股票名称
(CASE
WHEN youziicon = 1 THEN '一线游资'
WHEN youziicon = 2 THEN '顶级游资'
WHEN youziicon = 1 THEN '知名游资'
WHEN youziicon = 0 AND ( dname = '机构专用' ) THEN '机构'
WHEN youziicon = 0 AND ( dname LIKE '%股通专用' ) THEN '外资'
ELSE '其他' END) AS 'fund_type', -- 资金类型
groupicon, -- 资金标签
buy/10000 AS 'buy', -- 买入金额，万
sell/10000 AS 'sell' -- 卖出金额，万
FROM
lhb_list t1
LEFT JOIN hot_concept t2 ON t1.date=t2.date AND t1.`code`=t2.`code`
WHERE
t1.date = date(now()) AND buy>=50000000
GROUP BY
t1.`code`
'''
# 竞价明细
sql20 = '''
SELECT
`code`, -- 股票代码
money/10000 AS money -- 竞价总额，万
FROM
`call_auction`
WHERE
date = date(now())
'''
##########################################################################################################################################################
# 图形绘制
#1、近90日上证指数走势图
data01 = pd.read_sql(sql01,conn)
date = list(data01['trade_date'])
list = []
for index,row in data01.iterrows():
list.append([row['open'],row['close'],row['low'],row['high'])
kline01 = (
Kline()
.add_xaxis(date)
.add_yaxis("上证指数", list)
.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="近90日上证指数走势图"),
)
.render("kline_split_area.html")
)
# 2、近90日shibor利率趋势图
data02 = pd.read_sql(sql04,conn)
line02 = (
Line(init_opts=opts.InitOpts(theme=ThemeType.DARK))
.add_xaxis(list(data03['date']))
.add_yaxis("shibor利率", list(data01['on']))
.set_global_opts(
title_opts=opts.TitleOpts(title="shibor利率")
)
.render("gauge.html")
)
# 3、近90日全天量能、竞价量能
data03 = pd.merge(pd.read_sql(sql05,conn),pd.read_sql(sql06,conn),how='left',left_on='trade_date',right_on='date')
line03 = (
Line()
.add_xaxis(list(data03['trade_date']))
.add_yaxis("全天量能", list(data03['amount']))
.add_yaxis("竞价量能", list(data03['money']))
.set_global_opts(title_opts=opts.TitleOpts(title="近90日量能趋势"))
.render("line_base.html")
)
# 4、近90日北向资金趋势
data04 = pd.read_sql(sql07,conn)
line04 = (
Line()
.add_xaxis(list(data04['trade_date']))
.add_yaxis("沪股通", list(data04['hgt']))
.add_yaxis("深股通", list(data04['sgt']))
.add_yaxis("净流入", list(data04['north_money']))
.set_global_opts(title_opts=opts.TitleOpts(title="近90日北向资金趋势"))
.render("line_base.html")
)
# 5、近90日大单资金趋势
data05 = pd.read_sql(sql08,conn)
line04 = (
Line()
.add_xaxis(list(data04['trade_date']))
.add_yaxis("超大单净额", list(data04['elg_amount']))
.add_yaxis("大单净额", list(data04['lg_amount']))
.add_yaxis("中单净额", list(data04['md_amount']))
.add_yaxis("净流入", list(data04['net_mf_amount']))
.set_global_opts(title_opts=opts.TitleOpts(title="近90日大单资金趋势"))
.render("line_base.html")
)
# 大单流向
sql08 = '''
SELECT
trade_date, -- 交易日期
SUM(buy_md_amount-sell_md_amount)/1000 AS 'md_amount', -- 中单净额/亿
SUM(buy_lg_amount-sell_lg_amount)/1000 AS 'lg_amount', -- 大单净额/亿
SUM(buy_elg_amount-sell_elg_amount)/1000 AS 'elg_amount', -- 超大单净额/亿
SUM(net_mf_amount)/1000 AS 'net_mf_amount' -- 资金净额/亿
FROM
stock_moneyflow
GROUP BY
trade_date
'''
grid = (
Grid()
.add(bar, grid_opts=opts.GridOpts(pos_bottom="60%", pos_right='50%'))
.add(line, grid_opts=opts.GridOpts(pos_top="60%"))
# .render("grid_vertical.html")
)
grid1 = (
Grid()
.add(bar, grid_opts=opts.GridOpts(pos_bottom="60%", pos_right='50%'))
.add(line, grid_opts=opts.GridOpts(pos_top="60%"))
# .render("grid_vertical.html")
)
tab = Tab()
tab.add(grid, "bar-example")
# tab.add(line_markpoint(), "line-example")
# tab.add(pie_rosetype(), "pie-example")
# tab.add(grid_mutil_yaxis(), "grid-example")
tab.render("tab_base.html")
from pyecharts import options as opts
from pyecharts.charts import Bar
from pyecharts.faker import Faker
x = Faker.dogs + Faker.animal
xlen = len(x)
y = []
for idx, item in enumerate(x):
if idx <= xlen / 2:
y.append(
opts.BarItem(
name=item,
value=(idx + 1) * 10,
itemstyle_opts=opts.ItemStyleOpts(color="#749f83"),
)
)
else:
y.append(
opts.BarItem(
name=item,
value=(xlen + 1 - idx) * 10,
itemstyle_opts=opts.ItemStyleOpts(color="#d48265"),
)
)
c = (
Bar()
.add_xaxis(x)
.add_yaxis("series0", y, category_gap=0, color=Faker.rand_color())
.set_global_opts(title_opts=opts.TitleOpts(title="Bar-直方图（颜色区分）"))
.render("bar_histogram_color.html")
)
浙公网安备 33010602011771号