• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
DRSteele

Cross Currency Rates using Excel's Stock Data Types

Excel Version
  1. 365
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.

Book1
CDEF
9PairNamePriceTicker symbol
10SAR/ILSSaudi Arabian Riyal/Israeli Shekel FX Cross Rate₪ 0.83SARILS
Sheet1
Cell Formulas
RangeFormula
D10D10=[@Pair].Name
E10E10=[@Pair].Price
F10F10=[@Pair].[Ticker symbol]


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.

CROSS Currency.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1UTC
2Currency PairBase CurrencyQuote CurrencyQuotePrevious (quote)Change (quote)Change (%) (quote)InversePrevious (inverse)Change (inverse)Change (%) (inverse)Change (%)2 (inverse)10/22/2021 21:00
3USD/CADUSDCAD1.235901.23670-0.000800-0.065%0.809130.808600.0005230.065%0.065%
4USD/JPYUSDJPY113.46000113.460000.0000000.000%0.008810.008810.0000000.000%0.000%
5USD/EURUSDEUR0.858300.858300.0000000.000%1.165091.165090.0000000.000%0.000%
6USD/GBPUSDGBP0.726600.726600.0000000.000%1.376271.376270.0000000.000%0.000%
7USD/AUDUSDAUD1.339401.339400.0000000.000%0.746600.746600.0000000.000%0.000%
8USD/NZDUSDNZD1.397201.397200.0000000.000%0.715720.715720.0000000.000%0.000%
9USD/CHFUSDCHF0.915800.915800.0000000.000%1.091941.091940.0000000.000%0.000%
10
11
12
13QuoteBase CurrencyChangeBase Currency
14USDCADJPYEURGBPAUDNZDCHFUSDCADJPYEURGBPAUDNZDCHF
15Quote CurrencyUSD 0.809130.008811.165091.376270.746600.715721.09194Quote CurrencyUSD0.000520.000000.000000.000000.000000.000000.00000
16CAD1.23590 0.010891.439941.700940.922730.884551.34953CAD-0.00080 -0.00001-0.00093-0.00110-0.00060-0.00057-0.00087
17JPY113.4600091.80354 132.19154156.1519484.7095781.20527123.89168JPY0.000000.05939 0.000000.000000.000000.000000.00000
18EUR0.858300.694470.00756 1.181260.640810.614300.93721EUR0.000000.000450.00000 0.000000.000000.000000.00000
19GBP0.726600.587910.006400.84656 0.542480.520040.79340GBP0.000000.000380.000000.00000 0.000000.000000.00000
20AUD1.339401.083740.011811.560531.84338 0.958631.46255AUD0.000000.000700.000000.000000.00000 0.000000.00000
21NZD1.397201.130510.012311.627871.922931.04315 1.52566NZD0.000000.000730.000000.000000.000000.00000 0.00000
22CHF0.915800.741000.008071.066991.260390.683740.65545 CHF0.000000.000480.000000.000000.000000.000000.00000 
23
24Previous (quote)Base CurrencyChange (%)Base Currency
25USDCADJPYEURGBPAUDNZDCHFUSDCADJPYEURGBPAUDNZDCHF
26Quote CurrencyUSD0.808600.008811.165091.376270.746600.715721.09194Quote CurrencyUSD 0.065%0.000%0.000%0.000%0.000%0.000%0.000%
27CAD1.23670 0.010901.440871.702040.923320.885131.35040CAD-0.065% -0.065%-0.065%-0.065%-0.065%-0.065%-0.065%
28JPY113.4600091.74416 132.19154156.1519484.7095781.20527123.89168JPY0.000%0.065% 0.000%0.000%0.000%0.000%0.000%
29EUR0.858300.694020.00756 1.181260.640810.614300.93721EUR0.000%0.065%0.000% 0.000%0.000%0.000%0.000%
30GBP0.726600.587530.006400.84656 0.542480.520040.79340GBP0.000%0.065%0.000%0.000% 0.000%0.000%0.000%
31AUD1.339401.083040.011811.560531.84338 0.958631.46255AUD0.000%0.065%0.000%0.000%0.000% 0.000%0.000%
32NZD1.397201.129780.012311.627871.922931.04315 1.52566NZD0.000%0.065%0.000%0.000%0.000%0.000% 0.000%
33CHF0.915800.740520.008071.066991.260390.683740.65545 CHF0.000%0.065%0.000%0.000%0.000%0.000%0.000% 
USD_base
Cell Formulas
RangeFormula
O2O2=B3.[Last trade time]
C3:C9C3=B3.[From currency]
D3:D9D3=B3.Currency
E3:E9E3=B3.Price
F3:F9F3=B3.[Previous close]
G3:G9G3=B3.Change
H3:H9H3=B3.[Change (%)]
I3:J9I3=1/E3
K3:K9K3=I3-J3
L3:L9L3=K3/J3
M3:M9M3=1/(1+H3)-1
C15C15=IF($B15=C$14,"",$C16/XLOOKUP(C$14,$B$15:$B$22,$C$15:$C$22))
D15:J15D15=1/XLOOKUP(D14,$B$16:$B$22,$C$16:$C$22)
C16:C22C16=XLOOKUP(B16,$D$3:$D$9,$E$3:$E$9)
D16:J22D16=IF($B16=D$14,"",$C16/XLOOKUP(D$14,$B$15:$B$22,$C$15:$C$22))
Q15:W22Q15=IFERROR(D15-D26,"")
P16:P22P16=C16-C27
D26:J26D26=1/XLOOKUP(D25,$B$27:$B$33,$C$27:$C$33)
P26:W33P26=IFERROR(P15/C26,"")
C27:C33C27=XLOOKUP(B27,$D$3:$D$9,$F$3:$F$9)
D27:J33D27=IF($B27=D$25,"",$C27/XLOOKUP(D$25,$B$26:$B$33,$C$26:$C$33))
Author
DRSteele
Views
2,051
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from DRSteele

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top