Contents
1. Introduction
2. How to update the exchange rates step by step
2.1 – Open the received file on ticket "CAR3 Exchange Rates".
2.2 - Create a pivot table based on the latest broadcast.
2.3 – Copy the rates and paste to the table of sheet "ex-WBP" in the load file.
2.4 - Update sheet "RATE(OLD)"
2.5 - Update sheet "RATE(NEW)"
2.6 - Update sheet "TO BE UPLOADED"
2.7 - Copy sheet "TO BE UPLOADED" to CSV format file
3. Update on Data Loader the exchange rates
4. Confirm exchange rates update in Convergence
5. Conclusion

1. Introduction

This procedure explains how to update exchange rates in Convergence on a monthly basis.
The data owner is GPS Pur (Sylvie Severini and Yi Ting Kuo).
The exchange rates of the previous month are sent on the 5th of each month via e-mail (CAR3 Exchange Rates Broadcast). The excel file is attached to the e-mail and is created automatically by BW.

Please check below how information is received.

2. How to update the exchange rates step by step

     2.1 – Open the received file on ticket "CAR3 Exchange Rates".

    2.2 - Create a pivot table based on the latest broadcast.

• Pivot table data to be inserted
  Column (From currency) / Row (Exchg Rate Eff Date) / Values (Exchange rate CAR3 *1000000)
  
• Take only the exchange rate until current month-1 (In 01/01/2018 take until 1/12/2017).
• Change the dates into number format or text (Ex: 01-12-2017 to 43070).
  
  Note: If there are new currencies update the full table.

  2.3 – Copy the rates and paste to the table of sheet "ex-WBP" in the load file.

  Load file link: {+}https://docs.google.com/a/solvay.com/spreadsheets/d/1jOJCHxQwyrRFYyO6rtmxFChDmiiHOmLnabJlWlxu3uU/edit?usp=sharing+
  File name: CVG > Contracts + Value Creation > Rates
  
  -Add an empty row and copy the rates after the last row (01-11-2017 "43040")
  

  2.4 - Update sheet "RATE(OLD)"

• Push down all cells starting from the last line
• Expand the information from line 84 to 85 as per below example.
  

  2.5 - Update sheet "RATE(NEW)"

• Push down all cells starting from the last line
• Expand the information from line 84 to 85 as per below example.
  

  2.6 - Update sheet "TO BE UPLOADED"

• Push down all cells starting from the last line
• Expand the information from line 84 to 85 as per below example.
  

  2.7 - Copy sheet "TO BE UPLOADED" to CSV format file

• The information is to be saved in a new file in CSV format
  
  Note: The key, that is a text field, has to be stored in the following format: DD/MM/YYYY otherwise an error message occurs.
  
  
  

  3. Update on Data Loader the exchange rates

• Open Dataloader and select upsert.
  
  
  
• Step 2 "Select data objects" select "Exchange Rate" and choose the CSV file.

• Step 2a "Choose your field to use for matching" select "Key_c".

• Step 3 "Mapping" select "Create or Edit a Map" and "auto-Match Fields to Columns".
  Click OK to save the mapping.
  
  
  
• Step 4 "Finish" Select the directory where you want to save the error files.
  
  
  If there are errors on the transfer, the information should be saved on a file.
  L1 support must analyze the error messages and try to solve it, otherwise must be escalated to L2 (ticket from L1 to L2).

  4. Confirm exchange rates update in Convergence

• Open Convergence
• Select Exchange Rates and select the month-1 (Ex: 01-12-2017)
• Confirm the updated exchange rates
  
  
  
  
  

  5. Conclusion

  
  The objective of this procedure is to allow a user step by step on how to update exchange rates in Convergence using data loader.