I have a bunch of Functions that pull in financial data from Yahoo Finance. I've been using Internet Explorer objects, but that was slow and quirky. I recently discovered that the Windows HTTP service has its own object model that you can directly address from VBA: "WinHttp.WinHttpRequest.5.1"
This is WAY faster then using Explorer, and it's object model lets you adjust things like time outs and so forth. I love it.
However, its brought me to a deeper question, a more philosohpical programming issue. Maybe some of you hard core programmer types can shed some light.
If you need to use an Automation Object several times in one routine, is it better to just reuse the Object, or to expressly set it to "= nothing" and then re-create it? Does it matter?
The piece of code below pulls historically-adjusted closing-prices for stocks and mutual funds. Since the market is only only about 4.5 days a week, if there is no closing price for a particular date, it adjusts the date back by one day and tries again. It runs through a for loop, giving up afte r 7 days. This is why I need to reuse the Object.
Note that I'm using late binding becuase I am not sure whether or not the Object model is available on all potential end-users' machines.
[vba]Function YFGetPrice(Ticker As String, Price_Date As Date) As Currency
Dim URL As String
Dim strHTML As String
Dim StartPos As Long
Dim EndPos As Long
Dim CellNo As Integer
Dim x As Integer
Dim NewDate As Date
Dim WinHTTP As Object
For x = 0 To 7
Let NewDate = DateAdd("d", -x, Price_Date)
Let URL = "http://finance.yahoo.com/q/hp?s= + Ticker + Date"
GetHTTP:
On Error Resume Next
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
If WinHTTP Is Nothing Then
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5")
End If
On Error GoTo HTTPErr
With WinHTTP
.Open "GET", URL
.Send
strHTML = .ResponseText
End With
Set WinHTTP = Nothing
On Error GoTo 0
'data is in string then extract it and end function, else loop
If InStr(1, strHTML, yfNoAvail, vbTextCompare) = 0 Then
'EXTRACT PRICE - Code removed
Let YFGetPrice = Mid(strHTML, StartPos, EndPos - StartPos + 1)
Exit Function
End If
Next x
Exit Function
HTTPErr:
If Err = -2147012867 Then
Set WinHTTP = Nothing
Resume GetHTTP
End If
Set WinHTTP = Nothing
Err.Raise Err
End Function
[/vba]
Here is the same code, but with reusing the Object Variable:
[vba]On Error Resume Next
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
If WinHTTP Is Nothing Then
Set WinHTTP = CreateObject("WinHttp.WinHttpRequest.5")
End If
On Error GoTo 0
For x = 0 To 7
Let NewDate = DateAdd("d", -x, Price_Date)
'Set URL
Let URL = "http://finance.yahoo.com/q/hp?s= + Ticker + Date"
GetHTTP:
On Error GoTo HTTPErr
With WinHTTP
.Open "GET", URL
.Send
strHTML = .ResponseText
End With
On Error GoTo T0
'data is in string then extract it and end function, else loop
If InStr(1, strHTML, yfNoAvail, vbTextCompare) = 0 Then
'EXTRACT PRICE - Code removed
Set WinHTTP = Nothing
Let YFGetPrice3 = Mid(strHTML, StartPos, EndPos - StartPos + 1)
Exit Function
End If
Next x
Set WinHTTP = Nothing
Exit Function
HTTPErr:
If Err = -2147012867 Then Resume GetHTTP
Set WinHTTP = Nothing
Err.Raise Err[/vba]
After some time testing (with a few hundred repitions each), it does not appear that one method is consistently faster then the other, which was VERY surpirising to me.
Comments, feedback appreciated.
Reply With Quote
