GoogleFinance

Ciprico

Reading an interesting comment on here from Brian65 I decided to experiment with Googlefinance, with mixed results...

My aim was to create a simple spreadsheet that dynamically updated stock.

To start I download portfolio from II as CSV and copied into Google spreadsheets.

This provided tidy lists with symbols (with unhelpful .L, ie VERX.L) and quantity etc

Using simple function =googlefinance( "verx", "price") produces the correct price - all very good.

But for some funds the price was wrong, ie

Bankers IT (BNKR) =googlefinance( "bnkr", "price") produces £0.70 should be iro £10 (I tried BNKR.L)

Also some of the funds are not found ie VLS60, symbol B3TYHH9
=googlefinance( "B3TYHH9", "price") produces an error.

LINK FUND SOLUTIONS LTD TROJAN X ACC also doesn't work
=googlefinance( "bz6cns3", "price")

Is anyone able to give me some pointers ? Are the "B...." a different type of symbol...

Many Thanks

cloud_dog

Hi

Re BNKR, should the format be... =googlefinance("LSE:bnkr", "price")

...it just so happens there is a Canadian stock with the BNKR sticker, and it just happens to have stock price of 0.70 so, with google being a North American company it may default to any sticker without an exchange identifier to being under one of the North American exchanges.

I'm not a user of googlefinance but do they provide UK OIEC details/prices?

EdSwippet

cloud_dog wrote: »

I'm not a user of googlefinance but do they provide UK OIEC details/prices?

They used to, but unhelpfully dropped them a couple of years ago, around the point where Google "improved" Google Finance into something that is now pretty well useless (at least for non-US investors). More in this thread from another forum:

Unit trust and OEIC prices in Google Sheets - The Lemon Fool

One workround is to use importhtml() and a bunch of string extraction functions to scrape fund prices from other sources (FT funds, Morningstar, etc). A bit fiddly, but possible with patience.

Ciprico

That didn't work - nor did "lon"

After reading below it seems that googlefinance is a world of pain, I won't be pursuing it...

https://www.lemonfool.co.uk/viewtopic.php?t=10111

bowlhead99

123mat123 wrote: »

That didn't work - nor did "lon"

Prefixing London stock tickers with Lon: works fine for me when using Google sheets, just as it does on finance.google.com (or wherever the Google finance homepage redirects to these days)

See below.
I agree the lack of easy support for funds and some stocks makes it too tricky to bother with and it's a shame they stopped allowing you to do portfolio tracking on the old Google finance site online.

Brian65

I'm happy with Google Sheets but then I only have Shares, Investment Trusts and ETFs - all quoted on the London Stock Exchange.
You don't have to do it on excel first, but if you've already got it on excel its easy to import it into Google Sheets.
Handy being able to access the continually updated sheets from any computer without having to save and transfer it on usb sticks between each one. Or worry about losing the updated version if computer breaks down, gets stolen, or gets a virus and needs re-formatting etc.
But if you have other funds then perhaps the live share price feature isn't for you.

MK62

You can get Google sheets working using importhtml, but it is a bit fiddly to get it set up at first......once done it's fine though......that said, it might be easier to just use Trustnet's Portfolio feature.

I use both and there is little difference in the value reporting tbh (but that's expected as Sheets is scraping Trustnet for data)......though obviously with Sheets you then have the power of a spreadsheet to do all sorts of other things with the data.....

capital0ne

I use it, its dead easy once you've read the online help, but here is what I do
A B C D
1 ISIN QTY PRICE (p) VALUE (£)
2 LON:RDSB 520 2015.5 £10,481
3 LON:BNKR 600 1012 £6,072

First rule precede all FTSE shares with LON:
Above A2 contains LON:RDSB
B2 contains the number of shares I have, 520
C2 has the formula =GoogleFinance(A2,"price")
D2 has the formula =B2*C2/100
And I do a whole load of other calcs, like tracking my profit or loss
Simples if you know how and know excel

MK62

You can look these up online too, but here are the cell entries I use in Google Sheets, with some example funds.....the entries are "layered", each enclosing the prior entry in brackets (I've attempted to show that using colours).....

=ImportHTML("http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=G1HH","table",1) - imports table 1 from the URL at "http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=G1HH", where G1HH is the Citicode for the fund HSBC Global Strategy Dynamic C Acc
=index(ImportHTML("http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=G1HH","table",1),4,2) - as above, but also then extracts (indexes) the value located at 4 down, 2 across in that table (ie the current price in most cases).
=left(index(ImportHTML("http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=G1HH","table",1),4,2),6) - as above, but also then trims the value to be just the leftmost 6 characters

=ImportHTML("http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=ACDT","table",1)
=index(ImportHTML("http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=ACDT","table",1),4,2)
=right(index(ImportHTML("http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=ACDT","table",1),4,2),6)*100

As the G1HH example above, but now using Citicode ACDT (Vanguard LifeStrategy80 A Acc)
and extracting the rightmost 6 chars, and multiplying by 100 to put the price into pence rather than pounds (simply omit the "*100" off the end if price in pounds is the desired format).
The left/right entries are usually just for stripping off a leading "£", or trailing "p" - just omit if not required.

Just substitute the 4 digit/char citicode for the fund you want - you can get the citicode for the particular unit class you want by going to the Trustnet page for the fund in question.
eg for HSBC Global Strategy Dynamic C Acc, go to the Trustnet page for HSBC Global Strategy Dynamic, and select "All Units"....the citicodes are listed there for each class of the fund, in both ACC and INC versions (if available)

Occasionally (usually dual price funds) the current price is not at 4,2 in the table - it's usually then at 3,2 or 5,2 depending on whether you want the bid or offer price.
You can check by just importing the whole table and count down, then across, to the value you want.....=ImportHTML("http://webfund6.financialexpress.net/clients/dailymail/FundFactsheet.aspx?CitiCode=G1HH","table",1)....will import the whole table, so you can see what you want and count how to index (extract) it - do this in a blank sheet though to make sure it doesn't overwrite anything in your main sheet.

As I said earlier though, if you just want a snapshot of your portfolio value, then Google Sheets is arguably not worth the effort, just use Trustnet's Portfolio feature for that........but if you want to do more, Google Sheets is really quite good, once the initial setup pain is over.