The FX Downloader is a component of Apliqo FPM that imports daily foreign exchange rates from an online provider. Two online provide support by the FX Downloader; ECB Exchange Rates and Alpha Vantage. Both sources provide daily foreign exchange rates.

The ECB Exchange Rates only provide the exchange rates from the Euro Dollar against other currencies. To use ECB Exchange Rates the Lead Currency in Apliqo FPM must be EUR - Euro Dollar in Apliqo FPM.

Alpha Vantage is more flexible and offers exchange rates for a broader array of currency pairs. To access Alpha Vantage's data source, you need to acquire a Rapid API access token, which is free. The Basic tier service allows access to foreign exchange rates with certain restrictions, such as a limit of 500 API queries per month and a cap of 50 API calls per minute. Information regarding registration and subscription to Alpha Vantage can be found at https://docs.rapidapi.com/docs/api-pricing. The FX Downloader can be set up to prevent exhausting of API limits in terms of the number of API calls per minute, particularly if your plan has stricter restrictions than the default ones.

Data Flows

The FX Downloader fetches data from the chosen API data source and stages it into the FIN Currency xR Daily cube. Once in the FIN Currency xR Daily cube, the data is consolidated into a monthly granularity and can be transferred into the FIN Currency xR cube. Data is moved from the FIN Currency xR Daily cube to the FIN Currency xR cube using the process }APQ.C3.FIN.Cub.CurrencyXR.Update.From.CurrencyXRDaily.

To download the API data into the FIN Currency xR Daily cube, you can use a wrapper process }APQ.C3.FIN.Cub.CurrencyXRDaily.LoadFromAPI. The process specifies the date range of the exchange rates (required), the type of API data source (required) and list of currency pairs (optional). The wrapper process will allow you to provide necessary configuration steps to setup access to TM1 for the application and for the Rapid API.

Use Cases

UC1: Setup connection information for the application to access TM1.

Prerequisites

• Application has been installed into <FPM install location>\Batch\fxdownload folder.

• Setting Location: Scripts in the }APQ Settings cube points to <FPM install location>\Batch folder.

Setup

1. Locate <FPM install location>\Batch\fxdownload\Config\ folder and open config.json file in a text editor.

2. Locate tm1_instances object inside the config.json file. Out of the box version of the file contains a default connection object called “default”.

3. Specify connection information to your Apliqo FPM instance inside the default object data fields. You may specify following details, only basic are listed below, all are compatible with TM1Py TM1Service class. Do not modified any other values outside the default connection object data field. Save the config.json file after changing the connection information.

"default": {
  "address": "localhost", 
  "port": "8664",
  "ssl": true,
  "user": "admin",
  "keychain": "fpm.dev.login"
}

UC2: Setup credentials for the FX Downloader to access TM1.

Prerequisites

• You will need to provide a TM1 user account with ADMIN privileges in Apliqo FPM / TM1. The user must not be a read only user and the corresponding TM1 account must be enabled. See the }ClientProperties cube to check the ReadOnlyUser=0 and IsDisabled=0.

• The Windows Service running the Apliqo FPM model is using a Windows AD account. Check the Log On As account in Windows Services Console. A Windows AD account differs from TM1 account. Should you ever modify the Log On As user account, you will need to setup the credentials for the FX Downloader again.

Setup

To setup the credentials in an out of the box installation, you may use GUI in Daily Rates app.

Or run wrapper process }APQ.C3.FIN.Cub.CurrencyXRDaily.LoadFromAPI with following parameters:
pTransferMode: storecredentials
pUserName: TM1 account name to be used to connect to Apliqo FPM. Do not use a CAMID for the 'pUserName' parameter.
pPassword: TM1 account password.

Notes

• The credentials will be stored by the FX Download inside a system keychain on the server you are running the FX Downloader on. This guarantees the security of the credentials stored. The keychain is password protected and only available to the Windows AD account that is running the Apliqo FPM service. The FX Download does not store the sensitive information outside the keychain and doesn’t output the information to a display or any system logs.

