Google Finance portfolio tracking

[neovo]

Has anybody got any ideas why UKDV is displaying as 10.2? Shouldn’t it be 100.2?

Edit: I’ve just changed to “LON:UKDV” but this still hasn’t changed it?

[neovo]

I’ve just realised I’m not sure this is where the problem is: I have a column which indicates the current value

It’s saying the current value of UKDV is £0.10

Any help much appreciated , tearing my hair out

[neovo]

This is fantastic and I may of pinched all your formulas thanks

[Jim_mcgrain (Jim)]

Check the “currency” attribute - GBX shows as pence / GBP pounds.

[neovo]

Think it is? Here’s the formula , shamelessly stolen from @simram

Edit: are you saying this should be GBX?

[neovo]

Sorry to mither, complete n00b

Is it possible to insert a graph into google sheets which tracks portofilio value?

[neovo]

I’m happy to share mine if you like?

All you’d need to do is change the ticker and no of shares ?

I’m a bit of n00b but have managed it , I’m sure you can too

[simran (Simran Cashyap)]

I’ll add some improvements into the Portfolio Management when I can, and re-share.

Some thoughts:

• If you’d like to make a personal copy of the Google Sheet to enter your own portfolio - just go File > Make a Copy. I encourage you to copy it and improve!
• Google say that the currency of UK stocks are GBP, but then return it in pence, hence my formula hack to bring in line. I haven’t added in any FX considerations for other currencies, so we should add all to the static data pages.
• Tracking portfolio value over time is a bit of a pain, because you need to factor in when stocks were bought and sold, which is a bit harder in Google Sheets. Once done we can start looking at more interesting risk and performance metrics.
• The Google Finance API doesn’t support dividends and related data any more (as far as I know). They are available from Yahoo, but it’s a much more clunky to fetch them, e.g. something like this should work for the dividend yield:

=INDEX(SPLIT(IMPORTXML(CONCATENATE(“https://finance.yahoo.com/quote/“,“VOD.L”),”//*[@id=‘quote-summary’]/div[2]/table/tbody/tr[6]/td[2]“),”()”),1,2)

Obviously, something like this would be fab in Freetrade (depending on scope / priority) or to have portfolio data accessible via a Freetrade API, so we can build a proper web app.

[Chris]

This looks really useful. Going to try and do something via Excel.

[neovo]

Here’s mine, basically taken from sim

https://docs.google.com/spreadsheets/d/1rOFbKFU6TpJUBKtjTakbXlxaJZhhjUQE5fgiDbH8ylQ/edit?usp=sharing

[neovo]

Thanks for this insightful

Do you know if it’s possible to change how often prices change in google sheets?

[neovo]

Sorry to bombard this thread but everybody super helpful

Any ideas what could be going on here? It’s worked fine all week, opened this morning and it’s displaying this. All the other stocks/ tickers working with that same formula…

[Rat_au_van (Emma)]

Might just be an issue that it’s not returning data today, see it happening occasionally with stocks on the Apple app. See if it starts working later

[neovo]

Ah ok fair enough

Will check back later thanks!

[rod]

Sadly Google Finance has had intermittent trouble getting prices for UK-listed things since March 2018, when Google did a big update. And for UK listed mutual funds, not at all since then in my experience.

[Update: see Google Finance portfolio tracking - #67 by rod for UK mutual fund prices in Goog Sheets]

[neovo]

That’s annoying, still hadn’t been corrected

[Wulfy]

May be best to return the value in a hidden cell somewhere and then divide the value by 100?

[neovo]

The issue I had the other day was not putting ‘LON:’ in front of the ticker, have you done this?

[Jim_mcgrain (Jim)]

Put the “currency” attribute into a hidden column - if GBX divide by 100.

[Jim_mcgrain (Jim)]

What is it you want to do with dividends / yields? One of the Import functions might work for you.