Devart Blog

Getting Real Currency Exchange Rates with Data Generator for SQL Server

Posted by on July 1st, 2016

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

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.

Default

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:

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 round(data["rates"][n2],2)

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.

real

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

 

Leave a Reply