The Lemon Fool

Posted: **May 15th, 2023, 7:59 am**

Hi All

I have a google sheet that I use to keep some portfolio information on. This drags in share prices from google finance "live" using the =GOOGLEFINANCE("lon:Vod","price") function

Occasionally it has stopped working but normally gets resolved a bit later in the day. I assume something to do with not being able to find the look up on google. The individual share price does show up if you go on the web though.

Anyway this week it went wrong again, more than half my shares were showing issues with the share prices resulting in lots of N/A's in cells across the sheet. This time its been down for days including all week end and this morning.

Is anyone else having issues, or is google "updating" its website and "improving" it (for the worse)

Does anyone know how to get live prices for funds which dont have EPICs but have SEDOL or ISIN.

Posted: **May 15th, 2023, 8:01 am**

I found it was not working for 3 days last week, but it has been OK since Saturday

Posted: **May 15th, 2023, 8:15 am**

Gerry557 wrote:Does anyone know how to get live prices for funds which dont have EPICs but have SEDOL or ISIN.

Try https://www.fidelity.co.uk/

Posted: **May 15th, 2023, 8:23 am**

My google sheet portfolio is currently showing #N/A for just about everything

Posted: **May 15th, 2023, 8:27 am**

smokey01 wrote:
My google sheet portfolio is currently showing #N/A for just about everything

There's a separate but related thread on the go with a flexible Google Sheets scripting function that can get share-prices and other related information from a currently-reliable alternative Yahoo Finance data-source -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=39118#p589024

I'm happy to take any related questions on that thread if it's something that interests you, but the linked solution can be up and running in a couple of minutes on Google Sheets -

Source - my own Google Sheet

Cheers,

Itsallaguess

Posted: **May 15th, 2023, 8:30 am**

smokey01 wrote:My google sheet portfolio is currently showing #N/A for just about everything

so much for my first response, so is mine :roll:

Posted: **May 15th, 2023, 10:36 am**

Itsallaguess wrote:
smokey01 wrote:
My google sheet portfolio is currently showing #N/A for just about everything

There's a separate but related thread on the go with a flexible Google Sheets scripting function that can get share-prices and other related information from a currently-reliable alternative Yahoo Finance data-source -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=39118#p589024

I'm happy to take any related questions on that thread if it's something that interests you, but the linked solution can be up and running in a couple of minutes on Google Sheets -

Thanks, I’ll take a look and see if I can figure out and if not I’ll ask questions on that thread

Source - my own Google Sheet

Cheers,

Itsallaguess

Posted: **May 15th, 2023, 1:26 pm**

Just checked again and most are still not working. It's never been this many and for so long before. I assumed it was because of an update or something but maybe not.

I might try linking the yahoo info instead. I like the dividend bit. Currently I update mine manually after announcements, roughly keeping it live for providing buying yield. I often find websites divi info is out of date or just plain wrong but it would provide a good starting point.

Posted: **May 15th, 2023, 1:50 pm**

smokey01 wrote:
Itsallaguess wrote:
There's a separate but related thread on the go with a flexible Google Sheets scripting function that can get share-prices and other related information from a currently-reliable alternative Yahoo Finance data-source -

https://www.lemonfool.co.uk/viewtopic.php?f=27&t=39118#p589024

I'm happy to take any related questions on that thread if it's something that interests you, but the linked solution can be up and running in a couple of minutes on Google Sheets -

Thanks, I’ll take a look and see if I can figure out and if not I’ll ask questions on that thread

Source - my own Google Sheet

Cheers,

Itsallaguess

Whoops I notice my earlier reply went in the middle of the quoted text so let me try again:-

Thanks, I’ll take a look and see if I can figure out and if not I’ll ask questions on that thread

Posted: **May 15th, 2023, 2:31 pm**

Gerry557 wrote:Does anyone know how to get live prices for funds which dont have EPICs but have SEDOL or ISIN.

I've had some success 'scraping' Morningstar and/or FT Funds web pages. For example, something like:

Code: Select all

=index(importhtml("https://markets.ft.com/data/funds/tearsheet/historical?s=GB00B4KWNF91:GBP", "table", 1),2,5)

