Getting fund prices into excel

[NoBrakesNeeded]

Hi All,
Since morningstar decided to revamp their website and completely screw up my unsophisticated method of scraping fund prices and other data, I now need a new solution, ideally free!

Prior to the revamp, I used the "Get data from web" and grabbed the fund price which then was linked to a finance spreadsheet. I've managed to find another site (AJBell & HL) that can give me the data I need but it's going to be pretty brittle.

Does anyone know of a finance website that has a free API I can grab NAV, Charge, Asset Allocation by country from?

[C69]

Would Yahoo Finance allow you to do what you want to do?

[okgo]

Google sheets has some of this built in natively via Google finance, you use the ticker and then just update the amount of units held and it ll pull in live prices.

Not sure about all of the functions you need there but I suspect it may well be possible with Sheets too

[NowWatchThisDrive]

okgo's suggestion of Google Sheets, with its =GOOGLEFINANCE() function, is a good one. The only thing is that from memory I know it can definitely get data for listed instruments like stocks and ETFs, but I don't think it can for OEICs/unit trusts.

For the latter, I've not tried this myself but one option is to import the JSON from Yahoo Finance's API (e.g. https://query1.finance.yahoo.com/v8/finance/chart/... corresponding to https://uk.finance.yahoo.com/quote/0P00018XAR.L/ ). In Google Sheets It looks like you could do this using =IMPORTJSON(), and in Excel either using PowerQuery or - if you're up to it technically - knocking up a simple Python script to call the API and parse the results (using the requests and json modules) and installing an excellent free Excel plugin called xlwings which would then allow you to call that Python script within your sheet and output the result to your cell of choice.

[SlimJ]

Yes I ran into the same issue for tracking funds on Morningstar on my Google Sheets.

I changed my tracking to FT.com and it appears to work better for me (more reliable, updates sooner).

For 'Vanguard FTSE Global All Cap Index Fund GBP Acc' I just use this in Google Sheets.

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

(just amend 'GB00BD3RZ582' to the fund you need)

[Planet Claire]

Have you got O365? If so you can use the Stocks functionality.

This Youtube video will give you information on how to use it
https://www.youtube.com/watch?v=D44EDant0rs

[NowWatchThisDrive]

SlimJ said:

Yes I ran into the same issue for tracking funds on Morningstar on my Google Sheets.

I changed my tracking to FT.com and it appears to work better for me (more reliable, updates sooner).

For 'Vanguard FTSE Global All Cap Index Fund GBP Acc' I just use this in Google Sheets.

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

(just amend 'GB00BD3RZ582' to the fund you need)

Nice. OP, I'd say this is your simplest and best bet for anything you can't pull into Excel or Sheets using the inbuilt functions suggested previously.

The only thing with any hack around importing XML/JSON is at some point you'll inevitably run into the same issue as with Morningstar where they change the schema and it breaks. But if it's not too big a change then with a bit of trial and error inspecting the HTML and CSS elements you should be able to figure out how to get it back.

[ooid]

Here is the formula for Excel.

Obviously you would need to find ticker (ETF or Stock) name exactly, and you make sure that you are connected to the web!

I used monthly but you can obviously change it to daily, weekly and etc..



PS: Tiny detail, these are 'price' not 'total return'.

[NoBrakesNeeded]

Thanks All, I'll probably stick with the AJBell/HL approach...will let you all know if I find a free API approach!