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 is design to be used as so-called "Look-up" data to be able to simply, find the relationship (exchange rate) between 2 different currencies.Exchange rate data in the Data Lake is composed of 1 table view:

    • V_DIM_exchange_rate

    Project Space: prj-data-pricing-dev
    See how this entity relates to the others in the pricing data model here.

    Given the need to present only CAR3 and CAR4 exchange type data to be used by the transparency dashboard and pricing optimization tool, there is also an enriched version of the FX Rates data named as per below: 

    • V_DIM_exchange_rate_enriched

    
    

    The enriched version has the following characteristics and differences from the non-enriched version:

    Project Space: prj-data-pricing-dev
    See how this entity relates to the others in the pricing data model here.

    
    

  • Have consistency across the different tables where source system is present in the way sources are named.

  • Have only 1 unique FX rate per pair and type, regardless on whether the underlying data has multiple (equivalent) FX rates from multiple sources. Taking WP1 as the proffered system.

  • [In the final view] Not have a “source system id” column in the final view exposed to users (at pricing data lake level), however, it should be kept for traceability proposes at data ocean or ODS level.

  • Filter only FX rates types = CAR3 and CAR4.

  • Filter Fx rates on effective_date from 1st of Jan 2018. These should be kept.

  • Have an additional “to_currency” conversion into USD (in addition to EUR)

  • Have a new “forecasted FX rates” calculated values on FX rate type CAR 3 only (therefore we need to keep the forecasted data coming from CAR4 BUT replace the CAR 3 forecasted data) added as additional records in the pricing data lake based on the following criteria:

    1. Calculated for effective date for the next rolling 18 months on a daily basis.

    2. 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.

    3. 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.

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: prj-data-pricing-dev.DataOcean.V_DIM_exchange_rate