數(shù)據(jù)分析技術(shù)第8章

第八章股票數(shù)據(jù)分析綜合案例北京西普陽(yáng)光教育科技股份有限公司目錄

Contents數(shù)據(jù)展示本章小結(jié)0601股票數(shù)據(jù)背景分析02數(shù)據(jù)清洗03數(shù)據(jù)處理04數(shù)據(jù)分析05股票數(shù)據(jù)背景分析01股票數(shù)據(jù)背景描述案例圍繞“平安銀行”股票歷史數(shù)據(jù)展開(kāi)，包含證券交易所A股股票日線數(shù)據(jù)，時(shí)間區(qū)間為2010年1月1日至2015年12月31日，剔除假期休市等數(shù)據(jù)。利用數(shù)據(jù)分析技術(shù)對(duì)數(shù)據(jù)進(jìn)行清洗、分析、過(guò)濾，分析股票數(shù)據(jù)，通過(guò)對(duì)歷史數(shù)據(jù)的處理，尋找出前后數(shù)據(jù)之間的關(guān)系，建立關(guān)聯(lián)模型，然后通過(guò)歷史數(shù)據(jù)和所建立的關(guān)聯(lián)模型來(lái)預(yù)測(cè)時(shí)間序列的未來(lái)值。序號(hào)字段名字段解釋序號(hào)字段名字段解釋1stcok_id股票代碼14turnover_rate換手率(%)2stock_name股票名稱(chēng)簡(jiǎn)稱(chēng)15a_totleA股流通市值(元)3date日期16b_totleB股流通市值(元)4his_price前收盤(pán)價(jià)(元)17totle總市值(元)5open開(kāi)盤(pán)價(jià)(元)18a_equityA股流通股本(股)6high最高價(jià)(元)19b_equityB股流通股本(股)7low最低價(jià)(元)20totle_equity總股本(股)8close收盤(pán)價(jià)(元)21pe_ratio市盈率9volume成交量(股)22pb_ratio市凈率10turnover成交金額(元)23market_rate市銷(xiāo)率11change_price漲跌(元)24cash_rate市現(xiàn)率12change_rate漲跌幅(%)25collection_date采集時(shí)間13average_price均價(jià)(元)

數(shù)據(jù)清洗02數(shù)據(jù)清洗【任務(wù)描述】針對(duì)平安銀行2010年—2015年所有交易日的股票數(shù)據(jù)，進(jìn)行數(shù)據(jù)清洗，去所有的空值數(shù)據(jù)、無(wú)價(jià)值數(shù)據(jù)。【操作步驟】(1)仔細(xì)分析“平安銀行”Excel表，發(fā)現(xiàn)b_totle是空值，b_equity是零值，collection_date是采集時(shí)間，都是相同值，對(duì)本案例來(lái)說(shuō)也是無(wú)價(jià)值數(shù)據(jù)，因此，刪除這三列數(shù)據(jù)。(2)找出Excel表所有的空值，分析發(fā)現(xiàn)，都是股票停盤(pán)日無(wú)交易量的數(shù)據(jù)，可以通過(guò)篩選找出所有空值，并刪除之。①選擇“數(shù)據(jù)”→“篩選”，進(jìn)入自動(dòng)篩選的狀態(tài)。②單擊turnover字段的下拉按鈕，彈出篩選條件設(shè)置的對(duì)話框，選擇“N/A”空值，如圖8-1所示，單擊“確定”按鈕，結(jié)果如圖8-2所示。③由結(jié)果可知，所有停盤(pán)日無(wú)交易量的數(shù)據(jù)記錄全顯示出來(lái)了，選擇所有篩選出來(lái)的列，右擊，彈出快捷菜單，選擇“刪除行”，刪除帶所有的空值的記錄，如圖8-3所示。④單擊“篩選”按鈕，取消“篩選”，回歸狀態(tài)，還有1387條記錄，如圖8-4所示。數(shù)據(jù)清洗圖8-1

“篩選”數(shù)據(jù)表圖8-2

“篩選”空值記錄數(shù)據(jù)清洗圖8-3

刪除空值行圖8-4

