When testing an application that implements billing features, having meaningful test data is extremely important. In this article, we will learn how to quickly obtain and populate your database with live currency exchange rates in a few simple steps.

Before diving into the topic in more detail, let’s briefly discuss the meaning of the term “currency exchange rate” and why it is important in various aspects of our lives. A currency exchange rate is a reference value used for converting money from one currency to another. In other words, it is the price for which one national currency can be exchanged for another national currency. Whether you are a businessman or the owner of a multinational corporation, currency exchange rates directly or indirectly affect your business activities.

Currency rates can vary quite dramatically even within one day, so having up-to-date information on currency values can save you a good deal of money and help you mitigate risks. For example, if you need to test a corporate database and populate one of the tables with today’s currency exchange rates, it would be a challenging task to retrieve data from some source and enter them into the table manually.

Fortunately, there exists a tool that can provide real-life test data in a few seconds – dbForge Data Generator for SQL Server. This useful SQL generator tool includes a great number of built-in data generators that will help you generate meaningful test data of diverse types and for various purposes.

Let’s use the AdventureWorks database to demonstrate how real currency exchange rates can be generated by dbForge Data Generator for SQL Server. In the AdventureWorks database, there is a table called Sales.CurrencyRate that has the following columns:

• Average Rate – an average exchange rate on a specific day
• EndOfDayRate – a final exchange rate on a specific day

After launching dbForge Data Generator for SQL Server and connecting to the AdventureWorks database, you can select the Sales.CurrencyRate table and see a preview of the data to be generated by default. However, the resulting data in the AverageRate column do not resemble the real currency rates and, more importantly, they are not live and real.

To solve this problem, we can apply a Python generator available in dbForge Data Generator for SQL Server. The Python generator allows you to create your own scripts to generate any custom data you need. In this case, we will use the fixer.io JSON API to get real-time currency rates for our AverageRate column.

Instead of the predefined generator automatically assigned for the AverageRate column, select the Python generator and insert the following Python script:

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 with the EndOfDayRate column. The Python script for the EndOfDayRate column is 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]

As you can see, generating live currency exchange rates in SQL Server using dbForge Data Generator is as easy as pie. The values in the AverageRate column will be exactly what you wanted them to be.

In summary, dbForge Data Generator for SQL Server opens up great capabilities when it comes to generating large amounts of meaningful and realistic-looking data for various types of databases.