Google Finance portfolio tracking


#45

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


#48

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


#49

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


#50

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.


(Jim) #51

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


#52

Yeah, I’ve just divided by 100 where appropriate. In case anyone runs in to the same issue it:

=GOOGLEFINANCE(ticker)/100

I’m really pleased with the way it’s turned out I just wish we could track the dividend yield.


(Jim) #53

What is it you want to do with dividends / yields? One of the Import functions might work for you.


#54

Just something to show the current dividend paying yield of each stock would be nice. The tracker has everything else.


(Jim) #56

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.


#57

I did try to cut and paste the formulas in but just getting ERRORS. I’ll do a bit more research in to it later and try to figure out how to import the yields from yahoo finance. Thanks :slight_smile:


(Jim) #58

Example sheet here


(Vladislav Kozub) #59

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


#63

That looks gorgeous. Love a good spreadsheet


#64

How have you set up to update every minute?


(Giridhar Tammana) #65

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.


#66

File > Spreadsheet Settings > Calculation settings > Update upon change or every minute.


#67

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(“http://finance.yahoo.com/q?s="&A7,“table”,1),1,2),index(IMPORTHTML(“http://finance.yahoo.com/q?s=”&A7,"table”,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


(Chris) #68

Do we have any developers or product people interested in building something? Would be interesting to see if we could build something to avoid the FT team having to worry about this, leave them to focus on the product & service


(Simran Cashyap) #69

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!


#70

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