|
歡迎來到AiExcel！這里是您的Excel 知識(shí)充電站。 前言在日常工作中，許多場(chǎng)景需要獲取外部數(shù)據(jù)，并在 Excel 中進(jìn)行處理和分析。VBA 提供了強(qiáng)大的功能，能夠通過網(wǎng)絡(luò)接口自動(dòng)獲取所需的實(shí)時(shí)數(shù)據(jù)，幫助我們輕松完成各種自動(dòng)化任務(wù)。 本文將展示如何使用 VBA 編寫自定義函數(shù)，調(diào)用新浪財(cái)經(jīng) API 來獲取股票行情的實(shí)時(shí)數(shù)據(jù)。 一、編寫函數(shù)代碼1. 獲取實(shí)時(shí)數(shù)據(jù)的VBA函數(shù)以下代碼通過新浪財(cái)經(jīng) API 獲取指定股票的行情數(shù)據(jù)，包括股票名稱、當(dāng)前價(jià)格、開盤價(jià)等。 ' 函數(shù)：獲取指定股票信息
' StockCode：股票代碼（如 'sh601006'）
' Num：指定要提取數(shù)據(jù)的索引
'0 ------股票名稱
'1 ------今日開盤價(jià)
'2 ------昨日收盤價(jià)
'3 ------現(xiàn)價(jià)
'4 ------最高價(jià)
'5 ------最低價(jià)
'……
Function getStockInfo(ByRef StockCode As String, Num As Integer) As String
Dim Url As String, strData As String, StockData As Variant
' 構(gòu)建 API 請(qǐng)求的 URL
Url = 'http://hq./list=' & StockCode
' 發(fā)送 HTTP 請(qǐng)求并獲取響應(yīng)數(shù)據(jù)
strData = GetHttp(Url)
'以sh601006為例返回如下文本
'var hq_str_sh601006=”大秦鐵路, 27.55, 27.25, 26.91, 27.55, 26.20, 26.91, 26.92,22114263,
'589824680, 4695, 26.91, 57590, 26.90, 14700, 26.89, 14300,26.88, 15100, 26.87,3100,
'26.92, 8900, 26.93, 14230, 26.94, 25150, 26.95, 15220, 26.96, 2008-01-11, 15:05:32”;
' 去除回車換行符、雙引號(hào)和末尾的分號(hào)
strData = Replace(Replace(Replace(strData, vbCrLf, ''), '''', ''), ';', '')
' 提取等號(hào)后面的股票數(shù)據(jù)部分
strData = Mid(strData, InStr(strData, '=') + 1)
' 使用逗號(hào)將股票數(shù)據(jù)分割成數(shù)組
StockData = Split(strData, ',')
' 返回指定索引位置的股票信息
getStockInfo = StockData(Num)
End Function
2. 發(fā)送 HTTP 請(qǐng)求獲取股票數(shù)據(jù)在 getStockInfo 函數(shù)中，我們需要通過 HTTP 請(qǐng)求來獲取新浪財(cái)經(jīng)的股票數(shù)據(jù)。GetHttp 函數(shù)負(fù)責(zé)發(fā)送請(qǐng)求并處理響應(yīng)。 ' 函數(shù)：發(fā)送 HTTP 請(qǐng)求并返回響應(yīng)內(nèi)容
Function GetHttp(Url As String) As String
Dim objXML As Object
' 創(chuàng)建 HTTP 請(qǐng)求對(duì)象
Set objXML = CreateObject('WinHttp.WinHttpRequest.5.1')
With objXML
.Open 'GET', Url, False ' 打開同步 GET 請(qǐng)求
.setRequestHeader 'Referer', 'http://finance.sina.com.cn' ' 設(shè)置 Referer，避免被封
.Send ' 發(fā)送請(qǐng)求
' 將二進(jìn)制響應(yīng)體轉(zhuǎn)換為字符串（GB2312編碼）
GetHttp = BytesToBstr(.ResponseBody, 'GB2312')
End With
Set objXML = Nothing ' 釋放對(duì)象
End Function
3. 將字節(jié)流轉(zhuǎn)換為GB2312編碼新浪財(cái)經(jīng)返回的數(shù)據(jù)使用的是 GB2312 編碼，我們需要將其轉(zhuǎn)換為可用的文本格式。BytesToBstr 函數(shù)處理這一編碼轉(zhuǎn)換： ' 函數(shù)：將字節(jié)流轉(zhuǎn)換為指定編碼的字符串
Function BytesToBstr(strBody As Variant, CodeBase As String) As String
Dim objStream As Object
' 創(chuàng)建 Adodb.Stream 對(duì)象，用于處理字節(jié)流并轉(zhuǎn)換為文本
Set objStream = CreateObject('Adodb.Stream')
With objStream
.Type = 1 ' 設(shè)置為二進(jìn)制類型
.Open ' 打開流
.Write strBody ' 寫入字節(jié)流
.Position = 0 ' 將流位置重置到起點(diǎn)
.Type = 2 ' 轉(zhuǎn)換為文本
.Charset = CodeBase ' 設(shè)置字符編碼（如 'GB2312'）
BytesToBstr = .ReadText ' 讀取文本內(nèi)容并返回
.Close ' 關(guān)閉流
End With
Set objStream = Nothing ' 釋放對(duì)象
End Function
二、如何使用在 Excel 中，你可以直接使用 getStockInfo 函數(shù)來獲取實(shí)時(shí)數(shù)據(jù)。例如： getStockInfo(B2, 0) 獲取 sz000001 平安銀行的股票名稱。
--getStockInfo(B2, 3) 獲取股票的實(shí)時(shí)價(jià)格（返回的價(jià)格為文本格式，通過--轉(zhuǎn)換為數(shù)值）。
如果只想輸入 6 位代碼查詢股票信息，你可以使用 IF 函數(shù)來判斷股票代碼的前綴： 獲取 600036 招商銀行的現(xiàn)價(jià)，可以使用以下公式：
--getStockInfo(IF(OR(LEFT(B3, 1)='6', LEFT(B3, 1)='5'), 'sh', 'sz') & B3, 3)
IF函數(shù)將根據(jù)股票代碼自動(dòng)判斷是 sh 還是 sz 交易所，從而生成正確的股票代碼格式。
三、使用的VBA技巧在這一項(xiàng)目中，我們通過編寫函數(shù)來實(shí)現(xiàn)數(shù)據(jù)獲取，并應(yīng)用了幾個(gè)關(guān)鍵的 VBA 編程技巧： 編寫自定義函數(shù)：通過函數(shù)將特定任務(wù)封裝成獨(dú)立的代碼模塊，簡(jiǎn)化了數(shù)據(jù)處理和調(diào)用過程。 動(dòng)態(tài)字符串處理：使用 Replace 函數(shù)刪除不需要的換行符、雙引號(hào)和分號(hào)，確保返回的數(shù)據(jù)格式整潔且易于處理。 HTTP 請(qǐng)求處理：使用 WinHttpRequest 對(duì)象發(fā)送請(qǐng)求并獲取數(shù)據(jù)，模擬瀏覽器請(qǐng)求，避免被服務(wù)器屏蔽。 編碼轉(zhuǎn)換：通過 Adodb.Stream 將二進(jìn)制數(shù)據(jù)轉(zhuǎn)換為文本，確保返回的數(shù)據(jù)能夠正確顯示。 數(shù)組處理：利用 Split 函數(shù)將數(shù)據(jù)分割為數(shù)組，便于提取所需的信息。
結(jié)語通過 VBA 代碼，你可以在 Excel 中輕松獲取外部數(shù)據(jù)，并將其集成到工作流程中。無論是用于數(shù)據(jù)監(jiān)控還是其他自動(dòng)化任務(wù)，這些方法都可以顯著提高效率。如果有需要定時(shí)刷新數(shù)據(jù)，還可以使用 Application.OnTime 方法，實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)更新與實(shí)時(shí)監(jiān)控。
|
