World Currency Rates — Curating & Publishing Dashboards

The purpose of this note is to discuss how publically available data can be easily processed to get us a deeper understanding of the underline story that otherwise is impossible for us to perceive by looking at its raw version. As an example, let’s use the currency data (rates vs the US dollar) for building up this analysis.

Downloading Data

This data is available on the IMF website. One can easily download the exchange rates by selecting the possible options laid out here, as shown in the picture below.

Figure 1 — Screenshot of the IMF Exchange Rate Download Page

Curating Data

After having downloaded this data, one needs to curate it for further processing. The sample picture of the raw data is as below.

Figure 2 — Screenshot of Raw Data

Now, do you notice the cells anchored by red boxes? These cells have data that have to either be removed (like rows — 1 & 2) or need to be prefilled with preceding data for the “Average function” (that we will use later) to work properly. Why do we need the AVG function? The reason — Tablaue needs some form of aggregation for it to work- the visualization tool we will use here. And the only form of “Aggregation” that will end up working here is “AVG”, as “SUM” will end up adding all the adjacent currency rates when we decide to zoom out on the data axis using the “month”, “quarter” and “year” as aggregators. Also, not prefilling the data in the empty cells with those above will result in the “AVG function” throwing up incorrect results, as while doing “averages” the Tablaue will run it by the lowest granularity which in this dataset is set at the level of “Day”.

Problem in Curation

But filling up data with those just above it will lead us to some serious problems. Why? As the blank cells might be a result of “unavailable data” and it might run into multiple rows — throwing out the overall analysis into disarray, thereby resulting in wrong conclusions. Fortunately, we can deal with this problem easily. How? The value of the exchange rate will stay constant for the days with prefilled data, and these can be easily filtered at the time of calculating %Change, which will result in Zero — can be easily filtered from the rest of the data.

Process of Curation

For prefilling, the empty cells with the values above can be done using both excel and python. I find python easier and more convenient. The code used for that purpose is listed below.

file_to_process = "IMF_Currency_Rates.csv"

with open(file_to_process,"r") as file:
    df = pd.read_csv(file)

df =df.fillna(method='ffill')
df.to_csv("currencyratesimf.csv")

The snapshot of the curated file is embedded below in the following picture.

Figure 3 — Curated file for visualization

Caution — For the visualization tool to work seamlessly, make sure you change the type of column B to “date type” — either manually in the CSV file or using the processing code. Else Tablaue will simply ignore these rows for which the data field is wrongly typed in the input file.

Visualization of Data

The picture below gives a snapshot of the Tablaue worksheet.

Figure 4 — Snapshot of Tablaue worksheet

Notice that the key ingredients in this worksheet are some calculations — emanating from the raw data. Note, before you start the process, please pivot this raw data using Tablaue’s pivot feature, which will allow you to aggregate all countries under a single column and the exchange rates under the other.

Calculating %Change

Now in order to calculate the %Change in exchange rates we need to Lookup for the rates with which we plan to compare the current rates. This one can do easily by using Tablaue’s inbuilt LOOKUP Function. The code is embedded in the following picture.

Figure 5 — Lookup Rate Formula

The %Change can be calculated easily using the above, described under. Note this formula has a field called “Time Units in View”. This is a user-defined parameter that provides the flexibility to alter the width of the viewing window.

Figure 6 — Formula for %Change

Next, we need a “DateFilter” for us to be able to remove all the unnecessary clutter from the viewing window as was described in my earlier note — “Tableau — Managing the View of the Window”

Sorting Window Data

With all these above calculations we are almost done, except for one. What? We need the Dashboard to sort the data based on the value of the %Change. Now, for the sorting to work, we need to provide Tablaue with a specific column, and not many. Hence, we have to choose a specific column (out of the many defined by the parameter — “Time Units in View”. For this exercise, I have decided to pick up the last column. The formula for enabling that is as below.

Figure 7 — Latest % Change Calculations

Now using the RANKUNIQUE function of Tablaue we can list these % changes in the order of our choice — “asc” or “desc”. The Rank unique function is defined as under.

Figure 8 —The Rank Function for Sorting

After having written all the calculations we are all set. We just have to plug these calculations into the worksheet as has been shown in Figure 4. The snapshot of the final Dashboard in action is under.

Figure 9 — Final Dashboard

Interested readers that experience the real Dashboard in action here — https://paragkar.com/world-currency-rates-trends-with-respect-to-usd/

Many thanks for reading. Hope you found this analysis useful. Regards