数据分析技术第8章

第八章股票数据分析综合案例北京西普阳光教育科技股份有限公司目录

Contents数据展示本章小结0601股票数据背景分析02数据清洗03数据处理04数据分析05股票数据背景分析01股票数据背景描述案例围绕“平安银行”股票历史数据展开，包含证券交易所A股股票日线数据，时间区间为2010年1月1日至2015年12月31日，剔除假期休市等数据。利用数据分析技术对数据进行清洗、分析、过滤，分析股票数据，通过对历史数据的处理，寻找出前后数据之间的关系，建立关联模型，然后通过历史数据和所建立的关联模型来预测时间序列的未来值。序号字段名字段解释序号字段名字段解释1stcok_id股票代码14turnover_rate换手率(%)2stock_name股票名称简称15a_totleA股流通市值(元)3date日期16b_totleB股流通市值(元)4his_price前收盘价(元)17totle总市值(元)5open开盘价(元)18a_equityA股流通股本(股)6high最高价(元)19b_equityB股流通股本(股)7low最低价(元)20totle_equity总股本(股)8close收盘价(元)21pe_ratio市盈率9volume成交量(股)22pb_ratio市净率10turnover成交金额(元)23market_rate市销率11change_price涨跌(元)24cash_rate市现率12change_rate涨跌幅(%)25collection_date采集时间13average_price均价(元)

数据清洗02数据清洗【任务描述】针对平安银行2010年—2015年所有交易日的股票数据，进行数据清洗，去所有的空值数据、无价值数据。【操作步骤】(1)仔细分析“平安银行”Excel表，发现b_totle是空值，b_equity是零值，collection_date是采集时间，都是相同值，对本案例来说也是无价值数据，因此，删除这三列数据。(2)找出Excel表所有的空值，分析发现，都是股票停盘日无交易量的数据，可以通过筛选找出所有空值，并删除之。①选择“数据”→“筛选”，进入自动筛选的状态。②单击turnover字段的下拉按钮，弹出筛选条件设置的对话框，选择“N/A”空值，如图8-1所示，单击“确定”按钮，结果如图8-2所示。③由结果可知，所有停盘日无交易量的数据记录全显示出来了，选择所有筛选出来的列，右击，弹出快捷菜单，选择“删除行”，删除带所有的空值的记录，如图8-3所示。④单击“筛选”按钮，取消“筛选”，回归状态，还有1387条记录，如图8-4所示。数据清洗图8-1

“筛选”数据表图8-2

“筛选”空值记录数据清洗图8-3

删除空值行图8-4

