CODEWITHSUNDEEP
htmlxmlxpathgoogle-sheetsgoogle-sheets-formula
Thiago Gomes
Thiago Gomes

Reputation: 35

Trying to crawl some data on Google Sheets but getting beat by XPath

I'm trying to make a sheet for study purposes on stock market, and I'm using this website to get the data from. Taking this stock as example.

My goals here are:

  1. I want to grab some of the indicators from this div area (such as P/L, LPA, M. LÍQUIDA, and others);

  2. And some of the numbers from this tables's first column (such as row 11, 15, and others).

My issues:

  1. I'm not being able to fetch the data that I want from the div with the IMPORTXML function, neither with copying the XPath nor trying to find a specific class name to find a match.

  2. I'm being able to fetch the specific number that I want, but it's returning 3 different values from 3 different rows (I want only the first one), due the XPath that I'm using //table/tbody/tr[11]/td[2]/span.

There's 2 more tables down the page that uses the same XPath, and the function is returning the values from row #11 of the other tables, as you can see here. The only thing that makes the 3 different from one another it's their divs, but I'm not being able to figure out how to manipulate these divs. There's any way to fix this or any function that automatically deletes the other 2 rows?

Can someone give me a light? :(

Upvotes: 0

Views: 117

Answers (1)

scilence
scilence

Reputation: 1115

It's almost always easier to find the values you need by a reference. This should work to get the 20,76 from the first table

(//*[contains(text(), 'P/L')]/following::strong)[1]

As far as the second table goes, this should get 52.562,18 M

(//span[contains(text(), 'Receita Líquida')]/following::td)[1]

If you need to get different columns, you can just pass a higher index, this will return -0,07% for instance.

(//span[contains(text(), 'Receita Líquida')]/following::td)[5]

I also highly recommend getting some sort of xpath tester addon for your browser to play around with these if you don't already have one. I use ChroPath:

Firefox - https://addons.mozilla.org/en-US/firefox/addon/chropath-for-firefox/

Chrome - https://chrome.google.com/webstore/detail/chropath/ljngjbnaijcbncmcnjfhigebomdlkcjo?hl=en-US

Upvotes: 3

Related Questions