【python量化交易学习】从tushare获取股票交易数据，存入后再从mysql或excel读取数据，筛选股票，用pyecharts画出K线图。

本文链接：https://blog.csdn.net/weixin_42821041/article/details/123424149

选定日期，筛选涨幅达到10%的股票，并画出K线图。观察涨停后股票走势。

由于创业板涨停板为20%，科创板20%，北交所30%。因此筛选出的涨停股票不完全准确。考虑到目前市场打板主要集中在10%的主板股票中，暂时先观察10%涨幅股票。

参考：
【python量化交易学习】pandas获取tushare股票交易数据，写入mysql数据库或导出到excel。

从mysql中读取数据

strategy_select_share.py 筛选出涨停股票代码。

# 建立mysql数据库的连接
from sqlalchemy import create_engine
import pandas as pd


def select_up_limit(tradedate):
    conn = create_engine('mysql+pymysql://root:123456@localhost:3306/qtrade', encoding='utf8')
    mysql_1 = "SELECT  * FROM dailytrade WHERE trade_date = '" + tradedate + "' "
    df1 = pd.read_sql(mysql_1, conn)
    list = []

    for i in range(len(df1)):
        high = df1["high"][i]
        low = df1["low"][i]
        close = df1["close"][i]
        pre_close = df1["pre_close"][i]
        # 涨停价10%
        up_limit = pre_close * 1.1 - 0.01
        up_limit2 = pre_close * 1.1 + 0.01

        #开板或者拉版
        if high != low and up_limit <= close < up_limit2:
            list.append(df1["ts_code"][i])

    return list

from typing import List, Union
from pyecharts.charts import Line, Bar, Grid, Page
import pandas as pd
from sqlalchemy import create_engine
from pyecharts import options as opts
from pyecharts.charts import Kline
from strategy_select_share import select_up_limit

#从mysql获取数据
def get_data(input_share_code):
    # 建立mysql数据库的连接
    conn = create_engine('mysql+pymysql://root:123456@localhost:3306/qtrade', encoding='utf8')

    # "SELECT  * FROM dailytrade WHERE ts_code = '000001.SZ' "
    mysql = "SELECT  * FROM dailytrade WHERE ts_code = '" + input_share_code + "' order by trade_date"
    # 读取交易数据
    df = pd.read_sql(mysql, conn)

    # df[['a','b']]#取a、b列
    trade_values = df[['trade_date', 'open', 'close', 'low', 'high', 'amount', 'pre_close']]
    # 将格式转化成pyechart使用的格式
    trade_values_tolist = [trade_values.iloc[i].tolist() for i in range(len(trade_values))]

    # 解析数据
    return split_data(data=trade_values_tolist, share_code=input_share_code)

#将mysql储存的tushare数据中，提前pyecharts需要的字段
def split_data(data, share_code):
    # 日期
    category_data = []
    # 日期，交易4个价格，交易量
    values = []
    # 序号，收盘价，涨跌序号，涨为1，跌为-1
    volumes = []

    for i, tick in enumerate(data):
        category_data.append(tick[0])
        values.append(tick)
        volumes.append([i, tick[5], 1 if tick[6] > tick[2] else -1])

    return {"categoryData": category_data, "values": values, "volumes": volumes, "share_code": share_code}


# 计算移动均线。移动平均线(MA)计算方法就是求连续若干天的收盘价的算术平均
def calculate_ma(day_count: int, data):
    result: List[Union[float, str]] = []
    for i in range(len(data["values"])):
        if i < day_count:
            result.append("-")
            continue
        sum_total = 0.0
        for j in range(day_count):
            sum_total += float(data["values"][i - j][2])
        result.append(abs(float("%.3f" % (sum_total / day_count))))
    return result


