用Excel自動分析股價走勢！6張圖學會「巨集+抓取網頁資料 ...

唐愷是一名股票投資人員，他想將每日股票的交易資料做成圖表，當作自己的工作戰報，方便做投資決策。

但每日製作戰報未免太辛苦了，我的建議是可以 ... 購買前請先完成信箱驗證唷立即填寫購買前請先完成信箱驗證唷立即認證你有  筆未完成的訂單前往我的訂單類別最近搜尋最多人搜尋TOP5合作專區合作申請合作專區您好！這邊可以查看你的教師資訊喔！線上課程：實體課程：募資專案：課程管理目前總收入：元可提領收入：元收入管理文章數：篇訂閱人數：人文章管理合作申請我的課程系統通知課程通知訂閱通知沒有未讀訊息顯示全部購物車已加入堂課程你的購物車是空的唷！線上：募資中即將開課即將開課即將募資已開課實體：報名中prop.price%>總金額：登入/註冊我的課程個人檔案我的訂單我的優惠券我的訂閱超強師資邀請好友常見問題登出取消全部課程類別所有生活設計商業語言科技登入/註冊課程類別合作申請課程類別我的課程個人檔案我的訂單我的優惠券我的訂閱超強師資邀請好友合作申請合作專區常見問題登出課程類別全部生活設計商業語言科技用Excel自動分析股價走勢！6張圖學會「巨集+抓取網頁資料」功能，打造投資小程式2019-02-1910:52:14待發布編輯唐愷是一名股票投資人員，他想將每日股票的交易資料做成圖表，當作自己的工作戰報，方便做投資決策。

但每日製作戰報未免太辛苦了，我的建議是可以運用Excel匯入資料和巨集的功能，讓Excel自動代勞。

先為自己製作一個戰報檔案，其中包含三個工作表。

第一，是未來的操作頁面，命名為「設定」，用來輸入想查找的股票代號和抓取月份，工作表內容請參照下圖；第二和第三個工作表，則是要請Excel匯入的資料。

事前準備：你需要的資料，在哪個網站裡？網址1：取得每日個股的收盤行情此網址可以取得每日個股的收盤行情，重點是民國日期部份可以進行修改。

我們可以透過此網址，得知目前最新的股票上櫃代號和名稱。

網址2：取得指定年月和股票代號當月的收盤價格此網址可以取得民國103年1月份，股票代號：1258當月的每日交易收盤價格。

在Excel設定專門的表單，自動抓取網站資料Excel的「資料」→「取得外部資料」→「從Web」可以直接抓取網站的數據資料，下載到指定的工作表。

操作的方法如下：Step1.先複製「最新上櫃收盤價」的網址Step2.新增一個「最新上櫃收盤價」工作表Step3.在此工作表中，點選「資料」索引標籤→按下「從Web」鈕Step4.貼上網址，按下「到」或「Enter」，讓Excel爬梳上櫃股票收盤價Step5.按下「匯入」鈕→「確定」鈕Excel就會將上櫃股票行情下載到工作表中。

你可以重複以上的步驟，將指定月份的每日交易紀錄資料，也匯入到「取得指定月份每日交易記錄」工作表。

上述兩個網址雖然可以將網頁資料匯入至Excel，但網址上的日期、上櫃股市代號皆為固定。

因此，接下來我們透過函數和錄製巨集的方式，讓資料可以隨著你輸入的股票代號自動更新，變成一個你查詢股價的小程式。

更新上櫃收盤價的查詢日期在網頁上查詢個股最新上櫃收盤價，必須要輸入中華民國日期，但Excel中時間計算的方式都是西元。

因此這裡要運用函數，讓Excel自己抓到當天的中華民國日期。

在今天日期旁的儲存格輸入函數：=TEXT(TODAY(),"e/mm/dd")，意思是將今天的日期轉換成雙月雙日的「中華民國日期」。

函數的解釋如下：1.函數名稱：TODAY功能：取得今天的日期語法：TODAY()2.函數名稱：TEXT功能：將資料轉換成另一種格式語法：TEXT(資料,格式語法)※e/mm/dd可以將標準的西元日期轉換成雙月雙日的「中華民國日期」。

例：2017/3/8→106/03/08透過VLOOKUP取得目前代號的名稱與股價要知道指定年月和股票代號的當月收盤價格，就要告訴Excel，去「「最新上櫃收盤價」這張表找尋指定股票代號的資料，這時候就會用到比對函數VLOOKUP。

