ajn946946
01-24-2011, 01:13 AM
Hey Guys,
I am trying to use code posted here on Excel 2011 but it does not return a value when the Windows version works perfectly.
http://www.vbaexpress.com/forum/showthread.php?t=32704
Public 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
strURL = "http://www.google.com/finance?q=" & symbol
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "get", strURL, False
.send
x = .responsetext
End With
Set xmlhttp = Nothing
'find goolge's "Company ID" they assign to a symbol
sSearch = "_companyId ="
CompanyID = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
CompanyID = Trim(Mid(CompanyID, 1, InStr(1, CompanyID, ";") - 1))
'Use the company ID to retrieve data needed
'Here is an example of the last price:
'example: <span id="ref_14135_l">15.79</span>
sSearch = "ref_" & CompanyID & "_l"">"
getGoogPrice = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
getGoogPrice = Left(getGoogPrice, InStr(1, getGoogPrice, "<") - 1)
'Examine the Page Source to find the Span ID's for the other bits you want
'They all seem to use the company id
End Function
Would anyone have any ideas as to why?
I am trying to use code posted here on Excel 2011 but it does not return a value when the Windows version works perfectly.
http://www.vbaexpress.com/forum/showthread.php?t=32704
Public 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
strURL = "http://www.google.com/finance?q=" & symbol
Set xmlhttp = CreateObject("msxml2.xmlhttp")
With xmlhttp
.Open "get", strURL, False
.send
x = .responsetext
End With
Set xmlhttp = Nothing
'find goolge's "Company ID" they assign to a symbol
sSearch = "_companyId ="
CompanyID = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
CompanyID = Trim(Mid(CompanyID, 1, InStr(1, CompanyID, ";") - 1))
'Use the company ID to retrieve data needed
'Here is an example of the last price:
'example: <span id="ref_14135_l">15.79</span>
sSearch = "ref_" & CompanyID & "_l"">"
getGoogPrice = Mid(x, InStr(1, x, sSearch) + Len(sSearch))
getGoogPrice = Left(getGoogPrice, InStr(1, getGoogPrice, "<") - 1)
'Examine the Page Source to find the Span ID's for the other bits you want
'They all seem to use the company id
End Function
Would anyone have any ideas as to why?