November 12, 2014 at 9:24 am
Hi,
I have a table with a load of currency data in it. There are 5 columns I'm interested in.
The "from_currency_code" column holds the currency code for 9 main currencies. The "to_currency_code" column contains all currencies, so there's a one-to-many relationship in the data. N.B. There's no self-mapping in the fx table, e.g. there's no GBP to GBP relationship, but I will need this fabricated. The fx_period is for a particular time of day - I need 'SPOT' values. The effective date is the date/time the rate was entered into the system, and the mid_price is the exchange rate between "from_currency_code" and "to_currency_code" on the effective_date.
I basically need to derive all possible currency combinations, and the currency rate between them for a specific date, or range of dates. I was trying to use one of the "from_ccy_code" column codes as a constant for pivoting off, e.g. get the rate between BRL and GBP, then the rate from GBP to BAM, then derive the BRL to BAM rate. I thought I was nearly there, but not sure now. Any help greatly appreciated.
The table definition is:
CREATE TABLE fxrates (from_currency_code NVARCHAR(3), to_currency_code NVARCHAR(3), fx_period NVARCHAR(4), effective_date DATETIME, mid_price DECIMAL(27,7))
Sample data:
INSERT INTO fxrates(from_currency_code, to_currency_code, fx_period, effective_date, mid_price)
VALUES ('CAD','AED','SPOT','2014-11-10 16:00:00',3.2446077),
('CAD','ALL','SPOT','2014-11-10 16:00:00',98.94957),
('CAD','ANG','SPOT','2014-11-10 16:00:00',1.5812251),
('CAD','AOA','SPOT','2014-11-10 16:00:00',88.2083716),
('CAD','ARS','SPOT','2014-11-10 16:00:00',7.5169631),
('CAD','ATS','SPOT','2014-11-10 16:00:00',9.7683894),
('CAD','AUD','SPOT','2014-11-10 16:00:00',1.0220979),
('CAD','AWG','SPOT','2014-11-10 16:00:00',1.5812251),
('CAD','AZN','SPOT','2014-11-10 16:00:00',0.6924661),
('CAD','BAM','SPOT','2014-11-10 16:00:00',1.388439),
('CAD','BBD','SPOT','2014-11-10 16:00:00',1.7667195),
('CAD','BDT','SPOT','2014-11-10 16:00:00',68.3587988),
('CAD','BEF','SPOT','2014-11-10 16:00:00',28.6371657),
('CAD','BGN','SPOT','2014-11-10 16:00:00',1.3884668),
('CAD','BHD','SPOT','2014-11-10 16:00:00',0.3330272),
('CAD','BIF','SPOT','2014-11-10 16:00:00',1373.6255601),
('CAD','BMD','SPOT','2014-11-10 16:00:00',0.8833598),
('CAD','BND','SPOT','2014-11-10 16:00:00',1.1403245),
('CAD','BOB','SPOT','2014-11-10 16:00:00',6.1040049),
('CAD','BRL','SPOT','2014-11-10 16:00:00',2.2432162),
('CAD','BSD','SPOT','2014-11-10 16:00:00',0.8833598),
('CAD','BTN','SPOT','2014-11-10 16:00:00',54.3023295),
('CAD','BWP','SPOT','2014-11-10 16:00:00',8.2097353),
('CAD','BYR','SPOT','2014-11-10 16:00:00',9522.6216916),
('CAD','BZD','SPOT','2014-11-10 16:00:00',1.7623222),
('CAD','CDF','SPOT','2014-11-10 16:00:00',818.8761793),
('CAD','CHF','SPOT','2014-11-10 16:00:00',0.8536918),
('CAD','CLP','SPOT','2014-11-10 16:00:00',518.0021987),
('CAD','CNH','SPOT','2014-11-10 16:00:00',5.4090896),
('CAD','CNY','SPOT','2014-11-10 16:00:00',5.4058056),
('CAD','COP','SPOT','2014-11-10 16:00:00',1858.390248),
('CAD','CRC','SPOT','2014-11-10 16:00:00',475.0847736),
('CAD','CUP','SPOT','2014-11-10 16:00:00',0.8833598),
('CAD','CYP','SPOT','2014-11-10 16:00:00',0.4154908),
('CAD','CZK','SPOT','2014-11-10 16:00:00',19.5878489),
('CAD','DEM','SPOT','2014-11-10 16:00:00',1.388439),
('CAD','DKK','SPOT','2014-11-10 16:00:00',5.2805655),
('CAD','DOP','SPOT','2014-11-10 16:00:00',38.3952576),
('CAD','DZD','SPOT','2014-11-10 16:00:00',74.3560157),
('CAD','ECS','SPOT','2014-11-10 16:00:00',22083.9943225),
('CAD','EEK','SPOT','2014-11-10 16:00:00',11.107456),
('CAD','EGP','SPOT','2014-11-10 16:00:00',6.3160502),
('CAD','ESP','SPOT','2014-11-10 16:00:00',118.1168629),
('CAD','ETB','SPOT','2014-11-10 16:00:00',17.7210765),
('CAD','EUR','SPOT','2014-11-10 16:00:00',0.7099162),
('CAD','FIM','SPOT','2014-11-10 16:00:00',4.2208399),
('CAD','FJD','SPOT','2014-11-10 16:00:00',1.7209652),
('CAD','FRF','SPOT','2014-11-10 16:00:00',4.6566196),
('CAD','GEL','SPOT','2014-11-10 16:00:00',1.5505552),
('CAD','GHS','SPOT','2014-11-10 16:00:00',2.8466227),
('CAD','GMD','SPOT','2014-11-10 16:00:00',38.0286939),
('CAD','GNF','SPOT','2014-11-10 16:00:00',6210.0470346),
('CAD','GRD','SPOT','2014-11-10 16:00:00',241.8972753),
('CAD','GTQ','SPOT','2014-11-10 16:00:00',6.7121984),
('CAD','HKD','SPOT','2014-11-10 16:00:00',6.8497119),
('CAD','HNL','SPOT','2014-11-10 16:00:00',18.7713952),
('CAD','HRK','SPOT','2014-11-10 16:00:00',5.4397317),
('CAD','HTG','SPOT','2014-11-10 16:00:00',40.4915254),
('CAD','HUF','SPOT','2014-11-10 16:00:00',218.2967632),
('CAD','IDR','SPOT','2014-11-10 16:00:00',10735.0299185),
('CAD','IDT','SPOT','2014-11-10 16:00:00',0),
('CAD','IEP','SPOT','2014-11-10 16:00:00',0.5590994),
('CAD','ILS','SPOT','2014-11-10 16:00:00',3.3552921),
('CAD','INR','SPOT','2014-11-10 16:00:00',54.3023295),
('CAD','IRR','SPOT','2014-11-10 16:00:00',0),
('CAD','ISK','SPOT','2014-11-10 16:00:00',109.5807798),
('CAD','ITL','SPOT','2014-11-10 16:00:00',1374.5514458),
('CAD','JMD','SPOT','2014-11-10 16:00:00',99.5502769),
('CAD','JOD','SPOT','2014-11-10 16:00:00',0.6268403),
('CAD','JPY','SPOT','2014-11-10 16:00:00',101.277198),
('CAD','KES','SPOT','2014-11-10 16:00:00',79.3257076),
('CAD','KGS','SPOT','2014-11-10 16:00:00',50.8780719),
('CAD','KMF','SPOT','2014-11-10 16:00:00',349.2462219),
('CAD','KRW','SPOT','2014-11-10 16:00:00',958.4895216),
('CAD','KWD','SPOT','2014-11-10 16:00:00',0.2572987),
('CAD','KYD','SPOT','2014-11-10 16:00:00',0.7243606),
('CAD','KZT','SPOT','2014-11-10 16:00:00',159.8351284),
('CAD','LBP','SPOT','2014-11-10 16:00:00',1336.9650719),
('CAD','LKR','SPOT','2014-11-10 16:00:00',115.6317943),
('CAD','LSL','SPOT','2014-11-10 16:00:00',9.926665),
('CAD','LTL','SPOT','2014-11-10 16:00:00',2.4511425),
('CAD','LUF','SPOT','2014-11-10 16:00:00',28.6371657),
('CAD','LVL','SPOT','2014-11-10 16:00:00',0.4989285),
('CAD','MAD','SPOT','2014-11-10 16:00:00',7.8207398),
('CAD','MDL','SPOT','2014-11-10 16:00:00',13.1841585),
('CAD','MGA','SPOT','2014-11-10 16:00:00',2371.8221035),
('CAD','MKD','SPOT','2014-11-10 16:00:00',43.6688932),
('CAD','MNT','SPOT','2014-11-10 16:00:00',1653.2082046),
('CAD','MOP','SPOT','2014-11-10 16:00:00',7.0551891),
('CAD','MRO','SPOT','2014-11-10 16:00:00',258.3831232),
('CAD','MTL','SPOT','2014-11-10 16:00:00',0.3047508),
('CAD','MUR','SPOT','2014-11-10 16:00:00',27.8258607),
('CAD','MVR','SPOT','2014-11-10 16:00:00',13.568395),
('CAD','MWK','SPOT','2014-11-10 16:00:00',423.1304445),
('CAD','MXN','SPOT','2014-11-10 16:00:00',11.9589213),
('CAD','MYR','SPOT','2014-11-10 16:00:00',2.9420556),
('CAD','MZN','SPOT','2014-11-10 16:00:00',27.4283766),
('CAD','NAD','SPOT','2014-11-10 16:00:00',9.926665),
('CAD','NGN','SPOT','2014-11-10 16:00:00',148.7577858),
('CAD','NIO','SPOT','2014-11-10 16:00:00',23.32696),
('CAD','NLG','SPOT','2014-11-10 16:00:00',1.5643873),
('CAD','NOK','SPOT','2014-11-10 16:00:00',5.9918733),
('CAD','NPR','SPOT','2014-11-10 16:00:00',86.8837216),
('CAD','NZD','SPOT','2014-11-10 16:00:00',1.1369569),
('CAD','OMR','SPOT','2014-11-10 16:00:00',0.3400963),
('CAD','PAB','SPOT','2014-11-10 16:00:00',0.8833598),
('CAD','PEN','SPOT','2014-11-10 16:00:00',2.5869305),
('CAD','PGK','SPOT','2014-11-10 16:00:00',2.2637834),
('CAD','PHP','SPOT','2014-11-10 16:00:00',39.6363307),
('CAD','PKR','SPOT','2014-11-10 16:00:00',89.9127773),
('CAD','PLN','SPOT','2014-11-10 16:00:00',2.9928474),
('CAD','PTE','SPOT','2014-11-10 16:00:00',142.3214773),
('CAD','PYG','SPOT','2014-11-10 16:00:00',4092.5442097),
('CAD','QAR','SPOT','2014-11-10 16:00:00',3.2166374),
('CAD','RON','SPOT','2014-11-10 16:00:00',3.1411316),
('CAD','RSD','SPOT','2014-11-10 16:00:00',85.1912276),
('CAD','RUB','SPOT','2014-11-10 16:00:00',40.3319139),
('CAD','RWF','SPOT','2014-11-10 16:00:00',608.6353288),
('CAD','SAR','SPOT','2014-11-10 16:00:00',3.3145473),
('CAD','SBD','SPOT','2014-11-10 16:00:00',6.614539),
('CAD','SCR','SPOT','2014-11-10 16:00:00',12.5349141),
('CAD','SDG','SPOT','2014-11-10 16:00:00',0),
('CAD','SEK','SPOT','2014-11-10 16:00:00',6.5433191),
('CAD','SGD','SPOT','2014-11-10 16:00:00',1.1403245),
('CAD','SIT','SPOT','2014-11-10 16:00:00',170.1196182),
('CAD','SKK','SPOT','2014-11-10 16:00:00',21.3863516),
('CAD','SLL','SPOT','2014-11-10 16:00:00',3847.0373772),
('CAD','SRD','SPOT','2014-11-10 16:00:00',2.9150873),
('CAD','SVC','SPOT','2014-11-10 16:00:00',7.7263088),
('CAD','SZL','SPOT','2014-11-10 16:00:00',9.926665),
('CAD','THB','SPOT','2014-11-10 16:00:00',28.9543291),
('CAD','TND','SPOT','2014-11-10 16:00:00',1.622276),
('CAD','TOF','SPOT','2014-11-10 16:00:00',29.0095182),
('CAD','TOP','SPOT','2014-11-10 16:00:00',1.6875122),
('CAD','TRY','SPOT','2014-11-10 16:00:00',1.9925969),
('CAD','TTD','SPOT','2014-11-10 16:00:00',5.5913835),
('CAD','TWD','SPOT','2014-11-10 16:00:00',26.9689683),
('CAD','TZS','SPOT','2014-11-10 16:00:00',1510.5457683),
('CAD','UAH','SPOT','2014-11-10 16:00:00',13.1214272),
('CAD','UGX','SPOT','2014-11-10 16:00:00',2398.32234),
('CAD','USD','SPOT','2014-11-10 16:00:00',0.8833598),
('CAD','UYU','SPOT','2014-11-10 16:00:00',21.3773065),
('CAD','UZS','SPOT','2014-11-10 16:00:00',2109.6839776),
('CAD','VEF','SPOT','2014-11-10 16:00:00',10.6003173),
('CAD','VND','SPOT','2014-11-10 16:00:00',18806.7329047),
('CAD','VUV','SPOT','2014-11-10 16:00:00',90.4917759),
('CAD','WST','SPOT','2014-11-10 16:00:00',2.1269377),
('CAD','XCD','SPOT','2014-11-10 16:00:00',2.3850714),
('CAD','XDR','SPOT','2014-11-10 16:00:00',0.6017088),
('CAD','XEU','SPOT','2014-11-10 16:00:00',0.7099162),
('CAD','XOF','SPOT','2014-11-10 16:00:00',465.6616292),
('CAD','XPF','SPOT','2014-11-10 16:00:00',84.7131725),
('CAD','YER','SPOT','2014-11-10 16:00:00',189.9444769),
('CAD','ZAR','SPOT','2014-11-10 16:00:00',9.926665),
('CAD','ZDN','SPOT','2014-11-10 16:00:00',0),
('CAD','ZMW','SPOT','2014-11-10 16:00:00',5.6314325),
('CAD','ZWL','SPOT','2014-11-10 16:00:00',284.7558097),
('GBP','AED','SPOT','2014-11-10 16:00:00',5.8291),
('GBP','ALL','SPOT','2014-11-10 16:00:00',177.76785),
('GBP','ANG','SPOT','2014-11-10 16:00:00',2.84075),
('GBP','AOA','SPOT','2014-11-10 16:00:00',158.47075),
('GBP','ARS','SPOT','2014-11-10 16:00:00',13.5046),
('GBP','ATS','SPOT','2014-11-10 16:00:00',17.5494),
('GBP','AUD','SPOT','2014-11-10 16:00:00',1.83625),
('GBP','AWG','SPOT','2014-11-10 16:00:00',2.84075),
('GBP','AZN','SPOT','2014-11-10 16:00:00',1.24405),
('GBP','BAM','SPOT','2014-11-10 16:00:00',2.4944),
('GBP','BBD','SPOT','2014-11-10 16:00:00',3.174),
('GBP','BDT','SPOT','2014-11-10 16:00:00',122.81),
('GBP','BEF','SPOT','2014-11-10 16:00:00',51.4481),
('GBP','BGN','SPOT','2014-11-10 16:00:00',2.49445),
('GBP','BHD','SPOT','2014-11-10 16:00:00',0.5983),
('GBP','BIF','SPOT','2014-11-10 16:00:00',2467.787),
('GBP','BMD','SPOT','2014-11-10 16:00:00',1.587),
('GBP','BND','SPOT','2014-11-10 16:00:00',2.04865),
('GBP','BOB','SPOT','2014-11-10 16:00:00',10.96615),
('GBP','BRL','SPOT','2014-11-10 16:00:00',4.03005),
('GBP','BSD','SPOT','2014-11-10 16:00:00',1.587),
('GBP','BTN','SPOT','2014-11-10 16:00:00',97.55685),
('GBP','BWP','SPOT','2014-11-10 16:00:00',14.7492),
('GBP','BYR','SPOT','2014-11-10 16:00:00',17107.866),
('GBP','BZD','SPOT','2014-11-10 16:00:00',3.1661),
('GBP','CAD','SPOT','2014-11-10 16:00:00',1.79655),
('GBP','CDF','SPOT','2014-11-10 16:00:00',1471.152),
('GBP','CHF','SPOT','2014-11-10 16:00:00',1.5337),
('GBP','CLP','SPOT','2014-11-10 16:00:00',930.61685),
('GBP','CNH','SPOT','2014-11-10 16:00:00',9.7177),
('GBP','CNY','SPOT','2014-11-10 16:00:00',9.7118),
('GBP','COP','SPOT','2014-11-10 16:00:00',3338.691),
('GBP','CRC','SPOT','2014-11-10 16:00:00',853.51355),
('GBP','CUP','SPOT','2014-11-10 16:00:00',1.587),
('GBP','CYP','SPOT','2014-11-10 16:00:00',0.74645),
('GBP','CZK','SPOT','2014-11-10 16:00:00',35.19055),
('GBP','DEM','SPOT','2014-11-10 16:00:00',2.4944),
('GBP','DKK','SPOT','2014-11-10 16:00:00',9.4868),
('GBP','DOP','SPOT','2014-11-10 16:00:00',68.979),
('GBP','DZD','SPOT','2014-11-10 16:00:00',133.5843),
('GBP','ECS','SPOT','2014-11-10 16:00:00',39675),
('GBP','EEK','SPOT','2014-11-10 16:00:00',19.9551),
('GBP','EGP','SPOT','2014-11-10 16:00:00',11.3471),
('GBP','ESP','SPOT','2014-11-10 16:00:00',212.20285),
('GBP','ETB','SPOT','2014-11-10 16:00:00',31.8368),
('GBP','EUR','SPOT','2014-11-10 16:00:00',1.2754),
('GBP','FIM','SPOT','2014-11-10 16:00:00',7.58295),
('GBP','FJD','SPOT','2014-11-10 16:00:00',3.0918),
('GBP','FRF','SPOT','2014-11-10 16:00:00',8.36585),
('GBP','GEL','SPOT','2014-11-10 16:00:00',2.78565),
('GBP','GHS','SPOT','2014-11-10 16:00:00',5.1141),
('GBP','GMD','SPOT','2014-11-10 16:00:00',68.32045),
('GBP','GNF','SPOT','2014-11-10 16:00:00',11156.66),
('GBP','GRD','SPOT','2014-11-10 16:00:00',434.58055),
('GBP','GTQ','SPOT','2014-11-10 16:00:00',12.0588),
('GBP','HKD','SPOT','2014-11-10 16:00:00',12.30585),
('GBP','HNL','SPOT','2014-11-10 16:00:00',33.72375),
('GBP','HRK','SPOT','2014-11-10 16:00:00',9.77275),
('GBP','HTG','SPOT','2014-11-10 16:00:00',72.74505),
('GBP','HUF','SPOT','2014-11-10 16:00:00',392.18105),
('GBP','IDR','SPOT','2014-11-10 16:00:00',19286.018),
('GBP','IDT','SPOT','2014-11-10 16:00:00',0),
('GBP','IEP','SPOT','2014-11-10 16:00:00',1.00445),
('GBP','ILS','SPOT','2014-11-10 16:00:00',6.02795),
('GBP','INR','SPOT','2014-11-10 16:00:00',97.55685),
('GBP','IRR','SPOT','2014-11-10 16:00:00',0),
('GBP','ISK','SPOT','2014-11-10 16:00:00',196.86735),
('GBP','ITL','SPOT','2014-11-10 16:00:00',2469.4504),
('GBP','JMD','SPOT','2014-11-10 16:00:00',178.84705),
('GBP','JOD','SPOT','2014-11-10 16:00:00',1.12615),
('GBP','JPY','SPOT','2014-11-10 16:00:00',181.94955),
('GBP','KES','SPOT','2014-11-10 16:00:00',142.5126),
('GBP','KGS','SPOT','2014-11-10 16:00:00',91.405),
('GBP','KMF','SPOT','2014-11-10 16:00:00',627.4383),
('GBP','KRW','SPOT','2014-11-10 16:00:00',1721.97435),
('GBP','KWD','SPOT','2014-11-10 16:00:00',0.46225),
('GBP','KYD','SPOT','2014-11-10 16:00:00',1.30135),
('GBP','KZT','SPOT','2014-11-10 16:00:00',287.1518),
('GBP','LBP','SPOT','2014-11-10 16:00:00',2401.9246),
('GBP','LKR','SPOT','2014-11-10 16:00:00',207.7383),
('GBP','LSL','SPOT','2014-11-10 16:00:00',17.83375),
('GBP','LTL','SPOT','2014-11-10 16:00:00',4.4036),
('GBP','LUF','SPOT','2014-11-10 16:00:00',51.4481),
('GBP','LVL','SPOT','2014-11-10 16:00:00',0.89635),
('GBP','MAD','SPOT','2014-11-10 16:00:00',14.05035),
('GBP','MDL','SPOT','2014-11-10 16:00:00',23.686),
('GBP','MGA','SPOT','2014-11-10 16:00:00',4261.097),
('GBP','MKD','SPOT','2014-11-10 16:00:00',78.45335),
('GBP','MNT','SPOT','2014-11-10 16:00:00',2970.0712),
('GBP','MOP','SPOT','2014-11-10 16:00:00',12.675),
('GBP','MRO','SPOT','2014-11-10 16:00:00',464.1982),
('GBP','MTL','SPOT','2014-11-10 16:00:00',0.5475),
('GBP','MUR','SPOT','2014-11-10 16:00:00',49.99055),
('GBP','MVR','SPOT','2014-11-10 16:00:00',24.3763),
('GBP','MWK','SPOT','2014-11-10 16:00:00',760.175),
('GBP','MXN','SPOT','2014-11-10 16:00:00',21.4848),
('GBP','MYR','SPOT','2014-11-10 16:00:00',5.28555),
('GBP','MZN','SPOT','2014-11-10 16:00:00',49.27645),
('GBP','NAD','SPOT','2014-11-10 16:00:00',17.83375),
('GBP','NGN','SPOT','2014-11-10 16:00:00',267.2508),
('GBP','NIO','SPOT','2014-11-10 16:00:00',41.90805),
('GBP','NLG','SPOT','2014-11-10 16:00:00',2.8105),
('GBP','NOK','SPOT','2014-11-10 16:00:00',10.7647),
('GBP','NPR','SPOT','2014-11-10 16:00:00',156.09095),
('GBP','NZD','SPOT','2014-11-10 16:00:00',2.0426),
('GBP','OMR','SPOT','2014-11-10 16:00:00',0.611),
('GBP','PAB','SPOT','2014-11-10 16:00:00',1.587),
('GBP','PEN','SPOT','2014-11-10 16:00:00',4.64755),
('GBP','PGK','SPOT','2014-11-10 16:00:00',4.067),
('GBP','PHP','SPOT','2014-11-10 16:00:00',71.20865),
('GBP','PKR','SPOT','2014-11-10 16:00:00',161.5328),
('GBP','PLN','SPOT','2014-11-10 16:00:00',5.3768),
('GBP','PTE','SPOT','2014-11-10 16:00:00',255.68765),
('GBP','PYG','SPOT','2014-11-10 16:00:00',7352.4603),
('GBP','QAR','SPOT','2014-11-10 16:00:00',5.77885),
('GBP','RON','SPOT','2014-11-10 16:00:00',5.6432),
('GBP','RSD','SPOT','2014-11-10 16:00:00',153.0503),
('GBP','RUB','SPOT','2014-11-10 16:00:00',72.4583),
('GBP','RWF','SPOT','2014-11-10 16:00:00',1093.4438),
('GBP','SAR','SPOT','2014-11-10 16:00:00',5.95475),
('GBP','SBD','SPOT','2014-11-10 16:00:00',11.88335),
('GBP','SCR','SPOT','2014-11-10 16:00:00',22.5196),
('GBP','SDG','SPOT','2014-11-10 16:00:00',0),
('GBP','SEK','SPOT','2014-11-10 16:00:00',11.7554),
('GBP','SGD','SPOT','2014-11-10 16:00:00',2.04865),
('GBP','SIT','SPOT','2014-11-10 16:00:00',305.6284),
('GBP','SKK','SPOT','2014-11-10 16:00:00',38.42165),
('GBP','SLL','SPOT','2014-11-10 16:00:00',6911.395),
('GBP','SRD','SPOT','2014-11-10 16:00:00',5.2371),
('GBP','SVC','SPOT','2014-11-10 16:00:00',13.8807),
('GBP','SZL','SPOT','2014-11-10 16:00:00',17.83375),
('GBP','THB','SPOT','2014-11-10 16:00:00',52.0179),
('GBP','TND','SPOT','2014-11-10 16:00:00',2.9145),
('GBP','TOF','SPOT','2014-11-10 16:00:00',52.11705),
('GBP','TOP','SPOT','2014-11-10 16:00:00',3.0317),
('GBP','TRY','SPOT','2014-11-10 16:00:00',3.5798),
('GBP','TTD','SPOT','2014-11-10 16:00:00',10.0452),
('GBP','TWD','SPOT','2014-11-10 16:00:00',48.4511),
('GBP','TZS','SPOT','2014-11-10 16:00:00',2713.771),
('GBP','UAH','SPOT','2014-11-10 16:00:00',23.5733),
('GBP','UGX','SPOT','2014-11-10 16:00:00',4308.706),
('GBP','USD','SPOT','2014-11-10 16:00:00',1.587),
('GBP','UYU','SPOT','2014-11-10 16:00:00',38.4054),
('GBP','UZS','SPOT','2014-11-10 16:00:00',3790.15275),
('GBP','VEF','SPOT','2014-11-10 16:00:00',19.044),
('GBP','VND','SPOT','2014-11-10 16:00:00',33787.236),
('GBP','VUV','SPOT','2014-11-10 16:00:00',162.573),
('GBP','WST','SPOT','2014-11-10 16:00:00',3.82115),
('GBP','XCD','SPOT','2014-11-10 16:00:00',4.2849),
('GBP','XDR','SPOT','2014-11-10 16:00:00',1.081),
('GBP','XEU','SPOT','2014-11-10 16:00:00',1.2754),
('GBP','XOF','SPOT','2014-11-10 16:00:00',836.5844),
('GBP','XPF','SPOT','2014-11-10 16:00:00',152.19145),
('GBP','YER','SPOT','2014-11-10 16:00:00',341.24475),
('GBP','ZAR','SPOT','2014-11-10 16:00:00',17.83375),
('GBP','ZDN','SPOT','2014-11-10 16:00:00',0),
('GBP','ZMW','SPOT','2014-11-10 16:00:00',10.11715),
('GBP','ZWL','SPOT','2014-11-10 16:00:00',511.57805),
('JPY','AED','SPOT','2014-11-10 16:00:00',0.0320369),
('JPY','ALL','SPOT','2014-11-10 16:00:00',0.9770173),
('JPY','ANG','SPOT','2014-11-10 16:00:00',0.0156128),
('JPY','AOA','SPOT','2014-11-10 16:00:00',0.8709598),
('JPY','ARS','SPOT','2014-11-10 16:00:00',0.0742217),
('JPY','ATS','SPOT','2014-11-10 16:00:00',0.096452),
('JPY','AUD','SPOT','2014-11-10 16:00:00',0.0100921),
('JPY','AWG','SPOT','2014-11-10 16:00:00',0.0156128),
('JPY','AZN','SPOT','2014-11-10 16:00:00',0.0068373),
('JPY','BAM','SPOT','2014-11-10 16:00:00',0.0137093),
('JPY','BBD','SPOT','2014-11-10 16:00:00',0.0174444),
('JPY','BDT','SPOT','2014-11-10 16:00:00',0.6749673),
('JPY','BEF','SPOT','2014-11-10 16:00:00',0.2827602),
('JPY','BGN','SPOT','2014-11-10 16:00:00',0.0137096),
('JPY','BHD','SPOT','2014-11-10 16:00:00',0.0032883),
('JPY','BIF','SPOT','2014-11-10 16:00:00',13.5630289),
('JPY','BMD','SPOT','2014-11-10 16:00:00',0.0087222),
('JPY','BND','SPOT','2014-11-10 16:00:00',0.0112594),
('JPY','BOB','SPOT','2014-11-10 16:00:00',0.0602703),
('JPY','BRL','SPOT','2014-11-10 16:00:00',0.0221493),
('JPY','BSD','SPOT','2014-11-10 16:00:00',0.0087222),
('JPY','BTN','SPOT','2014-11-10 16:00:00',0.5361753),
('JPY','BWP','SPOT','2014-11-10 16:00:00',0.081062),
('JPY','BYR','SPOT','2014-11-10 16:00:00',94.0253274),
('JPY','BZD','SPOT','2014-11-10 16:00:00',0.017401),
('JPY','CAD','SPOT','2014-11-10 16:00:00',0.0098739),
('JPY','CDF','SPOT','2014-11-10 16:00:00',8.085494),
('JPY','CHF','SPOT','2014-11-10 16:00:00',0.0084293),
('JPY','CLP','SPOT','2014-11-10 16:00:00',5.1146972),
('JPY','CNH','SPOT','2014-11-10 16:00:00',0.0534088),
('JPY','CNY','SPOT','2014-11-10 16:00:00',0.0533763),
('JPY','COP','SPOT','2014-11-10 16:00:00',18.3495425),
('JPY','CRC','SPOT','2014-11-10 16:00:00',4.6909352),
('JPY','CUP','SPOT','2014-11-10 16:00:00',0.0087222),
('JPY','CYP','SPOT','2014-11-10 16:00:00',0.0041025),
('JPY','CZK','SPOT','2014-11-10 16:00:00',0.1934083),
('JPY','DEM','SPOT','2014-11-10 16:00:00',0.0137093),
('JPY','DKK','SPOT','2014-11-10 16:00:00',0.0521397),
('JPY','DOP','SPOT','2014-11-10 16:00:00',0.3791106),
('JPY','DZD','SPOT','2014-11-10 16:00:00',0.7341832),
('JPY','ECS','SPOT','2014-11-10 16:00:00',218.0549498),
('JPY','EEK','SPOT','2014-11-10 16:00:00',0.1096738),
('JPY','EGP','SPOT','2014-11-10 16:00:00',0.062364),
('JPY','ESP','SPOT','2014-11-10 16:00:00',1.166273),
('JPY','ETB','SPOT','2014-11-10 16:00:00',0.174976),
('JPY','EUR','SPOT','2014-11-10 16:00:00',0.0070096),
('JPY','FIM','SPOT','2014-11-10 16:00:00',0.0416761),
('JPY','FJD','SPOT','2014-11-10 16:00:00',0.0169926),
('JPY','FRF','SPOT','2014-11-10 16:00:00',0.045979),
('JPY','GEL','SPOT','2014-11-10 16:00:00',0.01531),
('JPY','GHS','SPOT','2014-11-10 16:00:00',0.0281072),
('JPY','GMD','SPOT','2014-11-10 16:00:00',0.3754912),
('JPY','GNF','SPOT','2014-11-10 16:00:00',61.3173267),
('JPY','GRD','SPOT','2014-11-10 16:00:00',2.3884673),
('JPY','GTQ','SPOT','2014-11-10 16:00:00',0.0662755),
('JPY','HKD','SPOT','2014-11-10 16:00:00',0.0676333),
('JPY','HNL','SPOT','2014-11-10 16:00:00',0.1853467),
('JPY','HRK','SPOT','2014-11-10 16:00:00',0.0537113),
('JPY','HTG','SPOT','2014-11-10 16:00:00',0.3998089),
('JPY','HUF','SPOT','2014-11-10 16:00:00',2.1554384),
('JPY','IDR','SPOT','2014-11-10 16:00:00',105.9965139),
('JPY','IDT','SPOT','2014-11-10 16:00:00',0),
('JPY','IEP','SPOT','2014-11-10 16:00:00',0.0055205),
('JPY','ILS','SPOT','2014-11-10 16:00:00',0.0331298),
('JPY','INR','SPOT','2014-11-10 16:00:00',0.5361753),
('JPY','IRR','SPOT','2014-11-10 16:00:00',0),
('JPY','ISK','SPOT','2014-11-10 16:00:00',1.0819887),
('JPY','ITL','SPOT','2014-11-10 16:00:00',13.572171),
('JPY','JMD','SPOT','2014-11-10 16:00:00',0.9829486),
('JPY','JOD','SPOT','2014-11-10 16:00:00',0.0061894),
('JPY','KES','SPOT','2014-11-10 16:00:00',0.7832534),
('JPY','KGS','SPOT','2014-11-10 16:00:00',0.5023645),
('JPY','KMF','SPOT','2014-11-10 16:00:00',3.4484191),
('JPY','KRW','SPOT','2014-11-10 16:00:00',9.4640209),
('JPY','KWD','SPOT','2014-11-10 16:00:00',0.0025405),
('JPY','KYD','SPOT','2014-11-10 16:00:00',0.0071523),
('JPY','KZT','SPOT','2014-11-10 16:00:00',1.5781946),
('JPY','LBP','SPOT','2014-11-10 16:00:00',13.2010472),
('JPY','LKR','SPOT','2014-11-10 16:00:00',1.1417357),
('JPY','LSL','SPOT','2014-11-10 16:00:00',0.0980148),
('JPY','LTL','SPOT','2014-11-10 16:00:00',0.0242023),
('JPY','LUF','SPOT','2014-11-10 16:00:00',0.2827602),
('JPY','LVL','SPOT','2014-11-10 16:00:00',0.0049264),
('JPY','MAD','SPOT','2014-11-10 16:00:00',0.0772211),
('JPY','MDL','SPOT','2014-11-10 16:00:00',0.1301789),
('JPY','MGA','SPOT','2014-11-10 16:00:00',23.4191126),
('JPY','MKD','SPOT','2014-11-10 16:00:00',0.4311819),
('JPY','MNT','SPOT','2014-11-10 16:00:00',16.3235974),
('JPY','MOP','SPOT','2014-11-10 16:00:00',0.0696622),
('JPY','MRO','SPOT','2014-11-10 16:00:00',2.5512468),
('JPY','MTL','SPOT','2014-11-10 16:00:00',0.0030091),
('JPY','MUR','SPOT','2014-11-10 16:00:00',0.2747495),
('JPY','MVR','SPOT','2014-11-10 16:00:00',0.1339729),
('JPY','MWK','SPOT','2014-11-10 16:00:00',4.1779438),
('JPY','MXN','SPOT','2014-11-10 16:00:00',0.1180811),
('JPY','MYR','SPOT','2014-11-10 16:00:00',0.0290495),
('JPY','MZN','SPOT','2014-11-10 16:00:00',0.2708248),
('JPY','NAD','SPOT','2014-11-10 16:00:00',0.0980148),
('JPY','NGN','SPOT','2014-11-10 16:00:00',1.4688181),
('JPY','NIO','SPOT','2014-11-10 16:00:00',0.2303279),
('JPY','NLG','SPOT','2014-11-10 16:00:00',0.0154466),
('JPY','NOK','SPOT','2014-11-10 16:00:00',0.0591631),
('JPY','NPR','SPOT','2014-11-10 16:00:00',0.8578804),
('JPY','NZD','SPOT','2014-11-10 16:00:00',0.0112262),
('JPY','OMR','SPOT','2014-11-10 16:00:00',0.0033581),
('JPY','PAB','SPOT','2014-11-10 16:00:00',0.0087222),
('JPY','PEN','SPOT','2014-11-10 16:00:00',0.0255431),
('JPY','PGK','SPOT','2014-11-10 16:00:00',0.0223523),
('JPY','PHP','SPOT','2014-11-10 16:00:00',0.3913648),
('JPY','PKR','SPOT','2014-11-10 16:00:00',0.887789),
('JPY','PLN','SPOT','2014-11-10 16:00:00',0.029551),
('JPY','PTE','SPOT','2014-11-10 16:00:00',1.4052667),
('JPY','PYG','SPOT','2014-11-10 16:00:00',40.4093349),
('JPY','QAR','SPOT','2014-11-10 16:00:00',0.0317607),
('JPY','RON','SPOT','2014-11-10 16:00:00',0.0310152),
('JPY','RSD','SPOT','2014-11-10 16:00:00',0.8411689),
('JPY','RUB','SPOT','2014-11-10 16:00:00',0.3982329),
('JPY','RWF','SPOT','2014-11-10 16:00:00',6.0095988),
('JPY','SAR','SPOT','2014-11-10 16:00:00',0.0327275),
('JPY','SBD','SPOT','2014-11-10 16:00:00',0.0653112),
('JPY','SCR','SPOT','2014-11-10 16:00:00',0.1237684),
('JPY','SDG','SPOT','2014-11-10 16:00:00',0),
('JPY','SEK','SPOT','2014-11-10 16:00:00',0.064608),
('JPY','SGD','SPOT','2014-11-10 16:00:00',0.0112594),
('JPY','SIT','SPOT','2014-11-10 16:00:00',1.6797425),
('JPY','SKK','SPOT','2014-11-10 16:00:00',0.2111665),
('JPY','SLL','SPOT','2014-11-10 16:00:00',37.9852272),
('JPY','SRD','SPOT','2014-11-10 16:00:00',0.0287833),
('JPY','SVC','SPOT','2014-11-10 16:00:00',0.0762887),
('JPY','SZL','SPOT','2014-11-10 16:00:00',0.0980148),
('JPY','THB','SPOT','2014-11-10 16:00:00',0.2858919),
('JPY','TND','SPOT','2014-11-10 16:00:00',0.0160182),
('JPY','TOF','SPOT','2014-11-10 16:00:00',0.2864368),
('JPY','TOP','SPOT','2014-11-10 16:00:00',0.0166623),
('JPY','TRY','SPOT','2014-11-10 16:00:00',0.0196747),
('JPY','TTD','SPOT','2014-11-10 16:00:00',0.0552087),
('JPY','TWD','SPOT','2014-11-10 16:00:00',0.2662886),
('JPY','TZS','SPOT','2014-11-10 16:00:00',14.9149641),
('JPY','UAH','SPOT','2014-11-10 16:00:00',0.1295595),
('JPY','UGX','SPOT','2014-11-10 16:00:00',23.680773),
('JPY','USD','SPOT','2014-11-10 16:00:00',0.0087222),
('JPY','UYU','SPOT','2014-11-10 16:00:00',0.2110772),
('JPY','UZS','SPOT','2014-11-10 16:00:00',20.8307894),
('JPY','VEF','SPOT','2014-11-10 16:00:00',0.1046664),
('JPY','VND','SPOT','2014-11-10 16:00:00',185.6956283),
('JPY','VUV','SPOT','2014-11-10 16:00:00',0.8935059),
('JPY','WST','SPOT','2014-11-10 16:00:00',0.0210012),
('JPY','XCD','SPOT','2014-11-10 16:00:00',0.0235499),
('JPY','XDR','SPOT','2014-11-10 16:00:00',0.0059412),
('JPY','XEU','SPOT','2014-11-10 16:00:00',0.0070096),
('JPY','XOF','SPOT','2014-11-10 16:00:00',4.5978921),
('JPY','XPF','SPOT','2014-11-10 16:00:00',0.8364486),
('JPY','YER','SPOT','2014-11-10 16:00:00',1.875491),
('JPY','ZAR','SPOT','2014-11-10 16:00:00',0.0980148),
('JPY','ZDN','SPOT','2014-11-10 16:00:00',0),
('JPY','ZMW','SPOT','2014-11-10 16:00:00',0.0556041),
('JPY','ZWL','SPOT','2014-11-10 16:00:00',2.8116478),
('USD','AED','SPOT','2014-11-10 16:00:00',3.67305),
('USD','ALL','SPOT','2014-11-10 16:00:00',112.015),
('USD','ANG','SPOT','2014-11-10 16:00:00',1.79),
('USD','AOA','SPOT','2014-11-10 16:00:00',99.8555),
('USD','ARS','SPOT','2014-11-10 16:00:00',8.5095),
('USD','ATS','SPOT','2014-11-10 16:00:00',11.05825),
('USD','AUD','SPOT','2014-11-10 16:00:00',0.86425),
('USD','AWG','SPOT','2014-11-10 16:00:00',1.79),
('USD','AZN','SPOT','2014-11-10 16:00:00',0.7839),
('USD','BAM','SPOT','2014-11-10 16:00:00',1.5718),
('USD','BBD','SPOT','2014-11-10 16:00:00',2),
('USD','BDT','SPOT','2014-11-10 16:00:00',77.385),
('USD','BEF','SPOT','2014-11-10 16:00:00',32.41845),
('USD','BGN','SPOT','2014-11-10 16:00:00',1.5718),
('USD','BHD','SPOT','2014-11-10 16:00:00',0.377),
('USD','BIF','SPOT','2014-11-10 16:00:00',1555),
('USD','BMD','SPOT','2014-11-10 16:00:00',1),
('USD','BND','SPOT','2014-11-10 16:00:00',1.2909),
('USD','BOB','SPOT','2014-11-10 16:00:00',6.91),
('USD','BRL','SPOT','2014-11-10 16:00:00',2.5394),
('USD','BSD','SPOT','2014-11-10 16:00:00',1),
('USD','BTN','SPOT','2014-11-10 16:00:00',61.4725),
('USD','BWP','SPOT','2014-11-10 16:00:00',0.1076),
('USD','BYR','SPOT','2014-11-10 16:00:00',10780),
('USD','BZD','SPOT','2014-11-10 16:00:00',1.995),
('USD','CAD','SPOT','2014-11-10 16:00:00',1.13205),
('USD','CDF','SPOT','2014-11-10 16:00:00',927),
('USD','CHF','SPOT','2014-11-10 16:00:00',0.9664),
('USD','CLP','SPOT','2014-11-10 16:00:00',586.4),
('USD','CNH','SPOT','2014-11-10 16:00:00',6.1233),
('USD','CNY','SPOT','2014-11-10 16:00:00',6.1196),
('USD','COP','SPOT','2014-11-10 16:00:00',2103.775),
('USD','CRC','SPOT','2014-11-10 16:00:00',537.815),
('USD','CUP','SPOT','2014-11-10 16:00:00',1),
('USD','CYP','SPOT','2014-11-10 16:00:00',0.47035),
('USD','CZK','SPOT','2014-11-10 16:00:00',22.17425),
('USD','DEM','SPOT','2014-11-10 16:00:00',1.5718),
('USD','DKK','SPOT','2014-11-10 16:00:00',5.9778),
('USD','DOP','SPOT','2014-11-10 16:00:00',43.465),
('USD','DZD','SPOT','2014-11-10 16:00:00',84.1741),
('USD','ECS','SPOT','2014-11-10 16:00:00',25000),
('USD','EEK','SPOT','2014-11-10 16:00:00',12.5741),
('USD','EGP','SPOT','2014-11-10 16:00:00',7.15005),
('USD','ESP','SPOT','2014-11-10 16:00:00',133.7132),
('USD','ETB','SPOT','2014-11-10 16:00:00',20.061),
('USD','EUR','SPOT','2014-11-10 16:00:00',1.24435),
('USD','FIM','SPOT','2014-11-10 16:00:00',4.7782),
('USD','FJD','SPOT','2014-11-10 16:00:00',1.9482),
('USD','FRF','SPOT','2014-11-10 16:00:00',5.27145),
('USD','GBP','SPOT','2014-11-10 16:00:00',1.587),
('USD','GEL','SPOT','2014-11-10 16:00:00',1.7553),
('USD','GHS','SPOT','2014-11-10 16:00:00',3.2225),
('USD','GMD','SPOT','2014-11-10 16:00:00',43.05),
('USD','GNF','SPOT','2014-11-10 16:00:00',7030),
('USD','GRD','SPOT','2014-11-10 16:00:00',273.83775),
('USD','GTQ','SPOT','2014-11-10 16:00:00',7.5985),
('USD','HKD','SPOT','2014-11-10 16:00:00',7.75415),
('USD','HNL','SPOT','2014-11-10 16:00:00',21.25),
('USD','HRK','SPOT','2014-11-10 16:00:00',6.158),
('USD','HTG','SPOT','2014-11-10 16:00:00',45.8381),
('USD','HUF','SPOT','2014-11-10 16:00:00',247.12105),
('USD','IDR','SPOT','2014-11-10 16:00:00',12152.5),
('USD','IDT','SPOT','2014-11-10 16:00:00',0),
('USD','IEP','SPOT','2014-11-10 16:00:00',1.58),
('USD','ILS','SPOT','2014-11-10 16:00:00',3.79835),
('USD','INR','SPOT','2014-11-10 16:00:00',61.4725),
('USD','IRR','SPOT','2014-11-10 16:00:00',0),
('USD','ISK','SPOT','2014-11-10 16:00:00',124.05),
('USD','ITL','SPOT','2014-11-10 16:00:00',1556.04935),
('USD','JMD','SPOT','2014-11-10 16:00:00',112.695),
('USD','JOD','SPOT','2014-11-10 16:00:00',0.7096),
('USD','JPY','SPOT','2014-11-10 16:00:00',114.65),
('USD','KES','SPOT','2014-11-10 16:00:00',89.8),
('USD','KGS','SPOT','2014-11-10 16:00:00',57.5961),
('USD','KMF','SPOT','2014-11-10 16:00:00',395.36125),
('USD','KRW','SPOT','2014-11-10 16:00:00',1085.05),
('USD','KWD','SPOT','2014-11-10 16:00:00',0.29125),
('USD','KYD','SPOT','2014-11-10 16:00:00',0.82),
('USD','KZT','SPOT','2014-11-10 16:00:00',180.94),
('USD','LBP','SPOT','2014-11-10 16:00:00',1513.5),
('USD','LKR','SPOT','2014-11-10 16:00:00',130.9),
('USD','LSL','SPOT','2014-11-10 16:00:00',11.2374),
('USD','LTL','SPOT','2014-11-10 16:00:00',2.7748),
('USD','LUF','SPOT','2014-11-10 16:00:00',32.41845),
('USD','LVL','SPOT','2014-11-10 16:00:00',0.5648),
('USD','MAD','SPOT','2014-11-10 16:00:00',8.8534),
('USD','MDL','SPOT','2014-11-10 16:00:00',14.925),
('USD','MGA','SPOT','2014-11-10 16:00:00',2685),
('USD','MKD','SPOT','2014-11-10 16:00:00',49.435),
('USD','MNT','SPOT','2014-11-10 16:00:00',1871.5),
('USD','MOP','SPOT','2014-11-10 16:00:00',7.98675),
('USD','MRO','SPOT','2014-11-10 16:00:00',292.5),
('USD','MTL','SPOT','2014-11-10 16:00:00',0.345),
('USD','MUR','SPOT','2014-11-10 16:00:00',31.5),
('USD','MVR','SPOT','2014-11-10 16:00:00',15.36),
('USD','MWK','SPOT','2014-11-10 16:00:00',479),
('USD','MXN','SPOT','2014-11-10 16:00:00',13.538),
('USD','MYR','SPOT','2014-11-10 16:00:00',3.3305),
('USD','MZN','SPOT','2014-11-10 16:00:00',31.05),
('USD','NAD','SPOT','2014-11-10 16:00:00',11.2374),
('USD','NGN','SPOT','2014-11-10 16:00:00',168.4),
('USD','NIO','SPOT','2014-11-10 16:00:00',26.4071),
('USD','NLG','SPOT','2014-11-10 16:00:00',1.771),
('USD','NOK','SPOT','2014-11-10 16:00:00',6.78305),
('USD','NPR','SPOT','2014-11-10 16:00:00',98.356),
('USD','NZD','SPOT','2014-11-10 16:00:00',0.77695),
('USD','OMR','SPOT','2014-11-10 16:00:00',0.385),
('USD','PAB','SPOT','2014-11-10 16:00:00',1),
('USD','PEN','SPOT','2014-11-10 16:00:00',2.9285),
('USD','PGK','SPOT','2014-11-10 16:00:00',2.5627),
('USD','PHP','SPOT','2014-11-10 16:00:00',44.87),
('USD','PKR','SPOT','2014-11-10 16:00:00',101.785),
('USD','PLN','SPOT','2014-11-10 16:00:00',3.388),
('USD','PTE','SPOT','2014-11-10 16:00:00',161.11385),
('USD','PYG','SPOT','2014-11-10 16:00:00',4632.93),
('USD','QAR','SPOT','2014-11-10 16:00:00',3.64135),
('USD','RON','SPOT','2014-11-10 16:00:00',3.5559),
('USD','RSD','SPOT','2014-11-10 16:00:00',96.44),
('USD','RUB','SPOT','2014-11-10 16:00:00',45.6574),
('USD','RWF','SPOT','2014-11-10 16:00:00',689),
('USD','SAR','SPOT','2014-11-10 16:00:00',3.7522),
('USD','SBD','SPOT','2014-11-10 16:00:00',0.13355),
('USD','SCR','SPOT','2014-11-10 16:00:00',14.19),
('USD','SDG','SPOT','2014-11-10 16:00:00',0),
('USD','SEK','SPOT','2014-11-10 16:00:00',7.4073),
('USD','SGD','SPOT','2014-11-10 16:00:00',1.2909),
('USD','SIT','SPOT','2014-11-10 16:00:00',192.5825),
('USD','SKK','SPOT','2014-11-10 16:00:00',24.2102),
('USD','SLL','SPOT','2014-11-10 16:00:00',4355),
('USD','SRD','SPOT','2014-11-10 16:00:00',3.3),
('USD','SVC','SPOT','2014-11-10 16:00:00',8.7465),
('USD','SZL','SPOT','2014-11-10 16:00:00',11.2374),
('USD','THB','SPOT','2014-11-10 16:00:00',32.7775),
('USD','TND','SPOT','2014-11-10 16:00:00',1.8365),
('USD','TOF','SPOT','2014-11-10 16:00:00',32.84),
('USD','TOP','SPOT','2014-11-10 16:00:00',0.5237),
('USD','TRY','SPOT','2014-11-10 16:00:00',2.2557),
('USD','TTD','SPOT','2014-11-10 16:00:00',6.3297),
('USD','TWD','SPOT','2014-11-10 16:00:00',30.53),
('USD','TZS','SPOT','2014-11-10 16:00:00',1710),
('USD','UAH','SPOT','2014-11-10 16:00:00',14.854),
('USD','UGX','SPOT','2014-11-10 16:00:00',2715),
('USD','UYU','SPOT','2014-11-10 16:00:00',24.2),
('USD','UZS','SPOT','2014-11-10 16:00:00',2388.25),
('USD','VEF','SPOT','2014-11-10 16:00:00',12),
('USD','VND','SPOT','2014-11-10 16:00:00',21290),
('USD','VUV','SPOT','2014-11-10 16:00:00',102.44),
('USD','WST','SPOT','2014-11-10 16:00:00',0.4155),
('USD','XCD','SPOT','2014-11-10 16:00:00',2.7),
('USD','XDR','SPOT','2014-11-10 16:00:00',0.68115),
('USD','XEU','SPOT','2014-11-10 16:00:00',1.24435),
('USD','XOF','SPOT','2014-11-10 16:00:00',527.14835),
('USD','XPF','SPOT','2014-11-10 16:00:00',95.89885),
('USD','YER','SPOT','2014-11-10 16:00:00',215.025),
('USD','ZAR','SPOT','2014-11-10 16:00:00',11.2374),
('USD','ZDN','SPOT','2014-11-10 16:00:00',0),
('USD','ZMW','SPOT','2014-11-10 16:00:00',6.375),
('USD','ZWL','SPOT','2014-11-10 16:00:00',322.355)
My poor attempt:
DECLARE @StartDate DATE = '2014-11-05'
DECLARE @EndDate DATE = '2014-11-11'
--Create a temporary table to hold the results of the following query
IF OBJECT_ID('tempdb..#fxList') IS NOT NULL
DROP TABLE #fxList
CREATE TABLE #fxList(from_currency_code VARCHAR(10), to_currency_code VARCHAR(10), mid_currency_code VARCHAR(10))
--Get a list of all the possible currency pairings by joining the "to_ccy_code" column to itself
;WITH myFXcte AS
(
SELECT DISTINCT to_currency_code, from_currency_code, effective_date, fx_period
FROM fxRates (NOLOCK) c1
)
INSERT INTO #fxList(from_currency_code, to_currency_code, mid_currency_code)
--GBP is one of the few "from_ccy_codes" that have a pairing to all other currencies. Looking at the fxRate table, the only "to_ccy_code" that's not mapped to GBP, is GBP.
--GBP will be used as the pivot currency for working out all currency pairs, e.g. AUD --> GBP --> SEK
SELECT DISTINCT c1.to_currency_code, c2.to_currency_code, 'gbp'
FROM
fxRates (NOLOCK) c1
CROSS JOIN
myFXcte c2
ORDER BY c1.to_currency_code, c2.to_currency_code
--Work out the currency pair from, for example, AUD to SEK by doing AUD--GBP rate / GBP --> SEK
--This bit gets the first leg fx rates
SELECT chmf.effective_date,tmpfx.from_currency_code,fxOut.mid_price1 as 'GBP_to_fromcnt', tmpfx.to_currency_code, chmf.mid_price as 'GBP_to_tocnt',
ISNULL(NULLIF(chmf.mid_price,0)/NULLIF(fxOut.mid_price1,0),0) as '3waymidprice'
FROM #fxList tmpfx
LEFT JOIN fxRates (NOLOCK) chmf
ON tmpfx.mid_currency_code = chmf.from_currency_code
AND tmpfx.to_currency_code = chmf.to_currency_code
AND chmf.effective_date >= @StartDate AND chmf.effective_date < @EndDate
AND chmf.fx_period = 'SPOT'
--this bit gets the second leg rates
OUTER APPLY
(
SELECT tmpfx1.from_currency_code, tmpfx1.to_currency_code, tmpfx1.mid_currency_code, chmf1.from_currency_code as 'rep_from_ccy_code1', chmf1.to_currency_code as 'rep_to_ccy_code1',
mid_price as 'mid_price1'
FROM #fxList tmpfx1
JOIN fxRates (NOLOCK) chmf1
ON tmpfx1.from_currency_code = chmf1.from_currency_code
--AND tmpfx1.from_currency_code = chmf1.to_currency_code
WHERE tmpfx1.from_currency_code = tmpfx.from_currency_code
AND tmpfx1.from_currency_code = tmpfx.from_currency_code
AND chmf1.effective_date = chmf.effective_date
AND chmf1.fx_period = chmf.fx_period
) fxOut
ORDER BY from_currency_code, to_currency_code, effective_date
November 12, 2014 at 10:04 am
Is your sample data correct? You've got GBP to USD showing as 1.587, and you've also got USD to GBP as the same rate.
I'd start by simplifying the exchange rate table, just listing GBP as the 'from' currency. Then if I need to work out AUD -> SEK I can do AUD -> GBP by calculating the reciprocal of the GBP-AUD rate, and then look up GBP-SEK as normal. Would this work?
November 12, 2014 at 10:13 am
Hi,
Yes, the sample data is correct - there are a couple of anomalies in there. The table itself is out of my control; I have to use it as is unfortunately. I also have no control over the data that goes in there.
I "think" that's what I was trying to essentially do with my code, but couldn't get it to work for all codes over a particular date range, simultaneously.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply