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.
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.
Jul 11
In today’s fast-paced organizations, new developments can unfold at a moment’s notice. Critical decisions often must be made on compressed time sche...
Jul 27
Originally published in the June 2017 issue of Strategic Finance.
Written by Kurt Reding, Wichita State University, & Carolyn Newman, Audimation Services
...
Jul 20
Where to Look and How to Find Them
Fraud is on the rise. And if you don’t know what fraud looks like, you’re not likely to find it. While most oc...
BROWSER NOT SUPPORTED
This website has been designed for modern browsers. Please update. Update my browser now
