2. Dynamics 365
  3. Add-In Lösungen
  4. Currency Online Update

Currency Online Update for Microsoft Dynamics AX (Axapta)

1. Application Area

Currency Online Update solution is implemented as automatic procedure or wizard that simplifies the ability to keep Dynamics AX (Axapta) currency rates information up-to-date.

Standard Dynamics AX (Axapta) doesn’t have built-in modules for this purpose, so user has to enter and update appropriate tables manually in Dynamics AX (Axapta) setup.

Currency Online Update solution provides quick and easy facility to update currency information from desired data sources – online Internet servers providing financial data.

2. Online Update

Currency update structural diagram is shown in Figure 1. It’s Currency Web Server that maintains currency information from different data sources. Gathering module downloads financial info from these sources (providers) and accumulates it in Currency Web Server database. Currency Web server is available for Dynamics AX (Axapta) applications. Each of them can query information by desired date, rate, and provider via Internet protocols.

Figure 1: Currency update structural diagram

2.1. Currency Web Server

Currency Update Server represents a server part of described system. This unit can be implemented via two different manners:

  1. Using Web-Service application.
  2. Using ASP/PHP technology.

Current version of Currency Web Server application is implemented using ASP approach. Web Server hosts the database served by MS-SQL Server 2000.

2.2. Currency Update module in Dynamics AX (Axapta)

Currency Update module in Dynamics AX (Axapta) is a client part of target system. Purpose of this unit is to inquiry exchange rate information from Currency Web Server and update Dynamics AX (Axapta) database. User is to select required date, provider, and currency code from user interface. Then Dynamics AX (Axapta) Update module sends appropriate request to Currency Web Server. Preview form displays downloaded rates table; user can correct or discard some of them. Finally, selected information will be imported to Dynamics AX (Axapta) currency table. Summary information dialog displays statistics.

2.3. Currency Rates Import module

Currency Rates Import module is a separate unit used for automatic gathering rates for Web Server. It can be considered as addition to Currency Web Server.

3. Currency Rates Gathering

Two technologies are considered that provide different approaches to gather exchange rate information. These are “Using Web Services” and “Importing data files”.

3.1. Using Web Services

Microsoft provides Web Services technology that can be utilized for currency rates retrieval. XML Web Services is a programmable entity that provides a particular element of functionality, such as application logic, and is accessible from any number of potentially disparate systems using ubiquitous Internet standards, such as XML and HTTP. XML Web Services depend heavily upon the broad acceptance of XML and other Internet standards to create an infrastructure that supports application interoperability at a level that solves many of the problems that previously hindered such attempts.

Each service is described by Web Services Description Language (WSDL). WDSL file describes the service and the operations (stored procedures and templates) that are available in the service. Configuration process creates specific complex types to describe the data format in the SOAP input and output.

Advantages of mentioned approach are as follows:

  • Easiness of implementation (Microsoft provides a set of objects for Web Services support, especially for the .NET platform).
  • Interface of data exchange is strongly defined by WSDL.
  • Possibility to request desired data range via Service methods, e.g. rates for definite date only.
  • Performance and transferred data size depend on requested information.
  • Using of modern technology.

Main disadvantage of Web Services is that its implementation strongly depends on the service used. There are some services on the Web (e.g. www.webservicex.net) that provide such info, but they implement different WSDL formats. Naturally, it is not difficult to support one of them, but implementation of “universal” client is non-trivial and expensive task. Let’s consider alternative approach described in the next chapter.

3.2. Importing data files

Many web sites provide currency rate tables using HTML, XLS, or other formats. Some of them are www.dailyfx.com, www.fxcm.com, www.reuters.com, statistiek.dnb.nl, etc.

Often, there are links to ready downloadable Excel or text files. E.g. statistiek.dnb.nl resource offers euro exchange rates with available Daily, Monthly, Quarterly currency documents in Excel format.

Independently on data format, currency rate information represents a data table. It is possible to implement a wizard that supplies such a source table and transfers information to Dynamics AX (Axapta). This approach doesn’t strongly depend on Web server as a previous one and has the following pro and contra.

Main advantages are as follows:

  • Independency on data provider service/site.
  • Support of different well-known data formats, such as XLS, TXT, CSV and XML.

However, not all web sites provide direct links to stored documents. In some cases, there are JavaScript or PHP scripts that don’t allow downloading of target file by ready HTTP link. In these cases, user has to download such files manually, and then specify its location on local computer.

The functionality of data files import is described further in details.

4. Currency Rates Import

4.1. Dataflow diagram

As shown in Figure 2, there might be several Web sites (sources) providing the data in various file formats. These files are downloaded automatically (if it’s allowed by providing server) or manually.

User can define a set of conversion templates. Each template represents a set of parameters that describes conversion rules for document source. Data conversion engine performs data conversion accordingly to these rules. There are additional options / parameters available from user interface dialogs. Finally, Dynamics AX (Axapta) tables are filled with converted exchange rates values.

Figure 2: Dataflow diagram

4.2. Currency rates source representation

In order to import currency information there are three values needed: currency code, date, and currency rate value. Usually, Web sites provide such information in two different views that can be called “Flat table” and “Cross table”.

Cross table data representation is provided by e.g. DNB service statistiek.dnb.nl. Each column here contains a set of rates for appropriate day, and currency codes are located by rows.

Flat table data representation view is a general case. In some cases, column “Date” or “Currency” is skipped. In such a way, there is information for fixed date or currency. Those cases can be processed also by specifying fixed date or currency value in UI parameters.

User is to select required view format, then assign column locations from user interface.

4.3. Currency codes dictionary

There is a standard currency code abbreviation that defines the name of currency in three letters, e.g. EUR, USD, GBP, etc. Most of internet services provide correct currency codes in their tables, but not all. Currency codes dictionary is implemented to resolve such cases. This dictionary contains different spelling variants of currency codes.

4.4. Conversion formats

There are various table-oriented data formats. The most popular data formats used on currency sites are:

  • TXT/CSV file format. It is normal text file with comma/tab separated columns.
  • XML file format. Extensible Markup Language (XML) is a markup language that provides a format for describing data. This facilitates more precise declarations of content and more meaningful search results across multiple platforms. In addition, XML enables the separation of presentation from data.
  • XLS document format. This format is used by Microsoft Excel application.

It is enough to support mentioned formats for most cases. Additional conversion modules can be implemented as well.

4.5. Conversion templates

Conversion template is predefined set of options that describes a data format structure for separate currency provider. There are such options as data file format (TXT, XLS, etc.), source table format (flat/cross-table), location of data rows, data region coordinates, conversion options for different data types, import preferences, etc. This template can be stored in disk file or database for future usage. Using of prepared conversion templates efficiently simplifies data conversion procedure.

5. User Interface

User interface is described for Currency Rates Import module preliminary.

Currency codes form is used for currency dictionary setup. There can be some abbreviations (synonyms) for each currency entry in Dynamics AX (Axapta). All of them are considered during import procedure.

Import template setup dialogs are intended to specify layout properties. There are two setup property pages.

Considering the first page, there is a possibility to select source table region for import. User is to specify left and top corners of source data area as well as table dimensions (width and height). Preview window displays selected region in red color. There is button “Detect” that allows detecting the region bounds in automatic mode.

At the second page, user is to select column positions for currency code, date, and rate values. It is possible to specify fixed value for some columns. For example, there is no “Date” column in the source document, so this value is to be selected in dialog option. “Save” button allows storing of predefined template for future usage. “Load” button is intended for stored template selection. “Base currency” option is available as well.

6. Conclusion

Currency Online Update functionality extends standard Dynamics AX (Axapta) features. According to the described concept, Dynamics AX (Axapta) users get an easy opportunity to keep currency rates information up-to-date. Currency Online Update module efficiently saves user’s time for maintenance of such information.