Google Finance portfolio tracking


#21

Yeah… the annoying thing is that UK mutual funds worked fine with Google’s weird identifiers (eg MUTF_GB:VANG_LIFE_80_1MOSZN3) for a few years, and then with ISIN codes (eg GB00B4PQW151.L) for a few years, and now… I can’t make it return the price of mutual funds, whether I use Citi, Sedol, ISIN… :frowning:

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


(Jim) #22

OK - to clarify I was talking about the GoogleFinance function within the Google Sheets iOS app :wink:


#23

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?


#24

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 :man_shrugging::man_shrugging::man_shrugging:

Any help much appreciated , tearing my hair out


#25

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


(Jim) #26

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


#27

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

Edit: are you saying this should be GBX?


#28

Sorry to mither, complete n00b

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


#29

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.


#30

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


#31

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).


(Simran Cashyap) #32

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) #33

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


(Chris) #34

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


#35

Here’s mine, basically taken from sim


#36

Thanks for this :+1: insightful

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


#41

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…


(Emma) #42

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


#43

Ah ok fair enough

Will check back later :nerd_face:thanks!


#44

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]