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
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).
I’ll add some improvements into the Portfolio Management when I can, and re-share.
- 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:
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.
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
This looks really useful. Going to try and do something via Excel.
Here’s mine, basically taken from sim
Thanks for this 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
Ah ok fair enough
Will check back later thanks!
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 Stock Market apps for UK mutual fund prices in Goog Sheets]
That’s annoying, still hadn’t been corrected
May be best to return the value in a hidden cell somewhere and then divide the value by 100?
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.