FX Rates

A summary of the load process for foreign exchange rates to convert traded currencies to GBP.

Step 1

Load FX Rates from Calypso.

Criteria: All currency conversion rates to GBP and USD

View SQL1

View SQL2

Load GBP fx rates from calypso

SELECT
	a.fx_date,
	cast(datename(mm, a.fx_date) as char(3)) || ' ' || cast(datename(yy, a.fx_date) as char(4)) as fx_mth_yr,
	a.fx_from_ccy,
	'GBP' as fx_to_ccy,
	case
		when a.fx_from_ccy = 'ZWD' then 1.0
		when a.fx_from_ccy = 'USD' then 1 / a.usd_conversion_fx_rate
		when a.fx_from_ccy = 'GBP' then 1.0
		when a.fx_from_ccy in ('EUR', 'NZD', 'AUD') then a.usd_conversion_fx_rate / b.gbp_conversion_fx_rate
		else 1 / b.gbp_conversion_fx_rate / a.usd_conversion_fx_rate
	end as fx_rate
FROM
	(
		SELECT
			qv.quote_date as fx_date,
			substring(qv.quote_name, 8, 3) as fx_from_ccy,
			(qv.bid + qv.ask) / 2 as usd_conversion_fx_rate
		FROM
			calypso.quote_value qv
			INNER JOIN calypso.pricing_env pe on qv.quote_set_name = pe.quote_set_name
		WHERE
			pe.pricing_env_name = 'ADORO_GBP_Spot'
			AND len(qv.quote_name) = 10
			AND substring(qv.quote_name, 4, 3) = 'USD'
			AND qv.quote_date >= convert(datetime,convert(varchar(10),dateadd(day,-1,getdate()),103),103)
		UNION
		SELECT
			qv.quote_date,
			case
				substring(qv.quote_name, 4, 3)
				when 'GBP' then 'USD'
				when 'ZAR' then 'GBP'
				else substring(qv.quote_name, 4, 3)
			end,
			case
				when substring(qv.quote_name, 4, 3) = 'ZAR' then 1.0
				else (qv.bid + qv.ask) / 2
			end
		FROM
			calypso.quote_value qv
			INNER JOIN calypso.pricing_env pe on qv.quote_set_name = pe.quote_set_name
		WHERE
			pe.pricing_env_name = 'ADORO_GBP_Spot'
			AND len(qv.quote_name) = 10
			AND substring(qv.quote_name, 4, 3) <> 'USD'
			AND qv.quote_date >= convert(datetime,convert(varchar(10),dateadd(day,-1,getdate()),103),103)
	) a
	LEFT JOIN (
		SELECT
			qv.quote_date as fx_date,
			(qv.bid + qv.ask) / 2 as gbp_conversion_fx_rate
		FROM
			calypso.quote_value qv
			INNER JOIN calypso.pricing_env pe on qv.quote_set_name = pe.quote_set_name
		WHERE
			pe.pricing_env_name = 'ADORO_GBP_Spot'
			AND len(qv.quote_name) = 10
			AND substring(qv.quote_name, 4, 3) = 'GBP'
			AND substring(qv.quote_name, 8, 3) = 'USD'
			and qv.quote_date >= convert(datetime,convert(varchar(10),dateadd(day,-1,getdate()),103),103)
	) b ON a.fx_date = b.fx_date

Load USD fx rates from calypso

SELECT
	a.fx_date,
	cast(datename(mm, a.fx_date) as char(3)) || ' ' || cast(datename(yy, a.fx_date) as char(4)) as fx_mth_yr,
	a.fx_from_ccy,
	'GBP' as fx_to_ccy,
	case
		when a.fx_from_ccy = 'ZWD' then 1.0
		when a.fx_from_ccy = 'USD' then 1 / a.usd_conversion_fx_rate
		when a.fx_from_ccy = 'GBP' then 1.0
		when a.fx_from_ccy in ('EUR', 'NZD', 'AUD') then a.usd_conversion_fx_rate / b.gbp_conversion_fx_rate
		else 1 / b.gbp_conversion_fx_rate / a.usd_conversion_fx_rate
	end as fx_rate
FROM
	(
		SELECT
			qv.quote_date as fx_date,
			substring(qv.quote_name, 8, 3) as fx_from_ccy,
			(qv.bid + qv.ask) / 2 as usd_conversion_fx_rate
		FROM
			calypso.quote_value qv
			INNER JOIN calypso.pricing_env pe on qv.quote_set_name = pe.quote_set_name
		WHERE
			pe.pricing_env_name = 'ADORO_GBP_Spot'
			AND len(qv.quote_name) = 10
			AND substring(qv.quote_name, 4, 3) = 'USD'
			AND qv.quote_date >= convert(datetime,convert(varchar(10),dateadd(day,-1,getdate()),103),103)
		UNION
		SELECT
			qv.quote_date,
			case
				substring(qv.quote_name, 4, 3)
				when 'GBP' then 'USD'
				when 'ZAR' then 'GBP'
				else substring(qv.quote_name, 4, 3)
			end,
			case
				when substring(qv.quote_name, 4, 3) = 'ZAR' then 1.0
				else (qv.bid + qv.ask) / 2
			end
		FROM
			calypso.quote_value qv
			INNER JOIN calypso.pricing_env pe on qv.quote_set_name = pe.quote_set_name
		WHERE
			pe.pricing_env_name = 'ADORO_GBP_Spot'
			AND len(qv.quote_name) = 10
			AND substring(qv.quote_name, 4, 3) <> 'USD'
			AND qv.quote_date >= convert(datetime,convert(varchar(10),dateadd(day,-1,getdate()),103),103)
	) a
	LEFT JOIN (
		SELECT
			qv.quote_date as fx_date,
			(qv.bid + qv.ask) / 2 as gbp_conversion_fx_rate
		FROM
			calypso.quote_value qv
			INNER JOIN calypso.pricing_env pe on qv.quote_set_name = pe.quote_set_name
		WHERE
			pe.pricing_env_name = 'ADORO_GBP_Spot'
			AND len(qv.quote_name) = 10
			AND substring(qv.quote_name, 4, 3) = 'GBP'
			AND substring(qv.quote_name, 8, 3) = 'USD'
			and qv.quote_date >= convert(datetime,convert(varchar(10),dateadd(day,-1,getdate()),103),103)
	) b ON a.fx_date = b.fx_date

Step 2

Load fx rates to production table

View SQL

Load fx rates into production table dw_fx_rates

INSERT INTO
	dbo.dw_fx_rates (fx_date, fx_from_ccy, fx_to_ccy, fx_rate)
SELECT
	distinct fx_date,
	fx_from_ccy,
	fx_to_ccy,
	fx_rate
FROM
	dbo.stage_calypso_fx_rates