I have a spreadsheet of my investments in USD which I update periodically. Someone on this forums gave me the formula to convert the total to GBP. However, I cannot tell if that is showing in real time and if, when I reopen the spreadsheet, it shows the conversion rate as of the day I made the enrtry of it always updates to the current rate?
The formula I have is : CURRENCY "GBP","USD", price
> I cannot tell if that is showing in real time and if, when I reopen the spreadsheet, it shows the conversion rate as of the day I made the enrtry of it always updates to the current rate?
It's neither.
It's based on the latest close-of-market price.
Each time you open the spreadsheet, it shows what the conversion rate was at end-of-day yesterday.
Therefore, it can best be described as 'near-time' since doesn't update real-time, and it's not fixed to the date/time you entered the formula.
> Is there a way to retain the exchange rate result for a specific date in the past? Other than typing it in manually each time I run the spreadsheet?
Sure - that's what CURRENCYH() does:
The CURRENCYH function returns historical data on the exchange rate between two
currencies for a given date. The value returned is in the currency to which
you’re converting (currency‑2).
So =CURRENCYH("GBP", "USD", "close", "1/24/2024") will return the closing exchange rate on January 24th, 2024
Without knowing your table layout, I'm assuming you have some column that has dates in it, so you can just reference that cell for the DATE parameter and it should work.
Thanks for the clarification. Is there a way to retain the exchange rate result for a specific date in the past? Other than typing it in manually each time I run the spreadsheet?
Currency conversion updating day by day (Numbers)