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.
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.
Currency Update Server represents a server part of described system. This unit can be implemented via two different manners:
Current version of Currency Web Server application is implemented using ASP approach. Web Server hosts the database served by MS-SQL Server 2000.
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.
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.
Two technologies are considered that provide different approaches to gather exchange rate information. These are “Using Web Services” and “Importing data files”.
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:
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.
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:
The functionality of data files import is described further in details.
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.
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.
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.
There are various table-oriented data formats. The most popular data formats used on currency sites are:
It is enough to support mentioned formats for most cases. Additional conversion modules can be implemented as well.
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.
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.
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.