• Supply a valid connection object name from config.json in parameter pConnection should you define your own connection information other than default.

• Connection test with the provided credentials will be attempted before populating the credential values into a system keychain. Should the connection test fail, the credentials will not be stored in the keychain.

• Success or failure of the credentials store action will be communicated by the Apliqo FPM messaging framework. Messages are output into }APQ Process Response Message cube and to the TM1 System Log (tm1server.log) and the FX Downloader log file. If the connection test succeeds the FX Downloader will set Credentials setup option in }APQ Settings cube to 'Y'.

UC3: Setup Rapid API access key for the application to use Alpha Vantage

Prerequisites

• You have registered with Rapid API and have obtained and tested an API access key. More information about API keys can be found here: https://docs.rapidapi.com/docs/keys.

• The TM1 service running Apliqo FPM is run under Windows AD account. Check the Log On As account in the Windows Services Console. A Window AD account differs from TM1 account. Should you ever modify the Windows AD account, you will need to setup the Rapid API access key for the FX Downloader again.

Setup

To setup Rapid API access key, you may use GUI in Daily Rates page.

Or run the process }APQ.C3.FIN.Cub.CurrencyXRDaily.LoadFromAPI with following parameters:
pTransferMode: storeapikey.
pRapidAPIKey: Rapid API key obtained after registering with Rapid API provider.

Notes

• The Rapid API access key is considered sensitive information and will be stored by the FX Downloader inside a system keychain.

• Supply a valid connection object name from config.json in parameter pConnection should you define your own connection information other than default.

• Connection test with provided credentials will be attempted before populating the credential values into a system keychain. Should the connection test fail, the credentials will not be stored!

• Success or failure of the credentials store action will be communicated by the Apliqo FPM messaging framework. Messages are output into }APQ Process Response Message cube and in the TM1 System Log (tm1server.log) and the application log file. If the connection test succeeds the FX Downloader will set Credentials setup option in }APQ Settings cube to Y.

UC4: Download ECB rates to the Apliqo FPM model

Prerequisites

• You have successfully setup connection information and credentials to your Apliqo FPM model (UC1 and UC2).

• You have set up the Currency module of FPM model. Setup Currencies in Apliqo FPM

• The Lead Currency parameter is set in the }APQ Settings cube. The FIN Currency dimension contains the currencies required by Apliqo FPM, see the All Leaf Currencies excl. Local rollup.

Setup

You may use GUI in Daily Rates app to download ECB FX rates.

Or initiate the download of ECB rates by running the process }APQ.C3.FIN.Cub.CurrencyXRDaily.LoadFromAPI with following parameters:
pTransferMode: quotes
pType: ecb
pStartDate: Start date of rates download (format YYYY-MM-DD).
pEndDate: End date of rates download (format YYYY-MM-DD).
pSymbols: You may optionally specify a list of currency pairs to be downloaded by specifying a parameter value elements:delimiter:delimited list of currency pairs. For example: elements:++:EURUSD++EURCHF++EURJPY. If currency pairs list is not supplied in pSymbols The application will use currency setup information from }APQ Settings cube and from FIN Currency dimension to obtain a list of relevant currency pairs.

Notes

• The FX Downloader will run the process }APQ.C3.FIN.Cub.CurrencyXRDaily.FillBlanks to clear the FIN Currency xR Daily cube in specified date range. Please keep in mind that all currency pairs data will be cleared regardless of pSymbols parameter settings.

• Supply a valid connection object name from config.json in parameter pConnection should you define your own connection information other than default.

• After the download of data is successfully finished, the FX Downloader will run the process, }APQ.C3.FIN.Cub.CurrencyXRDaily.FillBlanks to make sure currency rates are populated into non-trading days by repeating last non-zero currency rate from preceding trading day. Take extra care to specify a start date range (pStartDate) that is an actual trading day to avoid repeating invalid currency rates.

