When populating a database from a feed of exchange rate data it can be useful to derive rates for extra pairs of currencies that are not available from the feed itself [1]. Triangulation is a widely used method to derive rates between pairs of currencies.
The basic principle is straightforward. For example, if you know the exchange rates for any two currencies, A and B, relative to a third reference currency, C (usually USD), then you can calculate a notional exchange rate between A and B simply by dividing the two rates you started with. This method ignores any differential that might arise, due to market inefficiencies, between the actual A:B rate and the "triangulated" A→C→B rate. In practice the price achieved in the spot market may be different from the triangulated rate but a triangulated rate is a good enough assumption for many applications, such as end-of-day rates used for management information purposes. Triangulation is also used in spot trading to identify arbitrage opportunities. For more details see the Investopedia article referenced below.
Here's an example of how to derive the rate for euros to pounds (EUR, GBP) given the rates for those two currencies against the US dollar (USD):
| Base | Quote | Price |
| EUR | USD | 1.113961 |
| GBP | USD | 1.312852 |
1.113961 / 1.312852 = 0.848505
| Base | Quote | Price |
| EUR | GBP | 0.848505 |
The simplest way to do this in SQL is to use a self-join query as the source for an INSERT statement. Here is an example rates table:
CREATE TABLE fxrate (dt DATE NOT NULL, baseccy CHAR(3) NOT NULL, quoteccy CHAR(3) NOT NULL, price FLOAT NOT NULL, PRIMARY KEY (dt, baseccy, quoteccy));
Some sample data consisting of the dollar rate for eight major currencies:
INSERT INTO fxrate (dt,baseccy,quoteccy,price)
VALUES
('2016-08-04', 'AUD', 'USD', 0.762486),
('2016-08-04', 'CAD', 'USD', 0.766930),
('2016-08-04', 'CNY', 'USD', 0.150616),
('2016-08-04', 'EUR', 'USD', 1.113961),
('2016-08-04', 'GBP', 'USD', 1.312852),
('2016-08-04', 'HKD', 'USD', 0.128922),
('2016-08-04', 'JPY', 'USD', 0.00989218),
('2016-08-04', 'USD', 'USD', 1);
This is a basic triangulation query in SQL:
INSERT INTO fxrate (dt, baseccy, quoteccy, price)
SELECT pair.dt, pair.baseccy, pair.quoteccy, pair.price
FROM
(
SELECT f.dt, f.baseccy, t.baseccy quoteccy, (f.price/t.price) price
FROM fxrate f, fxrate t
WHERE f.dt = t.dt
AND f.quoteccy = t.quoteccy
AND f.quoteccy = 'USD'
) pair
LEFT OUTER JOIN fxrate
ON pair.dt = fxrate.dt
AND pair.baseccy = fxrate.baseccy
AND pair.quoteccy = fxrate.quoteccy
WHERE fxrate.baseccy IS NULL;
The essence of this query is the inner self-join based on date and currency code which returns a row for each currency pair for every date in the table:
SELECT f.dt, f.baseccy, t.baseccy quoteccy, (f.price/t.price) price
FROM fxrate f, fxrate t
WHERE f.dt = t.dt
AND f.quoteccy = t.quoteccy
AND f.quoteccy = 'USD'
Applying this query to the small sample of data given above yields 64 rows (8 x 8) of data for one day. The additional outer join part of the INSERT ... SELECT query ensures that only currency pairs not already in the table will get inserted.
Further refinements of this solution are possible. Typically you might want to run the same operation each day for just that day's data. Adding a filter on today's date to the inner self-join part of the query could improve performance if you only need to process one day at a time.
Using the above INSERT ... SELECT method on my desktop machine I find that I can generate 14 million rows of daily currency pairs for 28 currencies in about 45 seconds (Microsoft SQL Server 2016).
[1] Financial exchanges and data providers typically only list prices for major currencies and commonly traded currency pairs.
Investopedia: A Primer On Cross Currency Triangulation by Brian Twomey