PDA

View Full Version : [SOLVED] Web query help



thrain
04-18-2012, 12:16 AM
Hello everyone.

Is it possible to grab a value on a webpage using VBA? I've had success querying yahoo finance and grabbing CSV files, but I don't know how to grab a value on a page.

For example:

This page is updated everyday:
w w w dot bloomberg dot com/quote/SPVXSTR:IND

The value I want to get is the price, which as of today is:
7,307.06000


Any and all help is greatly appreciated. Thank you!

Crocus Crow
04-18-2012, 04:56 AM
Use a web query to import the entire page, then reference or find the cell containing the price. Do this manually with the macro recorder to generate the VBA as a starting point if you want to automate this.

p45cal
04-18-2012, 07:48 AM
try:
7882
backed up by a udf:


Function GetPrice(symb)
Const PriceTag = "span class="" price"">"
Dim oHttp As Object, txt$, i&, j&
On Error Resume Next
Set oHttp = CreateObject("MSXML2.XMLHTTP")
If Err <> 0 Then Set oHttp = CreateObject("MSXML.XMLHTTPRequest")
If oHttp Is Nothing Then MsgBox "MSXML2.XMLHTTP not found", 16, "Error": Exit Function
On Error GoTo 0
With oHttp
.Open "GET", "http://www.bloomberg.com/quote/" & symb, False
.Send
txt = .responseText
i = InStr(1, txt, PriceTag, 1)
If i = 0 Then
GetPrice = "PriceTag not found"
Else
i = i + Len(PriceTag)
j = InStr(i, txt, "<", 0)
GetPrice = Trim(Application.Clean(Mid(txt, i, j - i))) * 1
End If
End With
Set oHttp = Nothing
End Function

stolen from
http://www.vbaexpress.com/forum/showpost.php?p=207788&postcount=4

I fancy there may be a buffer to flush for repeated access.
I may also explore getelementbyid if I get time.

thrain
04-18-2012, 09:46 AM
Crocus and p45cal -- thank you very much!

This is a great start.