Get Data from Website (and probably Isolate Value)

Hey guys,

First time I've ever posted here, so please be gentle.

I'm trying to get data from a website, more specifically Investing.com. I tried to do so using the webquery tool, but it only allows me to import the whole page, not the sections I need. Which makes it a terribly long process, considering I would have to load at the very least 36 different pages, just to get those 4-6 valuable infos. So my guess is VBA/Macro would probably be the better option, but I'm not even sure it'll do.

Link: http://www.investing.com/etfs/horizo...l?period=86400

What I'm trying to do basically is to get the "Summary" part of the page, where it shows the Moving Averages, and Technical Indicators, and isolate six values so that I can use them after, in my report sheet. I need the two quotes (ie. Strong Sell, Sell, Buy, etc.) and the four indicators, but only the numbers.

Now I know how to do a bunch of little 'commun' things with Excel, but I've only been playing with VBA/Macros for a week. I've been able to do a bunch of cool little things so far (force entry in cap letters, and getting stocks info from Yahoo.com, thanks to a very cool tutorial), but this is probably way out of my league. However, I have no problem spending time doing my own research. I just didn't find anything so far, which makes me think that what I'm trying to do may not be possible.

So if someone here with more knowledge would be kind enough to take a glimpse at the page, and see if it can be done or if I'm just wasting my time trying, that would be a start. And if you have in mind a few terms that I should look for on the internet to achieve such a task, that would be greatly appreciated.

Thanks

KJ.

Hi KJ, and welcome to the forum.

You can try this script to get you going, the output is almost what you wanted.
Please keep in mind the macro will be based on a 3rd parties website, so you'll be dependant on their layout...
See also the URL in the comments, it would help you explain the subject even more.

Sub test()
    URL = "http://www.investing.com/etfs/horizons-betapro-tsx-60-bull-plus-technical?period=86400"
' Create and Send HTTP req
    Set objHttp = CreateObject("Microsoft.XMLHTTP")
    objHttp.Open "GET", URL, False
    objHttp.setRequestHeader "Content-Type", "text/xml"
    objHttp.send
' Handle Response
    sHTML = objHttp.ResponseText
'see http://scriptorium.serve-it.nl/view.php?sid=40 for more info about extracting the info
'Extract the desired information from the returned HTML code (text)
'To make things a little easier I usually cut of most of the unwanted code first
'so sHTML is smaller to work with.
    lTopicstart = InStr(1, sHTML, "<!-- ---------Summary Box----------------- -->", vbTextCompare)
    lTopicend = InStr(1, sHTML, "<!-- Tables -->", vbTextCompare)
    sHTML = Mid(sHTML, lTopicstart, lTopicend - lTopicstart)
'Now extract all text within the hyperlinks <a href..>..</a>
'because they represent the topics
    i = 1
    lTopicstart = 1
    lTopicend = 1
    Do While lTopicstart <> 0
        i = i + 1
        lTopicstart = InStr(lTopicend, sHTML, "<span class", vbTextCompare)
        If lTopicstart <> 0 Then
            lTopicstart = InStr(lTopicstart, sHTML, ">", vbTextCompare) + 1
            lTopicend = InStr(lTopicstart, sHTML, "</span>", vbTextCompare)
            sAllPosts = sAllPosts & Chr(13) & Mid(sHTML, lTopicstart, lTopicend - lTopicstart)
        End If
    Loop
'Clean up
    Set oHttp = Nothing
    Debug.Print sAllPosts
End Sub

Cheers,
Rick

You guys are awesome !

Can't test it right now, but I will as sure as I get back home. But just the fact that took the time to help me out...thank you !