See also the Data Governance page on Exchange Rate data governance, as well as the BW page on BW Currency Conversion.

Data Object Definition

Exchange rates are conversion rates set between two currencies, published in Reuters website, identified by a RIC code (*) and daily uploaded to Solvay systems. The rate is usually ECB (European Central Bank) rate. 

*RIC code: A Reuters Instrument Code, or RIC, is a ticker-like code used by Thomson Reuters to identify financial instruments and indices. The codes are used for looking up information on various Thomson Reuters financial information systems.


Key Concepts:

  1. "From currency" and "To currency" - The data is structured in a way where you can search for the "currency from" and the "currency to" in which you want to know the exchange rate. Therefore, each of the official currencies in the world (based on Reuters) will have its relationship (exchange rate) all the other currencies in the world.
  2. "Exchange rate" - Is the rate at which one currency will be exchanged for another currency.
  3. "Effective date" - It references to the date in which the exchange rate was captured. Normally the end of the month. Note that exchange rates are fluctuating 24x7 with no universal centralized market (the Forex market is considered to be an Over the Counter (OTC) Market) so the end of the month rate is taken as a reference for simplicity on currency conversion. This does not mean that the exchange rates are static within the given month in which was taken.
  4. "Exchange rate type" - Exchange rates can be calculated in different way depending on several parameters. A comprehensive view on what these parameters are and what exchange rate types is available here.


Data Model

Exchange rate data in pricing will be used as a reference data to translate the incoming transaction record currency into Euros and USD

Given the need to present only CAR3 and CAR4 exchange type data to be used by the transparency dashboard and pricing optimization tool, below view will act as enriched version of the FX Rates data comparing to the data extracted from source: 

  • V_DIM_exchange_rate_enriched
    • Filter only FX rates types = CAR3 and CAR4.
    • Filter Fx rates on effective_date from 1st of Jan 2018.
    • For each currency , the table will hold the Euro and USD conversion details.
    • Has a new “forecasted FX rates” calculated values on FX rate type CAR 3 only added as additional records in the pricing data lake based on the following criteria:
      • Calculated for effective date for the next rolling 18 months on a daily basis.
      • Taking on a daily basis the last available closed month (considering a closed the previous month from the day 1st of the current month) FX rate (closed month daily available FX Rate) to be taken as the value to be forecasted (for the next 18 months), generating one record per month. Note that since we are only using CAR3 and CAR4 these are monthly rates.
      • There is no need to keep forecasted FX Rates once is overdue, it needs to be replaced by the actual (real) FX rate. We don’t need to keep different versions of FX rates.
      • Add one additional row on daily basis (forecast and historical) with the EUR to EUR conversion set to 1 for the CAR3 and CAR4 data to be exposed to the pricing data lake.

Project Space: gcp-sqo-data-pricing-d
See how this entity relates to the others in the pricing data model here.


Additional Info & Comments

  • Rates are normally updated on either monthly or daily basis, depending on the update frequency in BW. A comprehensive list of "Exchange rate types" and their corresponding update frequencies is available here.  
  • When applying currency conversions using Exchange Rate data, users need to be careful to use the correct type of exchange rate. There can be significant differences between daily rates, end-of month rates, budget rates, etc. Applying exchange rates inconsistently (e.g. using a budget rate in BW but then converting back using a daily rate in Qlik) will result in variances and inconsistencies, which can be significant.
  • More information on the different types of Exchange rate data can be found in the Exchange Rates and the Currency Conversion pages of the BW reporting wiki.


Data Flow








Data at a Glance


<iframe width="1200" height="600" src="https://lookerstudio.google.com/embed/reporting/656626cb-9e98-4b20-aec6-6f15b55e0966/page/tEnnC" frameborder="0" style="border:0" allowfullscreen></iframe>



Tables & Attributes

View ID: gsp-sqo-data-pricing-d.DataOcean.V_DIM_exchange_rate_enriched