Google Finance portfolio tracking


#93

As far as I can tell you’ll need a script with a ‘Time-driven’ trigger to store the value every week to sheet, and then use those values to chart it.


#94

Hmm ok will have a gander

Thanks for your time


(Alex Sherwood) #95

Has anyone worked out the formula for retrieving market cap’s from Google Finance?

=googlefinance(symbol, "marketcap", date(2019, 1, 2))

isn’t working :grimacing:


(Simran Cashyap) #96

Without tinkering, not sure if historic market caps work. Try without the date?


(Alex Sherwood) #97

That worked, thanks!


#98

In light of Freetrades addition of US stocks, my Google sheet requires some tinkering

How do you display the current value column as US dollars instead of sterling

I’ve been using @simran 's template

I’ve tried simply changing GBP to USD but this has no effect :man_shrugging:


(Mike Smart) #99

I tweaked mine my just multiplying the dollar amount returned by the current exchange rate:

=googlefinance(“CURRENCY:USDGBP”)


#100

Don’t think I follow :flushed:

I’ve tried adding “CURRENCY:USDGBP” but no luck?


(Mike Smart) #101

Without fully seeing the formulas you are putting together it’ll be hard to diagnose the problem. But I’ll assume you have returned a dollar value in cell A1, to convert that value to GBP use:

=A1*googlefinance(“CURRENCY:USDGBP”)


#102

Apolgies, that would help

So I’ve probably wrongly inserted the price paid price as they amount I paid in Freetrade, which is sterling?

Which formulas would you need to see?

The price paid is simply the value
Current value is the formula I posted above ^


(Mike Smart) #103

Does this help at all?

=googlefinance(B3)

Above will give you the current price for Cisco in USD

=googlefinance(B3)*googlefinance(“CURRENCY:USDGBP”)

Will give you the price in GBP pence

=if(L3=“GBP”,(googlefinance(B3)*googlefinance(“CURRENCY:USDGBP”))/100,googlefinance(B3))

Will give you the price of a GBP share (as determined by column L) in pounds, else USD.

I think.


(Mike Smart) #104

In fact, I don’t think you should be displaying the current price in either USD or GBP, choose a currency (most likely GBP) and display all in that.

You’ll find it difficult to display the correct symbol (i.e. $ or £) when using these formulas.

Also, you may want to sum all your current price values, and with difference currencies that’ll be interesting.


#105

I think I follow!

The current value is now displaying £0.35 instead of £35

Thanks for you help, much appreciated!


#106

I’ve cracked it! Thanks a lot!!


(Simran Cashyap) #107

Glad you’ve resolved.

As @mikesmart said, UK stock prices are returned in pence, not pounds, so the /100 was a hack to change the units. Not the cleanest solution, but as far as I know GBP was the only currency with the problem.


#108

Working a dream :blush: thanks


(Nick Slade) #109

Morning all!

I’m trying to set up Yahoo finance to track a portfolio but I can’t seem to get it to work. Im sure its because I’ve entered a number incorrectly but for the life of me I cannot see where!


I’ve added some random sample data for the point of sharing, but as you can see my total shows £7k+ which is defonatly not right.

Here is how I have set up my holding:

Why on earth is the total so high? How do I correct this as the total of the shares I’ve ‘purchased’ is no more than £25 and I definatly havnt made £7k on top of that in 2 days :joy:


(Simran Cashyap) #110

It looks like the ^FTSE is skewing it (as it is measured in pounds not pence and current price is £7,160). Try removing that and see if the others falls in line.

In Yahoo Finance, symbols starting with ^ represent an index. I presume you have bought a fund (e.g. ISF) that is tracking the FTSE, so updating to the specific instrument you are holding (which will have a different price) should bring it in line.


(Nick Slade) #111

Thanks @simran that makes perfect sense and has now brought everything in line!


(Harry) #112

@alex.s Is there scope to work with IFTTT for something like this?

An auto export of your portfolio to Google Sheets would be great, would totally automate all of this reporting and tracking. Currently managing it all manually, but something like Monzo’s collaboration allowing export of spending data would be ideal!