Tech Tip: IDEA 11 Labs: CURRENCY CONVERTER

If you find yourself confronted with a file containing transaction records with different currencies, don’t panic! IDEA has a feature that automatically tracks and stores conversion rates, saving you hours or days of unnecessary effort.
 

IDEA v11.2 Labs

• Download and install IDEA Labs, CURRENCY CONVERTER. The Currency Converter plugin converts a Numeric field to another currency based on a specified date. The plugin uses the exchangeratesapi.io website as a source for the exchange rates. This site obtains the exchange rates from the European Central Bank. To use this plugin, your database must have an amount field, a date field (specifying the date of the conversion), and a field with the currency type. The plugin lets you choose from multiple currencies for the conversion (added 3-Feb-2020).
• This is a fairly simple, easy-to-use plugin. We have tested the process on a complex dataset containing over 3 million records. Thanks to IDEA v11.2 and a Solid-State Drive, this task was completed in under 5 minutes! NOTE: The exchange rate tables only go back to 1999, validate your data file to ensure that all dates are greater than or equal to 1-Jan-1999.
• Resulting Databases
  • Exchange Results Final – All records with valid 3-character currency codes with added columns for the Exchange Rate and the converted Transaction Amount.
  • Exchange Results with no match – Records that contain a currency code not recognized for conversion. Example: MXN not MXFT for Mexican peso.
  • Exchange Results Currency Selected – Records that are already in the currency selected for the conversion. OK to delete – records included in Exchange Results Final.
  • Exchange Rate 181537 – Work file, OK to delete.
  • Exchange Rate 18169 – Work file, OK to delete.
  • Exchange Results – Work file, OK to delete. Records included in Exchange Results Final.

IDEA v9/v10 – See also Tech Tip on RANGE JOIN

• Append field PRECNO @Precno() to Transaction Database
• Open your Transaction Database and select VISUAL CONECTOR task from ANALYSIS tab
  • Add your Currency Conversion Table
  • Add join on key (Currency Code) for exact match

   
  
   

  • Resulting database will contain 1 record for each transaction currency code and date possibility.
  • Perform DIRECT EXTRACTION criteria @BetweenDate(SALES_DATE, EFFECTIVE_DATE , END_DATE ) to get the correct Exchange Rate for the date.

   

  
   

  • Add numeric field with value  SALES_PRICE_AMT  *  CONVERSION_RATE
  • WARNING: If a currency code or effective period if missing from the Currency Conversion Table, you will have “orphans” that are not included in your final report. Perform a JOIN from the original Transaction Database to your results on PRECNO – Records with no secondary match.

   

  

  
  IDEA Labs, CURRENCY CONVERTER added 3-Feb-2020. IDEA 11.1 64-bit

  ---

  To upgrade to IDEA 11.2, please contact our client success team via [email protected].

  If you would like help using a specific IDEA feature or function, or even have general technical questions,
  our Help Desk is ready to support you every step of the way!

  Please contact them via [email protected] or directly at 1.888.641.2800, option 4.

  
  Best Practices , CaseWare IDEA , Tech Tip