UK fund prices in Google docs

IronWolf

Hi

I have built a Google spreadsheet to monitor my portfolio and managed to link cells to share prices on Yahoo, but this method doesn't seem to work for UK funds or trackers. Does anyone know a way of bringing in the prices from the web?

Thanks

ukflippy

I'm not sure about UK funds, but Google docs provide a Spreadsheet function to retrieve stock prices, which would remove your reliance on Yahoo :
=GoogleFinance("TSCO.L") gives the UK share price in pence.
=GoogleFinance("AMZN") gives the US stock price in dollars.
=GoogleFinance("CURRENCY:USDGBP) gives the exchange rate from dollars to pounds.

Apparently it's not fully internationalised yet, so if your portfolio is more diverse than mine, it might not work, but so far, it's matched what I've seen in my trading account. I'm also interested in getting fund prices to autoupdate - it'd save me 10 mins a day transcribing prices!

jimjames

Google finance has most UK funds on it. I've found a few that it resolves but doesn't show the price - well it does but it is 0.

I'm currently building a replacement for MS Money so I'm keen to find the best source - so far it has been Google Finance but I'm open to other options.

2shy

In a standard excel file you can link to a web page and get data from that.

For example go to the DATA ribbon, select FROM WEB and type in the web page you are interested in.

http://uk.finance.yahoo.com/q?s=GB00B4PQW151&ql=1
would give you the Vanguard Life Strategy 80% Acc Fund.

You could then select the area with the Previous Close price and put that into your sheet.

Shaolin_Monkey

I think Google Finance will find the prices of most OEIC funds. Many unit trusts seem to return a price of 0 (I guess it's probably set up to report mid price rather than bid or offer prices).

You can put them into a Google spreadsheet, e.g.

=GoogleFinance(Cell, "price")

"Cell" is the cell which contains the "ticker code" for the particular fund you want the price of (which you'd have to look up on the Google Finance site). There is an example spreadsheet here - https://docs.google.com/spreadsheet/ccc?key=0AmC2e0ZRb74vdEpPUDRXdmhxNndCXzg3UnJHamdyZ2c&usp=sharing

EDIT: Updated it with some further functions that are listed here. With the NAV date showing it's clear that some fund prices update quicker than others, which is fairly typical with free data. Obviously the Blackrock fund has missing info, because it's a unit trust.

Lokolo

2shy wrote: »

In a standard excel file you can link to a web page and get data from that.

For example go to the DATA ribbon, select FROM WEB and type in the web page you are interested in.

http://uk.finance.yahoo.com/q?s=GB00B4PQW151&ql=1
would give you the Vanguard Life Strategy 80% Acc Fund.

You could then select the area with the Previous Close price and put that into your sheet.

This is what I do with my Excel spreadsheet. I have a hidden sheet which updates everytime I load Excel.

I know this site: http://www.fundlistings.com/ would be very useful if you could download the data.

IronWolf

Shaolin_Monkey wrote: »

I think Google Finance will find the prices of most OEIC funds. Many unit trusts seem to return a price of 0 (I guess it's probably set up to report mid price rather than bid or offer prices).

You can put them into a Google spreadsheet, e.g.

=GoogleFinance(Cell, "price")

"Cell" is the cell which contains the "ticker code" for the particular fund you want the price of (which you'd have to look up on the Google Finance site). There is an example spreadsheet here - https://docs.google.com/spreadsheet/ccc?key=0AmC2e0ZRb74vdEpPUDRXdmhxNndCXzg3UnJHamdyZ2c&usp=sharing

EDIT: Updated it with some further functions that are listed here. With the NAV date showing it's clear that some fund prices update quicker than others, which is fairly typical with free data. Obviously the Blackrock fund has missing info, because it's a unit trust.

Great thanks that works

I didnt realise Google finance also had international stocks, looks like I dont need to use Yahoo afterall.

Gromitt

I use * (an old version before they ruined it) with a plugin for securities. It lets me describe the transfers from my bank account to the purchase of stocks and shares and then tracks the values over time and can then show performance graphs and loss/profits. I use it to keep all my bank accounts in one place, so its nice to keep everything together.

Actually, why am I typing this, as it bears no resemblance to the original question. Oops. I'm going to remove the package name before someone reports me for spamming or advertising which is not the intention.

Lakeuk

With excel I use a stock market functions add-in which gets prices from yahoo finance, it works very well, I use it to get historic pricing as well.