Python, VBA - Beautiful Soup for webscraping

In this post I use Python's Beautiful Soup library to webscrape data from a web page, hopefully other VBA developers will realise that this is a much better library to use than VBA. But I do give a COM wrapper class so that the Python can be called from VBA.

Use case background

I'm worried about Corona Virus and its impact on stock markets, I very much need to query the London Stock Exchange database of securities to look for some safe government bonds to buy into. I have a list of government bonds from the branch of government that manages the issuance of such bonds but their unique identifier is an ISIN which is unique across the globe. Unfortunately, stockbrokers do not use ISIN numbers and I need to find alternative IDs and ticker codes. The London Stock Exchange (I am UK resident/citizen) has this information so I need to cross-reference the issuance authority's ISINs code with the LSE's database to get codes to present to my stock broker. For this I choose to write a web scraping program for I do not know of an official REST service.

Say No to VBA Webscraping

There was a time on this blog when I shared code that allowed a VBA developer to create an instance of instance of Internet Explorer, navigate to a web page and then traverse the HTML DOM to extract the necessary information. Those days have past now for a number of reasons. Firstly, Internet Explorer is legacy as Microsoft have the Edge browser so it would be foolish to write code against Internet Explorer. Secondly, since I branched out into alternatives to VBA I have discovered the quality of other libaries such as the .NET ecosystem and the Python ecosystem which both have many-fold better libraries.
I'd urge no new development in VBA for webscraping as there are three or four better options depending on your architecture and programming language preferences:

• Chrome Extension, embedded JavaScript is best placed to traverse an HTML Dom and even sink events (needs a web server to relay info to though)
• .NET Html Agility Pack, so C# developers would recommends this.
• Python's Beautiful Soup
• Webdriver interface, formerly known as Selenium

In this post I'm using Python's Beautiful Soup library but I am giving a COM wrapper so that it is callable from VBA because this is an Excel development blog after all.

I do not wish to replicate the Beautiful Soup documentation so I will be brief. Initially, I felt frustrated because I like to use a CSS selector path such as the following but this didn't not work for me which is a shame as it is a nice one shot declarative way to access an element.

#contentIndex > div.search_results_list > table > tbody > tr > td:nth-child(1)

But, I then read the documentation and saw how easily it was to script against a DOM (and actually a DOM fragment as well!) that the resulting code was perfectly easy to write.
All the web-scraping is packed into a class called LSEWebServices. But I also promised a COM wrapper class and that is LSEWebServicesCOMWrapper which simply delegates to an instance of LSEWebServices; I guess I could have inherited perhaps.

Important note: this code has been updated because London Stock Exchange changes their website

import urllib.request as urllib2
from bs4 import BeautifulSoup
import pythoncom
import logging

class LSEWebServices:

    def ExtractLSESearchResults(self,searchTerm: str):
        try:
            req = urllib2.urlopen("https://www.londonstockexchange.com/search?searchtype=all&q=" + searchTerm)
            html = req.read().decode("utf-8")

            try:
                soup = BeautifulSoup(html, 'html.parser')
                searchResultList = soup.find_all('div','item-separator')
                if (len(searchResultList)==1):
                    subText = str(searchResultList[0])
                    soup2 = BeautifulSoup(subText, 'html.parser')
                    instrumentTidm = soup2.find('span','instrument-tidm').text 
                    instrumentDesc = soup2.find_all('span','instrument-uppercase') [1].text
                    instrumentLink = soup2.find('a','tidm-and-description').attrs['href']
                    return (instrumentTidm, instrumentDesc,instrumentLink)
                pass


            except Exception as ex:
                print(ex)


        except Exception as ex:
            print(ex)

    def ExtractLSESecurityInformation(self, link:str):
        try:
            req = urllib2.urlopen(link)
            html = req.read().decode("utf-8")

            soup = BeautifulSoup(html, 'html.parser')
            instrumentInformation = soup.find_all('div','chart-table-instrument-information')
            if (len(instrumentInformation)==1):
                subText = str(instrumentInformation[0])
                soup2 = BeautifulSoup(subText, 'html.parser')
                spanMarketSegmentParent = soup2.find('span',string=' Market segment ').parent()
                marketSegment  = spanMarketSegmentParent[1].text

                spanSEDOLParent = soup2.find('span',string=' SEDOL ').parent()
                SEDOL  = spanSEDOLParent[1].text

                spanMICParent = soup2.find('span',string=' Market identifier code (MIC) ').parent()
                MIC  = spanMICParent[1].text

                spanISINParent = soup2.find('span',string=' ISIN ').parent()
                ISIN  = spanISINParent[1].text

                return (marketSegment, MIC,  SEDOL, ISIN)

        except Exception as ex:
            print(ex)

