Working with Multiple Currencies
On this page you will find a guide to working with multiple currencies inside your Valsight model.
This is also explained in the Training Model T05.
Getting started
First of all, you need to chose a base currency. We will use EUR (€) in our example.
The next step is to create a new dimensions. The dimension (e.g. Currency) should contain all the currencies, so all foreign currencies and your base currency. Remember to add a "Country" dimension containing the different countries.
Afterwards, we'll create our currency base data. To do so, you can go ahead and create an excel file containing 2 worksheets: One for the exchange rates (e.g. Exchange Rate) and one to assign countries to the different currencies (e.g. Country).
The "Exchange Rate" worksheet should contain the exchange rates towards your base currency.
| Currency | OutputCurreny | Exchange |
|---|
The "Country" worksheet should contain the countries on the left followed by the currency in the middle. On the right there should be a value column (Value = 1), so that Valsight can properly load in the data from this worksheet.
| Country | Currency | Value |
|---|
Now we need to load this base data into Valsight. Therefore we create a new data source (e.g. Currencies) and upload our excel file.
Modelling different currencies
Now that we imported all the data into Valsight, we can start implementing it into our model.
To do so, we first need to create 3 nodes which are linked to our base data.
The formulas in your specific model may vary due to different naming
Because we want to simulate different currencies, we can not simply always select EUR as our unit. To switch bitween the different currencies, we need to create a new Unit (e.g. diverseCurrencies (divCncy)).
For more information see Configure Units.
| Node | Formula | Use |
|---|---|---|
| Countries-Currencies | DATA("Currencies", "Country", "Value") | Link the currencies to the countries |
| FX-Base | DATA("Currencies", "ExchangeRate", "Exchange Rate") | Import exchange rate for base currency |
| FX-Simulated | 1/DATA("Currencies", "ExchangeRate", "Exchange Rate") | Import exchange rate |
For the 'Countries-Currencies' node, we chose Quantity ("Qty") as a unit. With help of the "Combined Unit" function, we are able to define the desired units for each node, also shown in the last picture below.
With these nodes we can create a node (non-simulated) containing all exchange rates to our base currency, containing the "Currency" and "Country" dimension.
| Node | Formula | Dimensions |
|---|---|---|
| Rates-Real | 'Countries-Currencies' * 'FX-Base' | Currency, Country, |
Let's assume we upload our base data (e.g. revenue) with the revenue in our base currency €. The node is named BaseData and its dimension is country.
| Node | Formula | Dimensions |
|---|---|---|
| BaseData | DATA(DataSourceName, TableName, MeasureName [, Unit ]) | Country |
| Country | Totals |
|---|---|
| DEU | 239.00 |
| GBR | 349.00 |
To obtain the corresponding values in the currency of the specified country we do as shown below,
| Node | Formula | Dimensions |
|---|---|---|
| BaseData-Foreign | 'BaseData'*'Rates-Real' | Currency, Country |
This way, only the country based currencies remain.
| Country | Currency | Totals |
|---|---|---|
| DEU | EUR | 239.00 |
| GBR | GBP | 310.61 |
Similarly, if the revenue is available in the foreign currancy, we can use the same approach to transform it into our base currency.
To recieve the revenue in the unit EUR we use the DROPLEVEL command.
| Node | Formula | Dimensions |
|---|---|---|
| BaseData-Simulated | DROPLEVEL('BaseData-Foreign'*'FX-Simulated',"Currency") | Currency, Country |
| Country | Totals |
|---|---|
| DEU | 239.00 |
| GBR | 349.00 |
This way, you can use the currency logic either to transfer data in your base currency into different local currencies, or calculate the values of foreign currencies in your own defined base currency.
Below you can see an example of a model for the discussed scenario.
