2 similar functions, one caches results, the other doesn't Help please

[Blade Hunter]

Hi guys, just messing about here and playing with the page scraping and I have encountered something odd.

I have 2 functions, very similar in construct, one of them caches results (basically giving me WRONG results) and one works, here is an example:

Sheet1

■	A	B	C
Stock code	Google Price	Reuters Price
MSFT
BTFG	#VALUE!
AAPL

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:142px;"><col style="width:85px;"><col style="width:89px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]2.98[/TD]
[TD="align: right"]
41.01[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]538.79[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]538.79[/TD]
[TD="align: right"]
538.79[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =getgoogprice(A2) C2 =getReutersPrice(A2) B3 =getgoogprice(A3) C3 =getReutersPrice(A3) B4 =getgoogprice(A4) C4 =getReutersPrice(A4) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Here is the code:

Public Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
Alias "DeleteUrlCacheEntryA" _
(ByVal lpszUrlName As String) As Long

Function getGoogPrice(symbol As String) As Variant
    Dim xmlhttp As Object
    Dim strURL As String
    Dim CompanyID As String
    Dim X As String
    Dim sSearch As String, myDIV As Variant, myPrice As String
    strURL = "http://www.google.com/finance?q=" & symbol
    DeleteUrlCacheEntry (strURL)
    Set xmlhttp = CreateObject("msxml2.xmlhttp")
    With xmlhttp
        .Open "get", strURL, False
        .Send
        X = .responseText
    End With
    Set xmlhttp = Nothing
    symbol = UCase(symbol)
    myDIV = Split(X, "")
    myPrice = Right(Right(myDIV(34), Len(myDIV(34)) - InStr(1, myDIV(34), "id=ref")), Len(Right(myDIV(34), Len(myDIV(34)) - InStr(1, myDIV(34), "id=ref"))) - InStr(1, Right(myDIV(34), Len(myDIV(34)) - InStr(1, myDIV(34), "id=ref")), ">"))
    getGoogPrice = myPrice
End Function

Function getReutersPrice(symbol As String) As Variant
    Dim xmlhttp As Object
    Dim strURL As String
    Dim CompanyID As String
    Dim X As String
    Dim sSearch As String, myDIV As String, myPrice As String
     
    strURL = "http://www.reuters.com/finance/stocks/overview?symbol=" & symbol 'NESN.VX"
    DeleteUrlCacheEntry (strURL)
    Set xmlhttp = CreateObject("msxml2.xmlhttp")
    With xmlhttp
        .Open "get", strURL, False
        .Send
        X = .responseText
    End With
    Set xmlhttp = Nothing
    sSearch = "sectionQuoteDetail"
    myDIV = Mid(X, InStr(1, X, sSearch) + Len(sSearch))
    myDIV = Trim(Mid(myDIV, 1, InStr(1, myDIV, "") - 1))
    Y = Split(myDIV, "")
    myPrice = Mid(Y(1), InStrRev(Y(1), ">") + 1)
    myPrice = Replace(myPrice, Chr(13), "")
    myPrice = Trim(Replace(myPrice, Chr(9), ""))
    getReutersPrice = myPrice
End Function

Why is the Google one caching results but the Reuters one isn't?

 

[Blade Hunter]

FYI, This is what the results "should" be:

Excel Workbook
	A	B	C
1	Stock code	Google Price	Reuters Price
2	MSFT	41.01	41.01
3	BTFG	2.98	#VALUE!
4	AAPL	538.79	538.79
Sheet1