Google Finance portfolio tracking

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?

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.

1 Like

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.

1 Like

Example sheet here

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

2 Likes

That looks gorgeous. Love a good spreadsheet

1 Like

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

6 Likes

Iā€™m happy to get involved from a product perspective. Iā€™ve spent most of my career building solutions for investors :smile:

I can help with coding, but Iā€™m not a proper developer any more!

2 Likes

Thanks very much :+1::+1::+1:

ā€œOnce a developer, always a developerā€. Cannot remember who said that, probably Marcus Aurelius :thinking:

3 Likes

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?

1 Like

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.

2 Likes

I think you need to multiply the cell with this:

GOOGLEFINANCE(ā€œCURRENCY:USDGBPā€)

might have a typo as typing this on a phone

1 Like

Have you tried this

=GOOGLEFINANCE(A15) * GOOGLEFINANCE(ā€œCURRENCY:USDGBPā€)

1 Like

Works for me:

image

1 Like