CODEWITHSUNDEEP
google-sheetsweb-scraping
Nataliikaa PetroOwwa
Nataliikaa PetroOwwa

Reputation: 43

Scrape website with Google Sheets IMPORTXML function

I need to scrape the "Cpn Rate Ann Amt" cell from this website LINK.

I have tried this:

=IMPORTXML("https://www.quantumonline.com/search.cfm?tickersymbol=BAC-P&sopt=symbol","/html/body/font/table/tbody/tr/td[2]/p[1]/table[2]/tbody/tr[2]/td[2]/font")

as suggested by literally every tutorial I saw online, but I just couldn't get it to work. Maybe it just doesn't work since the website has .cfm pages.

Upvotes: 1

Views: 266

Answers (1)

Tanaike
Tanaike

Reputation: 201348

I believe your goal as follows.

  • From I need to scrape the "Cpn Rate Ann Amt" cell from this website LINK., you want to retrieve the value of the header of Cpn Rate Ann Amt using IMPORTXML.

In this case, how about the following sample formula and xpath? When I saw the HTML in the URL, I thought that the background color of <tr bgcolor="FFEFB5"> is only the table, and this might be able to be used as the xpath.

Sample formula:

=IMPORTXML(A1,"//tr[@bgcolor='FFEFB5']/../tr[2]/td[2]")
  • The cell "A1" has the URL of https://www.quantumonline.com/search.cfm?tickersymbol=BAC-P&sopt=symbol.

Result:

enter image description here

Note:

  • When you want to the value of Cpn Rate Ann Amt, you can also use the following formula.

      =IMPORTXML(A1,"//tr[@bgcolor='FFEFB5']/th[2]")

  • In this sample formula and xpath are for the URL of https://www.quantumonline.com/search.cfm?tickersymbol=BAC-P&sopt=symbol. So when the URL is changed, the xpath might not work. So please be careful this.

Reference:

Upvotes: 1

Related Questions