Cross Currency Rates using Excel's Stock Data Types

Excel allows us to retrieve currency quotes from Rifinitiv's database and populate a spreadsheet with the quotes. Using function XLOOKUP and some smartly laid-out tables, we can calculate all the cross-rates and their changes from the prior day's quotes. It's a simple process of refreshing the data when you need to, or let it do so every five minutes.

In this setup, there are just seven currency pairs. Thus there are 56 derived cross-rates that can be calculated by inference. What is notable here is that there is no need to have all currency pairs in a quote list, and indeed there are some currency pairs that don't exist in the currency markets and must be inferred by calculating a cross rate (though the seven listed here certainly exist).

Addendum, Dec 2021: Interestingly, the database of currency pairs has been updated so that the Stock Data Type quotes now include what are called Cross Rates in the 'name' field. For example, this pair provides a Cross Rate quote, and we can guess that it's probably not a popular trade.



I put the file in OneDrive for anyone to download. For those who want to use the MrExcel mini-sheet instead, here it is. The colours from the source table at the top correspond to the colours in the cross-rate tables beneath.