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:
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
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.
Stage 2 - Using Selenium & PhantomJS
The europa.eu 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.
from selenium import webdriver
from selenium.common.exceptions import TimeoutException as ex
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
import numpy as np
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')
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.