Learn About Currency Conversion Tables

Currency conversion tables let you take source data from different currencies in a model and convert it, either into a single currency for aggregation or into a set of currencies from a dimension.

Currency conversion tables are defined independently of models; you can then apply a selected table to any model you create. Currency tables are listed on a separate Currency Conversion page, where you can add, copy, and delete tables as required.

Note

Tenants based on the Cloud Foundry environment benefit from the latest user experience improvements brought to the currency rate tables, with a redesigned toolbar and an improved Add Missing Rates dialog with a search functionality. The user experience on tenants based on the Neo environment might differ.

The same currency table can apply to both models with measures and classic account models. For background information about currency conversion features in each model type, see Work with Currencies in a Model with Measures and Work with Currencies in a Classic Account Model.

Exchange Rates

A currency conversion table defines exchange rates for all currencies that are in use. The currency conversion is calculated as a source currency multiplied by an exchange rate to determine the equivalent value in a target currency. Rates tables can be accessed straight from the Modeler, or from the Calculations workspace if your conversion measure is missing rates.

From here, you can maintain the rate table manually. On Cloud Foundry tenants, you’ll find a toolbar that helps you add, copy, or delete rows, undo/redo actions, and open the Add Missing Rates dialog. On Neo tenants, you’ll be able to add missing rates using the dedicated panel on the right.

Each row in a currency conversion table represents a rate, and has the following settings:

Mandatory Settings

Source Currency: The three-letter currency code of the source currency. For example, EUR or USD.
Valid From: This is the start date when the rate becomes effective. For example, if the latest rate is set for January 1, 2020, the 2020 data will use this rate, but data for 2019 would use older rates. If there are no rates with Valid From dates that precede the data, it won't be converted.
Target Currency: The three-letter currency code of the target currency.
Caution
For each row of the table, make sure that source and target currencies are different to avoid generating errors within the table.
Exchange Rate: The source value is multiplied by this rate to convert it into the target currency.

Note
Currency conversions can be inverted. For example, if you have a rate for converting USD to CAD, the inverse rate can be used to convert CAD to USD in case an explicit rate doesn't exist. However, rates won't be automatically combined to perform indirect conversion across three or more currencies (for example, first CAD to EUR, and then EUR to USD).
Rate Type: This setting corresponds to the Rate Type property of an account or measure. Use it to set different conversion rates for different types of data.

Caution
To convert data from accounts or measures, the rate type must match this setting in one of the valid rates. A blank rate type in the currency conversion table won't match either closing or average rate types in your accounts or measures.

When you enable currency conversion in a model, a Rate Type property is added to any accounts and measures that exist in your model.

Accounts: For any accounts with an Account Type set, you’ll have to set a corresponding rate type before you can save the model. INC and EXP accounts use the Average rate, that is, the average conversion rate over a time period. That's because they include transactions that take place over the course of the period. AST and LEQ accounts represent what your organization owns and what it owes at the end of the period. They use the Closing rate, that is, the exchange rate at the end of the period.

For accounts without an account type, the setting is optional.

Measures: You can set a rate type for any base measures with currency data. For a model with measures, the structure priority determines whether the rate type for the account or for the source measure is used during currency conversions. The other structure's rate types have no effect. For details, see Set Structure Priority and Create Custom Solve Order.

If you are using an older model, which was created before Rate Types were introduced in the Modeler, you'll first need to migrate the model before you enable currency conversion. In this case, an additional Migrate option is available in the Model Preferences. Select this option to start the automatic migration procedure.

Optional Settings

You can use optional settings to maintain specific rates for different categories and versions of data. These settings can help you compare scenarios with different exchange rate forecasts, for example.

Category: If you want to create separate rates for one or more categories (such as actuals, budget, forecast, and so on), select a category for the rates. Leave this setting blank to create a generic conversion rate. Generic rates will apply if there aren't any rates for the specific category of the data.
Rate Version: You can create a rate for specific versions and scenarios using rate versions. Set the Category to Specific and then type a name for the rate version. You can assign public versions to a rate version in the Modeler, overriding the rate for the category. Users can also apply these rates when creating their own currency conversions, or versions in a classic account model.
Note
Specific versions and calculations will only use rates from that rate version. Generic rates without a category are not applied to the Specific category.

Creating a Currency Conversion Table

You can create a currency conversion table by copying and pasting data from a file, or by importing data from a data source. For details on how to import and schedule currency conversion data from a data source, see Import Currency Conversion Data.

To create a currency conversion table by copying and pasting:

From the Modeler start page, go to the Currency Conversions tab and select Currency Conversion Table.
Provide a name for your table, and select Create.
Type currency conversion data into the table, or copy and paste data from a file such as a spreadsheet.
Caution
For each row of the table, make sure that source and target currencies are different to avoid generating errors within the table.
Save your new table.