Google Sheets - ImportXML function and scraping data from FT.com
Posted: March 24th, 2024, 4:57 pm
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
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