而不是从HTML中提取它,该网站有一个应该更快地工作的api访问。
请注意,我用下面的代码早期绑定,但你总是可以切换到后期绑定,如果你需要。
另外,我选择回到双方最后交易和交易时间(UTC时间)。我使用正则表达式来提取这些。
还有其他可以使用的公共API调用。例如,如果您只对最后一笔交易的价格感兴趣,而不是时间,则可以获取股票信息。
结果写入A1和B1,但您可以设置一个例程,将结果顺序写入后续行。
也许是这样的:
Option Explicit
Sub LastTrade()
'Microsoft Windows HTTP Services 5.1
'Microsoft VBScript Regular Expressions 5.5
Dim httpRequest As WinHttpRequest
Dim sResponse As String
Dim sInfo As String
Dim RE As RegExp, MC As MatchCollection
Dim D As Double
Const sUrl As String = "https://api.kraken.com/0/public/Trades"
sInfo = "?pair=XBTUSD"
Set httpRequest = New WinHttpRequest
httpRequest.Open "Get", sUrl & sInfo
httpRequest.Send
httpRequest.WaitForResponse
sResponse = httpRequest.ResponseText
Set RE = New RegExp
With RE
.Global = False
.IgnoreCase = False
.Pattern = "\[""(\d+\.\d+)"",[^,]+,(\d+\.\d+)[^]]+]],""last"""
If .Test(sResponse) = True Then
Set MC = .Execute(sResponse)
[a1].NumberFormat = "$#,###.000"
[a1] = MC(0).SubMatches(0)
D = MC(0).SubMatches(1) 'Unix time
D = D/86400 + CDbl(#1/1/1970#)
With [b1]
.NumberFormat = "dd-mmm-yyyy hh:mm:ss"
.Value = D
End With
Else
[a1] = Right(sResponse, 100)
End If
End With
Set httpRequest = Nothing
End Sub