去空值后的數(shù)據(jù)表數(shù)據(jù)處理03數(shù)據(jù)處理【任務(wù)描述】對(duì)股票數(shù)據(jù)進(jìn)行數(shù)據(jù)處理，根據(jù)要求計(jì)算出月度、年度數(shù)據(jù)報(bào)表，要求如下：(1)根據(jù)股票數(shù)據(jù)，計(jì)算平安銀行2010年-2015年每個(gè)季度的成交量平均值以及平均市盈率。(2)計(jì)算平安銀行股票每年股價(jià)最低值和最高值，以及每年交易天數(shù)。(3)計(jì)算2015年平安銀行漲跌天數(shù)，即上漲天數(shù)與下跌天數(shù)。【操作步驟】(1)通過(guò)數(shù)據(jù)透視表可以方便地計(jì)算出每個(gè)季度的平均成交量以及市盈率。①單擊“插入”→“數(shù)據(jù)透視表”，彈出“創(chuàng)建數(shù)據(jù)透視表”對(duì)話框，如圖8-5所示。選擇需要分析的數(shù)據(jù)區(qū)域，以及數(shù)據(jù)透視表放置的位置，單擊“確定”按鈕。②進(jìn)入數(shù)據(jù)透視表的分析窗口，需分析的是平均成交量與市盈率，則拖動(dòng)volume、pe_ratio兩個(gè)字段放入“值”區(qū)域；要求按季度進(jìn)行統(tǒng)計(jì)，則拖動(dòng)date字段至“行”區(qū)域，date字段會(huì)在“行”區(qū)域自動(dòng)添加“年”“季度”的分類(lèi)，如圖8-6所示。數(shù)據(jù)處理圖8-6數(shù)據(jù)透視表字段設(shè)置圖8-7值字段設(shè)置快捷菜單數(shù)據(jù)處理③計(jì)算平均成交量與市盈率，則需修改“值”字段的屬性設(shè)置，依次選擇“值”字段中的pe_ratio及volume字段，分別右擊，彈出快捷菜單，選擇“值字段設(shè)置”，如圖8-7所示，彈出“值字段”設(shè)置的對(duì)話框，選擇“值字段匯總方式”為“平均值”。如圖8-8所示。④每個(gè)季度的成交量及市盈率的結(jié)果，如圖8-9所示。圖8-8“值字段設(shè)置”對(duì)話框圖8-9數(shù)據(jù)透視表結(jié)果數(shù)據(jù)處理【操作步驟】(2)通過(guò)分類(lèi)匯總可以計(jì)算出平安銀行股票每年股價(jià)最低值和最高值，以及每年股票的交易天數(shù)。①為更方便地計(jì)算出每年的匯總數(shù)據(jù)，添加年份的字段以便于進(jìn)行分類(lèi)。選擇D列右擊，彈出快捷菜單，選擇“插入”，添加一列，取字段名為year，如圖8-10所示。②year字段值取date字段的年份，使用函數(shù)“=year(C2)”即可得到D2單元格的值，如圖8-11所示，接下來(lái)直接拖動(dòng)公式至列尾即可。注意D列單元格的格式為“常規(guī)”，非“日期型”。結(jié)果如圖8-12所示。圖8-10插入列快捷菜單圖8-11年份計(jì)算函數(shù)圖8-12添加年份列結(jié)果數(shù)據(jù)處理【操作步驟】③以“年份”為分類(lèi)字段，進(jìn)行“分類(lèi)匯總”。單擊“數(shù)據(jù)”→“分類(lèi)匯總”，彈出“分類(lèi)匯總”對(duì)話框，不同的匯總項(xiàng)匯總方式不同，因此需要分開(kāi)多次分類(lèi)匯總。股價(jià)最低值，選擇匯總方式為“最小值”，匯總項(xiàng)為low，如圖8-13所示，單擊“確定”按鈕即可，結(jié)果如圖8-16所示；接下來(lái)匯總股價(jià)最高值，在原來(lái)分類(lèi)匯總的基礎(chǔ)上同樣的操作，注意“替換當(dāng)前分類(lèi)匯總”的選項(xiàng)不要勾選，設(shè)置如圖8-14所示，結(jié)果如圖8-17所示；同樣，每年股票的交易天數(shù)，也是再次分類(lèi)匯總，設(shè)置如圖8-15所示，結(jié)果如圖8-18所示。圖8-13分類(lèi)匯總設(shè)置一圖8-14分類(lèi)匯總設(shè)置二圖8-15分類(lèi)匯總設(shè)置三數(shù)據(jù)處理【操作步驟】③分類(lèi)匯總的結(jié)果。圖8-16分類(lèi)匯總結(jié)果一圖8-17分類(lèi)匯總結(jié)果二數(shù)據(jù)處理【操作步驟】③分類(lèi)匯總的結(jié)果。圖8-18分類(lèi)匯總結(jié)果三數(shù)據(jù)處理【操作步驟】(3)計(jì)算2015年平安銀行漲跌天數(shù)的比例，即上漲天數(shù)/下跌天數(shù)。通過(guò)篩選獲得2015年平安銀行股票數(shù)據(jù)，再通過(guò)對(duì)漲跌字段（change_price）正負(fù)值判斷計(jì)算上漲天數(shù)與下跌天數(shù)。①針對(duì)“平安銀行”數(shù)據(jù)表，單擊“數(shù)據(jù)”→“篩選”，進(jìn)入篩選狀態(tài)。②單擊date字段的篩選按鈕，彈出對(duì)話模型如所示，選擇“2015”，單擊“確定”按鈕，條件選擇如所示。單擊“確定”按鈕，即可篩選出2015年的數(shù)據(jù)。圖8-19“篩選”設(shè)置圖8-20“插入函數(shù)”設(shè)置一數(shù)據(jù)處理【操作步驟】③在數(shù)據(jù)表空白處兩個(gè)單元格上分別添加文字“上漲天數(shù)”“下漲天數(shù)”。通過(guò)函數(shù)countif分別計(jì)算“上漲天數(shù)”“下漲天數(shù)”。④單擊“確定”按鈕，即可得到上漲天數(shù)，通過(guò)同樣的方法，運(yùn)用countif函數(shù)，設(shè)置下跌天數(shù)的條件是“<0”,即可得到下跌天數(shù)，計(jì)算結(jié)果如圖。數(shù)據(jù)分析04數(shù)據(jù)分析【任務(wù)描述】針對(duì)平安銀行2010年—2015年所有交易日的股票數(shù)據(jù)，挖掘各列數(shù)據(jù)間的關(guān)系，并作預(yù)測(cè)分析。（1）根據(jù)2010年-2015年平安銀行股票數(shù)據(jù)，分析市盈率與市凈率、市盈率與市銷(xiāo)率、市盈率與市現(xiàn)率之間的相關(guān)性。（2）利用回歸分析，分析市盈率、市凈率、市銷(xiāo)率之間的線性回歸關(guān)系。并預(yù)測(cè)假如市凈率為10，市銷(xiāo)率為8，市盈率為多少？【操作步驟】(1)參考2010年-2015年平安銀行股票數(shù)據(jù)，計(jì)算市盈率與市凈率、市盈率與市銷(xiāo)率、市盈率與市現(xiàn)率之間的相關(guān)系數(shù)。①單擊“數(shù)據(jù)”→“數(shù)據(jù)分析”，彈出“數(shù)據(jù)分析”對(duì)話框，選擇“相關(guān)系數(shù)”，點(diǎn)擊“確定”按鈕，彈出“相關(guān)系數(shù)”對(duì)話框。②設(shè)置“相關(guān)系數(shù)”的各項(xiàng)參數(shù)。首先計(jì)算市盈率(pe_ratio)與市凈率（pb_ratio）之間的相關(guān)系數(shù)。輸入?yún)^(qū)域設(shè)置為$S$1:$T$1387，輸出區(qū)域設(shè)置為本表X2開(kāi)始的區(qū)域，具體設(shè)置如圖8-24所示。計(jì)算結(jié)果如圖8-5所示。數(shù)據(jù)分析【操作步驟】③利用同樣的操作方法，可以計(jì)算出市盈率(pe_ratio)與市銷(xiāo)率(market_ratio)、市盈率(pe_ratio)與市現(xiàn)率(cash_ratio)之間的相關(guān)系數(shù)，如圖8-26所示。在計(jì)算兩者的相關(guān)系數(shù)時(shí)，注意兩列數(shù)據(jù)一定要相鄰，為此要適時(shí)的調(diào)整兩列數(shù)據(jù)的位置。④由上操作結(jié)果可知，市盈率與市凈率的相關(guān)系數(shù)為0.93、市盈率與市銷(xiāo)率之間的相關(guān)系數(shù)為0.946，說(shuō)明市盈率與市凈率之間、市盈率與市銷(xiāo)率之間顯著相關(guān)；市盈率與市現(xiàn)率之間的相關(guān)系數(shù)為0.3，說(shuō)明兩者間相關(guān)度很低。數(shù)據(jù)分析【操作步驟】(2)利用回歸分析，分析市盈率、市凈率、市銷(xiāo)率之間的線性回歸關(guān)系并作預(yù)測(cè)。根據(jù)上述的分析，市盈率與市凈率、市銷(xiāo)率之間高度相關(guān)，而與市現(xiàn)率之間相關(guān)度低，為此，求解市盈率與市凈率、市銷(xiāo)率的回歸關(guān)系。①單擊“數(shù)據(jù)”→“數(shù)據(jù)分析”，彈出“數(shù)據(jù)分析”對(duì)話框，選擇“回歸”選項(xiàng)，單擊“確定”按鈕，彈出“回歸”對(duì)話框。②設(shè)置回歸分析各項(xiàng)參數(shù)。Y值輸入?yún)^(qū)域是市盈率所在的區(qū)域，X值輸入?yún)^(qū)域是市凈率、市銷(xiāo)率兩列數(shù)據(jù)所在區(qū)域。置信度選擇95%，輸出區(qū)域選擇空白外X15開(kāi)始的區(qū)域，具體設(shè)置如圖8-27所示，回歸分析的結(jié)果如圖8-28所示。數(shù)據(jù)分析

