0

I'm trying to access all the information on the following table:

https://www.tradingview.com/markets/stocks-usa/sectorandindustry-industry/biotechnology/

using the following VBA code:

Sub GetInfo()

    Dim XMLPage As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim HTMLTable, HTMLRow, HTMLCell, button As MSHTML.IHTMLElement

    XMLPage.Open "GET", "https://www.tradingview.com/markets/stocks-usa/sectorandindustry-industry/biotechnology/", False
    XMLPage.send

    HTMLDoc.body.innerHTML = XMLPage.responseText

    Set HTMLTable = HTMLDoc.getElementsByTagName("tr")

    For Each HTMLRow In HTMLTable
        For Each HTMLCell In HTMLRow.Children
            Debug.Print HTMLCell.innerText
        Next HTMLCell
    Next HTMLRow

End Sub

This works great but isn't capable of getting everything on the page. On the bottom of the page, there is a button you must click to "Load More" information. I think the button is defined below:

<div class="tv-load-more tv-load-more--screener js-screener-load-more">
    <span class="tv-load-more__btn">Load More</span>

I've tried a few different methods but haven't found success yet. Is there a way to automate VBA to keep clicking that button until everything is loaded, then run the VBA code above?

Christopher Weckesser
  • 373
  • 1
  • 8
  • This may help, although I'm guessing the `IE` object might not be supported any more. https://stackoverflow.com/questions/41268757/click-a-javascript-button-with-vba – Nick.Mc May 06 '20 at 01:35
  • 1
    VBA is not the best platform for this. Basically you either need to find a library that can interact with a web page (Apparently Selenium does this https://stackoverflow.com/questions/29385156/invoking-onclick-event-with-beautifulsoup-python) or you need to reverse engineer what the actual click code is doing. Usually it's running another GET, exactly like you are already doing and you need to call that GET to get the next page of data. – Nick.Mc May 06 '20 at 01:40
  • And if it's a stock market website, they are going to make what you want to do as difficult as possible on purpose... for a reason.. – braX May 06 '20 at 02:28
  • I am not sure you can do this because that URL doesn't see to work with IE (very weird). Also, the normal COM components are not exposed in other browsers, so you can't interact with the website dynamically. Although, maybe there is a workaround. Maybe someone here will come up with some clever way of doing this. A combination of Python and Selenium would do the job. Are you open to that option or does this need to be done with Excel/VBA? – ASH May 06 '20 at 17:07
  • Yeah, I tried with IE too and it doesn't seem like the website supports that browser. I made the transition from Python to VBA for this specific project so the program can be easily shared as an excel file and used by anyone. Maybe some trial and error with Selenium will work using a chrome driver? Or maybe another data source will be better? I'm looking for a way to pull all stock tickers and their associated market caps from each industry. I'm also hoping to not spend money on API's. I know, its quite demanding. – Christopher Weckesser May 06 '20 at 22:34

1 Answers1

2

Here's some code using Selenium. The only problem is that it's much slower than the original code... I think I'll continue looking for other data sources. Once again, any suggestions are much appreciated!

Sub GetTickerInfo()

    Dim driver As New Selenium.ChromeDriver
    Dim click As Boolean

    With driver
        .AddArgument "--headless"
        .Get "https://www.tradingview.com/markets/stocks-usa/sectorandindustry-industry/biotechnology/"
    End With

    click = True

    On Error GoTo done
    While click
        driver.FindElementByClass("tv-load-more__btn").click
        Application.Wait Now + TimeValue("00:00:01")
    Wend

done:
    Resume continue
continue:

    For Each tr In driver.FindElementsByTag("tr")
       For Each td In tr.FindElementsByTag("td")
           Debug.Print td.Text
       Next td
    Next tr

End Sub
Christopher Weckesser
  • 373
  • 1
  • 8