douglascolquitt.com - SQL Blog 3

Importing RBA Exchange Rates

RBA Exchange Rate Historical Data ETL into Azure

The Reserve Bank Of Australia maintains an historical exchange rate data page. I have downloaded Exchange Rates ‐ Daily ‐ 2023 to Current. I will:

Transform the contents using Python
Load the transformed data in Azure

The source file has the Exchange Date on the vertical axis and the Exchange Rate for each currency on the horizontal access:

            F11.1  EXCHANGE RATES ,,,,,,,,,,,,,,,,,,,,,,,
Title,A$1=USD,Trade-weighted Index May 1970 = 100,A$1=CNY,A$1=JPY,A$1=EUR,A$1=KRW,A$1=GBP,A$1=SGD,A$1=INR,A$1=THB,A$1=NZD,A$1=TWD,A$1=MYR,A$1=IDR,A$1=VND,A$1=AED,A$1=PGK,A$1=HKD,A$1=CAD,A$1=ZAR,A$1=CHF,A$1=PHP,A$1=SDR
Description,AUD/USD Exchange Rate; see notes for further detail.,Australian Dollar Trade-weighted Index,AUD/CNY Exchange Rate,AUD/JPY Exchange Rate,AUD/EUR Exchange Rate,AUD/KRW Exchange Rate,AUD/GBP Exchange Rate,AUD/SGD Exchange Rate,AUD/INR Exchange Rate,AUD/THB Exchange Rate,AUD/NZD Exchange Rate,AUD/TWD Exchange Rate,AUD/MYR Exchange Rate,AUD/IDR Exchange Rate,AUD/VND Exchange Rate,AUD/AED Exchange Rate,AUD/PGK Exchange Rate,AUD/HKD Exchange Rate,AUD/CAD Exchange Rate,AUD/ZAR Exchange Rate,AUD/CHF Exchange Rate,AUD/PHP Exchange Rate,AUD/SDR Exchange Rate
Frequency,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily,Daily
Type,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative,Indicative
Units,USD,Index,CNY,JPY,EUR,KRW,GBP,SGD,INR,THB,NZD,TWD,MYR,IDR,VND,AED,PGK,HKD,CAD,ZAR,CHF,PHP,SDR
,,,,,,,,,,,,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,
Source,WM/Reuters,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,RBA,IMF
Publication date,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,26-Mar-2025,01-Oct-2024,01-Oct-2024,26-Mar-2025,26-Mar-2025,01-Oct-2024,26-Mar-2025,26-Mar-2025,26-Mar-2025
Series ID,FXRUSD,FXRTWI,FXRCR,FXRJY,FXREUR,FXRSKW,FXRUKPS,FXRSD,FXRIRE,FXRTB,FXRNZD,FXRNTD,FXRMR,FXRIR,FXRVD,FXRUAED,FXRPNGK,FXRHKD,FXRCD,FXRSARD,FXRSF,FXRPHP,FXRSDR
03-Jan-2023,0.6828,61.40,4.6994,88.48,0.6400,867.16,0.5656,0.9134,56.51,23.47,1.0760,20.97,2.9975,10635,16039,,,5.3316,,,0.6310,38.07,0.5131
04-Jan-2023,0.6809,61.50,4.6906,89.08,0.6439,866.04,0.5678,0.9138,56.41,23.33,1.0851,20.88,2.9990,10634,16022,,,5.3222,,,0.6351,38.12,0.5120
05-Jan-2023,0.6815,61.50,4.6916,90.17,0.6426,865.90,0.5661,0.9145,56.41,23.08,1.0852,20.92,3.0000,10644,16002,,,5.3276,,,0.6341,38.12,0.5106
06-Jan-2023,0.6769,61.20,4.6378,90.63,0.6431,856.58,0.5677,0.9100,55.92,23.00,1.0839,20.79,2.9753,10582,15887,,,5.2853,,,0.6338,37.66,0.5077
09-Jan-2023,0.6929,62.00,4.7029,91.25,0.6490,862.65,0.5707,0.9221,57.08,23.22,1.0830,21.15,3.0325,10794,16252,,,5.4091,,,0.6415,38.20,0.5224
10-Jan-2023,0.6904,61.70,4.6750,91.13,0.6434,857.24,0.5678,0.9196,56.77,23.08,1.0833,21.03,3.0205,10763,16190,,,5.3909,,,0.6364,37.88,0.5156
11-Jan-2023,0.6911,61.70,4.6803,91.43,0.6435,860.03,0.5684,0.9192,56.50,23.12,1.0832,21.04,3.0225,10691,16206,,,5.3985,,,0.6373,37.96,0.5152
...
19-Mar-2025,0.6357,60.20,4.6005,95.06,0.5816,924.02,0.4895,0.8468,55.03,21.38,1.0941,20.98,2.8177,10512,16239,,,4.9400,0.9098,,0.5577,36.38,0.4769
20-Mar-2025,0.6344,60.00,4.5905,94.14,0.5819,926.00,0.4879,0.8446,54.70,21.32,1.0953,20.94,2.8079,10451,16214,,,4.9296,0.9091,,0.5563,36.25,0.4761
21-Mar-2025,0.6289,59.70,4.5602,93.97,0.5807,922.66,0.4860,0.8403,54.25,21.28,1.0939,20.77,2.7819,10383,16084,,,4.8879,0.9014,,0.5557,36.07,0.4726
24-Mar-2025,0.6278,59.60,4.5575,94.03,0.5800,921.38,0.4860,0.8396,53.97,21.32,1.0970,20.74,2.7849,10400,16103,,,4.8803,0.9006,,0.5547,36.02,0.4719
25-Mar-2025,0.6287,59.80,4.5649,94.65,0.5819,923.97,0.4865,0.8416,53.87,21.38,1.0985,20.77,2.7911,10440,16115,,,4.8880,0.9005,,0.5550,36.06,0.4731
26-Mar-2025,0.6311,60.00,4.5848,95.01,0.5852,925.33,0.4879,0.8447,54.12,21.46,1.0981,20.89,2.7942,10464,16150,,,4.9064,0.9009,,0.5578,36.41,0.4752

Firstly I will create a table to hold the final data in Azure:

            create table forex
(
    exchg_id int not null identity,
    exchg_date date not null,
    exchg_currency varchar(4) not null,
    exchg_rate decimal(19,4) null,
    constraint PK_forex primary key (exchg_id),
    constraint UQ_EXCHG_DATE_CURRENCY unique(exchg_date, exchg_currency)
)

The Unique Constraint UQ_EXCHG_DATE_CURRENCY not only ensures data integrity and 3rd normal form but will also act as an index and will be very useful where results are ordered by exchg_date, exchg_currency.

The following Python code takes the source CSV and transforms it into an output file with three columns: Date, Currency and Exchange Rate. The third parameter, format_for_sql determines whether the output file is a SQL select statement which could be run directly in SQL Server Manegement Studio or a flat file for use in ETL.

            import os
import sys
CLI_SEPARATOR = "======================================================================\n"
CLI_HEADER = "\nprocess_forex_csv\nProcesses CSV version of RBA Exchange Rate history:\nhttps://www.rba.gov.au/statistics/historical-data.html#exchange-rates\nfrom supplied format to a format ready for ETL"
CLI_USAGE = "Usage:\nprocess_forex_csv.py [input_filename] [output_filename] [format_as_sql (y/n)]\n"
OUTPUT_HEADER = "Date,Currency,Rate\n"
FILE_HEADER = "EXCHANGE RATES ,,,,,,,,,,,,,,,,,,,,,,,"
FOREX_HEADER = "Series ID"
months = {
    "jan": "01",
    "feb": "02",
    "mar": "03",
    "apr": "04",
    "may": "05",
    "jun": "06",
    "jul": "07",
    "aug": "08",
    "sep": "09",
    "oct": "10",
    "nov": "11",
    "dec": "12"
}
class ProcessForexCSV:
    def __init__(self, input_filename, output_filename, format_for_sql):
        self.input_filename = input_filename
        self.output_filename = output_filename
        self.format_for_sql = format_for_sql
        self.currencies = []
    def process_file(self):
        input_file_path = os.path.join(os.getcwd(), self.input_filename)
        output_file_path = os.path.join(os.getcwd(), self.output_filename)
        with open(input_file_path, "rt") as f:
            new_lines = ""
            line = f.readline()
            if line.find(FILE_HEADER) < 0:
                print("Invalid file format.")
                return
            line = f.readline()
            while line.find(FOREX_HEADER) < 0:
                line = f.readline()
            tokens = line.split(",")
            i = 0
            for token in tokens:
                if i > 0:
                    self.currencies.append(token[len(token)-3:].strip())
                i += 1
            print("Currencies:\n{0}".format(CLI_SEPARATOR))
            for currency in self.currencies:
                print("{0}".format(currency))
            line = f.readline().strip()
            line_count = 0
            while line != "":
                line_count += 1
                line_tokens = line.split(",")
                token_count = len(line_tokens)
                exchg_date = line_tokens[0]
                original_exchg_date = exchg_date
                date_tokens = exchg_date.split("-")
                exchg_date = date_tokens[2] + months[date_tokens[1].lower()] + date_tokens[0]
                i = 1
                while i < token_count:
                    if line_tokens[i] == "":
                        line_tokens[i] = "null"
                    exchg_line = ""
                    if not self.format_for_sql:
                        exchg_line = "{0},{1},{2}\n".format(original_exchg_date, self.currencies[i-1], line_tokens[i])
                    else:
                        exchg_line = "select '" + exchg_date + "','" + self.currencies[i-1] + "'," + line_tokens[i] + " union all\n"
                    i += 1    
                    if exchg_line != "":
                        #print(exchg_line)
                        new_lines += exchg_line
                line = f.readline().strip()
        f.close()
        # get rid of the final "union all"
        if self.format_for_sql:
            new_lines = new_lines[:len(new_lines)-len(" union all\n")]
        else:
            new_lines = new_lines[:len(new_lines)-len("\n")]
        #print(new_lines)
        with open(output_file_path, "wt") as f2:
            f2.write(OUTPUT_HEADER)
            f2.write(new_lines)
        f2.close()
        print("{0}\n{1} exchange rates written to {2}\n{3}".format(CLI_SEPARATOR, line_count, self.output_filename, CLI_HEADER))
if __name__ == "__main__":
    print(CLI_HEADER)
    print(CLI_SEPARATOR)
    print(CLI_USAGE)
    
    if len(sys.argv) == 4:
        input_filename = sys.argv[1]
        output_filename = sys.argv[2]
        format_for_sql = True if str(sys.argv[3]).lower() == "y" else False
        process = ProcessForexCSV(input_filename, output_filename, format_for_sql)
        process.process_file()
    else:
        print("Invalid number of parameters.")

If format_for_sql is 'y' SQL a select union all statement with 12,719 rows is produced:

            select '20230103','USD',0.6828 union all
select '20230103','TWI',61.40 union all
select '20230103','RCR',4.6994 union all
select '20230103','RJY',88.48 union all
select '20230103','EUR',0.6400 union all
select '20230103','SKW',867.16 union all
select '20230103','KPS',0.5656 union all
select '20230103','RSD',0.9134 union all
select '20230103','IRE',56.51 union all
select '20230103','RTB',23.47 union all
select '20230103','NZD',1.0760 union all
select '20230103','NTD',20.97 union all
select '20230103','RMR',2.9975 union all
select '20230103','RIR',10635 union all
select '20230103','RVD',16039 union all
select '20230103','AED',NULL union all
select '20230103','NGK',NULL union all
select '20230103','HKD',5.3316 union all
select '20230103','RCD',NULL union all
select '20230103','ARD',NULL union all
select '20230103','RSF',0.6310 union all
select '20230103','PHP',38.07 union all
select '20230103','DR',0.5131 union all

If format_as_sql is 'n' a flat CSV file is produced:

            Date,Currency,Rate
03-Jan-2023,USD,0.6828
03-Jan-2023,TWI,61.40
03-Jan-2023,RCR,4.6994
03-Jan-2023,RJY,88.48
03-Jan-2023,EUR,0.6400
03-Jan-2023,SKW,867.16
03-Jan-2023,KPS,0.5656
03-Jan-2023,RSD,0.9134
03-Jan-2023,IRE,56.51
03-Jan-2023,RTB,23.47
03-Jan-2023,NZD,1.0760
03-Jan-2023,NTD,20.97
03-Jan-2023,RMR,2.9975
03-Jan-2023,RIR,10635
03-Jan-2023,RVD,16039
03-Jan-2023,AED,NULL
03-Jan-2023,NGK,NULL
03-Jan-2023,HKD,5.3316
03-Jan-2023,RCD,NULL
03-Jan-2023,ARD,NULL
03-Jan-2023,RSF,0.6310
03-Jan-2023,PHP,38.07
03-Jan-2023,DR,0.5131

It actually works out a lot easier to use the SQL statements rather than construct an ETL package. Just need to add one line to the top of the select statements. Though for a more complex ETL project we would create staging tables and use an ETL platform such as SSIS. This will be the subject of my next SQL Blog.

insert forex(exchg_date, exchg_currency, exchg_rate)

SSMS Insert Result

Select USD Exchange Rate ordered by date:

SSMS Select USD Result