I’m recently trying to create my spreadsheet to track portfolio, everything was OK since I found out that I need FX rates USDGBP and EURGBP anyone who knows better could provide me with formula i can use?
1 Like
=Googlefinance("GBPUSD")
=Googlefinance("USDGBP")
=Googlefinance("USDGBP", "price", "01/01/2020",365)
In my experience Google sheets has the best tools for portfolio tracking.
1 Like
Cheers @Richard.W those are live rates or somehow delayed?
They will be delayed as the Google sheet only updates every few minutes. However, they are sufficiently uptodate for most purposes. I don’t know any free way to obtain live fx rates into a spreadsheet. If you want to check live prices, you can refer to the rates on the CFD platform.
Few minutes is more than good enough, thanks
Depends on what information you want to retrieve. I noticed a lot of stocks outside US/Canada are not available. On my Mac I use Apple Numbers, which uses data from Yahoo Finance. Also not complete, but for Mac users a bit better choice.
Good point. As I am not using a Mac, for things not available directly with googlefinance function I use screen scraping using a Regex search. E.g
=importRegex(C8, "mod-ui-data-list__value..([0-9]*.[0-9]*)")
gives me the price of CF Lindsell Train UK Equity Acc. In cell C8 I have
https://markets.ft.com/data/funds/tearsheet/summary?s=GB00B18B9X76:GBX
This is clunky but works. I also scape from other sources, such as Yahoo and Morningstar pages.
1 Like
Sorry @Ashige saw your email in my inbox but haven’t got around to it
You can do it through your finki api
just use
https://finki.io/callAPI.php?isin=GB00BH4HKS39&function=fxUSDGBP&key=
https://finki.io/callAPI.php?isin=GB00BH4HKS39&function=fxEURGBP&key=
ths “isin” is nonsense in this instance, but just leave a genuine 12 digit isin in…
Sorry, my inbox is out of control at the moment
1 Like
Is it possible to reference values of a stock using googlesheets?
I can pull in the “current” stock value, but, is there a way of referencing the value on a particular day?
For example, it would be interesting to pull stock values before the Covid-19 market dip, say reference the 20th of February or the 31st of December if you want the end of the financial year. Is there an easy googlesheets function for this?
=index(GOOGLEFINANCE("AAPL","price","02/20/2020"),2,2)
gives 320.3
=GOOGLEFINANCE("USDGBP", "price", "01/01/2020",500)
gives a list of exchange rates since the start of the year.
1 Like
So I have another problem… Its about OD7F traded on Xetra… When I’m trying to get 52 week low/high from googlefinance I’m getting error message… What exchange symbol should I use for Xetra stock? Or maybe you @Finki have function on your API which could help me?
Google finance does not have OD7F as far as I know. If you cannot find a ticker on www.google.com/finance/, it does not show up using the API as well.
I use Yahoo to get values for stocks missing in Google Finance. OD7F and previously 4GLD. I only fetch current value from Yahoo, didn’t try to get 52 week high/low. These formulas are fragile as they depend on the page structure of Yahoo. Still, If you are interested, here is a sample formula:
=index(IMPORTXML(concat("https://finance.yahoo.com/quote/",A3), "//*[@id='quote-header-info']//span"),2)
where A3 is OD7F.SG
1 Like