Forum Discussion
Generalized currency conversion
QWeelon Almost correct. You need to make the references to the lookup array and the return array absolute, using $ signs.
Removed the table style in the attached workbook, so that you can see what I mean. Though, I'd recommend to learn about structured tables. The will make your Excel-life a lot easier.
Trevlig helg!
One more variant is to use STOCKHISTORY function (microsoft.com) if it is available for your subscription.
Function returns exchange rates in the specified period, e.g.
=STOCKHISTORY("EUR/SEK",TODAY()-5, TODAY(), 0, 0, 1, 0)returns EUR/SEK exchange rate for the latest 5 days. If interval is just TODAY() it returns current exchange rate - if only we have trading today. Function skips days without trading and if there are only such days within interval we have an error. Thus we need to take some range and take exchange rate for the latest trading date. Since function returns data from oldest to newest dates we need to sort returned array by date in descending order to have latest date on first position.
=SORT( STOCKHISTORY("EUR/SEK",TODAY()-5, TODAY(),0,0,1,0), 2, -1)Now we don't need dates any more, only take the latest exchange rate
=INDEX( SORT( STOCKHISTORY("EUR/SEK",TODAY()-5, TODAY(),0,0,1,0), 2, -1), 1, 1)
Finally for such pattern
we apply currency format to column D, skip conversion if it is not required (initial price is in SEK) and apply above formula overwise to multiply price on exchange rate:
= IF( $B3 = "SEK", $C3, $C3 * INDEX( SORT( STOCKHISTORY($B3 & "/SEK",TODAY()-5, TODAY(),0,0,1,0), 2,-1), 1, 1) )Please check in attached file.
- Interesting. I'm currently using the Excel Stock function for currency data, but it requires manual updates to acquire the latest (15 min delay) the latest rates.
I'll have to look further into this to determine if this might be favourable for me!
Thanks!If you need current exchange rate that doesn't work. STOCKHISTORY() has an option to show it on open, close, high and low for each day in interval.
Stock data type is updating constantly, thus you have the latest exchange rate for now. Not sure about delay in data, perhaps yes, 15 minutes. On the other hand you can't use currency pair as parameter here, it is predefined.
As for the refresh, option is coming for Excel. Not it is available for Beta version, it looks like
