#013 | Extract, Transform & Load FX rates: User Acceptance Testing

Overview

I built a basic Extract, Transform & Load ("ETL") process to store historical foreign currency rate ("FX rate") data for multiple Target Currencies relative to one Base Currency, the Malaysian Ringgit.

To recap, an FX rate tells you how much of a Target Currency can be exchanged for one Base Currency.

To test the ETL process, I did User Acceptance Testing ("UAT").

Test Parameters

• Period: Nov 1, 2024 to Dec 31, 2024
• Interval: Daily
• Target Currencies: Singapore Dollar ("SGD") & US Dollar ("US Dollar")
• Base Currency: Malaysian Ringgit ("MYR")

UAT Results: Pre-Fix

The first round of UAT failed, as I made a critical mistake of inputting the wrong currency order in yfinance's download() function. This returned FX rates with the Malaysian Ringgit as the Target Currency (instead of the Base Currency).

For example, instead of "MYRUSD=X" (how many US Dollars one Malaysian Ringgit buys), I input "USDMYR=X" (how many Malaysian Ringgit one US Dollar buys).

This potentially fatal mistake was overlooked during the Build phase, which I covered in the the previous post.

Thankfully doing UAT meant the mistake could be picked up.

Fixing the Code

I fixed the currency order to retrieve the desired FX rate.

Unfortunately, yfinance returned an error saying the "MYRSGD" currency pair/ticker was invalid. The "SGDMYR" pair, however, is valid.

It appears yfinance does not support Cross Currency Triangulation on every currency permutation. This means the MVP has to handle this transformation.

I reverted back to the original currency order input, but added a line to calculate the inverse of the returned FX rate. This transforms the Malaysian Ringgit into a Base Currency (from a Target Currency).

UAT Results: Post-Fix

The UAT was successful after applying a fix, and the data is now correctly transformed and loaded into Xano.

Before fixing the error:

After fixing the error:

--Ends