Excel 與 VBA 財務應用初階班 第 11 課
Eugene Chang (張佑成)
December 5th 2024
# Excel 與 VBA 財務應用初階班  --- # 從網路截取資料 人類從網路搜尋、並且集合、彙整、與分析資料 這個行為其實和網際網路存在的時間一樣長 --- # Excel 截取資料的解決方案 - 從 web 匯入資料 (圖形界面) Excel 其實有內建一個擷取網頁資料的功能，進入**資料** > **從 web 擷取資料** --- # 範例 Excel 檔案 連結：[範例檔案](https://drive.google.com/uc?export=download&id=1U9iiK2OaP7OGdIXILpFXKu3DmSvLLNm9) --- # 從 web 匯入資料  *Mac 版 Excel 不包含這個功能 --- # 從 web 匯入資料 範例網站：https://tw.stock.yahoo.com/q/q?s=2330 --- # 其實 這個功能背後是使用了 VBA 的 querytable 物件 官方文件: [querytable](https://docs.microsoft.com/zh-tw/office/vba/api/excel.querytable) --- # 但是 這些看似強大的功能卻有一些嚴重的問題： 1. querytable / 從 web 匯入資料因爲核心是透過 IE 運作, 效能相對慢 2. 網絡上的教學資源 / 文件相對少 --- # 如何利用程式從網路截取資料？ 假設我們今天不是透過手動，而是利用程式透過網路自動去擷取資料時 一般的作法有以下兩種： - 透過**網頁爬蟲** - 透過**API** --- # 網頁爬蟲 說穿了，就是從網頁上截取彙整、與分析資料的自動化程式 --- ## 我們想截取資料的網頁  --- # 最後希望呈現在 Excel 上的格式  --- # 網絡基礎： HTTP 溝通協定 HTTP 用白話講，就像是**電腦與電腦之間的共同語言**，電腦需要通過這個共同語言，才能在網絡上面互相溝通 而像是 FB, Google, Yahoo 等網站，其實背後都是一堆運行在雲端伺服器上的程式 這些程式的輸入是根據**HTTP 動詞**(HTTP Verb) 以及**網址**運作 輸出則是網頁 (html) 檔案 --- # 網絡基礎： HTTP 溝通協定 舉例： GET https://www.facebook.com/ 過程就是： 1. 用戶端 (你的瀏覽器) 針對 FB 的雲端伺服器發送請求(request) 2. 而 FB 的雲端伺服器在收到請求後，將資料都計算完成，回傳一個 html 檔案 3. 用戶端 (你的瀏覽器) 在下載了請求回傳的 html 檔案之後，將 html 轉成人看得懂的網頁，呈現給使用者 --- ## 網頁開發 101 任何網頁都是由 **html 標籤(tag)** 所組成，基本結構如下： ```html <標籤名稱 class="類別名稱">內容</標籤名稱> <標籤名稱 id="id名稱">內容</標籤名稱> ``` 今天我們要擷取的任何內容，一定是被包裹在在某一個標籤裡面 --- ## 網頁開發 101 而今天若網頁開發者需要改變任何一個標籤的**樣式**，就需要用到 **css** 語法 以上面的網頁為例，人名都是以綠色顯示，所以就先宣告一個名為 **green** 的 css 類別： ```html <style> .green{ color:#55ff55; } </style> ``` --- # 網頁開發 101 若今天希望讓一個標籤的内容文字變成綠色，可以使用定義好的 .green 這個 css 類別： ```html <span class="green">Prince Vasili Kuragin</span> ``` *想了解更多 html 的知識可以看一下 Mozilla 官網的教學：[HTML 基礎](https://developer.mozilla.org/zh-TW/docs/Learn/Getting_started_with_the_web/HTML_basics) --- # VBA 與網頁資料截取 若要用 VBA 語言實作截取網頁爬蟲，則可以選擇用以下解決方案： 1. **querytable** 2. **IE 物件** 3. **xmlhttp** --- # IE 物件 簡單來説就是 VBA 會開啓一個 IE 瀏覽器 --- # 問題是找到有用的資料如同大海撈針... --- ## 分析一下我們要爬的網頁  --- ## 分析一下我們要爬的網頁 收盤價是在一個 table 標籤内部的一個 td 標籤 該 table 標籤内有兩個 tr 標簽 第二個 tr 標籤内的第八個 td 標簽是我們要的收盤價 --- ## 分析一下我們要爬的網頁  --- # html 標簽的關聯 簡單來説，就是一個樹狀圖的概念：  --- # html table 標簽的結構 網頁上的資料大多都是匯整在表格、而 html 的表格則是由 table 標簽構成的：  --- # html table 標簽的結構  --- # 延伸閱讀 w3 school： [連結](https://www.w3schools.com/html/html_tables.asp) Mozilla：[連結](https://developer.mozilla.org/zh-TW/docs/Web/HTML/Element/table) --- # IE 物件 說穿了，就是讓你透過 VBA 去操控 IE 瀏覽器 --- # IE 物件實戰 ```vb Sub Test() '建立/開啓一個新的 IE 瀏覽器物件 Set IE = CreateObject("InternetExplorer.Application") '執行時是可以被看見的 IE.Visible = True '連到我們制訂的網頁連結 IE.navigate ("https://tw.stock.yahoo.com/q/q?s=2330") '等待網頁下載完畢，並顯示在瀏覽器上 Do While IE.Busy '若網頁還沒下載完畢，就多等待一秒鐘 Application.Wait DateAdd("s", 1, Now) Loop '下載了網頁(html檔案之後) 過濾出所有的 table 標簽 For Each Table In IE.Document.GetElementsByTagName("table") '把每一個 table 標簽内的文字印出來 Debug.Print Table.innerText Next Table IE.Quit '關閉 IE 瀏覽器 End Sub ``` --- # IE 物件實戰 ```vb Sub Test() '建立/開啓一個新的 IE 瀏覽器物件 Set IE = CreateObject("InternetExplorer.Application") '執行時是可以被看見的 IE.Visible = True '連到我們制訂的網頁連結 IE.navigate ("https://tw.stock.yahoo.com/q/q?s=2330") '等待網頁下載完畢，並顯示在瀏覽器上 Do While IE.Busy Application.Wait DateAdd("s", 1, Now) Loop '將第二個 table 讀取出來，存入 myTable 變數 Set myTable = IE.Document.GetElementsByTagName("table")(2) '將 myTable 内的 td 標簽過濾出來 For Each Tag In myTable.GetElementsByTagName("td") Debug.Print Tag.innertext Next IE.Quit '關閉 IE 瀏覽器 End Sub ``` --- # IE 物件實戰 ```vb Sub Test() '建立/開啓一個新的 IE 瀏覽器物件 Set IE = CreateObject("InternetExplorer.Application") '執行時是可以被看見的 IE.Visible = True '連到我們制訂的網頁連結 IE.navigate ("https://tw.stock.yahoo.com/q/q?s=2330") '等待網頁下載完畢，並顯示在瀏覽器上 Do While IE.Busy Application.Wait DateAdd("s", 1, Now) Loop '將第二個 table 讀取出來，存入 myTable 變數 Set myTable = IE.Document.GetElementsByTagName("table")(2) Cells(2, "A").Value = Date Cells(2, "B").Value = myTable.GetElementsByTagName("td")(8).innertext Cells(2, "C").Value = myTable.GetElementsByTagName("td")(9).innertext Cells(2, "D").Value = myTable.GetElementsByTagName("td")(10).innertext Cells(2, "E").Value = myTable.GetElementsByTagName("td")(7).innertext Cells(2, "F").Value = myTable.GetElementsByTagName("td")(6).innertext Cells(2, "G").Value = myTable.GetElementsByTagName("td")(5).innertext IE.Quit '關閉 IE 瀏覽器 End Sub ``` --- # 用 For Loop 迭代多個不同股票的頁面 ```vb Sub yahooTWStock() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True lastRow = Worksheets("Portfolio").Range("A1").End(xlDown).Row '將 A2 到 A5 的值讀出來，存入 stocks stocks = Worksheets("Portfolio").Range("A2:A" & lastRow).Value For i = 2 To UBound(stocks) + 1 Debug.Print Range("A" & i).Value stockId = Worksheets("Portfolio").Range("A" & i).Value Debug.print stockId '動態產生需要爬的連結 IE.navigate ("https://tw.stock.yahoo.com/q/q?s=" & stockId) Do While IE.Busy Application.Wait DateAdd("s", 1, Now) Loop '把每一個頁面的第二個 table 讀出來 Set myTable = IE.Document.GetElementsByTagName("table")(2) '讀取 table 内的文字 Debug.Print myTable.innerText Next i IE.Quit End Sub ``` --- # 投資組合爬蟲完成版 ```vb Sub yahooTWStock() Set IE = CreateObject("InternetExplorer.Application") IE.Visible = True lastRow = Worksheets("Portfolio").Range("A1").End(xlDown).Row stocks = Worksheets("Portfolio").Range("A2:A" & lastRow).Value For i = 2 To UBound(stocks) + 1 Debug.Print Range("A" & i).Value stockId = Worksheets("Portfolio").Range("A" & i).Value IE.navigate ("https://tw.stock.yahoo.com/q/q?s=" & stockId) Do While IE.Busy Application.Wait DateAdd("s", 1, Now) Loop Set myTable = IE.Document.GetElementsByTagName("table")(2) Set stockSheet = Worksheets(CStr(stockId)) Debug.Print stockSheet.Name stockSheet.Cells(2, "A").Value = Date stockSheet.Cells(2, "B").Value = myTable.GetElementsByTagName("td")(8).innerText stockSheet.Cells(2, "C").Value = myTable.GetElementsByTagName("td")(9).innerText stockSheet.Cells(2, "D").Value = myTable.GetElementsByTagName("td")(10).innerText stockSheet.Cells(2, "E").Value = myTable.GetElementsByTagName("td")(7).innerText stockSheet.Cells(2, "F").Value = myTable.GetElementsByTagName("td")(6).innerText stockSheet.Cells(2, "G").Value = myTable.GetElementsByTagName("td")(5).innerText Next i IE.Quit End Sub ``` --- # 透過 Web API 截取股價資料 --- # 先説一下 API 是什麽？ **Application Programming Interface (API)** 應用程式界面 可以想象成程式 / 系統的界面，不過這個界面並非是給人使用，而是讓其它程式使用的 其目的就是希望不同語言/架構/廠商寫的程式，若能夠有一些共同的界面， 彼此之間就可以互相串連起來，達成共享資料或是擴充功能的目的 --- # API 在今天... 普偏會被直接理解成 "網頁程式的 API"， 這個世界其實就是由許多 API 透過 HTTP 這個網路的溝通協定串起來的。 --- # 來看一下我們這堂課會使用的 Fugle API 連結：https://developer.fugle.tw/realtime/document --- ## 這節課的福利：增送 Fugle API 優惠序號 請各位同學看一下這個 Google SpreadSheet: https://docs.google.com/spreadsheets/d/1QaC4pi3KbwGL_IhWVIEwYgLyN53dNLvOFK353rJkfIA/edit?usp=sharing 1. 請找到工作表一與你的 email 相對應的字串，這是你的優惠序號 --- ## 這節課的福利：增送 Fugle API 優惠序號 2. 請找到工作表一與你的 email 相對應的字串，這是你的優惠序號 3. 請注冊並且登入 Fugle，需要 Email 與手機號碼：  --- ## 這節課的福利：增送 Fugle API 優惠序號 4. 請找到工作表二你的名字相對應的字符，將字符放到工作表一優惠序號的最後，變成一個長度為20的字串，這是你的完整的優惠序號 5. 請登入 Fugle，設定 > 我的優惠，輸入你的優惠序號：  --- ## 這節課的福利：增送 Fugle API 優惠序號 6. 接下來請回到 https://developer.fugle.tw/realtime/document#/ 切換到 API Token 分頁，點擊 Apply for new token 按鈕便會產生 api_token  --- # 截取資料 ```vb Sub testAPI() Dim URL, HTMLsourcecode, GetXml Dim httpsource As Object Set HTMLsourcecode = CreateObject("htmlfile") Set httpsource = CreateObject("msxml2.xmlhttp") URL = "https://api.fugle.tw/realtime/v0/intraday/quote?symbolId=2884&apiToken=demo" httpsource.Open "GET", URL, False httpsource.send Debug.Print httpsource.ResponseText Debug.Print TypeName(httpsource.ResponseText) End Sub ``` --- # 什麼是 JSON? - JSON（JavaScript Object Notation，JavaScript 物件表示法） - 以純文字為基礎，來儲存和交換簡單結構的輕量級「資料交換格式」 - 獨立於語言 一個由零或多個 **key (鍵)** 與 **value (值)**對所構成的**無序結構** ```javascript { "foo": "bar" } ``` --- # JSON 用更白話一點的方法來解釋，請想象一下**貨櫃**這個容器，今天任何**商品能夠被放入貨櫃，它就能夠被海運** 同理，今天**任何資料能夠被封裝進 JSON 這個格式，就能夠被網路傳送，而且能夠被任何一個語言寫成的程式快速的解析與處理** --- # JSON 讀取 JSON 格式的資料，我們需要一個第三方程式庫 [VBA-JSON](https://github.com/VBA-tools/VBA-JSON) --- # 每一筆值都會對應到一個獨特的鍵，就像是字典一樣 --- # 也可以想象成置物櫃  --- # 一把鑰匙只能取得一個置物櫃裡的東西 --- # 小結： 1. 學習與實作網頁爬蟲是一個**投資報酬率極高的事務** 3. 實作上，最困難的部分在於**解析網頁的 html 結構** 4. 網頁的資料很大的機率都是被封裝在 **table** 這個 html 標簽下 5. 但若今天**網頁改版，原先寫好的爬蟲就有可能截取不到資料** 6. 若追求系統的穩定性，透過 API 截取資料是相對比爬蟲安全的做法 7. 最後就是一個取捨的問題
Thanks for Watching
http://kyosei.ai
Contact: yuyueugene84@gmail.com
Download PDF
