Unit trust and OEIC prices in Google Sheets

[TedSwippet]

Google Sheet's GoogleFinance() function is a nifty way to insert stock prices into a spreadsheet. It also works for UK unit trusts and OEICs, but apparently only if you know the magic incantation that encodes the fund for which you want the price.

For example, to fetch the Vanguard UK FTSE UK All Share Index Inc price you have to use:

=GoogleFinance("MUTF_GB:VANG_FTSE_UK_BSJM2F")

In the old Google Finance offering it was sort-of possible, with a bit of fiddling around, to uncover this magic incantation from a fund's name and a well-targeted search. But now, with the New! and Improved! Google Finance, I have not been able to find any way to do that.

For the moment my spreadsheet still works, but only because I already know the magic strings for the funds I hold. As soon as I buy a fund that I haven't held before it looks like I could be SOL on capturing prices from GoogleFinance() even if Google Finance holds them, because they'll be hidden. I will then be reduced to screen-scraping Trustnet or FT Funds. Doable -- indeed, I do that for a few funds not available from GoogleFinance() -- but fiddly and all too reliant on Google's backends for ImportHtml().

Has anyone else encountered this? If yes, have you found any way around it? Thanks.

[Itsallaguess]

For example, to fetch the Vanguard UK FTSE UK All Share Index Inc price you have to use:

=GoogleFinance("MUTF_GB:VANG_FTSE_UK_BSJM2F")

In the old Google Finance offering it was sort-of possible, with a bit of fiddling around, to uncover this magic incantation from a fund's name and a well-targeted search. But now, with the New! and Improved! Google Finance, I have not been able to find any way to do that.

Does this help?

https://finance.google.com/finance?q=un ... 1&num=2000

https://finance.google.com/finance?q=oe ... 1&num=2000

I think using the search box on the above pages will also help get you to the string you need, if any of the above two links don't specifically hold the one you're looking for. Note that there are pages on the above links, so you're only looking at a sub-set of the list on any one page...

Text file here containing all the pages from the first link, for searching on if it helps (and which holds your VANG_FTSE_UK_BSJM2F string too...) -

http://www.filedropper.com/fundlist

Cheers,

Itsallaguess

[TedSwippet]

Does this help?

Why... yes, it does :-) Thanks.

That said, I wonder for how much longer. The results returned here are clearly the old Google Finance stuff, including the now-deprecated Portfolios feature. I fear it may be only a matter of time before this stuff disappears entirely from Google Finance. Meanwhile, perhaps I had better brush up my Xpath and/or Javascript skills...

[MusingMarket]

OEIC pricing seems to have been killed off overnight? Now getting no data for the handful of OEICs I hold, eg. Fundsmith:

"When evaluating GOOGLEFINANCE, the query for the symbol: 'FUND_EQUI_I_HW6YXS' returned no data."

Google have also stopped the ability to use the RDSB.L nomenclature for stocks but LON:RDSB still works.

Annoying since I'll now have to scrape sites for stocks individually using IMPORTXML (in combination with REGEXEXTRACT if needbe). I've always had to do that with my Lindsell Train Global holding that Google never seemed to list but it is a frailer way of doing things.

[mike922]

OEIC pricing seems to have been killed off overnight? Now getting no data for the handful of OEICs I hold, eg. Fundsmith:

"When evaluating GOOGLEFINANCE, the query for the symbol: 'FUND_EQUI_I_HW6YXS' returned no data."

Google have also stopped the ability to use the RDSB.L nomenclature for stocks but LON:RDSB still works.

Annoying since I'll now have to scrape sites for stocks individually using IMPORTXML (in combination with REGEXEXTRACT if needbe). I've always had to do that with my Lindsell Train Global holding that Google never seemed to list but it is a frailer way of doing things.

I'm having the same issue with OEICs MusingMarket.

Can you please share an example of the IMPORTXML which you used?

Thanks

[TedSwippet]

When evaluating GOOGLEFINANCE, the query for the symbol: 'FUND_EQUI_I_HW6YXS' returned no data.

Yup, it looks they have finally killed it entirely. Thanks for nothing, Google.

Annoying since I'll now have to scrape sites for stocks individually using IMPORTXML (in combination with REGEXEXTRACT if needbe). I've always had to do that with my Lindsell Train Global holding that Google never seemed to list but it is a frailer way of doing things.

What sites are you scraping, and how?

