用Excel自動分析股價走勢！6張圖學會「巨集+抓取網頁資料」功能，打造投資小程式

唐愷是一名股票投資人員，他想將每日股票的交易資料做成圖表，當作自己的工作戰報，方便做投資決策。但每日製作戰報未免太辛苦了，我的建議是可以運用Excel匯入資料和巨集的功能，讓Excel自動代勞。

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

 

事前準備：你需要的資料，在哪個網站裡？

  • 網址1：取得每日個股的收盤行情
    此網址（http://www.tpex.org.tw/web/stock/aftertrading/daily_close_quotes/stk_quote_print.php?l=zh-tw&d=106/03/06）可以取得每日個股的收盤行情，重點是民國日期部份可以進行修改。我們可以透過此網址，得知目前最新的股票上櫃代號和名稱。

  • 網址2：取得指定年月和股票代號當月的收盤價格
    此網址可以取得民國103年1月份，股票代號：1258當月的每日交易收盤價格。（http://www.tpex.org.tw/web/stock/aftertrading/daily_trading_info/st43_print.php?l=zh-tw&d=103/1&stkno=1258&s=0）

 

在Excel設定專門的表單，自動抓取網站資料

Excel的「資料」→「取得外部資料」→「從Web」可以直接抓取網站的數據資料，下載到指定的工作表。操作的方法如下：

excel巨集1.gif

陳智揚

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

Excel就會將上櫃股票行情下載到工作表中。你可以重複以上的步驟，將指定月份的每日交易紀錄資料，也匯入到「取得指定月份每日交易記錄」工作表。

上述兩個網址雖然可以將網頁資料匯入至Excel，但網址上的日期、上櫃股市代號皆為固定。因此，接下來我們透過函數和錄製巨集的方式，讓資料可以隨著你輸入的股票代號自動更新，變成一個你查詢股價的小程式。

 

更新上櫃收盤價的查詢日期

在網頁上查詢個股最新上櫃收盤價，必須要輸入中華民國日期，但Excel中時間計算的方式都是西元。因此這裡要運用函數，讓Excel自己抓到當天的中華民國日期。

excel巨集2.gif

陳智揚

在今天日期旁的儲存格輸入函數：=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」
接下來就準備製圖啦！

excel巨集3.gif

陳智揚

Step1. 切換至「取得指定月份每日交易記錄」工作表
Step2. 同時選取「A2:A22」、「D2:G22」兩個範圍
Step3. 「插入」索引標籤→「股票圖」
Step4. 點選「圖表標題」，在「資料編輯列」輸入公式：
=取得指定月份每日交易記錄!$A$1
※讓圖表標題內容與A1儲存格內容「同步」
Step5. 「圖表工具」→「格式」 索引標籤，選擇「上漲橫條圖1」
Step6. 修改成「紅色」填滿與框線
Step7. 「圖表工具」→「格式」 索引標籤，選擇「下跌橫條圖1」
Step8. 修改成「綠色」填滿與框線

這下，你已經有一個用紅色標註上漲、綠色標註下跌的制式圖表。接下來，我們就要錄製巨集，讓Excel往後都可以反覆「自動」取得指定年月、股票代號等資料。

 

使用巨集，以後只要按個鈕就能直接做出圖表

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

這裡比較複雜，建議大家直接參考圖表的操作示範，流程如下：

excel巨集4.gif

陳智揚

Step1. 切換至「取得指定月份每日交易記錄」工作表
Step2. 對「A1」儲存格按下滑鼠右鍵，點選「編輯查詢」
Step3. 按下「匯入」鈕
Step4. 切換至「設定」工作表
Step5. 點選「股票圖」，按下滑鼠右鍵點選「選取資料」
Step6. 按下「水平類別座標軸」的「編輯」鈕
Step7. 將最後的數字修改成較小的數值
Step8. 按下「確定」鈕
Step9. 按下「開發人員」索引標籤 / 巨集 鈕。
Step10. 選擇「GetMonthData」，按下「編輯」鈕。
Step11. 將程式碼中「固定儲存格」範圍以及網址固定的參數，修改成「設定」工作表中對應的儲存格代號
Step12. 修改完畢，回到「設定」工作表，將按鈕指定給「GetMonthData」巨集
Step13. 透過上述的步驟，也可以錄製並修改每日的股市更新資料「GetNewData」

excel巨集5.gif

陳智揚

修改語法解析：

  • 雙引號：固定不會改變的字串，必須使用雙引號包起來
  • &符號：串接文字(左右必須空白)
  • Sheets("設定").Range("B1").Value：取得「設定」工作表，B1儲存格的內容

只要每次開啟Excel檔，按下「更新股價」鈕就可以看到每檔股票的最新資訊。而想知道過去的歷史資料，只要修改「B5儲存格」的「年月」，再按下「抓取月份資料繪製股票圖」鈕，就可以看到該年月的股票圖。

 

使用測試結果

excel巨集6.gif

陳智揚

想在股市獲利並非依靠「八卦消息」或「明牌」，而是要「勤做功課」並且瞭解產業狀況廣泛取得資訊。本文章僅分享如何透過Excel來協助您分析大量的股市資訊，並非教您如何在股市中獲利，若您擁有財經或會計背景，就可以將這些專業知識透過Excel VBA實作，大量並自動的分析每一檔股票，設計屬於自己的股市獲利紅綠燈。

聯絡我們