Audimation Services has been acquired by Caseware International Learn More.

X
Icon


Blog Image

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.

 

Nov-Newsletter Picture 4

 

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

     
    Nov-Newsletter Picture 5
     

    • 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



Posted By

By Kris Willison
Kris joined the Professional Services team in January of 2015 as a Solutions Specialist. She has an extensive background in Software and Database Development accumulated from thirty years in IT support with twenty years’ experience in database development, cleanup, audit and migration using Microsoft Access. In her time with Audimation, she has received client praise for her “Top Tier” engagement on Monitor and Scripting projects. Kris enjoys looking at problems from new angles to determine the most efficient means of meeting the clients’ needs. Kris has been breeding/showing purebred Balinese cats since 1972 and Oriental Longhairs since 1996. She also hosts one of the largest online pedigree database sites for Siamese and related breeds with nearly 600 users worldwide.


Related Posts
Get to the Point!
May 09 Visualization allows us to explain complex data and make connections that would otherwise remain unknown. The new functionality of IDEA 10 gives you the ability...
School Districts
Jan 01 Schools have many of the standard ledgers, payroll and accounts payable can be key concerns. Typical test areas include:   Asset Management   I...
Exporting Data from 3 Commonly Used Applications
Jun 16 Before any work can be done in IDEA, you’ll first need to acquire the data to be analyzed. Here are the steps to export data from three commonly used a...
BROWSER NOT SUPPORTED

This website has been designed for modern browsers. Please update. Update my browser now

×