Google Finance portfolio tracking

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

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

Edit: are you saying this should be GBX?

Sorry to mither, complete n00b

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

I tried to recreate my portfolio in Google sheets but it’s beyond me. Kept getting “error” in the columns. I’ve downloaded Yahoo finance instead and will settle for that. It’s great to have a watchlist and this is something Freetrade badly needs in app IMO. I also like the Google sheets portfolio some of you guys have made above with graphs hopefully we get these things in app one day.

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

1 Like

Here’s a great YouTube tutorial for Google Sheets I found:

I’m going to try and make one once I get access to my Freetrade portfolio again (it’s currently switching to an ISA).

4 Likes

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.

7 Likes

If anybody is looking for a ‘free’ API then check this out. For anybody not into scripting or programming there is an excel plug-in available as well.

My personal app uses this and the data is fairly good but it is not realtime, just incase anybody is looking for this

1 Like

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

Here’s mine, basically taken from sim

2 Likes

Thanks for this :+1: insightful

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

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…

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

1 Like

Ah ok fair enough

Will check back later :nerd_face:thanks!

1 Like

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. :frowning:

[Update: see Stock Market apps for UK mutual fund prices in Goog Sheets]

1 Like

That’s annoying, still hadn’t been corrected :unamused:

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

3 Likes

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

I’ve tried putting LON:PSN as the ticker but it has no effect. I’m going to try dividing the relevant ones by 100 as the poster above suggested.

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