CODEWITHSUNDEEP
excelvbaseleniumweb-scrapingxpath
Pedro Matheus
Pedro Matheus

Reputation: 21

NoSuchElementError. Element not found for XPath using VBA

I'm trying to extract the analysts recommendation from a stock on tipranks.com

I have a spreadsheet to place the number of analysts with a buy, hold and sell recommendation on a stock. I used Selenium and ChromeDriver to open the web site and then find the values (number of analysts per recommendation) and copy to the spreadsheet.

The code works until it tries to copy the first value. It says that it can't find the element.

"Element not found for XPath [XPath direction]"

If I copy the full XPath while the code is paused because of the error, from the Chrome window the program opened, and paste it again and click continue, then the program will run smoothly, and will find the rest of the elements on the website despite the XPath is the same as I was using before.

The problem seems to be when it tries to find the first XPath element when I run the program, regardless of the element I try to find, and once I resolve that it will find the rest.

Dim obj As New WebDriver
Dim xyz As WebElement
Dim destino As Range
obj.Start "chrome", ""
obj.Get "https://www.tipranks.com/stocks/aapl/forecast"

Application.Wait (Now + TimeValue("0:00:5"))

Dim BUYs As Range
Set BUYs = ActiveCell
BUYs = obj.FindElementByXPath("/html/body/div[1]/div/div/div[2]/article/div[2]/div/main/div[1]/section/div[2]/div[1]/div[2]/div/div[2]/div/ul/li[1]/span[2]/b").Text
ActiveCell.Offset(0, 1).Select
Set HOLDs = ActiveCell
Dim HOLDs As Range
HOLDs = obj.FindElementByXPath("/html/body/div[1]/div/div/div[2]/article/div[2]/div/main/div[1]/section/div[2]/div[1]/div[2]/div/div[2]/div/ul/li[2]/span[2]/b").Text
ActiveCell.Offset(0, 1).Select
Dim SELLs As Range
Set SELLs = ActiveCell
SELLs = obj.FindElementByXPath("/html/body/div[1]/div/div/div[2]/article/div[2]/div/main/div[1]/section/div[2]/div[1]/div[2]/div/div[2]/div/ul/li[3]/span[2]/b").Text

Upvotes: 2

Views: 1194

Answers (1)

QHarr
QHarr

Reputation: 84465

I can't test in VBA but the following is a translation from python where I use a css selector to target the desired elements. I target the parent node such that I can split by the space to generate both the description and the value e.g. Buy 19. This should avoid the problem you have with your xpath.

Dim items As WebElements, item As WebElement, tempArr() As String, arr() As Variant, r As Long
ReDim arr(1 To 3, 1 To 2)

items = obj.FindElementByCss('.client-components-pie-style__legend span + span')
For Each item in items
    r = r + 1
    tempArr = split(item.text, ' ') 
    arr(r, 1) = tempArr(0)
    arr(r, 2) = tempArr(1)
Next

Activesheet.Cells(1,1).Resize(UBound(arr,1), UBound(arr,2)) = arr

Your horribly long xpath is tripping up on the first div[2]. I would suggest, if you want to stick with xpath, use a relative, more robust path. The equivalent of my css in terms of matching would be:

Dim items As WebElements

Set items = obj.FindElementsByXpath('.//*[@class="client-components-pie-style__legendColor"]/following-sibling::span')

Upvotes: 1

Related Questions