Import currency exchange rates

One of the things you often find in a web shop is the possibility to shop in your native currency. To achieve this we need to hold a table with exchange rates that is up to date. This can be done manually, but is a laborious task, so why not just find a site that delivers the rates and make a scheduled task doing it for you. Here is a example of how to fetch exchange rates based on Euro from the European Central Bank Feed. You can find several other both free of charge and pay per month for business use. 

How we build our task to fetch the currencies and update our table

To do this we will use a transfer, a schedule, a task, a support table and a few DQLs. The transfer will connect to a feed from the European Central Bank to fetch a xml with todays exchange rates from Euro to 28 different currencies and add them to an empty support table. The schedule will once every day call a task. The task will contain cleanup support tale DQL, run transfer to fetch currencies, run a DQL that update our currency table. We could do a direct update to the currency table, but you never know when feeds are changed or internet connections are down. This structure will also make it easier for you do take this solution and update one of your own tables by simply change the update DQL.

The transfer

We have to create that use custom source. The code to add is.

from debdata import TableDef, FieldDef, Record, RepoBase, SourceBase
import os
import tempfile
import requests
import urllib2
import xml.etree.ElementTree as ET

def getrepo(ctx=None):
    return SourceRepo(ctx=ctx)

class SourceRepo(RepoBase,SourceBase):
    def __init__(self,ctx = None):
        RepoBase.__init__(self,ctx=ctx)
        SourceBase.__init__(self)
        self.max=9999
        self.tdf = None
        try:
            contentpath = tempfile.gettempdir() + os.path.sep
            if not contentpath or not os.path.exists(contentpath):
                contentpath='D:`\\DeployedDG3Apps\\'
        except Exception as e:
                contentpath='D:`\\DeployedDG3Apps\\'
                f = open( '%serror.txt' % contentpath,"w")
                f.write("Failed because %s" % e)
                f.close()
        ufp = '%seurofxref-daily.xml' % contentpath
        url = 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml'
        self.rates = None
        try:
            #s = urllib2.urlopen(url)
            #xmlcode = s.read()
            r = requests.get(url)
            xmlcode = r.text
            f = open(ufp,'w')
            f.write(xmlcode)
            f.close()
            self.dom =  ET.fromstring(xmlcode)
        except Exception as e:
            print "Failed to read %s because: %s" % (url,e)
            raise e

    def getsource(self,tdf=None,filter=None):
        return self

    def cleanup(self):
        self.dom=None
        self.products = None

    def gettdf(self,name):
        self.tdf = TableDef(name='MySource',type=TableDef.REPOTYPE.TABLE)
        self.tdf.add(FieldDef(name='currency',type=FieldDef.FIELDTYPE.TEXT))
        self.tdf.add(FieldDef(name='rate',type=FieldDef.FIELDTYPE.FLOATNUMBER,length=10,decimals=5))
        return self.tdf

    def __iter__(self):
        self.row = -1
        if not self.tdf:
            self.gettdf('MySource')
        try:
            exchange = self.dom[2]#('gesmes:Envelope')
            self.rates=exchange[0]
            self.numrows = len(self.rates)
            return self
        except Exception as e:
            print "Failed to find rates because: %s" % e

    def next(self):
        self.row+=1
        fields = []
        if self.rates and self.row < self.numrows:
            try:
                self.rate = self.rates[self.row]
                currency = self.rate.attrib.get('currency')
                fields.append(currency)
                rate = self.rate.attrib.get('rate')
                fields.append(rate)
            except Exception as e:
                print "Failed to add row %d because: %s" % (self.row,e)
            rec = Record(self.row,self.tdf,fields)
            return rec
        else:
            raise StopIteration

if __name__ == '__main__':
    repo = getrepo()
    heading = []
    for field in repo.gettdf('GetData'):
        heading.append(field.name)
    print heading
    count = 0
    for rec in repo:
        count+=1
        recarr = []
        for field in rec:
            recarr.append(field)
        print "%d: %s" % (count,recarr)

This fetch the feed at http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml and outputs all currencies as a table with the field currency and rate. This is added to our ImportExchangeRates table. The part after if __name__ == '__main__': is test code so you can create and run the code in an external tool like PyDev. It is not used or run in our custom source in the transfer. You can find full documentation on custom transfers in the reference guide.

List of other sources of exchange rates