def TestLSEWebServices():
    lse = LSEWebServices()
    results = lse.ExtractLSESearchResults("gb0031790826")
    print(results)
    sec_info = lse.ExtractLSESecurityInformation(results[2])
    print(sec_info)
    dummy = 1 # a line of code upon which I can place a breakpoint
    pass

class LSEWebServicesCOMWrapper(object):
    _reg_clsid_ = "{81F3D23E-83E5-42DF-96E8-5042933379CF}"
    _reg_progid_ = 'PythonInVBA.LSEWebServicesCOMWrapper'
    _reg_clsctx_ = pythoncom.CLSCTX_LOCAL_SERVER 
    _public_methods_ = ['ExtractLSESearchResults','ExtractLSESecurityInformation']

    def ExtractLSESearchResults(self,searchTerm: str):
        try:
            lse = LSEWebServices()
            results = lse.ExtractLSESearchResults(searchTerm)
            
            if (results is not None):
                return list(results) 
            else:
                return None

        except Exception as ex:
            print(ex)

    def ExtractLSESecurityInformation(self, link:str):
        try:
            lse = LSEWebServices()
            results = lse.ExtractLSESecurityInformation(link)

            if (results is not None):
                return list(results) #.tolist()
            else:
                return None

        except Exception as ex:
            print(ex)

def TestLSEWebServicesCOMWrapper():
    lse = LSEWebServicesCOMWrapper()
    results = lse.ExtractLSESearchResults("gb0031790826")
    print(results)
    sec_info = lse.ExtractLSESecurityInformation(results[2])
    print(sec_info)
    dummy = 1
    pass

def RegisterThis():
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(LSEWebServicesCOMWrapper)

if __name__ == '__main__':
    #TestLSEWebServicesCOMWrapper()
    RegisterThis()

Client VBA Code

So the Python code needs to be run once to register the COM servers and for this you need Administrator rights; once run the following VBA code can run.
All the hard work has been done for us in the Python code. In VBA, we create the COM wrapper class and call a couple of methods, the Python returns Python lists which are converted into COM/VBA variant arrays and we just pull out the relevant item. Simple.

Option Explicit

Sub TestLSEWebServicesCOMWrapper()
    
    Dim obj As Object
    Set obj = VBA.CreateObject("PythonInVBA.LSEWebServicesCOMWrapper")
    
    Dim vResult As Variant
    vResult = obj.ExtractLSESearchResults("gb0031790826")
    
    'Stop
    Dim vSecInfo As Variant
    vSecInfo = obj.ExtractLSESecurityInformation(vResult(2))

    Debug.Print "ISIN:" & vSecInfo(3), vResult(1), "TIDM:" & vResult(0), "SEDOL:" & vSecInfo(2), ""
    
    'Stop

End Sub

And the above code gives the output

ISIN:GB0031790826           UNITED KINGDOM 2% IL TREASURY 35          TIDM:T2IL     SEDOL:3179082 

So now referring back to the use case I can verify that my stock broker recognizes T2IL and 3179082 as identifiers. So we have converted the ISIN into something useful.

VBA - IE - CreateEvent and DispatchEvent to synthesise an event

Summary: Inject Javascript to create and dispatch an event in Internet Explorer using CreateEvent and DispatchEvent

IE is different from other browsers and so there sometimes is an 'IE way of doing things'. This is true when trying to synthesise an event, typically during web-scraping. To synthesise an event in IE requires calling CreateEvent and then calling DispatchEvent. The calling syntax is not obvious so here I lay down an example for reference.

The code injects javascript using the execScript method. Also a console stack trace feature is given.

TIP: It turns out that when webscraping and manipulating HTML input boxes etc. it is better for the manipulated HTML element to take the focus; this can obviate the need to synthesise events.

The VBA code

Option Explicit

'* Tools - References
'*      MSHTML      Microsoft HTML Object Library                   C:\Windows\SysWOW64\mshtml.tlb
'*      SHDocVw     Microsoft Internet Controls                     C:\Windows\SysWOW64\ieframe.dll
'*      Shell32     Microsoft Shell Controls And Automation         C:\Windows\SysWOW64\shell32.dll

Private Function ReacquireInternetExplorer(ByVal sMatch As String) As Object
    Dim oShell As Shell32.Shell: Set oShell = New Shell32.Shell
    Dim wins As Object: Set wins = oShell.Windows
    Dim winLoop As Variant
    For Each winLoop In oShell.Windows
        If "C:\Program Files (x86)\Internet Explorer\IEXPLORE.EXE" = winLoop.FullName Then

            Dim sFile2 As String
            sFile2 = "file:///" & VBA.Replace(sMatch, "\", "/")
            If StrComp(sFile2, winLoop.LocationURL, vbTextCompare) = 0 Then
                Set ReacquireInternetExplorer = winLoop.Application
                GoTo SingleExit
            End If
        End If
    Next
SingleExit:
End Function

Sub test()

    Dim objIE As InternetExplorer
    Set objIE = New InternetExplorer
    Dim oHtml As HTMLDocument
    Dim HTMLtags As IHTMLElementCollection


    Dim sUrl As String
    sUrl = "C:\Users\Simon\source\repos\WebApplication2\WebApplication2\HtmlPage1.html"

    objIE.Visible = True
    objIE.Navigate sUrl

    If StrComp(Left(sUrl, 3), "C:\") = 0 Then
        Stop '* give chance to clear the activex warning box for the local file
        Set objIE = ReacquireInternetExplorer(sUrl)
    End If
    Do Until objIE.readyState = READYSTATE_COMPLETE: DoEvents: Loop
    Set oHtml = objIE.Document

    Do
        '* wait for the input box to be ready
        Set HTMLtags = oHtml.getElementsByClassName("OrderForm_input-box_XkGmi")
        DoEvents
    Loop While HTMLtags.Length = 0

    Dim objWindow As MSHTML.HTMLWindow2
    Set objWindow = objIE.Document.parentWindow


    
    Const csJavaScriptConsoleTrace As String = "var divTotal = document.querySelector('div.OrderForm_total_6EL8d'); " & _
                                                 "divTotal.onchange = function() { console.trace(); }"
    
    objWindow.execScript csJavaScriptConsoleTrace


    '* next line sets the input box and raises an event, works on local file but not on GDAX
    
    Const csJavaScriptSynthesiseEvents As String = _
                "var inputBox = document.querySelector('div.OrderForm_input-box_XkGmi input'); " & _
                "inputBox.value = 100; " & _
                "if (document.createEvent) { " & _
                "  var event2 = document.createEvent('HTMLEvents'); " & _
                "  event2.initEvent('input', false, false); " & _
                "  event2.eventName = 'input'; inputBox.dispatchEvent(event2); " & _
                "}"
    objWindow.execScript csJavaScriptSynthesiseEvents


    'get the Total(LTC) to cross check
    Do
        '* wait for the order total div to be ready
        Set HTMLtags = oHtml.getElementsByClassName("OrderForm_total_6EL8d")
        DoEvents
    Loop While HTMLtags.Length = 0

    Dim divTotal As HTMLDivElement
    Set divTotal = oHtml.querySelector("div.OrderForm_total_6EL8d")
    Debug.Print divTotal.innerText & " Total(LTC)"

    Stop

End Sub

The HTML page

    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8" />
        <title></title>
    </head>
    <body>
        <input id="Button1" type="button" value="Programmatically write textbox value" onclick="TestAlert()" />

        <form class="OrderForm_form_25r0u">
            <ul class="OrderForm_trade-type_2QyK4">
                <li class="OrderForm_trade-type-tab_uWGMp OrderForm_active_Di-9p">MARKET</li>
                <li class="OrderForm_trade-type-tab_uWGMp">LIMIT</li>
                <li class="OrderForm_trade-type-tab_uWGMp">STOP</li>
            </ul>
            <ul class="OrderForm_toggle_120Ka">
                <li class="OrderForm_toggle-tab_bZZnC OrderForm_buy_38n5g OrderForm_active_Di-9p">BUY</li>
                <li class="OrderForm_toggle-tab_bZZnC OrderForm_sell_3vYRQ">SELL</li>
            </ul>
            <div class="market-order">
                <div class="OrderForm_section_2Znad">
                    <div class="OrderForm_section-header_fwFDB">Amount</div>
                    <div class="OrderForm_input-box_XkGmi">
                        <input type="number" step="0.01" min="0" name="amount" 
           placeholder="0.00" value="" autocomplete="off" oninput="myOnInputHandler()">
                        <span>EUR</span>
                    </div>
                </div>
            </div>
            <div class="OrderForm_order-total_3Mkdz">
                <div>
                    <b>Total</b>
                    <span>(LTC)</span>
                    <b>≈</b>
                </div>
                <div class="OrderForm_total_6EL8d" >0.00000000</div>
            </div>
        </form>

        <script language="javascript">
            function myOnInputHandler() {
                print_call_stack();
                alert('you input something');
            }

            function print_call_stack() { console.trace(); }

            function print_call_stack2() {
                var stack = new Error().stack;
                console.log("PRINTING CALL STACK");
                console.log(stack);
            }


            function TestAlert() { setInputBox(document); }

            function setInputBox() {
                try {
                    var inputBox = document.querySelector('div.OrderForm_input-box_XkGmi input'); 
     inputBox.value = 100; 
     if (document.createEvent) { 
      var event2 = document.createEvent("HTMLEvents"); 
      event2.initEvent("input", true, true); 
      event2.eventName = "input"; inputBox.dispatchEvent(event2); 
     }

                    return ({ success: true });
                }
                catch (ex) {
                    return ({ exception: ex, myMsg: '#error in setInputBox!' });
                }
            }

        </script>
    </body>
    </html>

VBA - Webscraping - jQuery selectors available with MSHTML's querySelector and querySelectorAll

So another SO question about web scraping in VBA. I wrote some code but was not happy with it and so revisited it. It turns out that the jQuery selector syntax can be quite advanced, a bit like XPath for Xml.

Tip #1 When using querySelectorAll() use Early-binding

I have seen some strangeness when using querySelectorAll() when getting the length, the problem goes away if you use early binding type library (Tools->References->Microsoft HTML Object Library)

    Dim oSelectors As MSHTML.IHTMLDOMChildrenCollection
    Set oSelectors = oHtml.querySelectorAll("div.blocoCampos input")
    
    Dim lSelectorResultList As Long
    lSelectorResultList = oSelectors.Length

Note above the selector gets input elements which are children of div elements with the class 'blocoCampos'.

Tip #2 When using querySelectorAll() use item to acquire each element not For Each

I have also seen some strangeness when using querySelectorAll() that errors on the Next line of a For Each loop. So avoid by establishing the length of the result array and then use a standard integer loop and acquire each element with item.

    Dim lSelectorResultLoop As Long
    For lSelectorResultLoop = 0 To lSelectorResultList - 1

        Dim objChild As Object
        Set objChild = oSelectors.Item(lSelectorResultLoop)

Selecting grandchild anchor off second span child of a div with id

Given the following HTML source the questioner wanted to navigate to the anchor links. The anchors do have not id and no class; neither do their parent span elements; but the spans' parent div element have (non-unique) id so we can start the capture there.

<div id="resumopesquisa">

  <div id="itemlistaresultados" style="background-color: #EDEDED">
   <span class="labellinha">Acórdãos de Repetitivos</span>
   <!-- <span>  PIS E ICMS E COFINS E CALCULO E BASE E DE REPETITIVOS.NOTA.
   </span> -->
   
   <span><a href="/SCON/jurisprudencia/toc.jsp?livre=ICMS+BASE+DE+CALCULO+PIS+COFINS&repetitivos=REPETITIVOS&&b=ACOR&thesaurus=JURIDICO&p=true">1
     documento(s) encontrado(s)</a></span>
   
  </div>
  
 
 <div id="itemlistaresultados">
  <span class="labellinha">Acórdãos</span>
  <!-- <span>  PIS E ICMS E COFINS E CALCULO E BASE E DE
  </span> -->
  
  <span><a href="/SCON/jurisprudencia/toc.jsp?livre=icms+base+de+calculo+pis+cofins&&b=ACOR&thesaurus=JURIDICO&p=true">284
    documento(s) encontrado(s)</a></span>
  
 </div>
</div>

So let's build up our jQuery selector expression, first let's get the divs but specifiying their id ( yeah, I know I though ids were unique as well) ...

div#itemlistaresultados

But then we need to get the second child span element of the div, we can do this with jQuery's nth-child selector. We simply add a space between the div expression and the span expression to express the parent child relationship ...

div#itemlistaresultados span:nth-child(2)

Finally we pick out the anchor element with

div#itemlistaresultados span:nth-child(2) a

So we can put this jQuery selector expression into MSHTML's querySelectorAll method (use querySelector for singleton results), here is the VBA

    Set oHtml = ie.Document
    Dim objResultList As MSHTML.IHTMLDOMChildrenCollection
    Set objResultList = oHtml.querySelectorAll("div#itemlistaresultados span:nth-child(2) a")

    Dim lResultCount As Long
    lResultCount = objResultList.Length

    Debug.Print
    Dim lResultLoop As Long
    For lResultLoop = 0 To lResultCount - 1

        Dim anchorLoop As MSHTML.HTMLAnchorElement
        Set anchorLoop = objResultList.Item(lResultLoop)

        Debug.Print achLoop.href

    Next

Tip #3 When not required use late binding to get aggregated interface

So when dealing with an input checkbox then it must be understood that its functionality is defined across a great many number of different interfaces such as MSHTML.HTMLInputElement, MSHTML.IHTMLInputElement and many more. Perhaps the input box is a worst case example because it is a multifaceted definition but for illustration here is what OLEView gives the interfaces implemented by the coclass MSHTML.HTMLInputElement ...

    coclass HTMLInputElement {
        [default] dispinterface DispHTMLInputElement;
        [default, source] dispinterface HTMLInputTextElementEvents;
        [source] dispinterface HTMLInputTextElementEvents2;
        [source] dispinterface HTMLOptionButtonElementEvents;
        [source] dispinterface HTMLButtonElementEvents;
        interface IHTMLElement;
        interface IHTMLElement2;
        interface IHTMLElement3;
        interface IHTMLElement4;
        interface IHTMLUniqueName;
        interface IHTMLDOMNode;
        interface IHTMLDOMNode2;
        interface IHTMLDOMNode3;
        interface IHTMLDatabinding;
        interface IHTMLElement5;
        interface IHTMLElement6;
        interface IElementSelector;
        interface IHTMLDOMConstructor;
        interface IHTMLElement7;
        interface IHTMLControlElement;
        interface IHTMLInputElement;
        interface IHTMLInputElement2;
        interface IHTMLInputTextElement;
        interface IHTMLInputTextElement2;
        interface IHTMLInputHiddenElement;
        interface IHTMLInputButtonElement;
        interface IHTMLInputFileElement;
        interface IHTMLOptionButtonElement;
        interface IHTMLInputImage;
        interface IHTMLInputElement3;
        interface IHTMLInputRangeElement;
    };

So instead of figuring out on which interface of the list above a method is implemented it is better to declare the variable with As Object to use late binding, and then all the methods from all of the interfaces are aggregated onto a IDispatch interface.

Links

• jQuery API Documentation - nth-child() Selector
• Stack Overflow - vba - Clicking on a link without Id or class

VBA - WebScraping - firing an HTML button's event from VBA with Click, FireEvent and Window.ExecScript

So, many questions on the StackOverflow VBA thread are concerned with web-scraping and so driving the browser in VBA code is a useful skill. Here we're see that we can push the boundary a little more by firing events in the HTML object model.

Code To Write HTML

Here we write some code to write the html file locally. The code uses the HtmlElementStack class (given separately below) to ensure our HTML is well-formed. There is a script tag which defines the function wired to the button's click handler. But we shall see that because the function is defined in the HTML page's global scope, i.e. Window object, then it can be called with Window.execScript

Private Const msFILENAME As String = "N:\TestJavascript2.html"

Function WriteHTML()

    Dim dicHTMLStack As HtmlElementStack
    Set dicHTMLStack = New HtmlElementStack

    With dicHTMLStack
        .SetFileName msFILENAME 
        
        .OE "html"
        .OE "head"
        .OE "title"
        .WL "Some test html with javascript"
        .CE
        .CE
        .OE "body"
        .OE "div", "id='div1'"
        .WL "Some Text"
        .CE
        .OE "button", "id='button1' type='button' onclick='throwMsgBox()'"
        .WL "Click Me!"
        .CE
        .OE "script", "language='jscript'"
        .WL "function throwMsgBox() { alert('hi there'); }"
        .CE
    End With
    
    Set dicHTMLStack = Nothing

End Function

HtmlElementStack class

This class allows us to be a little lazy when writing html files. We keep a stack, ie. Last In Last Out (LIFO) structure in a Scripting.Dictionary, that records all the open elements that need closing. It also manages its own text stream because we need to write off all pending close elements before the text stream is closed.

Option Explicit

'* Tools->References
' Scripting            Microsoft Scripting Runtime     C:\Windows\SysWOW64\scrrun.dll


Private mdicStack As New Scripting.Dictionary

Private mtxt As Scripting.TextStream
Private msFILENAME As String
Private mfso As New Scripting.FileSystemObject

'Private Sub SetStream(ByVal txt As Scripting.TextStream)
'    Set mtxt = txt
'End Sub

Public Sub SetFileName(ByVal sFileName As String)
    msFILENAME = sFileName
    Set mtxt = mfso.CreateTextFile(msFILENAME)
End Sub

Public Sub Write_(ByVal sText As String)
    mtxt.Write sText
End Sub

Public Sub WL(ByVal sText As String)
    mtxt.WriteLine sText
End Sub

Public Sub OE(ByVal sNodeName As String, Optional ByVal sAttribs As String)
    
    If Not mtxt Is Nothing Then
        If Len(sAttribs) = 0 Then
            mtxt.WriteLine "<" & sNodeName & ">"
        Else
            mtxt.WriteLine "<" & sNodeName & " " & sAttribs & ">"
        End If
    
        
    End If

    mdicStack.Add mdicStack.Count, sNodeName
    

End Sub

Public Sub CE()

    If mdicStack.Count > 0 Then
        Dim sLastNode As String
        sLastNode = mdicStack.Item(mdicStack.Count - 1)
        
        Call mdicStack.Remove(mdicStack.Count - 1)
    
        mtxt.WriteLine ""
    End If

End Sub

Private Sub Class_Terminate()

    While mdicStack.Count > 0
        DoEvents
        CE
        DoEvents
    Wend
    
    mtxt.Close

    Set mtxt = Nothing
End Sub

Code to drive IE and call the click handler function 3 different ways

So in this code we create an instance of IE and navigate to our newly written html file. We call the button's click handler function 3 different ways. Firstly, by navigating to element and call 'Click'. Secondly, by calling the function in the global scope (i.e. off the window object) using ExecScript. Thirdly, similar to first but a looser couple FireEvent method.

I recommend acquiring the element immediately before calling a method because I have witnessed a type of stale reference bug.

Private Const msFILENAME As String = "N:\TestJavascript2.html"

Public Sub TestFire()
    
'* Tools->References
'SHDocVw    Microsoft Internet Controls C:\Windows\SysWOW64\ieframe.dll
    
    Dim oIE As InternetExplorerMedium
    Set oIE = New InternetExplorerMedium
    
    oIE.Visible = True
    oIE.navigate msFILENAME
    While oIE.Busy Or oIE.readyState < 4
        DoEvents
    Wend
    
    
    Stop
    '* recommend re-acquiring element before using as I suspect IE suffers from stale references
    oIE.Document.getElementById("button1").Click
    
    Stop

    '* call the function via the global scope, for html global scope is the *window*
    Call oIE.Document.parentWindow.execScript("throwMsgBox()", "JavaScript")

    Stop
    
    '* recommend re-acquiring element before using as I suspect IE suffers from stale references
    oIE.Document.getElementById("button1").FireEvent "onclick"
    
    Stop
    
    
    oIE.Quit
End Sub

Links

• StackOverflow - How to find and call javascript method from vba

IE VBA Webscraping - resorting to the ScriptEngine and using Javascript

Another SO webscraping question, this time a bounty. The given web page is seriously unfriendly and won't be scripted giving Nulls and crashing. In the end I resorted to writing the logic as a JavaScript program running on the ScriptEngine.

Option Explicit

'*Tools->References
'*    Microsoft Scripting Runtime
'*    Microsoft Scripting Control
'*    Microsoft Internet Controls
'*    Microsoft HTML Object Library

Sub Torrent_Data()
    Dim row As Long
    Dim IE As New InternetExplorer, html As HTMLDocument
    Dim post As Object

    With IE
        .Visible = True
        .navigate "https://yts.am/browse-movies"
        Do While .readyState <> READYSTATE_COMPLETE:
            DoEvents
        Loop
        Set html = .document
    End With

    Dim dicFilms As Scripting.Dictionary
    Set dicFilms = New Scripting.Dictionary

    Call GetScriptEngine.Run("getMovies", html, dicFilms)
    
    Dim vFilms As Variant
    vFilms = dicFilms.Keys
    
    Dim vYears As Variant
    vYears = dicFilms.Items
    
    Dim lRowLoop As Long
    For lRowLoop = 0 To dicFilms.Count - 1
        
        Cells(lRowLoop + 1, 1) = vFilms(lRowLoop)
        Cells(lRowLoop + 1, 2) = vYears(lRowLoop)
    
    Next lRowLoop
    
    Stop

    IE.Quit
End Sub

Private Function GetScriptEngine() As ScriptControl
    '* see code from this SO Q & A
    ' https://stackoverflow.com/questions/37711073/in-excel-vba-on-windows-how-to-get-stringified-json-respresentation-instead-of
    Static soScriptEngine As ScriptControl
    If soScriptEngine Is Nothing Then
        Set soScriptEngine = New ScriptControl
        soScriptEngine.Language = "JScript"

        soScriptEngine.AddCode "function getMovies(htmlDocument, microsoftDict) { " & _
                                    "var titles = htmlDocument.querySelectorAll('a.browse-movie-title'), i;" & _
                                    "var years = htmlDocument.querySelectorAll('div.browse-movie-year'), j;" & _
                                    "if ( years.length === years.length) {" & _
                                    "for (i=0; i< years.length; ++i) {" & _
                                    "   var film = titles[i].innerText;" & _
                                    "   var year = years[i].innerText;" & _
                                    "   microsoftDict.Add(film, year);" & _
                                    "}}}"

    End If
    Set GetScriptEngine = soScriptEngine
End Function

IE VBA WebScraping - interacting with a search engine

So another SO question about webscraping. I pushed my link of course. This time we interactive with a search engine, and we need a live copy of Internet Explorer to drive the interaction. Here is my code based on OP's code (but bugfixed by me)

Option Explicit


'See this http://exceldevelopmentplatform.blogspot.co.uk/2018/01/vba-mshtml-webscraping-looking-for-new.html

Sub SearchBot()

    'dimension (declare or set aside memory for) our variables
    Dim objIE As InternetExplorer 'special object variable representing the IE browser
    Dim aEle As HTMLLinkElement 'special object variable for an  (link) element
    Dim y As Integer 'integer variable we'll use as a counter
    Dim result As String 'string variable that will hold our result link

    'initiating a new instance of Internet Explorer and asigning it to objIE
    Set objIE = New InternetExplorer

    'make IE browser visible (False would allow IE to run in the background)
    objIE.Visible = True

    'navigate IE to this web page (a pretty neat search engine really)
    objIE.navigate "https://www.boersen-zeitung.de/index.php?li=310&subm=suche"
    'objIE.navigate "https://duckduckgo.com"

    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    Dim vCarManufacturer As Variant
    vCarManufacturer = Sheets("Sheet1").Range("A2").value

    vCarManufacturer = "Daimler" 'overriden

    Dim vSearchURL As Variant
    vSearchURL = Sheets("Sheet1").Range("C1").value
    vSearchURL = "" 'overriden

    Debug.Assert Not objIE.document Is Nothing

    Dim htmlSuche As Object
    Set htmlSuche = objIE.document.querySelector("input.suche_unternehmen")
    Debug.Assert Not htmlSuche Is Nothing

    'in the search box put cell "A2" value, the word "in" and cell "C1" value
    htmlSuche.value = _
      vCarManufacturer '& " in " & vSearchURL

    'click the 'go' button
    'objIE.document.getElementById("search_button_homepage").Click
    objIE.document.querySelector("input.suche_button21").Click

    'wait again for the browser
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    'the first search result will go in row 2
    y = 2

    'for each <a> element in the collection of objects with class of 'result__a'...

    Dim objResults As Object
    Set objResults = objIE.document.querySelectorAll("a.ue_fl_l")

    For Each aEle In objResults ' objIE.document.querysselec("result__a")

        '...get the href link and print it to the sheet in col C, row y
        Dim anchorResult As MSHTML.IHTMLAnchorElement
        Set anchorResult = aEle

        result = aEle
        Sheets("Sheet1").Range("C" & y).value = anchorResult.href ' result

        '...get the text within the element and print it to the sheet in col D
        Sheets("Sheet1").Range("D" & y).value = anchorResult.innerText
        Debug.Print aEle.innerText

        'is it a yellowpages link?
        If InStr(result, "yellowpages.com") > 0 Or InStr(result, "yp.com") > 0 Then
            'make the result red
            Sheets("Sheet1").Range("C" & y).Interior.ColorIndex = 3
            'place a 1 to the left
            Sheets("Sheet1").Range("B" & y).value = 1
        End If

        'increment our row counter, so the next result goes below
        y = y + 1

    'repeat times the # of ele's we have in the collection
    Next

    'add up the yellowpages listings
    Sheets("Sheet1").Range("B1").value = _
      Application.WorksheetFunction.Sum(Sheets("Sheet1").Range("B2:B100"))

    'close the browser
    objIE.Quit


'exit our SearchBot subroutine
End Sub