Scraping product prices from E-commerce using Excel VBA / cooking handling

Hi all,

I've written a code that actually works but is really slow and can be improved to make it less error sensitive and faster.

It's a code that uses IE automation in Excel VBA and extracts store prices from a supermarket website. The thing is that across the different stores (in different cities) the prices are different for the same products. To extract the different 'price lines' for the products the codes does/ must do the following:

1- visit the website of the supermarket and select the first store / city. The selected store is saved in a browser cookie.
2- the next code part visits the specified product (in total, finally it will be a fixed list around 200 products, using a loop). (The prices are extracted in a worksheet, not includes in this code yet)
3- next a second city/supermarket is selected and stored in the browser cookie
4- the same code (#2, see above) is called and the prices for the same 200 products is scraped from the store, and saved.


When using IE automation the specific store/city is remembered when visiting the product pages. I found that when using a different method like XMLHTTP request the code is much faster but the problem then is that the product page is not linked to the store that was saved in a browser cookie. Any suggestions how to use the XMLHHTP code (provided at the bottom) or a different method together with a cookie in which the correct store is saved while scraping the product prices?


Below the code, it's now using only one store and only one sample product to illustrate.

Selecting the Store using IE automation and saving it in browser cookie (using IE automation)

This code will set the value of the browser cookie named/stored at "www.jumbo.com > cookies > HomeStore" to the value "YC8KYx4XB88AAAFIDcIYwKxJ"
If there is a cleaner/faster way to only set/change this value, it would would help already for this part:)

Code:

---

Sub SetStore()   
    ' this code will set the value of the cookie named "www.jumbo.com > cookies > HomeStore" to the value "YC8KYx4XB88AAAFIDcIYwKxJ"
    ' when using sample store 2 (Brielle) it should be set to "OG8KYx4XP4wAAAFIlsEYwKxK"
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLSearchbox As MSHTML.IHTMLElement
    Dim HTMLSearchboxes As MSHTML.IHTMLElementCollection
    Dim HTMLButton As MSHTML.IHTMLElement
    Dim HTMLButtons As MSHTML.IHTMLElementCollection
    Dim HTMLSearchButton As MSHTML.IHTMLElement
    Dim HTMLSearchButtons As MSHTML.IHTMLElementCollection
    Dim HTMLStoreID As MSHTML.IHTMLElement
    Dim HTMLStoreIDs As MSHTML.IHTMLElementCollection
    Dim HTMLSaveStore As MSHTML.IHTMLElement
    Dim HTMLSaveStores As MSHTML.IHTMLElementCollection   
    ' set on False to hide IE screen
    IE.Visible = True   
    ' navigate to url with limited content
    IE.navigate "https://www.jumbo.com/content/algemene-voorwaarden/"   
    Do While IE.readyState <> READYSTATE_COMPLETE
    Loop
    Set HTMLDoc = IE.document
    Set HTMLButtons = HTMLDoc.getElementsByTagName("button")       
    For Each HTMLButton In HTMLButtons   
        If HTMLButton.getAttribute("data-jum-action") = "openHomeStoreFinder" Then
            HTMLButton.Click
            Exit For
        End If
    Next HTMLButton   
    Application.Wait Now + #12:00:02 AM#       
    Set HTMLSearchboxes = HTMLDoc.getElementsByTagName("input")
    For Each HTMLSearchbox In HTMLSearchboxes       
        If HTMLSearchbox.getAttribute("id") = "searchTerm__DkKYx4XylsAAAFJktpb2Guy" Then   
            ' input field store name/location to show search results
            ' Sample 1 "Oosterhout Zuiderhout"
            ' Sample 2 "Brielle"
            ' change value below to search correct store (also change corresponding value later in this code)
            HTMLSearchbox.Value = "Oosterhout Zuiderhout" 
            Application.Wait Now + #12:00:03 AM#
            HTMLSearchbox.Click
            Exit For
        End If 
    Next HTMLSearchbox
    Set HTMLSearchButtons = HTMLDoc.getElementsByTagName("button")       
    For Each HTMLSearchButton In HTMLSearchButtons   
        If HTMLSearchButton.getAttribute("data-jum-filter") = "search" Then
              HTMLSearchButton.Click
              Exit For
          End If   
      Next HTMLSearchButton   
      Application.Wait Now + #12:00:05 AM#   
      Set HTMLStoreIDs = HTMLDoc.getElementsByTagName("li")       
      For Each HTMLStoreID In HTMLStoreIDs
          ' Sample 1 oosterhout = YC8KYx4XB88AAAFIDcIYwKxJ
          ' Sample 2 brielle = OG8KYx4XP4wAAAFIlsEYwKxK
          ' change value below to switch store
          If HTMLStoreID.getAttribute("data-jum-store-id") = "YC8KYx4XB88AAAFIDcIYwKxJ" Then   
              HTMLStoreID.Click
              Application.Wait Now + #12:00:03 AM#
              Exit For
          End If   
      Next HTMLStoreID   
      Set HTMLSaveStores = HTMLDoc.getElementsByTagName("button")   
      For Each HTMLSaveStore In HTMLSaveStores   
            If HTMLSaveStore.getAttribute("data-jum-action") = "saveHomeStore" Then
                HTMLSaveStore.Click
                Exit For
          End If   
        Next HTMLSaveStore   
      ' IE.Quit   
    End Sub

---

Extracting the product price (using IE automation)

The following part will extract the price (via debug.print in Immediate window) for one sample product (Bastogne Koeken).
When using the first store (Oosterhout Zuiderhout) the price is 1,35 for this product.
When using the second store (Brielle) the price is 1,44 (prices might change over time, but should be different between the two stores).
When no store is selected (no HomeStore cookie value is set) the price is 1,44

Code:

---

Sub GetJumboPriceIE()
    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim JumInputs As MSHTML.IHTMLElementCollection
    Dim JumInput As MSHTML.IHTMLElement
    Dim JumPrice As MSHTML.IHTMLElement
    Dim JumboPrice As Double
    Dim Price_In_Cents_Tag As String
    Dim SKU_tag As String, SKU_url As String
    SKU_tag = "173140KST"
    SKU_url = "https://www.jumbo.com/lu-bastogne-koeken-original-260g/173140KST/"
    IE.Visible = False
  IE.navigate SKU_url
    Do While IE.readyState <> READYSTATE_COMPLETE
    Loop
    Set HTMLDoc = IE.document
    IE.Quit
    Set JumInputs = HTMLDoc.getElementsByTagName("input")
    Price_In_Cents_Tag = "PriceInCents_" & SKU_tag
    Set JumPrice = HTMLDoc.getElementById(Price_In_Cents_Tag)
    JumboPrice = JumPrice.getAttribute("value") / 100
    Debug.Print JumboPrice
End Sub

---

As said, the code is working but is slow due to the IE automation it's taking very long when using it for 200 products and 5 different stores.

Below a code for the price extraction part that is much faster (using XML HTTP method) but doesn't work because i need to set the correct store in the cookie first and i cannot figure it how i do this. Any help is welcome!


Extracting the price using XML HTTP method
The following code uses XML HTTP request to get the price. No store is selected or used from a cookie, so 1,44 is the extracted price. I cannot get the 1,35 from the Oosterhout Zuiderhout store when using the XML HTTP method. >> any suggestions?

Code:

---

Sub GetJumboPriceXML()
    Dim XMLReq As New MSXML2.XMLHTTP60
    Dim HTMLDoc As New MSHTML.HTMLDocument
    Dim JumInputs As MSHTML.IHTMLElementCollection
    Dim JumInput As MSHTML.IHTMLElement
    Dim JumPrice As MSHTML.IHTMLElement
    Dim JumboPrice As Double
    Dim Price_In_Cents_Tag As String
    Dim SKU_tag As String, SKU_url As String
    SKU_tag = "173140KST"
    SKU_url = "https://www.jumbo.com/lu-bastogne-koeken-original-260g/173140KST/"
    XMLReq.Open "GET", SKU_url, False
    XMLReq.send
    If XMLReq.Status <> 200 Then
        MsgBox "Problem" & vbNewLine & XMLReq.Status & " - " & XMLReq.statusText
        Exit Sub
    End If
    HTMLDoc.body.innerHTML = XMLReq.responseText
    Set JumInputs = HTMLDoc.getElementsByTagName("input")
    Price_In_Cents_Tag = "PriceInCents_" & SKU_tag
    Set JumPrice = HTMLDoc.getElementById(Price_In_Cents_Tag)
    JumboPrice = JumPrice.getAttribute("value") / 100
    Debug.Print JumboPrice
End Sub

---

Thanks so much for any help, please let me know if i can clarify any further.