The world is getting smaller, with businesses and organizations working across national borders more than ever before. With this globalization comes data challenges that can be solved through a number of data preparation strategies. One typical use case for multinational organizations is determining currency conversion rates in order to ensure the financial data being analyzed is consistent and being accurately compared/aggregated.
Converting rates such as currency seems like such a simple task. Multiply the currency rate by the original value, and you have the currency you require. Easy, right? Not so fast. Depending on the level of accuracy you want, using a single, constant exchange rate might not be sufficient. Currency exchange rates fluctuate throughout the day, every day.
Which rate you use may make a significant difference even in smaller transactions; added up over time, the small differences can accrue, resulting in over- or understated results or profits. The frequency of the conversion rates (hourly, daily, weekly, etc.) is another important decision to make. Greater frequency assists with accuracy but makes the conversion rate tables more difficult to apply, especially to different time periods.
Applying conversion rates consistently is also key, especially when you are working across a number of different business units. Let’s look at how to do this within Prep.
Using the 2020: Week 6 Preppin’ Data challenge, let’s work through a currency conversion from British Pounds (GBP) to US Dollars (USD).
First, you need to import the input data (Figure 38-1 and Figure 38-2).
These two data sets are not initially ready for conversion, as you don’t have a field to join them on. For these data sets, you need to “roll up” the individual dates at the day level to the week level to match the Date column in the conversion rate table in Figure 38-1. This is a judgment call; ultimately, you need to decide with what frequency you want to do the conversion, then find the rate within that period and ensure the correct conversion rate is held for that level.
This example requires you to find the best- and worst-case conversion scenario. In other words, the data preparation task is to find the minimum and maximum value of US dollars that would be exchanged for one British pound per week. Other scenarios to consider include:
Finding the average value for a time period
Finding the “opening” or “closing” value as an indicator of the likely value of an exchange in that time period
The context for the question being posed is what will ultimately determine which technique you need to deploy.
Using a Join step in Prep Builder, you can now join together the two different data sets (Figure 38-3). If everything is in one table already in your use case, then you can skip this step.
Now that everything is in one table, you can apply the conversion rate to the value you are converting (Figure 38-4).
This applies the specified conversion rate to the value, making analysis for the end users a lot easier than if they had to build separate calculations themselves in each Desktop workbook.
Creating a solution for a one-off preparation flow is fine, but if the flow will be reused multiple times and/or the data updates, then you’ll need longer-term strategies to ensure accuracy and robustness.
One of the most challenging aspects of handling conversion rates like currency is knowing the frequency with which the business will want to apply those rates. If a single value per hour, day, week, month, quarter, and year is held as a reference table, this is very useful—but that table is going to get pretty big, pretty quickly.
By storing only the data you are likely to need and ensuring the rules are clear for determining the correct rate to use, you will avoid spending a lot of time fixing mismatched values further down the reporting pipeline.
As just noted, reference tables can get big very quickly. Building robust history tables, with the latest values incrementally updating over time, is a worthwhile effort. Ensuring that the history tables are kept somewhere central where every “data prepper” can access them is very important. How far back the users’ questions go will determine how long you’ll need to maintain the history tables.
With more organizations trading across the world, it’s especially important to be consistent in your approach to converting currencies. Using Prep Builder to apply the techniques covered in this chapter makes converting values very easy to do. Setting up methods for storing the rates used and keeping those methods consistent will help you more accurately report everything from earnings to costs.
