Loop through VBA

[Av8tordude]

The vba codes below are similar in scope, except the first code only looks up one price, however the second code is suppose to loop through each row to get each price in the range. However, it does not accomplish the task. I'm keeping getting the same numbers each time I run the second code. Can someone explain... Thank you kindly.


Getting price (Without Loop)....

        URL = "https://finance.yahoo.com/quote/" & Range("A6") & Format(Range("H6"), "yymmdd") & Left(Range("E6"), 1) & Format(Range("G6") * 1000, "00000000") & "?p=" & Range("A6") & Format(Range("H6"), "yymmdd") & Left(Range("E6"), 1) & Format(Range("G6") * 1000, "00000000")


        XMLPage.Open "GET", URL, False        XMLPage.send
    
        HTMLDoc.body.innerHTML = XMLPage.responseText 'Get the source (code) of the webpage . . .
        
        Set HTMLtables = HTMLDoc.getElementsByTagName("table") 'Set reference to all tables in htmlDoc.body.innerHTML . . .
        
        For Each HTMLtable In HTMLtables 'Loop through all table-tags . . .
            If HTMLtable.className = "W(100%)" Then '.and search for classname "W(100%) . . ."
                Set HTMLtds = HTMLDoc.getElementsByTagName("td") 'When found, set reference to all td elements in table . . .
                For Each HTMLtd In HTMLtds 'Loop through all td elements . . .
                    If HTMLtd.getAttribute("data-test") = "BID-value" Then 'and search for the attribute "data-test" with value "BID-value" . . .
                        Range("M" & i) = HTMLtd.innerText 'When found, print value to immediate window . . .
                    End If
                Next HTMLtd
            End If
        Next HTMLtable

Getting price (With Loop)....

For i = 3 To 12
    If Range("A" & i).Value <> "" Then

        URL = "https://finance.yahoo.com/quote/" & Range("A" & i) & Format(Range("H" & i), "yymmdd") & Left(Range("E" & i), 1) & Format(Range("G" & i) * 1000, "00000000") & "?p=" & Range("A" & i) & Format(Range("H" & i), "yymmdd") & Left(Range("E" & i), 1) & Format(Range("G" & i) * 1000, "00000000")
        
        XMLPage.Open "GET", URL, False
        XMLPage.send
    
        HTMLDoc.body.innerHTML = XMLPage.responseText 'Get the source (code) of the webpage . . .
        
        Set HTMLtables = HTMLDoc.getElementsByTagName("table") 'Set reference to all tables in htmlDoc.body.innerHTML . . .
        
        For Each HTMLtable In HTMLtables 'Loop through all table-tags . . .
            If HTMLtable.className = "W(100%)" Then '.and search for classname "W(100%) . . ."
                Set HTMLtds = HTMLDoc.getElementsByTagName("td") 'When found, set reference to all td elements in table . . .
                For Each HTMLtd In HTMLtds 'Loop through all td elements . . .
                    If HTMLtd.getAttribute("data-test") = "BID-value" Then 'and search for the attribute "data-test" with value "BID-value" . . .
                        Range("M" & i) = HTMLtd.innerText 'When found, print value to immediate window . . .
                    End If
                Next HTMLtd
            End If
        Next HTMLtable
     End if
Next i

 

[strooman]

Because this line doesn't change and therefore executes all the time:
If HTMLtd.getAttribute("data-test") = "BID-value"

 

[Av8tordude]

How does this affect the results. It seems after the market open and the prices are updated, the prices results in 0. However, now that market is closed, the prices are update correctly. Any correlation? Thanks. How can this be corrected?

 

[strooman]

How does this affect the results. It seems after the market open and the prices are updated, the prices results in 0. However, now that market is closed, the prices are update correctly. Any correlation? Thanks. How can this be corrected?

Like I pointed out in previous posts #8 and #10 in another thread you should give us more information. The change that you get help is bigger. And perhaps more people from this forum will participate. Also, try to stay in the same topic because the nature of your question is the same.

So, please look at the questions asked in above mentioned posts.
- Can you provide us the desired results?
- What values are you after?
- What are the values in columns A, G, H, E because you use these values to create the url. Now we have to guess.

When I go to this url:
https://finance.yahoo.com/quote/AAP181221C00170000?p=AAP181221C00170000

I get these values.
- Are these the values you are after?

 

[Av8tordude]

- Can you provide us the desired results?

The desired result is to get the Bid Price, which the code accomplishes this task. However, I notice when I execute the code when the market is open and the prices are updated, the code returns 0. Now that the market is closed the code is returning the correct Bid Price. Not sure why? Is it possible an explanation can be provided?

What are the values in columns A, G, H, E because you use these values to create the url. Now we have to guess.

A = AAPL
G = $175.00
H = 01/18/2018
E = PUT

AAPL

Apple Inc.

11/23/2018

2

Put

5

$ 175.00

01/18/2019

<tbody>
</tbody>