Expanding the code ? (Get Data from Website)

**KomicJ** · 09-05-2015, 06:14 PM

Hey guys,

First and foremost, I need to say a big thank you to this community. Been a silent reader most of the time, but you have no idea how helpful you've been so far for this project of mine. So a big thumb up to every single one of you.

Now to be honest, VBA still looks like chinese to me most of the time. Sometimes I feel like I understand what I'm doing, then everything falls apart and I realize that I do not. This is one of those case where I have absolutely no idea how to edit the code I have, so that it'll react the way I'm looking for. And not quite sure where to start my research. Not even sure if it's just a simple tweak, or something that would take days to edit.

Now I understand how specific my problem is, so feel free to completely disregard this topic if you feel like this is way too big of a task to be discussed here. But if you have any pointers whatsoever, I'll be all eyes and all ears.

Here's the deal.

I have this tab, called 'GET - Investing' in which I'd like to retrieve very specific data from a bunch of different pages (all with the exact same layout) of Investing.com website. The layout of this tab goes as follow;

Column A = Symbols (manually entered by the user, it's just a reference that I'll use later on for a VLOOKUP on my report sheet)
Column B = URLs (manually entered by the user, to determine the pages we need)
*These two columns don't need any changes, or to be affected by the macro.

Column C = Moving Average Text (Data from website - Strong Buy, Buy, Neutral, Sell or Strong Sell)
Column D = Moving Average "Buy" (Data from website - Numerical value from 1 to 12)
Column E = Moving Average "Sell" (Data from website - Numerical value from 1 to 12)
Column F = Technical Indicator Text (Data from website - Strong Buy, Buy, Neutral, Sell or Strong Sell)
Column G = Technical Indicator "Buy" (Data from website - Numerical value from 1 to 12)
Column H = Technical Indicator "Sell" (Data from website - Numerical value from 1 to 12)
Column I = Formula to calculate the number of "Neutral" technical indicators, doesn't need to be touched

Now, someone here (Hey Rkey !) has been kind enough to give me a code that seems to retrieve exactly the infos that I need. The results on the Debug.Print screen are precisely what I'm looking for. The VBA code is:

Please Login or Register  to view this content.

Debug Screen shows the following...

Please Login or Register  to view this content.

I just don't know what to do with these results now. Tried a couple of things, but being the newbie that I am, nothing seems to work. So what I'm looking for basically is...

a) Instead of a fixed URL, contained within the VBA code itself, is there a way to use the URL contained in Column B (starts at B3) to retrieve the same infos from that page ? I'm pretty sure it's a simple change at the beginning of the macro, but have no idea what the syntax would be.

b) How do I assign the results to the correct cell, on the same line. Bascially, say the URL is in B3, how do I transfer the "Strong Sell" indicator to C3, "maBuy" to D3, "maSell" to E3, and so on...

c) Then at the end, how do I loop this routine, so that it will start from the URL contained in B3, and go down the list for B4, B5, B6, applying the correct results for each, on the appropriate line (so B4 results to C4 through H4, B5 results to C5 through H5)...and loop that routine until there's no more URL in column B to be treated ?

I have something that works right now, but it's an entirely different way of doing it, and it takes up to 10 minutes to retrieve that data from 57 pages...mainly because (well, I think) it copies the whole page instead of extracting the portion of data I need. Which is why I went back to this macro, because I have a feeling it might make things a lot faster. But it's SO above my level...

So if you feel like helping me out, I'll be forever grateful. If not, I still like you...like I said, most of you have been very helpful already.

A silent reader in need of help.

KJ

**Marc L** · 09-05-2015, 07:21 PM

Hi !

Without an attached workbook, how can be try to loop URLs ?‼ …

**KomicJ** · 09-05-2015, 07:39 PM

See, that's my inexperience showing, right there.

Since all the URLs are written in a table, I thought it would as easy as "Done with B3, move on to B4". There are 57 links right now, but the plan is to add more as time goes by. So it would have to loop as long as there are URLs in column B.

**Marc L** · 09-05-2015, 11:38 PM

PHP Code:


Sub Demo() 
    Const HTTP = "http://" 
      Dim oReq As Object, SP$(), VA(1 To 6) 
    Application.Goto Sheet1.Cells(1), True 
 
    With Cells(1).CurrentRegion.Rows 
      If .Count < 3 Then Beep: Exit Sub 
      .Item("3:" & .Count).Columns("C:H").ClearContents 
      Set oReq = CreateObject("MSXML2.XMLHttp") 
            L& = 1 
        For R& = 3 To .Count 
            If R > L Then 
                ActiveWindow.ScrollRow = L 
                With ActiveWindow.VisibleRange.Rows:  L& = .Item(.Count).Row - 1:  End With 
            End If 
            With .Cells(R, 2) 
                If .Value > "" Then 
                    oReq.Open "GET", IIf(Left(.Value, 7) = HTTP, "", HTTP) & .Value, False 
                    oReq.SetRequestHeader "DNT", "1" 
                    On Error Resume Next 
                    oReq.Send 
                    On Error GoTo 0 
                    If oReq.Status = 200 Then 
                                  SP = Split(oReq.ResponseText, "\"" title=\""") 
                        If UBound(SP) > 1 Then 
                            VA(1) = Split(Split(SP(1), """>")(1), "<")(0) 
                            VA(2) = Split(Split(Split(SP(1), " id=""maBuy"" ")(1), ">(")(1), ")")(0) 
                            VA(3) = Split(Split(Split(SP(1), " id=""maSell"" ")(1), ">(")(1), ")")(0) 
                            VA(4) = Split(Split(SP(2), """>")(1), "<")(0) 
                            VA(5) = Split(Split(Split(SP(2), " id=""tiBuy"" ")(1), ">(")(1), ")")(0) 
                            VA(6) = Split(Split(Split(SP(2), " id=""tiSell"" ")(1), ">(")(1), ")")(0) 
                            .Offset(, 1).Resize(, 6).Value = VA 
                        End If 
                    End If 
                End If 
            End With 
        Next 
      Set oReq = Nothing:  ActiveWindow.ScrollRow = 1 
    End With 
End Sub

Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

**KomicJ** · 09-06-2015, 08:21 AM

If I like it ? I think I may have found myself a new god to worship !

I'm running a couple of tests right now, but so far...it does EXACTLY what I was looking for. Thank you SOOOO much ! You've just made my life at least 1000 times easier !

**Marc L** · 09-06-2015, 09:26 AM

Thanks for the rep' !

A word about your attached workbook :

• column B URLs displays french website addresses but sometimes hyperlinks point to english website …

• For a better design, remove this column B and place hyperlinks (try to uniformize english or french) on column A symbols.
Will need a code slight mod to directly read hyperlinks instead of cells values …

**KomicJ** · 09-06-2015, 09:38 AM

Regarding column B, it's already done. I erased them all, and put them back one-by-one, all in french (which is what my partner uses).

I kept column B, but hid it. Column A is gonna be used for a VLOOKUP formula on another sheet, and I wasn't sure how it would react to a hyperlink. Probably would've been fine, but I didn't want to chance it. Plus, it'll be easier for my partner if he can just copy/paste the URL in its own column.

I'll make sure to click the reputation button on your code post, but looks like I have to spread some love around first (mistakenly clicked on your first reply, and now get a message saying: "You must spread some Reputation around before giving it to Marc L again." when I try to click on the one with the code). I'll take care of that, as soon as I'm done typing this...which is now !

Thanks you SO much again.

**Marc L** · 09-06-2015, 12:49 PM

I forgot the VLookup formula so, yes, you're right to keep column B …

In fact I saw after my last post you gave me rep' on my first post.
Thanks again for the reps'!

Expanding the code ? (Get Data from Website)

LinkBack

Thread Tools

Rate This Thread

Display

Expanding the code ? (Get Data from Website)

Re: Expanding the code ? (Get Data from Website)

Re: Expanding the code ? (Get Data from Website)

Try this demonstration !

Re: Try this demonstration !

Re: Expanding the code ? (Get Data from Website)

Re: Expanding the code ? (Get Data from Website)

Re: Expanding the code ? (Get Data from Website)

Thread Information

Users Browsing this Thread

Similar Threads

this code grabbing the whole website data of the url but i need only required data

Expanding Loop to get all the data from website into Excel

Code to copy data from excel to website search box and run it one by one

Macro/VBA code for COLLECT DATA FROM VARIOUS PAGES OF ONE WEBSITE..

Parse Data from HTML Code / Website to Excel

VBA code to arrange data obtain from website

Expanding data range in code

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions