Skip to main content

This article will present an example of an FX Rate management set up built in Pigment in a matter of minutes by leveraging the Google Sheet Connector and Pigment’s import capabilities.

 

Before diving into the explanation, what does FX Rate management set up refers to?

 

It’s used here to describe the collection of the historical exchange rates of multiple currencies used during your planning process to be able to convert amounts to your unique reporting currency. For example, your Budget Owners are planning their employees salaries in their local currency. In order to see a consolidated amount you need to convert all the salaries to your official reporting currency. Before choosing to use the method described below, you should validate the process internally!

 

 

Step 1: Create a Google Sheet that will contain Daily FX Rate

 

In the Google Sheet you created you can define your reporting currency, meaning the currency you will want to convert to in your Pigment applications. In this example I used the “EUR” currency code (see the ISO 4217 code list).

The function used is GOOGLEFINANCE() with the reporting currency as parameter and any other currency I want to add to my FX Rate logic (here: USD, CAD, SGD and MAD). The goal is to retrieve the daily rate for the last 60 days. To load the historical FX Rate, we will change this parameter to 2 years or more depending on the need and then switch it back to 60 days.

Each time I will open this Google Sheet, the values will be updated to display the last 60 days daily FX Rate. We chose to retrieve the Daily FX Rate instead of the Weekly because we can easily link a day to a particular Month in Pigment to calculate an average FX Rate per Month, which isn’t the case with Weeks.

This is how your Google Sheet should look like:

Xn1yh0VITLGnoI4jYhzWNgZPbpdjvRIhLv0it7Uj58Rt1qt1UsxsUhyrgTUBeSFCZ-ymJsRxMWcsNhICIzVWuTcz9rsC5lSLZiSbznC5j0e5zhP26h7wkkOgEHUn-IAhKwcTBTYeUPRMrjXP80_L3Ns

 

Step 2: Connect the Google Sheet to Pigment using the native Connector

 

Once the Google Sheet is ready, we can connect it to Pigment through the native Connector. If you haven’t already installed the connector, you can find detailed instructions on how to set it up here

VgCk39G7HIDOnfj_n9OWkoILS5tkHm5a3p6vAL69oXyicGT3lt8s4XbIxo5LJTbIwwNAc70yObKlP5IjWEyRf-s87YYdxFYDhv2PqVccjpUJsD1pH0zfD1W_dKUHNyIwP3Hl4kBKDCE7gUHdYqVZfk0

We will use the “Push data from Google Sheets Into Pigment” button. If it’s your first time pushing data from Google Sheet to Pigment, you will find detailed instructions here.

 

Step 3: Generate the import ID

 

Follow the instructions in the article above to fill the Pigment tab automatically created. The only missing information will be the “Import ID”. To find this information, you will need to first run the import manually with a CSV file.

This article also explains how to generate the import ID.

Start by filling all the fields except the import ID like indicated below:

2YSpKR0aGi2mJ-X5bi6Q6bnoFOQ-tbTLcPb1pETmtGiOZqnbU3dj9iw0B_OREudc1WR_QquoO6b2WTnXwbcCbDnmx9n7BCfGoMDprRgQ_wTeGTj1Pojy6QosYkc3NFz2ch4TVoN96zdjwCfM6f7p7LE

You can now use the “Download export data as CSV” button on the side bar to generate a CSV file that you will import manually in Pigment to generate the import ID needed to automate the setup.

XYGOxi1LcQEqSvJ5u5CtbunSFEnxwoNpqiQgGzKFXiBLMumuIH_k7pEKMojdGg1sOTR3Xz3Wd-tXCzOwjhIBz_-TRN8LxTihmU0rBeKDQVvsaPSl-WS4tJbFKPI0712VXhqWZQxdTSMVvKp2bf4HqAE

Your CSV file should look like this:

KWWFqnTNV1EGPVQM5tjIvMPPKr0wYzcAc-86uarsVEHy-Yxj3kJwRFrJy9PN8pxeQrOYcsV3DQ6BhyOD-AE63IuvOH8mBTMGg3vWe2p3X0DtbLnhp9VcT-QvTBZzGkbu1TrUP9pQFvQ5LI4m_Pm_UP8

Important note: You need to have a calendar with the “Day” dimension as the data generated on the Google Sheet is by day. We will use the “Day” as our scoping parameter to reload the data and keep all historical exchange rate while adding new data. See in this article how to add the dimension Day in your calendar

VVF6aJs5C2_K8mUxlIW3VVOGXHObjM5kv35qbwaKnYQEfMl1TYigLGyI4yH8Znl9aonbahNPnvMIud7Q5uapx_XfrISWoFP_zh_ar4fEFUQItccrLdVXpL2WNwyKyzi9cNcTdbptVvBNMRwEiqWQVAE

Import the CSV file that has been previously generated by using the “Start from an import” option in Pigment in a new Transaction List.

FMgzeeB1tC5zIf1S5qvHG4m-dkOGei76bEFGl1_7Pei1WQI6dmcANnR0hJDQBf_bhUs9EmKewSSU3SGayifyBKZ8vfDyCn-ONKv2T81F9xhMwFhXeZxNTcMRn8yiUpOVwJNuxm1jQ-sd-0gKPnpJDNU

Here is how your import configuration should look like:

Dfq_urEArYlj343BT5Nm9c8s9xk5Hw7xXAHkCdWiPGbxdX2DiTyZAFmHYeLCaEc8EfbnF1g2NlLUNfjISd7VhfPODEDootrqp6CI-0FOd3D24ZUH0492izVixnVrYLyzpB12sb49MOrLlezEzl4_AFY

We will only keep the first column with the “Day” information and the ones containing the FX Rate for each currency. The “Day” will be our unique property and also the dimension we will use to scope the import. That way we will keep the historical data and update the transaction list with the new FX rate. 

-_TukVhdLSLQWHnNrJtzL47z6Q8apV5zZcuXIaZsEuP-e5KZJzyuOSHWPUgNaViqqQ68UzqZeqDzh-NJIJyHmUcrK2mXekGKtMCXDt1WMemFzSYK_1jBzwtfoxjNL3zvBdFix10A7zXwvZSzOCGcdp8

7EgCSpF2UfWAzPA65YGfmU4RI1dYgp3NsC8fsdHq5G3b9-AbjAbLadmbmlBM0konB8ijscns0EomTPMyqOv0I3LaCxN7wnAbrCU4c5IhKOMVZmcrWZDjERTuawVTEssfzvZOTSyCRDZflf9JZ0b8N7I

Don’t forget to save the import configuration once the import has been completed; we need this to be able to use the import ID that will be generated when saving.

-1Ueo5wDj6xdwXLJ5Ia8MdKHPJIWUJzghg6wihemnwGYadJRo-GTEBaVLBRiL2q6diVmT65kxxXsnXsdzY0pzs9OG1JSpFE1KXSK8d3ZEmzLimR7PoM7pbtodIavlGot7DpjzMpKwbi_B4IdwgpWOh0

When the manual import has been completed successfully, you should have a transaction list like shown below.

uk16Uh_XQoW69On2_ZTdrxHmAPQqJ84RlTr2tQMCzJVZmQK6ciThLBIB43_uSnBlTn1S1aQ7j8l9CReMAzbGytxdr1sWR5avtduEhnWSj7qJFw7J9pTG6UG5Q1F42DD79tu2swRjNfPLpKQMOKIbO2Y

You can now copy the import ID from the settings of the Transaction List that was just created and paste it into the “Pigment” tab of the Google Sheet. To run a test, you can delete the entire transaction list you just manually loaded in Pigment and reload it directly from Google Sheet by hitting “Export data to Pigment” on the Pigment sidebar.

If all goes well and you carefully followed the previous steps, you should see a popup indicating the success of the import in your Google Sheet:

WMWPe9K8zrgDwNEJmLhJBkIQgkL6KsMnUU2hdVb1H0nULg6BBCewmPs-rwd5M8O6516r_rjHLA9eQfIiEaHQVIG0dtSWUy2TySrcB6x2ELDt4inWpn9h-cZvnlGUBn2KFavHZ07Jc7vesXmRMg_eLGY

The transaction list in Pigment has been updated with the values from the Google Sheet. To update your Transaction List and send the most up to date FX Rate to Pigment, you will need to open your Google Sheet, open the Pigment Connector panel and use the “Export data to Pigment” button and that’s it!

 

Step 4: Create the FX Rate Metric

 

Now the fun part, to be able to use the data from the Transaction List we just created, we will need to create a Metric that will be aggregating this data. Use the formula Wizard from the Playground to build the formula or build it from scratch.

We will use the different properties of the transaction list to create this FX Metric. If applicable, add the Version dimension to the structure of the Metric to be able to have a different FX Rate depending on the Version.

A7TzoKP2QrRQFNnrzBi2UkTPZ7ZuokuAI6FsDM2ZDF1TDnF5YLoJ_iihkDY3YtO1__gukCIWwdNvkpT3ZqXjWAANzO8BstnGWl8BvxIMX7IHYXQgREw_dhh2-wjji-GOeki9nzopoJ-xWoRlnr0cbwI

If you would like to use the last known FX Rate to forecast the future Month, you can use the previous() function:

KLEg8rjptu97ybGKOgnSji3bfhkOZk6_Kn_nN2LLeTdW4IjHcIiRmho8HHZdFxiVjgdaDZwu6GmYEM6S5nmCjHazaAHkMSOipjZv3aSxvsWan5knPiK6MW22eiCMKxBT1LK0JvXKlFPK47T5ZYslHtY

Once your Metric is created, don’t forget to share it in the library with other applications from the settings of the Metric.

You can now use the FX Rate Metric to convert any amount to EUR in any application with the library activated.

Note: this formula is performing an average value of all the FX rates of the Month. You can replace the aggregation parameter with  “LASTNONBLANK” to use the last value of the Month if it’s the preferred method.

 

Step 5: Build a nice Board in Pigment with instructions to update the FX Rate

 

To finish the job, the last step will be to build a nice board with a button that you can link to the Google Sheet for a quick update. You can also display the last load date for reference and give the possibility for an admin to override the FX Rate on a particular Version (like the FX rate used for the Budget for example).

Here is an example of a Board for FX rate Management:

LlRo1V43qQwygwvCi1u0kkT8lOn6Pu2rLrR6jVMJ5GhhbfoVF052ALpyDsb5U73hBM4dhm4rd4hAuyml0XVtEbA8wsnyDt2aTLHBraCEeLNje-6DvWUVPc7a03pyRph8WApjisrfdUOEbYRPaRxtZcw

If you want to add new currencies in you setup, you will need to follow the steps below:

  1. Change the Google Sheet by adding new FX Rates as columns
  2. After changing the end Range on the Pigment tab, generate a new CSV
  3. Upload this new CSV manually in Pigment and map the additional columns on newly created properties
  4. Save the new import configuration
  5. You are good to go 🙂 !

 

Summary

  1. Create a Google Sheet that will display the historical exchange rates on a given set of currencies using the GOOGLEFINANCE() function.
  2. Push the FX Rate data to Pigment by using the Google Sheet connector (scope the import by using the Day dimension).
  3. Create an FX Rate Metric using the data imported in the Transaction List from the Google Sheet and share it in the Library. 
  4. Create a nice Board with clear instructions and a shortcut to the Google Sheet for future updates.
  5. Use the output FX Rate Metric to convert amount to your reporting currency.
  6. Update the FX Rate Transaction List load at least once per Month.
  7. High five to yourself for reading the entire article!!
Be the first to reply!

Reply