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
Got a credit card? use our Credit Card & Finance Calculators
Thanks to bruncher,niord,gvonge,Shelford,GrahamPlatt, for Donating to support the site
Google Sheets - ImportXML function and scraping data from FT.com
Re: Google Sheets - ImportXML function and scraping data from FT.com
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.
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
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
Re: Google Sheets - ImportXML function and scraping data from FT.com
JoNP, Thanks for the workaround tip. I tried this but alas with no success.
-
- 2 Lemon pips
- Posts: 142
- Joined: July 29th, 2022, 5:06 pm
- Has thanked: 20 times
- Been thanked: 36 times
Re: Google Sheets - ImportXML function and scraping data from FT.com
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
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
Return to “Portfolio Management & Review”
Who is online
Users browsing this forum: No registered users and 4 guests