自動化查詢基金淨值的VBA腳本詳解

高效且準確地從網頁抓取指定日期的基金淨值數據

主要重點

• 自動化流程設計：通過VBA腳本自動化查詢和填充Excel中的基金淨值數據。
• 動態URL構建：根據不同區域（境內或境外）動態構建查詢URL，確保數據準確抓取。
• 錯誤處理與優化：引入錯誤處理機制和查詢間隔，提升腳本穩定性和效率。

一、VBA腳本的基本結構與功能概述

1.1 目標與需求分析

在現代投資管理中，定期更新基金淨值（NAV）是非常重要的一環。手動查詢和輸入淨值數據不僅耗時，而且容易出錯。為此，本腳本旨在通過VBA自動化，根據Excel中的基金代碼、查詢日期和區域，從指定的網頁中抓取當天的淨值並填入相應的單元格。

1.2 總體流程

腳本的主要流程包括：

• 遍歷Excel表格中的每一行數據
• 根據區域動態構建查詢URL
• 發送HTTP請求並獲取網頁內容
• 解析HTML以提取指定日期的淨值
• 將淨值填入C欄
• 處理可能出現的錯誤並優化查詢效率

二、VBA腳本實現細節

2.1 環境設置與參考庫

在開始編寫VBA腳本之前，確保Excel中已經啟用了必要的參考庫。這些參考庫包括：

• Microsoft XML, v6.0：用於發送HTTP請求
• Microsoft HTML Object Library：用於解析HTML內容

可以通過VBA編輯器中的「工具」->「參考設定」來啟用這些庫。

2.2 VBA腳本代碼詳解

以下是完整的VBA腳本，實現根據基金代碼和日期自動查詢淨值並填充到Excel中的C欄：

Sub 查詢基金淨值()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    For i = 2 To lastRow
        Dim fundCode As String
        Dim queryDate As String
        Dim region As String
        Dim baseUrl As String
        Dim url As String
        Dim netValue As String
        
        ' 讀取A欄標的代碼，B欄查詢日期，F欄區域，H欄基礎網址
        fundCode = Trim(ws.Cells(i, "A").Value)
        queryDate = Format(ws.Cells(i, "B").Value, "yyyy/mm/dd")
        region = Trim(ws.Cells(i, "F").Value)
        baseUrl = Trim(ws.Cells(i, "H").Value)
        
        ' 根據區域動態構建URL
        If region = "境內" Then
            url = Replace(baseUrl, "{code}", fundCode)
        ElseIf region = "境外" Then
            url = Replace(baseUrl, "{code}", fundCode)
        Else
            ws.Cells(i, "C").Value = "區域錯誤"
            GoTo NextIteration
        End If
        
        ' 發送HTTP請求
        Dim http As Object
        Set http = CreateObject("MSXML2.XMLHTTP.6.0")
        On Error GoTo ErrorHandler
        http.Open "GET", url, False
        http.send
        
        If http.Status = 200 Then
            ' 解析HTML內容
            Dim htmlDoc As Object
            Set htmlDoc = CreateObject("HTMLFile")
            htmlDoc.body.innerHTML = http.responseText
            
            ' 提取淨值
            netValue = ExtractNetValue(htmlDoc, queryDate)
            
            If netValue <> "" Then
                ws.Cells(i, "C").Value = netValue
            Else
                ws.Cells(i, "C").Value = "無資料"
            End If
        Else
            ws.Cells(i, "C").Value = "連線錯誤"
        End If
        
        ' 延遲以防止過快請求被封鎖
        Application.Wait Now + TimeValue("0:00:01")
        
NextIteration:
        On Error GoTo 0
    Next i
    
    MsgBox "所有淨值查詢完成！", vbInformation
    Exit Sub
    
ErrorHandler:
    ws.Cells(i, "C").Value = "查詢失敗"
    Resume NextIteration
End Sub

Function ExtractNetValue(htmlDoc As Object, queryDate As String) As String
    Dim tables As Object
    Set tables = htmlDoc.getElementsByTagName("table")
    
    Dim tbl As Object
    Dim tr As Object
    Dim td As Object
    Dim found As Boolean
    found = False
    
    For Each tbl In tables
        For Each tr In tbl.getElementsByTagName("tr")
            Dim cells As Object
            Set cells = tr.getElementsByTagName("td")
            If cells.Length >= 2 Then
                Dim dateText As String
                dateText = Trim(cells(0).innerText)
                If dateText = queryDate Then
                    ExtractNetValue = Trim(cells(1).innerText)
                    found = True
                    Exit Function
                End If
            End If
        Next tr
        If found Then Exit For
    Next tbl
    
    ExtractNetValue = ""
