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?
Put the ācurrencyā attribute into a hidden column - if GBX divide by 100.
What is it you want to do with dividends / yields? One of the Import functions might work for you.
Try hooking into Yahoo Finance here - the display format on Yahoo is slightly different for Shares and ETFs but for two of the examples on your sheet :
=ImportXML( āhttps://finance.yahoo.com/quote/NEX.L","//*[@id=āquote-summaryā]//td[@data-test=āDIVIDEND_AND_YIELD-valueā]ā)
=ImportXML( āhttps://finance.yahoo.com/quote/STHS.L","//*[@id=āquote-summaryā]//td[@data-test=āTD_YIELD-valueā]ā)
ETA : if cutting / pasting check the quote characters are correct.
If that will not work, you could try WeBull app and add your portfolioās relevant transactions with dates. It will fetch all related dividend yields for you, as well as reflect on them if you changed your positionsā size or liquidated them. It does have UK stocks and ETFs too. Looks sort of like this (with a nice total at the bottom):
That looks gorgeous. Love a good spreadsheet
How have you set up to update every minute?
I use WeBull and its good.
Iām also thinking of using Google script to parse Gmail inbox and update spreadsheet.
This way it would be broker agnostic, and can send email to myself with trade details incase broker doesnāt.
At the moment I have something basic working to parse IB emails.
If time permits Iāll start expand it to track portfolio.
For Google Sheets fans with UK mutual funds in their portfolio, hereās a way your spreadsheet can grab the recent prices from Yahoo finance. (The work of @simran , @Jim_mcgrain , others and this and got me there, thanks.)
=iferror(index(IMPORTHTML(āSymbol Lookup from Yahoo Financeā,1),1,2))
is the formula which grabs the price (currently it is the previous close, not the current price, but thatās fine for me because mutual funds are priced once a day I think. The formula tries a couple of times because Sheets sometimes fails the first time it tries to get the price.)
And cell A7 contains the fundās ISIN code with .L appended. So eg for Vanguard Lifestrategy 80, A7 would contain GB00B4PQW151.L
Iām happy to get involved from a product perspective. Iāve spent most of my career building solutions for investors
I can help with coding, but Iām not a proper developer any more!
Thanks very much
āOnce a developer, always a developerā. Cannot remember who said that, probably Marcus Aurelius
Build what, in particular? Much of the discussion in this thread has been around a spreadsheet that tracks the performance of your investments. Are you thinking it would be nice if there were an app that did this? And you donāt think that app should be Freetrade?
Agree with you there. There will be further improvements so might be best to wait and see what they come up with in app first.
I would love to see this in Freetrade.
I see this more as a stop-gap whilst the team focus on the most critical features. Once the roadmap is public, weāll know which features are lined up next (and those out of scope), but I assume there will be still be a window where we donāt have coverage of all stocks, curated portfolio risk and performance metrics.
Whilst a spreadsheet can definitely do the job, I know I would find a tool useful (and Iām sure others could too). Whether that tool is an improved spreadsheet, a webpage or an app would be open for discussion.
I think you need to multiply the cell with this:
GOOGLEFINANCE(āCURRENCY:USDGBPā)
might have a typo as typing this on a phone
Have you tried this
=GOOGLEFINANCE(A15) * GOOGLEFINANCE(āCURRENCY:USDGBPā)
Works for me: