1

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
Improve this question

1 Answer 1

Reset to default
2

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).

MS Excel Reference to HTML ObjectLibrary

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

I added your code to the original post but there were problems.
You haven't highlighted anything. Check your formatting.
Add a reference VBE > Tools > References > HTML Object Libary
Or use late bound and have Dim HTML As Object: Set HTML = CreateObject("HTMLFile") and then no reference is required.
@QHarr I had clean forgot about references!
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.