Managertoday 經理人

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

2019-09-17 12:56:36
Managertoday
https://bnextmedia.s3.hicloud.net.tw/image/album/2017-03/img-1490240388-65697@900.jpg
想了解每日股票交易資料,就幫自己做個戰報吧!Excel巨集和自動抓取資料的功能,可以幫你代勞。

唐愷是一名股票投資人員,他想將每日股票的交易資料做成圖表,當作自己的工作戰報,方便做投資決策。但每日製作戰報未免太辛苦了,我的建議是可以運用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」可以直接抓取網站的數據資料,下載到指定的工作表。操作的方法如下:

陳智揚

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來協助您分析大量的股市資訊,並非教您如何在股市中獲利,若您擁有財經或會計背景,就可以將這些專業知識透過Excel VBA實作,大量並自動的分析每一檔股票,設計屬於自己的股市獲利紅綠燈。

延伸課程:怎麼無痛學習VBA函數,讓年後上班效率翻倍?

延伸閱讀 /

1. 別再做Excel苦工!6 張圖學會「巨集」功能,自動完成重複的操作步驟

2. 別再手動輸入客戶姓名!5 張圖學會Excel+Word一起用,自動發送大量、客製化Email