Automatic currency rate updates every day to your database.

• Download source files - 60.1 Kb

Introduction

Are you running an online store and want to keep your currency conversion rates up to date? Then this program can save you some time. I developed this as a prototype to show how such data can be loaded.

The program can accept currency feeds from xe.com. Just go to xe.com and subscribe for email updates of currency here.

Block Diagram of the System

The program will connect to the mail box to which updates are sent from xe.com, retrieve the email, parse it and update a SQL database. This program can be scheduled using Windows Scheduler to keep your database with upto date currency information. Feel free to edit the source and use as needed. This is the functionality in brief.

Let's look at the details.

xe.com email updates consist of the following text layout.

Rates as of 2002.03.14 21:20:02 UTC (GMT). Base currency is USD.

Currency Unit                 USD per Unit     Units per USD
================            ===============   ===============
USD United States Dollars     1.00000           1.00000
EUR Euro                      0.882525          1.13311
GBP United Kingdom Pounds     1.42053           0.703961
......ZMK Zambia Kwacha       0.000223339       4477.50

The core of the system consist of a class called CurrencyUpdater.

The class has a single constructor. It takes no arguments. The constructor loads the configuration information about the mail box user name, password and POP3 server name from the App.config file:

public CurrencyUpdater()
{
 user=
  System.Configuration.ConfigurationSettings.AppSettings.Get("currency_mailbox_user");
 ...
}

Then the main routine invoke the UpdateCurrency() method on the CurrencyUpdater class. This function does the following things:

1. Calls the GetUpdateString() and gets the rates string from the POP3 email box.
2. Calls ProcessEmailString() which processes the string and generates the appropriate SQL and executes it against the database.

   public string GetUpdateString()
   {
     Log("Connecting to mailbox....");
   
     // Connect to XeRates Currency update email box
   
     string body=null;
     string subject;
     long messagecount=0;
     try
     {
       p=new Pop3Client(user,password,server);
       if(p!=null)
       {
         p.OpenInbox();
         messagecount=p.MessageCount;
         for(int i=1;i<messagecount;i++)
         {
           p.NextEmail();
           subject=p.Subject;
           if(subject!="")
           {
             if(subject.StartsWith("Today's Currency Update (SGD)"))
             {
               body=p.Body;
               break;
             }
           }
         }
       }
     }
     catch(Exception ex)
     {
       body=null;
       throw ex;
     }
     if(p!=null)
       p.CloseConnection();
     Log("Got string from mailbox...");
     return body;
   }

Configure the App.config file. It's pretty straight forward:

<add key="currency_mailbox_user" value="<<mailbox user name>>" />
 <add key="currency_mailbox_password" value="<<mail password>>" />
 <add key="currency_mailbox_server" value="<<mail server>>" />
 <add key="constring" value="<<connection string>>" />
 <add key="MailServer" value="<<mail server>>" />
 <add key="NOTIFY_SUCCESS" value="1" />
 <add key="NOTIFY_FAIL" value="1" /> 
 <add key="SUCCESS_FROM" value="<<from email for success>>" />
 <add key="SUCCESS_TO" value="<<to email for successful run>>" />
 <add key="SUCCESS_CC" value="<<cc email for successful run>>" /> 
 <add key="FAIL_FROM" value="<<from email for failures>>" />
 <add key="FAIL_TO" value="<<to email for failures>>" />
 <add key="FAIL_CC" value="<<cc email for failures>>" /> 
 <add key="SQL_1" 
   value="select Rate from tbCurrency where Currency='[[CURRENCY_CODE]]'" />
 <add key="SQL_2" 
   value="update tbCurrency set Rate=[[RATE]], 
     UpdatedOn=getdate() where Currency='[[CURRENCY_CODE]]'" />

In the above keys, the keys which need further explanation are SQL_1 and SQL_2:

• SQL_1 - This is the SQL to retrieve the current currency rates from the table. This is for sending update success email with old rates.
• SQL_2 - This is the update SQL. Note that [[RATE]] and [[CURRENCY_CODE]] will be substituted at run time.

Compile the project.

There you go .......

This project uses the POP3 component developed by Desmond McCarter and hosted here.