Web query help

[thrain]

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]

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]

try:
2012-04-18_154041.jpg
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/show...88&postcount=4

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

[thrain]

Crocus and p45cal -- thank you very much!

This is a great start.