數(shù)據(jù)展示05數(shù)據(jù)展示【任務(wù)描述】根據(jù)平安銀行2010年—2015年所有交易日的股票數(shù)據(jù)，繪制股票趨勢(shì)圖。(1)篩選出2015年3月份的數(shù)據(jù)，繪制“開(kāi)盤(pán)—盤(pán)高—盤(pán)低—收盤(pán)”的股價(jià)圖。(2)利用組合圖的形式繪制出平安銀行2015年的股價(jià)變化趨勢(shì)圖和市盈率變化曲線圖?！静僮鞑襟E】(1)篩選出2015年3月份的數(shù)據(jù)，繪制“開(kāi)盤(pán)—盤(pán)高—盤(pán)低—收盤(pán)”的股價(jià)圖。①篩選出2015年3月的平安銀行數(shù)據(jù)，形成“2015-3平安銀行”數(shù)據(jù)表。②選中date、open、high、low、close四列數(shù)據(jù)，單擊“插入”→“圖表”，彈出“插入圖表”對(duì)話框，單擊“所有圖表”選項(xiàng)卡，選擇“股價(jià)圖”，右邊子圖選擇“開(kāi)盤(pán)—盤(pán)高—盤(pán)低—收盤(pán)圖”，如圖8-29所示。數(shù)據(jù)展示【操作步驟】③單擊“確定”按鈕，最終形成圖表，如圖。④美化圖表，使得圖表更有表現(xiàn)力。設(shè)置圖表標(biāo)題，選中生成的圖表，單擊“設(shè)計(jì)”→“添加圖表元素”，可以設(shè)置圖表標(biāo)題、橫坐標(biāo)軸標(biāo)題、縱坐標(biāo)軸標(biāo)題，如圖。數(shù)據(jù)展示【操作步驟】⑤仔細(xì)觀察圖表，發(fā)現(xiàn)股價(jià)集中在10-14元之間，可以調(diào)整縱坐標(biāo)刻度，最小值為10、最大值為14。選中圖表的縱坐標(biāo)刻度，在右側(cè)“設(shè)置坐標(biāo)軸格式”任務(wù)窗格中，選擇“坐標(biāo)軸選項(xiàng)”按鈕，打開(kāi)“坐標(biāo)軸選項(xiàng)”的設(shè)置，設(shè)置邊界的最大值為14，最小值為10。如圖8-32所示。經(jīng)修改圖表的設(shè)置，形成的新圖表如圖8-33所示。數(shù)據(jù)展示【操作步驟】(2)利用組合圖的形式繪圖，用簇狀柱形圖描述平安銀行2015年的股價(jià)、用折線圖描述市盈率變化。①篩選出2015年平安銀行的數(shù)據(jù)，形成“2015年平安銀行”數(shù)據(jù)表。在此表中，選中date、close、pe_ratio三列，根據(jù)三列數(shù)據(jù)創(chuàng)建圖表。②單擊“插入”→“圖表”，彈出“插入圖表”對(duì)話框，單擊“所有圖表”選項(xiàng)卡，選擇“組合”，右邊子圖選擇“自定義組合”，c