Getting Real Currency Exchange Rates with SQL Server Data Generator

This article will demonstrate how to get live currency exchange rates with the help of Python and dbForge Data Generator for SQL Server.

The currency exchange rate is the reference information used in the translation of monetary values from one currency to another. The exchange rate expresses the value of one currency in terms of another.

The AdventureWorks demo database contains the Sales.CurrencyRate table. The table stores currency exchange rates and consists of the following columns:

• CurrencyRateID — Primary key for CurrencyRate records.
• CurrencyRateDate — Date and time the exchange rate was obtained.
• FromCurrencyCode — Exchange rate was converted from this currency code.
• ToCurrencyCode — Exchange rate was converted to this currency code.
• Average Rate — Average exchange rate for the day.
• EndOfDayRate — Final exchange rate for the day.
• ModifiedDate — Date and time the record was last updated.

With the help of dbForge Data Generator for SQL Server, we can easily generate test values for that table. Simply select the Sales.CurrencyRate table and you will see the data generated by default.

Definitely, you can play with settings to adjust values to be generated, but the result will not look as precise as you may need. Note, the generated values do not look like real currency rates and most probably will not fit your business logic.

Moreover, if the SQL unit testing is a part of your development process, tests can fail due to incorrect values.

Now let’s see how we can easily resolve this problem with the help of the Python generator provided by dbForge Data Generator for SQL Server.

Across the internet, there are a number of resources that provide JSON API for foreign exchange rates and currency conversion. We will use fixer.io in this demo.

Select the Python generator for the AverageRate column and use the following script to get the data:

# The generator generates the currency rates based on the exchange rate date and currency codes.

import clr
clr.AddReference("System")
clr.AddReference("Newtonsoft.Json")
import urllib, json
from urllib2 import urlopen
from System import DateTime
from Newtonsoft.Json import JsonConvert

def main(config): 
     
# API key is now required for the free server.
# Get your free API key: https://free.currencyconverterapi.com/free-api-key
  apiKey = "[YOUR_API_KEY]"
  
# CurrencyRateDate – name of the column in the current table that contains exchange rate dates.
# FromCurrencyCode – name of the column in the current table that contains a currency code to convert from.
# ToCurrencyCode – name of the column in the current table that contains a currency code to convert to.

  fromCode = str(FromCurrencyCode)
  toCode = str(ToCurrencyCode)
  from_to_codes =  str(fromCode) + "_" + str(toCode)
  
  dt = DateTime.Parse(str(CurrencyRateDate))
  convert_date = str(dt.Year) + "-"+  str(str(dt.Month).zfill(2)) +"-"+ str(str(dt.Day).zfill(2)) 

# Free version only allows up to 1 year earlier.  
  url = "http://free.currconv.com"
  url += "/api/v7/convert?q=" + from_to_codes + "&date=" + convert_date + "&compact=ultra" + "&apiKey=" + apiKey
   
  response = urllib.urlopen(url)
  jsonString = response.read()
  data = json.read(jsonString)
  
  if not data.ContainsKey(from_to_codes):
    return jsonString
  
  jsonObj = JsonConvert.DeserializeObject(str(data));    
  return str(jsonObj[from_to_codes][convert_date])

Do the same action towards the EndOfDayRate column. The Python script will be as follows:

import clr
clr.AddReference("System")
import urllib, json
from urllib2 import urlopen
from System import DateTime

def main(config): 
    dtStr = str(CurrencyRateDate)
    dt = DateTime.Parse(dtStr)
    year = dt.Year
    month = dt.Month  
    day = dt.Day
    n1 = str(FromCurrencyCode)
    n2 = str(ToCurrencyCode) 

    if not n1 or not n2:
       return "N/A" 

    url = "http://api.fixer.io/"+ str(year) +"-"+ str(str(month).zfill(2)) +"-"+ str(str(day).zfill(2))+ "?base="+str(n1)+ "&symbols="+ n1+","+n2

    response = urllib.urlopen(url)
    data = json.read(response.read())

    if not data.has_key("rates"):
       return "N/A" 

    return data["rates"][n2]

Now you can see that we have the real currency exchange rates.

Keep in mind that the scripts we have provided are only an example. The API key constantly changes on the side of the provider, thus, we cannot keep the scripts up-to-date at all times. Make sure to adjust the provided scripts according to the requirements of your API provider.

dbForge Data Generator for SQL Server is a powerful tool that provides you unlimited opportunities and flexibility while resolving domain-specific tasks.