Audimation Services has been acquired by Caseware International Learn More.


Blog Image


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 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
Tech Tip: Using Top Records Extraction
May 26 One of the critical advantages data analytics delivers is the ability to slice and dice data, then search for any anomolies or anything else out of place that m...
No Image
May 09 Visuals show us at one glance what might take pages to communicate. When one of the world’s leading suppliers to the oil and gas industry wanted to devel...
No Image
Feb 16 CaseWare Analytics has released IDEA 10.1, which is fully compatible with Microsoft Windows 10. This latest release includes some new functionality including: ...

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