End Function
  

2.3 代碼功能解析

• 遍歷數據行：腳本從第二行開始，遍歷到最後一行，依次讀取每一行的基金代碼、查詢日期、區域和基礎URL。
• URL構建：根據區域（境內或境外），將基金代碼動態插入到基礎URL中，生成具體查詢URL。
• HTTP請求與回應處理：使用`MSXML2.XMLHTTP.6.0`對構建的URL發送GET請求，並檢查回應狀態碼。
• HTML解析：將獲取的HTML內容加載到`HTMLFile`對象中，並通過遍歷表格和行來查找匹配的查詢日期和淨值。
• 錯誤處理：腳本包括簡單的錯誤處理機制，將失敗的查詢標記為「查詢失敗」或「連線錯誤」。
• 延遲設置：在每次請求後引入1秒的延遲，防止因過快請求導致IP被封鎖。

三、動態URL構建與區域處理

3.1 根據區域動態調整URL

在腳本中，根據F欄的區域（境內或境外），需要動態調整查詢URL的構建方式。例如：

在這些URL中，`{code}`是需要被替換為具體的基金代碼的占位符。腳本通過`Replace`函數將`{code}`替換為A欄的基金代碼，從而生成具體的查詢URL。

3.2 URL模板設計

設計URL模板時，需要確保模板能夠靈活應對不同區域和基金代碼的組合。例如：

• 境內基金：URL模板可能來自東方財富網，如`http://fundf10.eastmoney.com/jbgk_{code}.html`。
• 境外基金：URL模板可能來自其他金融網站，如`http://example.com/fund/{code}/nav`。

通過這種模板設計，腳本可以適應不同的URL結構，確保查詢的靈活性和可擴展性。

四、HTML解析與淨值提取

4.1 HTML結構分析

為了準確提取指定日期的淨值，需要分析目標網頁的HTML結構。通常，基金淨值會以表格（`

<table>
  <tr>
    <td>2025/01/21</td>
    <td>10.42</td>
  </tr>
  <!-- 更多行 -->
</table>
  

4.2 提取函數解析

在VBA腳本中，`ExtractNetValue`函數負責遍歷所有的表格，並在每個表格中查找匹配的日期，以提取對應的淨值：

Function ExtractNetValue(htmlDoc As Object, queryDate As String) As String
    Dim tables As Object
    Set tables = htmlDoc.getElementsByTagName("table")
    
    Dim tbl As Object
    Dim tr As Object
    Dim td As Object
    Dim found As Boolean
    found = False
    
    For Each tbl In tables
        For Each tr In tbl.getElementsByTagName("tr")
            Dim cells As Object
            Set cells = tr.getElementsByTagName("td")
            If cells.Length >= 2 Then
                Dim dateText As String
                dateText = Trim(cells(0).innerText)
                If dateText = queryDate Then
                    ExtractNetValue = Trim(cells(1).innerText)
                    found = True
                    Exit Function
                End If
            End If
        Next tr
        If found Then Exit For
    Next tbl
    
    ExtractNetValue = ""
End Function
  

這個函數首先獲取所有的表格元素，然後遍歷每一行，檢查第一個單元格（日期）是否與查詢日期匹配。如果找到匹配的行，則提取第二個單元格中的淨值。

4.3 處理不同的HTML結構

不同的基金網站可能具有不同的HTML結構。為了提高腳本的通用性，建議對主要的基金網站進行詳細分析，並根據需要調整提取邏輯。例如，如果某些網站的淨值信息包含在特定的`

' 延遲以防止過快請求被封鎖
Application.Wait Now + TimeValue("0:00:01")
  

6.2 查詢結果示例

運行腳本後，C欄將自動填充對應的淨值，如下所示：

七、結論

通過本詳細指南，您可以輕鬆地編寫並運行VBA腳本，自動化查詢並填充Excel中的基金淨值數據。這不僅提高了工作效率，還減少了手動輸入可能帶來的錯誤。隨著財務數據的不斷變化，這樣的自動化工具將成為現代投資管理中不可或缺的一部分。

參考資料

• VBA Web Scraping to Excel (Step-by-Step Guide) - Oxylabs
• Trying to extract ONE value from a webpage with VBA in Excel
• Extract data from a webpage and display it on a cell - Mr. Excel
• Is this possible in VBA? Search a website based on data in a cell ...
• What's a VBA code for looking up the contents of a cell on a web ...