IDEA Users Working Remotely: We’re here to help! Please contact our IDEA Help Desk to ensure there is minimal disruption to your daily workflow.

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
Loans and Mortgages
Jan 01 The following tests are relevant where the accounts are either commercial or personal loans.   Analyses   Stratify balances and create a summar...
Tech Tip: Fuzzy Join
Sep 24 CaseWare IDEA 11.1 provides a robust plugin to perform a fuzzy join between IDEA databases, searching for any overlap or possible duplicates between the two dat...
Tech Issues: Data Analytics Locks in Relevance
Nov 11 As a CPA, you’ve always been the one who can pick the relevant data out of an array of long columns and rows. These days, however, the amount of data collecte...
BROWSER NOT SUPPORTED

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

×