Generalized currency conversion

[QWeelon]

Dear community!

I have a modified setup as figure below. I want to generalize my currency conversion to SEK from different currencies (not limited to the ones listed below). In other words, I want the same formula in C11 and downwards using the information provided. You can see some examples how the conversion is executed today in cells C11-C14. As you can see, most of the data is already in SEK and needs no conversion.

I'm not very good myself with this kinds of formulas, and would appreciate some input from the community. If there is a way to solve this without even using the ticker-rows, that would be even better!

Thanks in advance

Regards

QWeelon

[#2]

@QWeelon Since you tagged your question with Office365, I assume your Excel version supports XLOOKUP. The attached workbook contains a mock-up based on your picture. See if it works for you and if you can implement it in your real life workbook. 

By the way, you'll notice that I included one extra currency, the SEK, in the lookup table at the top with a rate of 1.00.

[#3]

Thank you for your reply!

Yes, i'm currently in Office365 but unfortunately I'm not able to get it to work - probably because of the table formulas, it is not something I'm familiar with at the moment.

I tried to augment the formula to the following without success:

=XLOOKUP(A11,B4:B7,C4:C7,"!",0)*B11

Do I need to create tables for it to work properly? If Yes, how do I do in a way that they are compatible? (Never really worked with tables unless I needed to sort something, which is very rarely).

Sorry for the inconvienience

/Q

[#4 · Accepted solution]

@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!

[#5]

To be honest, I have no idea why it don't work without the $, never thought it was something necessary unless you need to lock cell - I usually add them after getting the formulas to work. Anyway, it works like a charm! Great!

Thank you very much!

Maybe I'll watch a excel tutorial on structured tables on a rainy work days (I rarely use Excel in my work so this is just a 'hobby'). Do you have any videos to recommend?

However, for my current setup, it is important for things to be scalable, i.e I'm continuously adding rows every week and I want to do this without adjusting a table every time.

Anyhow, thank you again for your help! It is much appreciated!

Regards
/Q

[#6]

Noterade precis din svenska! Haha! Trevlig helg och tusen tack!

[#7]

@QWeelon Var så god! Hela poängen med struktrerade tabeller är ju att du inte behöver ora dig när du lägger till några rader med valuta kurser. Allt uppdateras då automatisk utan att man måste skriva om alle formel som pekar till dessa valuta kurser.  Men förstår vad du menar. Det ser inte så lätt ut om man inte är van med sådana tabeller.

[#8]

@QWeelon 

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.

[#9]

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!

[#10]

@QWeelon 

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