# 画图
def draw_charts(sharecode) -> Grid:
    chart_data = get_data(sharecode)
    share_code = chart_data["share_code"]
    # 获得数据
    kline_data = [data[1:-1] for data in chart_data["values"]]
    # 画K线
    kline = (
        Kline()
            # 加载x轴数据
            .add_xaxis(xaxis_data=chart_data["categoryData"])
            # 加载y轴数据
            .add_yaxis(
            # K线名称
            series_name=share_code,
            # 加载y轴数据
            y_axis=kline_data,
            # 设置y轴数据的颜色
            itemstyle_opts=opts.ItemStyleOpts(color="#ec0000", color0="#00da3c"),
        )
            # 设置全局变量
            .set_global_opts(
            # 图例配置项，隐藏图例
            legend_opts=opts.LegendOpts(
                is_show=False, pos_bottom=10, pos_left="center"
            ),
            # DataZoomOpts：区域缩放配置项。type_: str = "slider",组件类型，可选 "slider", "inside"。
            # is_show: bool = True, 是否显示 组件。如果设置为 false，不会显示，但是数据过滤的功能还存在。
            datazoom_opts=[
                opts.DataZoomOpts(
                    is_show=False,
                    type_="inside",
                    xaxis_index=[0, 1],
                    range_start=98,
                    range_end=100,
                ),
                opts.DataZoomOpts(
                    is_show=True,
                    xaxis_index=[0, 1],
                    type_="slider",
                    pos_top="85%",
                    range_start=98,
                    range_end=100,
                ),
            ],
            yaxis_opts=opts.AxisOpts(
                is_scale=True,
                splitarea_opts=opts.SplitAreaOpts(
                    is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
                ),
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="cross",
                background_color="rgba(245, 245, 245, 0.8)",
                border_width=1,
                border_color="#ccc",
                textstyle_opts=opts.TextStyleOpts(color="#000"),
            ),
            visualmap_opts=opts.VisualMapOpts(
                is_show=False,
                dimension=2,
                series_index=5,
                is_piecewise=True,
                pieces=[
                    {"value": 1, "color": "#00da3c"},
                    {"value": -1, "color": "#ec0000"},
                ],
            ),
            axispointer_opts=opts.AxisPointerOpts(
                is_show=True,
                link=[{"xAxisIndex": "all"}],
                label=opts.LabelOpts(background_color="#777"),
            ),
            brush_opts=opts.BrushOpts(
                x_axis_index="all",
                brush_link="all",
                out_of_brush={"colorAlpha": 0.1},
                brush_type="lineX",
            ),
            title_opts=opts.TitleOpts(
                title = chart_data["share_code"]
            )
        )
    )

    # 画 MA 移动均线图
    line = (
        Line()
            .add_xaxis(xaxis_data=chart_data["categoryData"])
            .add_yaxis(
            series_name="MA5",
            y_axis=calculate_ma(day_count=5, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .add_yaxis(
            series_name="MA10",
            y_axis=calculate_ma(day_count=10, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .add_yaxis(
            series_name="MA20",
            y_axis=calculate_ma(day_count=20, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .add_yaxis(
            series_name="MA30",
            y_axis=calculate_ma(day_count=30, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
            .set_global_opts(xaxis_opts=opts.AxisOpts(type_="category"))
    )

    # 画交易量柱状图
    bar = (
        Bar()
            .add_xaxis(xaxis_data=chart_data["categoryData"])
            .add_yaxis(
            series_name="Volume",
            y_axis=chart_data["volumes"],
            xaxis_index=1,
            yaxis_index=1,
            label_opts=opts.LabelOpts(is_show=False),
        )
            .set_global_opts(
            xaxis_opts=opts.AxisOpts(
                type_="category",
                is_scale=True,
                grid_index=1,
                boundary_gap=False,
                axisline_opts=opts.AxisLineOpts(is_on_zero=False),
                axistick_opts=opts.AxisTickOpts(is_show=False),
                splitline_opts=opts.SplitLineOpts(is_show=False),
                axislabel_opts=opts.LabelOpts(is_show=False),
                split_number=20,
                min_="dataMin",
                max_="dataMax",
            ),
            yaxis_opts=opts.AxisOpts(
                grid_index=1,
                is_scale=True,
                split_number=2,
                axislabel_opts=opts.LabelOpts(is_show=False),
                axisline_opts=opts.AxisLineOpts(is_show=False),
                axistick_opts=opts.AxisTickOpts(is_show=False),
                splitline_opts=opts.SplitLineOpts(is_show=False),
            ),
            legend_opts=opts.LegendOpts(is_show=False),
        )
    )

    # Kline And Line
    overlap_kline_line = kline.overlap(line)

    # Grid Overlap + Bar
    grid_chart = Grid(
        init_opts=opts.InitOpts(
            width="1000px",
            height="800px",
            animation_opts=opts.AnimationOpts(animation=False),
        )
    )
    grid_chart.add(
        overlap_kline_line,
        grid_opts=opts.GridOpts(pos_left="10%", pos_right="8%", height="50%"),
    )
    grid_chart.add(
        bar,
        grid_opts=opts.GridOpts(
            pos_left="10%", pos_right="8%", pos_top="63%", height="16%"
        ),
    )

    return grid_chart


def page_draggable_layout():
    #选定某一天
    share_list = select_up_limit('20220228')
    page = Page(layout=Page.DraggablePageLayout)

    for i in range(0, len(share_list)):
        page.add(
            draw_charts(share_list[i]),
        )

    page.render("page_draggable_layout.html")


if __name__ == "__main__":
    page_draggable_layout()

结果：到出pdf再截图
在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
等等

从excel中读取数据

main.py

from typing import List, Union
from pyecharts.charts import Kline, Line, Bar, Grid, Page
import pandas as pd
from pyecharts import options as opts
from share_selected_strategies import chuban


# 从mysql获取数据
def get_data(input_share_code, df):
    # df[['a','b']]#取a、b列
    trade_values = df[['trade_date', 'open', 'close', 'low', 'high', 'amount', 'pre_close']]
    # 将格式转化成pyechart使用的格式
    trade_values_tolist = [trade_values.iloc[i].tolist() for i in range(len(trade_values))]

    # 解析数据
    return split_data(data=trade_values_tolist, share_code=input_share_code)


# 将mysql储存的tushare数据中，提前pyecharts需要的字段
def split_data(data, share_code):
    # 日期
    category_data = []
    # 日期，交易4个价格，交易量
    values = []
    # 序号，收盘价，涨跌序号，涨为1，跌为-1
    volumes = []

    for i, tick in enumerate(data):
        category_data.append(tick[0])
        values.append(tick)
        volumes.append([i, tick[5], 1 if tick[6] > tick[2] else -1])

    return {"categoryData": category_data, "values": values, "volumes": volumes, "share_code": share_code}


# 计算移动均线。移动平均线(MA)计算方法就是求连续若干天的收盘价的算术平均
def calculate_ma(day_count: int, data):
    result: List[Union[float, str]] = []
    for i in range(len(data["values"])):
        if i < day_count:
            result.append("-")
            continue
        sum_total = 0.0
        for j in range(day_count):
            sum_total += float(data["values"][i - j][2])
        result.append(abs(float("%.3f" % (sum_total / day_count))))
    return result


# 画图
def draw_charts(sharecode, df_daily) -> Grid:
    chart_data = get_data(sharecode, df_daily)
    # 获得数据
    kline_data = [data[1:-1] for data in chart_data["values"]]
    # 画K线
    kline = (
        Kline()
        .add_xaxis(xaxis_data=chart_data["categoryData"])
        .add_yaxis(
            series_name=sharecode,
            y_axis=kline_data,
            itemstyle_opts=opts.ItemStyleOpts(color="#ec0000", color0="#00da3c"),
        )
        .set_global_opts(
            legend_opts=opts.LegendOpts(
                is_show=False, pos_bottom=10, pos_left="center"
            ),
            datazoom_opts=[
                opts.DataZoomOpts(
                    is_show=False,
                    type_="inside",
                    xaxis_index=[0, 1],
                    range_start=0,
                    range_end=100,
                ),
                opts.DataZoomOpts(
                    is_show=True,
                    xaxis_index=[0, 1],
                    type_="slider",
                    pos_top="85%",
                    range_start=98,
                    range_end=100,
                ),
            ],
            yaxis_opts=opts.AxisOpts(
                is_scale=True,
                splitarea_opts=opts.SplitAreaOpts(
                    is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
                ),
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="cross",
                background_color="rgba(245, 245, 245, 0.8)",
                border_width=1,
                border_color="#ccc",
                textstyle_opts=opts.TextStyleOpts(color="#000"),
            ),
            visualmap_opts=opts.VisualMapOpts(
                is_show=False,
                dimension=2,
                series_index=5,
                is_piecewise=True,
                pieces=[
                    {"value": 1, "color": "#00da3c"},
                    {"value": -1, "color": "#ec0000"},
                ],
            ),
            axispointer_opts=opts.AxisPointerOpts(
                is_show=True,
                link=[{"xAxisIndex": "all"}],
                label=opts.LabelOpts(background_color="#777"),
            ),
            brush_opts=opts.BrushOpts(
                x_axis_index="all",
                brush_link="all",
                out_of_brush={"colorAlpha": 0.1},
                brush_type="lineX",
            ),
            title_opts=opts.TitleOpts(
                title=sharecode
            )
        )
    )

    line = (
        Line()
        .add_xaxis(xaxis_data=chart_data["categoryData"])
        .add_yaxis(
            series_name="MA5",
            y_axis=calculate_ma(day_count=5, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
        .add_yaxis(
            series_name="MA10",
            y_axis=calculate_ma(day_count=10, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
        .add_yaxis(
            series_name="MA20",
            y_axis=calculate_ma(day_count=20, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
        .add_yaxis(
            series_name="MA30",
            y_axis=calculate_ma(day_count=30, data=chart_data),
            is_smooth=True,
            is_hover_animation=False,
            linestyle_opts=opts.LineStyleOpts(width=3, opacity=0.5),
            label_opts=opts.LabelOpts(is_show=False),
        )
        .set_global_opts(xaxis_opts=opts.AxisOpts(type_="category"))
    )

    bar = (
        Bar()
        .add_xaxis(xaxis_data=chart_data["categoryData"])
        .add_yaxis(
            series_name="Volume",
            y_axis=chart_data["volumes"],
            xaxis_index=1,
            yaxis_index=1,
            label_opts=opts.LabelOpts(is_show=False),
        )
        .set_global_opts(
            xaxis_opts=opts.AxisOpts(
                type_="category",
                is_scale=True,
                grid_index=1,
                boundary_gap=False,
                axisline_opts=opts.AxisLineOpts(is_on_zero=False),
                axistick_opts=opts.AxisTickOpts(is_show=False),
                splitline_opts=opts.SplitLineOpts(is_show=False),
                axislabel_opts=opts.LabelOpts(is_show=False),
                split_number=20,
                min_="dataMin",
                max_="dataMax",
            ),
            yaxis_opts=opts.AxisOpts(
                grid_index=1,
                is_scale=True,
                split_number=2,
                axislabel_opts=opts.LabelOpts(is_show=False),
                axisline_opts=opts.AxisLineOpts(is_show=False),
                axistick_opts=opts.AxisTickOpts(is_show=False),
                splitline_opts=opts.SplitLineOpts(is_show=False),
            ),
            legend_opts=opts.LegendOpts(is_show=False),
        )
    )

    # Kline And Line
    overlap_kline_line = kline.overlap(line)

    # Grid Overlap + Bar
    grid_chart = Grid(
        init_opts=opts.InitOpts(
            animation_opts=opts.AnimationOpts(animation=False),
        )
    )
    grid_chart.add(
        overlap_kline_line,
        grid_opts=opts.GridOpts(pos_left="10%", pos_right="8%", height="50%"),
    )
    grid_chart.add(
        bar,
        grid_opts=opts.GridOpts(
            pos_left="10%", pos_right="8%", pos_top="63%", height="16%"
        ),
    )

    return grid_chart


def page_draggable_layout():
    # 选定某一天

    trade_date = 20220315
    df_read = pd.read_excel('/Users/PycharmProjects/0317.xlsx', sheet_name="1", engine="openpyxl",
                            index_col=None)
    df = df_read.loc[df_read["trade_date"] == trade_date]
    share_list = chuban(df)
    page = Page(layout=Page.DraggablePageLayout)

    for i in range(0, len(share_list)):
        df_daily = df_read.loc[df_read["ts_code"] == share_list[i]]
        df_daily = df_daily.iloc[::-1]
        page.add(
            draw_charts(share_list[i], df_daily),
        )

    page.render(str(trade_date) + "日触板股票.html")


if __name__ == "__main__":
    page_draggable_layout()

share_selected_strategies.py

import pandas as pd


def chuban(df):
    df1 = df
    list = []

    for i in range(0, len(df1)):
        up_10 = '%.2f' % (df1["pre_close"].iloc[i] * 1.1)
        high = '%.2f' % (df1["high"].iloc[i])
        # 选出触板
        if high == up_10:
            list.append(df1["ts_code"].iloc[i])
    return list


def tradeamount_5yi(df):
    # 交易额过5亿

    df1 = df
    list = []

    # 交易额大于5亿的股票
    for i in range(0, len(df1)):
        amount = df1["amount"].iloc[i] / 100000

        # 选出触板
        if amount > 5:
            list.append(df1["ts_code"].iloc[i])

    return list