點選「名稱」旁的儲存格，輸入=VLOOKUP(B1,最新上櫃收盤價!A:Q,2,0)，意思是讓Excel從「最新上櫃收盤價」這張工作表A到Q欄位查找指定的股票代號（B1），回傳第二欄（名稱）的值。

而在「股價」旁的儲存格，輸入=VLOOKUP(B1,最新上櫃收盤價!A:Q,3,0)，則是讓Excel從「最新上櫃收盤價」這張工作表A到Q欄位查找指定的股票代號（B1），回傳第三欄（股價）的值。

如此，以後只要輸入股票代號後，Excel就會自動抓取股票名稱和股價的值。

繪製股票成交資訊圖表你已經成功讓Excel蒐集了股票資訊，那該怎麼看出股票漲跌趨勢？這時候就需要繪製成表，才容易觀察。

因此，我們來設定一個股票圖的格式，讓Excel往後都可以按照此格式，產生出最新的圖表。

首先，你得先運用COUNTA函數，計算指定儲存格範圍的資料筆數，方便製圖。

函數名稱：COUNTA功能：計算指定範圍中，有多少儲存格有資料語法：COUNTA(範圍)Step1.點選「抓取月份最後資料列數」旁的儲存格Step2.輸入「=COUNTA(取得指定月份每日交易記錄!A:A)-1」接下來就準備製圖啦！Step1.切換至「取得指定月份每日交易記錄」工作表Step2.同時選取「A2:A22」、「D2:G22」兩個範圍Step3.「插入」索引標籤→「股票圖」Step4.點選「圖表標題」，在「資料編輯列」輸入公式：=取得指定月份每日交易記錄!$A$1※讓圖表標題內容與A1儲存格內容「同步」Step5.「圖表工具」→「格式」索引標籤，選擇「上漲橫條圖1」Step6.修改成「紅色」填滿與框線Step7.「圖表工具」→「格式」索引標籤，選擇「下跌橫條圖1」Step8.修改成「綠色」填滿與框線這下，你已經有一個用紅色標註上漲、綠色標註下跌的制式圖表。

接下來，我們就要錄製巨集，讓Excel往後都可以反覆「自動」取得指定年月、股票代號等資料。

使用巨集，以後只要按個鈕就能直接做出圖表之前提過巨集是可以幫助你用一個鍵反覆執行細碎操作的好工具，這裡，我們要將「更新股價」和「抓取月份資料繪製股票圖」兩個方框變成巨集按鈕，只要按個鍵，Excel就可以執行完抓取資料、繪製圖表的工作。

這裡比較複雜，建議大家直接參考圖表的操作示範，流程如下：Step1.切換至「取得指定月份每日交易記錄」工作表Step2.對「A1」儲存格按下滑鼠右鍵，點選「編輯查詢」Step3.按下「匯入」鈕Step4.切換至「設定」工作表Step5.點選「股票圖」，按下滑鼠右鍵點選「選取資料」Step6.按下「水平類別座標軸」的「編輯」鈕Step7.將最後的數字修改成較小的數值Step8.按下「確定」鈕Step9.按下「開發人員」索引標籤/巨集鈕。

Step10.選擇「GetMonthData」，按下「編輯」鈕。

Step11.將程式碼中「固定儲存格」範圍以及網址固定的參數，修改成「設定」工作表中對應的儲存格代號Step12.修改完畢，回到「設定」工作表，將按鈕指定給「GetMonthData」巨集Step13.透過上述的步驟，也可以錄製並修改每日的股市更新資料「GetNewData」修改語法解析：雙引號：固定不會改變的字串，必須使用雙引號包起來&符號：串接文字(左右必須空白)Sheets("設定").Range("B1").Value：取得「設定」工作表，B1儲存格的內容只要每次開啟Excel檔，按下「更新股價」鈕就可以看到每檔股票的最新資訊。

而想知道過去的歷史資料，只要修改「B5儲存格」的「年月」，再按下「抓取月份資料繪製股票圖」鈕，就可以看到該年月的股票圖。

本文經授權轉載自《用Excel自動分析股價走勢！6張圖學會「巨集+抓取網頁資料」功能，打造投資小程式》，非經原作者同意不得擷取部分或全部轉載。

文章標籤上一篇下一篇相關課程：堂｜相關文章：篇管理訂閱已訂閱訂閱文章標籤分享：上一篇下一篇