Project Exchange Rate Bot

This project is a pure software implementation based around Python, Javascript and output files from Xero accountancy software. Using Xero reports can be generated in CSV format. A report might contain a number of transactions which are dated and have a an associated currency amount (GBP). The requirement of this project was to parse the CSV, extract the dates, format them and automatically manipulate a currency exchange website. For each date the 'average' exchange rate for that period was extracted and saved alongside each transaction.

Key Specification Points:

  • Autonomous process.
  • Directly works with a financial report from Xero.
  • Robust enough to read and format a number of different date formats.
  • Use a headless browser to improve speed and run as a background task.
  • Post the extracted rates within the original report minimising user interaction.

The Completed Software

Project Image
Please Note: Due to the sensitive nature of this prject, not all code or steps are shown..

Key Development Stages

Stage 1 - Exporting from Xero into CSV

Xero allows the user to generate an 'Account Transaction' report. This allows any account to be selected between a date range. Then a "filter" allows a sub-account to be selected. As an example, imagine you have a project where all transactions are coded to an account. Within that project you may have sub-account codes such as, 'equipment', 'materials', 'time' and so on. The report allows you to individually group and analyse these sub account transactions which then could be exported in CSV format.

Project Image

Stage 2 - Using Selenium & PhantomJS

The site contains the GBP/EUR exchange for any given date. The concept behind this code was to use a Selenium web driver to manipulate this webpage in order to enter the dates of the transactions and to obtain the corresponding exchange rates. A headless browser was used in the form of PhantomJS in order to increase the execution speed.

import selenium
from selenium import webdriver
from selenium.common.exceptions import TimeoutException as ex
from import WebDriverWait  
from import expected_conditions as EC  
from import By

import numpy as np
import datetime
import csv


driver = webdriver.PhantomJS()

element = WebDriverWait(driver, 30).until(EC.presence_of_element_located((By.ID, "map-min-max-avg")))
datefields = driver.find_elements_by_class_name("amChartsInputField")

The routine detected the HTML elements on the page. The main elements were the date ranges, the search button and the element containing the averaged rate. This were manipulated autonomously.

Stage 3 - Retrieving Dates and Applying to the Webpage

The transaction CSV file is read and the dates obtained. The formatting of the dates is changed and there is an incompatibility between Xero and the EU site. Each date string is read and converted and then applied to the data entry boxes on the site.

with open('C:\\XXXXXX\\file_name.csv', 'rb') as csvfile:
    reader = csv.DictReader(csvfile, delimiter=',')
    i = 0
    for row in reader:
        #arr[i,0] = row['Date']
        arr[i,0] = row['SOME TEXT']
        i = i + 1

for i in range(8, reader.line_num - 6):
    # load a date
    datestr = arr[i,0]

    if(datestr != 'SOME TEXT TO TEST'):

            # re-format it - converts from 00/00/2016 to 00-00-2016
            newstr = datetime.datetime.strptime(datestr, '%d %b %Y').strftime('%d-%m-%Y')
        except ValueError:
            newstr = datestr

        # fill in the fields

        #submit the request

        #read the result
        result = driver.find_element_by_id("map-min-max-avg")

This routine was repeated for each date in the transaction list and on each pass the 'average' value read out and written back to the CSV file.

Contact Us