Getting ETF price data in Google Sheets is easy as it’s provided via the GOOGLEFINANCE command. Accessing fund prices is much tougher as they aren’t available via this command any more (they were for a while but it stopped getting updated and was then removed several years ago). The best solution the internet has come up with is scraping the price off a website. In my case I use the Financial Times website but a lot of people use Yahoo Finance. All you need is a webpage structure that doesn’t change rapidly.
I should point out that I didn’t think of this but many of the explanations I’ve come across are short and wouldn’t be particularly easy to understand for people that don’t work in computing.
For example this example I’m looking to get the daily price of the “Vanguard LifeStrategy 80% Equity Fund A Acc”. I’ll be scraping it from the Financial times website. The first thing you need is the URL of the page you want to scrape. This is as simple as going to the Financial Time Markets website and finding the fund you’re interested in.
You should end up with a URL that looks something like this:
https://markets.ft.com/data/funds/tearsheet/summary?s=GB00B4PQW151:GBP
The code at the end of the URL is the ISIN or International Securities Identification Number, it’s a unique number for a particular security.
Next you need to write an XPath statement which will locate the price in the page. This is easier said that done if you’ve never worked with XML before. Fortunately you can find plenty of examples and help online and with a little perseverance you should be able to figure it out.
I’m sure this is possible in Chrome and other browsers as well but if you use Firefox you can test XPath statements against the loaded page using the built in developer tools. If you open the developer tools (F12) and switch to the console tab you can type XPath in directly. Helpfully it runs what you type against the page in realtime so you can see exactly when you’ve screwed up. You need to wrap the XPath statement like this:
$x(""/xpath/goes/here")
Now you know how to test an XPath statement it’s time to develop one. My first pass at pulling the price out of the page was this:
//span[@class='mod-ui-data-list__value']/text()
This does the job but it leaves a bit to be desired as it blindly selects the text from the first span that has the given class. There are, in fact, three span elements that have this class at the time of writing. The span with the price just happens to be first so this works.
A better solution is to use a query like this:
//li[span[@class='mod-ui-data-list__label']='Price (GBP)']/span[2]/text()
This finds the list item that contains a span which with the correct style and has the text “Price (GBP)”. This is a good indication that the value extracted will be the correct one. It then selects the second span in that list item, which contains the price.
The complete command in Google Sheets would look something like this:
=importxml(https://markets.ft.com/data/funds/tearsheet/summary?s=GB00B4PQW151:GBP,"//li[span[@class='mod-ui-data-list__label']='Price (GBP)']/span[2]/text()")
I usually don’t include the URL directly in the function as I feel that it clutters an already cluttered cell. Instead I place it in another cell and then reference it. This has the added advantage that you can also just drag-copy the cell to populate other cells where this functionality is needed.