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.
Hmm ok will have a gander
Thanks for your time
Has anyone worked out the formula for retrieving market cap’s from Google Finance?
=googlefinance(symbol, "marketcap", date(2019, 1, 2))
Without tinkering, not sure if historic market caps work. Try without the date?
That worked, thanks!
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
I tweaked mine my just multiplying the dollar amount returned by the current exchange rate:
Don’t think I follow
I’ve tried adding “CURRENCY:USDGBP” but no luck?
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:
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 ^
Does this help at all?
Above will give you the current price for Cisco in USD
Will give you the price in GBP pence
Will give you the price of a GBP share (as determined by column L) in pounds, else USD.
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.
I think I follow!
The current value is now displaying £0.35 instead of £35
Thanks for you help, much appreciated!
I’ve cracked it! Thanks a lot!!
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.
Working a dream thanks
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
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.
Thanks @simran that makes perfect sense and has now brought everything in line!
@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!