Looping help needed

Question

I have a list of 400 stock symbols in cell A1 of an excel spreadsheet. I then go to a macro and load this website:

https://finviz.com/quote.ashx?t=" & Range("A1").Value

The macro, parsehtml_0 below pulls data into excel from 400 snapshot stock tables. The results load starting in row 1-400 of the spreadsheet.

The problem is that 400 is the limit of snapshot stock tables you can bring in on 1 page and I have many more.

Therefore, I have to make a second macro, parsehtml_1 with 400 more stock symbols in cell A2 to load in 400 more stock symbols by loading this website:

https://finviz.com/quote.ashx?t=" & Range("A2").Value

These results load starting in row 401 to 800.

My question is that since most of the code repeats, is there any way to run a loop to reduce the amount of code and macros. Thanks so much.

Public Sub parsehtml_0()
    Dim http As Object, html As New HTMLDocument, topics As Object, titleElem As Object, titleElem2 As Object, detailsElem As Object, topic As HTMLHtmlElement
    Dim i As Integer
    URL = "https://finviz.com/quote.ashx?t=" & Range("A1").Value
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", URL, False
    http.send
    html.body.innerHTML = http.responseText
    Set topics = html.getElementsByClassName("snapshot-table2")
    i = 1
    For Each topic In topics
    Set titleElem = topic.getElementsByTagName("tr")(2)
    Set titleElem2 = topic.getElementsByTagName("td")(1)
    Sheets(1).Cells(i, 3).Value = titleElem.getElementsByTagName("b")(0).innerText
    Set titleElem = topic.getElementsByTagName("tr")(3)
    Set titleElem2 = topic.getElementsByTagName("td")(2)
    Sheets(1).Cells(i, 4).Value = titleElem.getElementsByTagName("b")(0).innerText
    i = i + 1
    Next
    Set topics = html.getElementsByClassName("fullview-title")
    i = 1
    For Each topic In topics
    Set titleElem = topic.getElementsByTagName("tr")(0)
    Set titleElem2 = topic.getElementsByTagName("td")(0)
    Sheets(1).Cells(i, 2).Value = titleElem.getElementsByTagName("a")(0).innerText
    i = i + 1
    Next

    End Sub


Public Sub parsehtml_1()
Dim http As Object, html As New HTMLDocument, topics As Object, titleElem As Object, titleElem2 As Object, detailsElem As Object, topic As HTMLHtmlElement
Dim i As Integer
URL = "https://finviz.com/quote.ashx?t=" & Range("A2").Value
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", URL, False
http.send
html.body.innerHTML = http.responseText
Set topics = html.getElementsByClassName("snapshot-table2")
i = 401
For Each topic In topics
Set titleElem = topic.getElementsByTagName("tr")(2)
Set titleElem2 = topic.getElementsByTagName("td")(1)
Sheets(1).Cells(i, 3).Value = titleElem.getElementsByTagName("b")(0).innerText
Set titleElem = topic.getElementsByTagName("tr")(3)
Set titleElem2 = topic.getElementsByTagName("td")(2)
Sheets(1).Cells(i, 4).Value = titleElem.getElementsByTagName("b")(0).innerText
i = i + 1
Next
Set topics = html.getElementsByClassName("fullview-title")
i = 401
For Each topic In topics
Set titleElem = topic.getElementsByTagName("tr")(0)
Set titleElem2 = topic.getElementsByTagName("td")(0)
Sheets(1).Cells(i, 2).Value = titleElem.getElementsByTagName("a")(0).innerText
i = i + 1
Next

End Sub

When I add that code, see below, the macro parsehtml dissapears. If I run the code anyway or run loader, I get an error on this line with the bolded part highlighted.

Dim http As Object**, html As New HTMLDocument**, topics As Object, titleElem As Object, titleElem2 As Object, detailsElem As Object, topic As HTMLHtmlElement

Public Sub parsehtml(page As String)
    Dim http As Object**, html As New HTMLDocument**, topics As Object, titleElem As Object, titleElem2 As Object, detailsElem As Object, topic As HTMLHtmlElement
    Dim i As Integer
    URL = "https://finviz.com/quote.ashx?t=" & page
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", URL, False
    http.send
    html.body.innerHTML = http.responseText
    Set topics = html.getElementsByClassName("snapshot-table2")
    i = 1
    For Each topic In topics
    Set titleElem = topic.getElementsByTagName("tr")(2)
    Set titleElem2 = topic.getElementsByTagName("td")(1)
    Sheets(1).Cells(i, 3).Value = titleElem.getElementsByTagName("b")(0).innerText
    Set titleElem = topic.getElementsByTagName("tr")(3)
    Set titleElem2 = topic.getElementsByTagName("td")(2)
    Sheets(1).Cells(i, 4).Value = titleElem.getElementsByTagName("b")(0).innerText
    i = i + 1
    Next
    Set topics = html.getElementsByClassName("fullview-title")
    i = 1
    For Each topic In topics
    Set titleElem = topic.getElementsByTagName("tr")(0)
    Set titleElem2 = topic.getElementsByTagName("td")(0)
    Sheets(1).Cells(i, 2).Value = titleElem.getElementsByTagName("a")(0).innerText
    i = i + 1
    Next

    End Sub

    Sub Loader()
    parsehtml Range("A1").Value
    parsehtml Range("A2").Value

    End Sub

Alan Alan · Accepted Answer · 2018-06-14T00:26:18

Use parameters in your Subroutine.

See the Microsoft documentation and this additional resource.

Instead of calling

Public Sub parsehtml_0()

you should call

Public Sub parsehtml(page as String)

Then you can change one line in your Sub:

URL = "https://finviz.com/quote.ashx?t=" & Range("A1").Value

becomes:

URL = "https://finviz.com/quote.ashx?t=" & page

From there, you can create an initial Sub which handles the loop for as many times as you want:

Sub Loader() 
  parsehtml Range("A1").Value
  parsehtml Range("A2").Value
End Sub

With just two entries, this will do; if you go on to have lots of pages to load, you can look into how to replace A1 with a generated number and wrap the load in a loop.

In order for Excel to find the HTMLDocument object, a reference is needed. Add a reference VBE > Tools > References > HTML Object Libary(as per QHarr's comment).

Your main body of code currently reuses the same space. You could move the code to a Workbook module and assign the output of each page to a difference Worksheet but it's easiest to simply make your counter variable i a static variable.

Replace:

Dim i As Integer

with:

Static i As Integer

This will preserve the value of i across runs. Note the value will be lost when you close the workbook. If you want to preserve the value across that boundary, simply assign the counter to a cell - Range("B1").Value = i.

Looping help needed

1 Answers