• Success or failure of the credentials store action will be communicated by the Apliqo FPM messaging framework. Messages are output into the }APQ Process Response Message cube, in the TM1 System Log (tm1server.log) and the FX Downloader log file.

UC5: Download Alpha Vantage rates to the Apliqo FPM model

Prerequisites

• You have successfully setup the connection and credentials to your Apliqo FPM model (UC1 and UC2).

• You have set up the Currency module of FPM model. Setup Currencies in Apliqo FPM

• The Lead Currency parameter is set in the }APQ Settings cube. The FIN Currency dimension contains the currencies required by Apliqo FPM, see the All Leaf Currencies excl. Local rollup.

Setup

You may use GUI in Daily Rates app to download Alpha Vantage FX currency rates, or you may initiate the download of Alpha Vantage rates by running the process }APQ.C3.FIN.Cub.CurrencyXRDaily.LoadFromAPI with following parameters:
pTransferMode: quotes
pType: alpha_vantage
pStartDate: Start date of rates download (format YYYY-MM-DD).
pEndDate: End date of rates download (format YYYY-MM-DD).
pSymbols: You may optionally specify a list of currency pairs to be downloaded by specifying a parameter value elements:delimiter:delimited list of currency pairs. For example: elements:++:EURUSD++EURCHF++EURJPY. If currency pairs list is not supplied in pSymbols parameter, the application will use currency setup information from the }APQ Settings cube and from the FIN Currency dimension to obtain a list of relevant currency pairs.

Notes

• The FX Downloader will run the process, }APQ.C3.FIN.Cub.CurrencyXRDaily.FillBlanks that will clear the FIN Currency xR Daily cube for a specified date range. All currency pairs will be cleared regardless of pSymbols parameter settings.

• Supply a valid connection object name from config.json in parameter pConnection should you define your own connection information other than 'default'.

• After the download of data is successfully finished, the FX Downloader will run the process, }APQ.C3.FIN.Cub.CurrencyXRDaily.FillBlanks to make sure currency rates are populated into non-trading days by repeating last non-zero currency rate from preceding trading day. Take extra care to specify a start date range (pStartDate) that is an actual trading day to avoid repeating invalid currency rates.

• Success or failure of the credentials store action will be communicated by the Apliqo FPM messaging framework. Messages are output into the }APQ Process Response Message cube, in the TM1 System Log (tm1server.log) and the FX Downloader log file.

UC6: Push downloaded FX rates to the FPM model

Prerequisites

• You have successfully setup the connection and credentials to your Apliqo FPM model (UC1 and UC2).

• You have downloaded currency rates from ECB or Alpha Vantage for the required date range (UC4 or UC5).

Setup

You may use GUI in Administration > Currency > Daily Conversion page to push daily currency rates from the FIN Currency xR Daily cube to the FIN Currency xR cube.

Or, you may run the process }APQ.C3.FIN.Cub.CurrencyXR.Update.From.CurrencyXRDaily with following parameters:
pCurrencyVersion: Target currency version to load the data into (default is ACT).
pCurrency: You may supply a list of delimited target currencies to be updated. The source currency is the Lead Currency set in }APQ Settings cube. Target currencies are all currencies under the Reporting Currency rollup in FIN Currency dimension.
pYear: The Financial Year for the required date range.
pMonthFrom: Starting financial month corresponding to required date range.
pMonthTo: Ending financial month corresponding to required date range.

Notes

• Success or failure of the data push will be communicated by the Apliqo FPM messaging framework. Messages are output into the }APQ Process Response Message cube, in the TM1 System Log (tm1server.log).

Troubleshooting

• The FX Downloader log can be found in .\<FPM install location>\Batch\fxdownload\log\application.log for basic troubleshooting. The log is available in highest verbosity (debug).

• You may set pLogLevel parameter value of process }APQ.C3.FIN.Cub.CurrencyXRDaily.LoadFromAPI to “DEBUG” to have debug level logs routed into TM1 message cube.