Extracting Specific Data from Web Pages in Google Sheets

Other Questions Google Sheets
1

Hey everyone! I’m trying to pull some financial data into my Google spreadsheet but I’m hitting a wall. I want to grab the Current Price, 1-Year Forecast, and 5-Year Forecast for a cryptocurrency from a prediction website.

I’ve got the URL in cell C3 and I’m using IMPORTXML in D3 like this:

=ImportXML(C3, "//div[@class='col-md-4 col-xs-12 np']//div[contains(@class, 'Current Price')]/@href")

But it’s just giving me an empty result. I’ve tried a bunch of different XPath expressions but no luck.

I did manage to get all three numbers with this:

=TRANSPOSE(IMPORTXML(C3,"//div[@class='col-md-4 col-xs-12 np']//span[@class='bignum']"))

But then I have to use LEFT to chop off the ‘USD’ part. It works, but it’s messy.

Anyone know how I can grab each number separately and cleanly? I’d love to avoid using extra functions if possible. Thanks for any help!

2

hey emma, i’ve had similar issues. have u tried using regex with importxml? something like this might work:

=importxml(c3, “//span[contains(@class, ‘bignum’)][1]/text()”)

that should grab just the first number without the usd. adjust the [1] for other forecasts. hope this helps!

3

I’ve dealt with similar challenges extracting financial data. One trick that’s worked wonders for me is using Google Apps Script to fetch the data directly. It’s a bit more involved, but gives you way more control.

You’d need to write a custom function in Script Editor that uses UrlFetchApp to grab the page content, then parse it with regex or DOM methods. Something like:

function GETCRYPTODATA(url) {
var response = UrlFetchApp.fetch(url);
var content = response.getContentText();
// Parse content here
return [currentPrice, forecast1Y, forecast5Y];
}

Then in your sheet, you’d just call =GETCRYPTODATA(C3)

It’s more work upfront, but it’s incredibly flexible and reliable once set up. Plus, you can add error handling and caching to make it really robust.

4

I’ve encountered this problem before when working with cryptocurrency data. One approach that’s worked well for me is using IMPORTHTML instead of IMPORTXML. Try this formula:

=INDEX(IMPORTHTML(C3, “table”, 1), 1, 2)

This assumes the data you want is in the first table on the page, in the second column. You might need to adjust the numbers depending on the exact layout. For the other forecasts, you can modify the row number:

=INDEX(IMPORTHTML(C3, “table”, 1), 2, 2)

This method often bypasses issues with complex XPath expressions and tends to be more reliable in my experience. Let me know if this works for your specific case.