Google Sheets - ImportXML function and scraping data from FT.com

[JPGH]

I had, up to last week, been able to load up a Google Sheet I developed that has numerous (100 or so) cells that use the ImportXML function to scrape data from FT.COM for my specific data needs i.e. for UK Unit Trust/Pension Fund data that I can't source via using the GoogleFinance function or Yahoo.com. I have/had been using my work laptop in all cases, accessing Sheets either via work internet or via home wifi.

I had been using Google sheets for about 3 months or so since Dec 2023 and all was fine, albeit very slow, to get data then earlier this week the data updates stopped functioning or at at least it is at best a sporadic "scrape" in which I get a little data for a random few cells, but mostly I don't get any at all, and instead get a "N/A" in the cell, and a message "could not fetch URL https://markets.ft.com/data/funds/tears ... ZDNB53:GBP" (using example here of a fund for which I wanted to get data).

Furthermore for the example fund "GB00B0ZDNB53:GBP" (which I locate in cell A4 of Sheet) I use A4 in formula =importxml("https://markets.ft.com/data/funds/tearsheet/summary?s="&A4,"/html/body/div[3]/div[2]/section[1]/div/div/div[1]/div[2]/ul/li[1]/span[2]") to get it's price and a couple of other bit of data from scripts located in its FT.COM page.

Anyway the weird thing is that if I paste in full the erroneous Google sheet contents (including formats, formula/equations) into a new Google sheet it works i.e fetches/scrapes the data for a while......until I close the sheet, and then when I reopen/update data I start to get sporadic successful data scrapes or the dreaded "N/A" appearing in the cells again.

I have googled to see if there are any other users out there with a recent similar issue and haven't seen any issues raised on any forums.

Any ideas as to what is happening with reliability of my google sheet scraping FT.COM data?

TIA

JPGH

[JoNP]

I've started to get this recently, not sure what causes it but I found if I put a space between here on the end . /span[2] ") it fixes it for a bit. I used to get the same thing with morningstar until I switched to FT but I get the same thing with FT now. This is one of mine.

Code: Select all

=QUERY(IMPORTXML("https://markets.ft.com/data/funds/tearsheet/summary?s=ie00b1s75374:gbp", "//span[@class='mod-ui-data-list__value']"),"select * limit 0",1)

I add a space here __value'] ") starts working again , stops working I remove the space and it works again... Never figured out what causes it though but it's annoying lol

[JPGH]

JoNP, Thanks for the workaround tip. I tried this but alas with no success.

[y0rkiebar]

Yes, I get this also. I just modify the query by adding a trailing space and it seems to then work. I put it down to some kind of caching or timeout when loading the data initially. Modifying the formula seems to make it re-evaluate and pull in the data.

I use this query to get the price:

=IFERROR(ImportXML("https://markets.ft.com/data/funds/tearsheet/historical?s="&C30,"(//span[@class='mod-ui-data-list__value'])[1]"),0)*100

Where C30 in this case is the fund code e.g. GB00B4PQW151:GBP