去空值后的数据表数据处理03数据处理【任务描述】对股票数据进行数据处理，根据要求计算出月度、年度数据报表，要求如下：(1)根据股票数据，计算平安银行2010年-2015年每个季度的成交量平均值以及平均市盈率。(2)计算平安银行股票每年股价最低值和最高值，以及每年交易天数。(3)计算2015年平安银行涨跌天数，即上涨天数与下跌天数。【操作步骤】(1)通过数据透视表可以方便地计算出每个季度的平均成交量以及市盈率。①单击“插入”→“数据透视表”，弹出“创建数据透视表”对话框，如图8-5所示。选择需要分析的数据区域，以及数据透视表放置的位置，单击“确定”按钮。②进入数据透视表的分析窗口，需分析的是平均成交量与市盈率，则拖动volume、pe_ratio两个字段放入“值”区域；要求按季度进行统计，则拖动date字段至“行”区域，date字段会在“行”区域自动添加“年”“季度”的分类，如图8-6所示。数据处理图8-6数据透视表字段设置图8-7值字段设置快捷菜单数据处理③计算平均成交量与市盈率，则需修改“值”字段的属性设置，依次选择“值”字段中的pe_ratio及volume字段，分别右击，弹出快捷菜单，选择“值字段设置”，如图8-7所示，弹出“值字段”设置的对话框，选择“值字段汇总方式”为“平均值”。如图8-8所示。④每个季度的成交量及市盈率的结果，如图8-9所示。图8-8“值字段设置”对话框图8-9数据透视表结果数据处理【操作步骤】(2)通过分类汇总可以计算出平安银行股票每年股价最低值和最高值，以及每年股票的交易天数。①为更方便地计算出每年的汇总数据，添加年份的字段以便于进行分类。选择D列右击，弹出快捷菜单，选择“插入”，添加一列，取字段名为year，如图8-10所示。②year字段值取date字段的年份，使用函数“=year(C2)”即可得到D2单元格的值，如图8-11所示，接下来直接拖动公式至列尾即可。注意D列单元格的格式为“常规”，非“日期型”。结果如图8-12所示。图8-10插入列快捷菜单图8-11年份计算函数图8-12添加年份列结果数据处理【操作步骤】③以“年份”为分类字段，进行“分类汇总”。单击“数据”→“分类汇总”，弹出“分类汇总”对话框，不同的汇总项汇总方式不同，因此需要分开多次分类汇总。股价最低值，选择汇总方式为“最小值”，汇总项为low，如图8-13所示，单击“确定”按钮即可，结果如图8-16所示；接下来汇总股价最高值，在原来分类汇总的基础上同样的操作，注意“替换当前分类汇总”的选项不要勾选，设置如图8-14所示，结果如图8-17所示；同样，每年股票的交易天数，也是再次分类汇总，设置如图8-15所示，结果如图8-18所示。图8-13分类汇总设置一图8-14分类汇总设置二图8-15分类汇总设置三数据处理【操作步骤】③分类汇总的结果。图8-16分类汇总结果一图8-17分类汇总结果二数据处理【操作步骤】③分类汇总的结果。图8-18分类汇总结果三数据处理【操作步骤】(3)计算2015年平安银行涨跌天数的比例，即上涨天数/下跌天数。通过筛选获得2015年平安银行股票数据，再通过对涨跌字段（change_price）正负值判断计算上涨天数与下跌天数。①针对“平安银行”数据表，单击“数据”→“筛选”，进入筛选状态。②单击date字段的筛选按钮，弹出对话模型如所示，选择“2015”，单击“确定”按钮，条件选择如所示。单击“确定”按钮，即可筛选出2015年的数据。图8-19“筛选”设置图8-20“插入函数”设置一数据处理【操作步骤】③在数据表空白处两个单元格上分别添加文字“上涨天数”“下涨天数”。通过函数countif分别计算“上涨天数”“下涨天数”。④单击“确定”按钮，即可得到上涨天数，通过同样的方法，运用countif函数，设置下跌天数的条件是“<0”,即可得到下跌天数，计算结果如图。数据分析04数据分析【任务描述】针对平安银行2010年—2015年所有交易日的股票数据，挖掘各列数据间的关系，并作预测分析。（1）根据2010年-2015年平安银行股票数据，分析市盈率与市净率、市盈率与市销率、市盈率与市现率之间的相关性。（2）利用回归分析，分析市盈率、市净率、市销率之间的线性回归关系。并预测假如市净率为10，市销率为8，市盈率为多少？【操作步骤】(1)参考2010年-2015年平安银行股票数据，计算市盈率与市净率、市盈率与市销率、市盈率与市现率之间的相关系数。①单击“数据”→“数据分析”，弹出“数据分析”对话框，选择“相关系数”，点击“确定”按钮，弹出“相关系数”对话框。②设置“相关系数”的各项参数。首先计算市盈率(pe_ratio)与市净率（pb_ratio）之间的相关系数。输入区域设置为$S$1:$T$1387，输出区域设置为本表X2开始的区域，具体设置如图8-24所示。计算结果如图8-5所示。数据分析【操作步骤】③利用同样的操作方法，可以计算出市盈率(pe_ratio)与市销率(market_ratio)、市盈率(pe_ratio)与市现率(cash_ratio)之间的相关系数，如图8-26所示。在计算两者的相关系数时，注意两列数据一定要相邻，为此要适时的调整两列数据的位置。④由上操作结果可知，市盈率与市净率的相关系数为0.93、市盈率与市销率之间的相关系数为0.946，说明市盈率与市净率之间、市盈率与市销率之间显著相关；市盈率与市现率之间的相关系数为0.3，说明两者间相关度很低。数据分析【操作步骤】(2)利用回归分析，分析市盈率、市净率、市销率之间的线性回归关系并作预测。根据上述的分析，市盈率与市净率、市销率之间高度相关，而与市现率之间相关度低，为此，求解市盈率与市净率、市销率的回归关系。①单击“数据”→“数据分析”，弹出“数据分析”对话框，选择“回归”选项，单击“确定”按钮，弹出“回归”对话框。②设置回归分析各项参数。Y值输入区域是市盈率所在的区域，X值输入区域是市净率、市销率两列数据所在区域。置信度选择95%，输出区域选择空白外X15开始的区域，具体设置如图8-27所示，回归分析的结果如图8-28所示。数据分析

数据展示05数据展示【任务描述】根据平安银行2010年—2015年所有交易日的股票数据，绘制股票趋势图。(1)筛选出2015年3月份的数据，绘制“开盘—盘高—盘低—收盘”的股价图。(2)利用组合图的形式绘制出平安银行2015年的股价变化趋势图和市盈率变化曲线图。【操作步骤】(1)筛选出2015年3月份的数据，绘制“开盘—盘高—盘低—收盘”的股价图。①筛选出2015年3月的平安银行数据，形成“2015-3平安银行”数据表。②选中date、open、high、low、close四列数据，单击“插入”→“图表”，弹出“插入图表”对话框，单击“所有图表”选项卡，选择“股价图”，右边子图选择“开盘—盘高—盘低—收盘图”，如图8-29所示。数据展示【操作步骤】③单击“确定”按钮，最终形成图表，如图。④美化图表，使得图表更有表现力。设置图表标题，选中生成的图表，单击“设计”→“添加图表元素”，可以设置图表标题、横坐标轴标题、纵坐标轴标题，如图。数据展示【操作步骤】⑤仔细观察图表，发现股价集中在10-14元之间，可以调整纵坐标刻度，最小值为10、最大值为14。选中图表的纵坐标刻度，在右侧“设置坐标轴格式”任务窗格中，选择“坐标轴选项”按钮，打开“坐标轴选项”的设置，设置边界的最大值为14，最小值为10。如图8-32所示。经修改图表的设置，形成的新图表如图8-33所示。数据展示【操作步骤】(2)利用组合图的形式绘图，用簇状柱形图描述平安银行2015年的股价、用折线图描述市盈率变化。①筛选出2015年平安银行的数据，形成“2015年平安银行”数据表。在此表中，选中date、close、pe_ratio三列，根据三列数据创建图表。②单击“插入”→“图表”，弹出“插入图表”对话框，单击“所有图表”选项卡，选择“组合”，右边子图选择“自定义组合”，c