I have had decent success using ImportHtml() with both Morningstar and FT Funds. Neither is over-tricky to dissect -- a combination of Mid(), Index() and some magic numbers seems to suffice. And when I do see flakiness and frailty, it is generally not from the underlying site but rather from Google's own backends not pulling the data assertively enough for the ImportHtml() function.

Trustnet, on the other hand, I have found to be a scramble of entirely unscrapeable Javascript.

Sigh.

[LittleDorrit]

What a pain, my beautiful spreadsheet wrecked! Is this what you get for complaining about facebook.

My old expression for pulling MG Strategic Bond from google was:-

=GOOGLEFINANCE(MUTF_GB:MG_STRA_CORP_H3S7ID, "price")

Simply because I happen to have a dailymail portfolio I went to their factsheet:-

http://webfund6.financialexpress.net/cl ... iCode=0ZFW

and pulled from the table, line 4,column 2, the price, which seems to work.

= index(ImportHTML("http://webfund6.financialexpress.net/cl ... ,"table",1),4,2)

[LittleDorrit]

Ahh, one problem I can't remove the "p" sign after the value to form a number.

[MusingMarket]

Can you please share an example of the IMPORTXML which you used?

Thanks

Sure, this is the quick and dirty way I do it:
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Cell A2 has the unwieldy xpath query. This has been grabbed from the specific webpage with the fund price using the selectorgadget chrome extension (info at http://selectorgadget.com/).

The B column uses the IMPORTXML function with the fund specific url and the cell with the xpath query.
The C column strips the text using REGEXTRACT for the price.

Since the price in column C could be in pounds or pence:
The D column gives GBP (pounds) for all prices by checking whether the scraped text starts with GBX (pence) and dividing by 100 if it does so.
The E column does the opposite of D (by checking for GBP and multiplying by 100 if so).

What sites are you scraping, and how?

I have had decent success using ImportHtml() with both Morningstar and FT Funds.

I scrape Morningstar in the above example but it should work for any website with fund prices using the correct xpath query. I should use ImportHtml but I didn't immediately get how you could get the granularity provided with ImportXML.

[LittleDorrit]

O.K. Just needs another cell to trim the number.

i.e. =LEFT(........,4)

[mike922]

Wow, That's awesome.

Thanks MusingMarket

[LittleDorrit]

Sure, this is the quick and dirty way I do it:
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Musing,

Many thanks for the elegant explanation.

LD

[TedSwippet]

I scrape Morningstar in the above example but it should work for any website with fund prices using the correct xpath query. I should use ImportHtml but I didn't immediately get how you could get the granularity provided with ImportXML.

The ImportHtml() is along the same lines as described by LittleDorrit upthread. Retrieve the relevant HTML table containing the price, then index() into that. The magic numbers will probably be different for Morningstar, but a quick look at the HTML source and/or a bit of experimentation uncovers them.

I have the converse problem to you. I have never been able to get ImportXml() to do quite what I want, nor have I ever been able to persuade Xpath to satisfactorily extract the target data (and looking at the arcane Xpath query in your spreadsheet example, no wonder!). Thanks for sharing.

[mike922]

MusingMarket,

Any tips on using SelectorGadget ? I tried reading the docs but I can't seem to select a Xpath.

In Chome, I tried inspect and getting the xpath from the developer console but it doesn't like that either.

Thanks.

[mike922]

MusingMarket,

Any tips on using SelectorGadget ? I tried reading the docs but I can't seem to select a Xpath.

In Chome, I tried inspect and getting the xpath from the developer console but it doesn't like that either.

Thanks.

No need, got it to work.

Just a typo.

Thanks again for the detailed explanation MusingMArket.

[syrio]

Sure, this is the quick and dirty way I do it:
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Really helpful thanks - perhaps you could put this in a thread of its own so it is easier to find.

I found that I needed to change A2 to $A$2 in the REGEXTRACT expression so that it worked when I copied rows.

[xeny]

Today the prices have reappeared, for me at least.

[TedSwippet]

Today the prices have reappeared, for me at least.

Yes, thanks. I'd noticed that too.

Given that the query upthread for finding the magic GoogleFinance() function string for UK OEICs and unit trusts appears to permanently no longer work though, it's a bit of an open question as to how long this service will last. Best make hay while the sun shines, I guess.

[TedSwippet]

I haven't been able to retrieve any UK OEIC fund prices from Google Sheets for over a week now.

It looks like they really are all gone for good this time. Sigh.

[xeny]

It's looking as if the solution is to use the importhtml function - see the end of this thread:

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