Updating NetSuite’s Currency Rates Programmatically

This article is relevant if you are using NetSuite’s multiple currency rates and you are not satisfied with the default daily automatic price update feature.

Foreign Currency Exchange Rate Updates


NetSuite does a good job supporting over 190 different currencies native.  Out of the box, you can ask NetSuite to update currency rates nightly.  However, what do you do if you are not satisfied with the rates they provide or the frequency / timing for the updates?  You can take matters into your own hands.

Three ways to Update Foreign Currency Exchange Rates

NetSuite offers three ways to update the foreign currency rates:

  1. Manually via the menu Lists, Accounting, Currency Exchange Rates.
  2. Via CSV Import.
  3. Via Web Services (NetSuite’s SuiteTalk).
Now, if you are going to use your own exchange rate pricing mechanism, the manual option isn’t going to work due to the ongoing maintenance. ¬†Importing a CSV manually every day sounds costly; yet a little better than manually updating rates — maybe. ¬†SuiteTalk is always good, but that demands a third party external system to push the rates into NetSuite.

Interestingly, using SuiteScript to update rates is not natively supported.  However, with recent capacities to automate a CSV import, you can use SuiteScript to update foreign exchange rates through this approach.  Here is what to do.

How to Update NetSuite’s Currency Exchange Rates with SuiteScript

The following SuiteScript code snippet assumes that you are getting your rates from some other service and this function will then be used to “push” it into the NetSuite. ¬†The idea is to use remote services, such as JSON, to collect rates that you want to use and then push that into NetSuite automatically under your prescribed schedule. ¬†It’s important to note that the CSV import runs asynchronously and you can not programmatically know if the job succeeded. ¬†Generally, once you have this setup up right, this is not an issue in practice.
function scheduleCSVCurrencyUpdate(csvLine) {
	//csvLine represents a line of comma separated values of price information

	var mappingFileId = "CUSTIMPORT_rate_trx_csv"; // this references a saved CSV import map with header info below

	// add a header to the import
	var primaryFileAsString =  "Base Currency,Currency,Exchange Rate,Effective Date\n" + csvLine;

	nlapiLogExecution('DEBUG', 'scheduleCSVCurrencyUpdate', csvLine); 

	// create the CSV import job with a description that leverages the date
	var job = nlapiCreateCSVImport();
	job.setMapping(mappingFileId);
	job.setPrimaryFile(primaryFileAsString);
	job.setOption("jobName", "MY_CURRENCY_UPDATE: " + new Date());

	nlapiSubmitCSVImport(job);
	nlapiLogExecution('DEBUG', 'scheduleCSVCurrencyUpdate', 'Job submited!');
}
Let us know if you have found a better way to use SuiteScript to update currency exchange rates.  We love to innovate on the platform.  If you need help automating your NetSuite operations, contact us.

 

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , , , | Category: NetSuite, Technical | 5 Comments

4 Comments

  1. John
    Posted July 26, 2016 at 5:07 am | Permalink

    Hi Marty! For those who want to set alarms and receive notifications via email about their exchange rates, I recommend this online service http://exrate.today/

  2. Posted August 25, 2016 at 7:46 pm | Permalink

    Hi John,

    Thanks for this reference. The trick is to get this into NetSuite automatically.

    Marty

  3. Waleed Ahmed
    Posted October 8, 2018 at 5:29 am | Permalink

    Hi,

    Could you give example csv contents for import job? I am tying to import currency rates following you example but getting following error:

    Invalid transactioncurrency reference key “USD”.

    Where my csv line is ‘EUR,USD,1.1506,05.10.2018’

  4. Posted October 21, 2018 at 9:36 am | Permalink

    Be sure to see how you are referencing currency definitions in the Lists, Accounting, Currencies menu. Once you solve it using standard NetSuite import approaches, you should be able to understand what you need to do for programmatic updates.

    Marty

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>