A bit fiddly, and can sometimes take a while to load, particularly if you have several of these in a sheet (throttled Google backend?).

Also, the currency indicator (GBP or GBX) isn't entirely reliable over time. I sometimes see a chunk of my portfolio has dropped or risen by two orders of magnitude(!), because despite the apparent currency indicated in the request (after the colon), the page changed from pricing in pounds to pricing in pence, or vice versa. It's infrequent enough that I haven't bothered scraping the page for the GBP/GBX indicator; I see this maybe once a quarter or so, and it's very obvious when it happens.

The googlefinance() function used to provide full UK unit trust and fund prices, but stopped a number of years ago, after fading in and out for week or two. (It still works for US funds; apparently we just weren't worth the effort in the UK.) It's unclear if UK ETFs and stock prices are going the same way, or if this is just a glitch.

Posted: **May 16th, 2023, 5:56 am**

looks like googlefinance() is up and running again this morning

Posted: **May 16th, 2023, 7:46 am**

Thanks just checked mine, and it's all OK, let's hope it lasts a bit longer this time

Posted: **May 16th, 2023, 8:41 am**

nmdhqbc wrote:looks like googlefinance() is up and running again this morning

Unless you own Vodafone which is apparently worth zero. :shock:

Posted: **May 16th, 2023, 8:55 am**

I might try adding the yahoo thing to my sheet even on another tab and have dual views just in case

Ted, thanks for that. I might give that a go too. I did have some success with a couple of funds with Google Finance but as you point out it was a while ago. Searching for funds was hit and miss often finding similar ones but not the exact version I held.

Posted: **May 16th, 2023, 10:43 am**

Gerry557 wrote:Ted, thanks for that. I might give that a go too. I did have some success with a couple of funds with Google Finance but as you point out it was a while ago. Searching for funds was hit and miss often finding similar ones but not the exact version I held.

FWIW, I have a more efficient (albeit even less clear!) scrape. For FT Funds:

Code: Select all

=mid(importxml("https://markets.ft.com/data/funds/tearsheet/historical?s=GB00B4KWNF91:GBP", "//span[@class='mod-ui-data-list__value']"),1,99)

It's also possible to scrape Morningstar, xpath query "//td[@class='line text']", but based on URL's constructed to contain Morningstar's internal fund ID rather than ISIN, so a bit less convenient.

With both FT Funds and Morningstar, if you have more than a handful of these in a sheet it can take seconds or even minutes for them to load. Throttling, either by Google backends or by the sites themselves. Either way though, they usually resolve eventually.

I do feel mildly guilty pulling down a whole complex web page just to pick a single number out of it, but it's only necessary because Google dropped providing UK fund prices through the Googlefinance() function. It's also a bit brittle, and will probably fail at some point, since it both relies heavily on precise details of a web page and takes icky xpath shortcuts.

Posted: **May 17th, 2023, 6:42 am**

While GoogleFinance maybe working now as far as returning values goes I notice today that it is returning a price for Vodafone of 87.27 while YahooFinance is giving a price of 83.33 which I believe is the correct price.

I have been noticing discrepancies like this for a couple of months, however the previous close price does always match other sources.

As I only have an iPad I cannot use HYPTUS or implement the JSON code mentioned in the Yahoo scrape thread. So a bit annoying that I cannot rely on my Google Sheets spreadsheet!

Posted: **May 17th, 2023, 1:26 pm**

Gerry557 wrote:
nmdhqbc wrote:looks like googlefinance() is up and running again this morning

Unless you own Vodafone which is apparently worth zero.

Ah, the 'forward look' function!

Posted: **May 17th, 2023, 6:48 pm**

I hope that forward function is not too near by.

Share price a bit closer at end of play 82.20 v 82.34 on yahoo.

I have my Vodafone price now working. I cut and pasted the function from ulvr holding and replaced the ulvr with Vod and this got it working again.

I wonder if a lower case "v" might have been the issue.

The Lemon Fool

Google Finance - gone wobbly ?

Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?

Re: Google Finance - gone wobbly ?