Share on:

There is nothing more frustrating than spending time dealing with expenses since it is an activity that adds zero value to our customers and contributes nothing to our personal development. In short, doing expenses is an utter waste of time.

Some employers believe that they have an “expenses solution” because they run some sort of clunky web-based application such as Oracle iExpenses. Whereas in the past a regular employee would only need to hand over an envelope full of receipts, now he or she has to spend countless hours scanning them and entering their details on various forms. Thus, the use of a tool like iExpenses does not represent automation; it is merely shifting the expenses processing job from accountants to regular employees. Adam Smith would be turning on his grave if he knew we have learned nothing from his account on pin workers written three centuries ago.

To automate our expenses workflow—based on the assumption that our employer requires us to enter expenses on some AltaVista-era site—we require the following:

1. An expenses app such as Expensify
2. A web-browser scripting extension such as Greasemonkey
3. JavaScript skills

Expensify charges USD 5 (£3.5 or more subject to Brexit inflation) per month. Yes, it costs money and so what? The hours that I save by using this service are many times higher—on a pro rata basis—than the cost of a frappuccino. I’d rather not have a frappuccino than do all the monkey work that Expensify spares me from doing:

• Scanning receipts with my phone: I take a picture of every receipt on the spot and just forget about it
• Automatic recognition of price, date, merchant and category: no need to enter details by hand for most receipts
• Google Maps integration to calculate miles: no need to keep a mileage log in the car
• Uber integration: Uber sends receipts automatically to Expensify
• Currency conversion: no need to look up the exchange rate
• PDF report with pictures: no need to undergo the humiliating ritual of gluing receipts to an A4 page

Greasemonkey allows to interact programmatically with a web site by modifying its Document Object Model (DOM) on the fly; we can populate forms and press on buttons via JavaScript.

The Process

In an ideal world, Expensify would feed our reports directly into our employer’s back-end systems but since we are doing the integration ourselves, we need to create a CSV export for each report that we want to enter onto the expenses site. Since we will be dealing in JavaScript, it is convenient to convert the CSV file to JSON. For this purpose, I have a couple of Haskell functions as follows:

 1csv2expensify :: [[String]]
2              -> [(Day,Title,Currency,Amount,Category,Billable)]
3csv2expensify =
4   map (\row ->
5           let date      = read . take 10 $row!!0 6 title = row!!1 7 currency = row!!8 8 amount = parseFloat$ row!!9
9               category  = row!!4
10               billable  = "yes" isInfixOf (map toLower $row!!7) 11 in (date,title,currency,amount,category,billable) 12 ) 13 . safeTail . ensureLength 8 14 15expensify2json :: [(Day,Title,Currency,Amount,Category,Billable)] 16 -> String 17expensify2json items = 18 concat ["var expenses = [\n" 19 ,concatMap (\((date,title,currency,amount,category,_),l) -> 20 concat [" { " 21 ,"\"date\":\"",formatDayOracle date,"\", " 22 ,"\"title\":\"",title,"\", " 23 ,"\"currency\":\"",currency,"\", " 24 ,"\"amount\":\"",roundToStr 2 amount,"\", " 25 ,"\"category\":\"",category,"\"" 26 ," }" 27 ,if l then "" else "," 28 ,"\n" 29 ] 30 ) 31$ listLast \$ reverse items
32         ,"]\n"
33         ]


The above can be written in any language such as Python or JavaScript itself. Haskell just happens to be my go-to language. The final result will be a file as follows:

1var expenses = [
2{ "date":"30-Aug-16", "title":"Welcom", "currency":"EUR", "amount":"17.00", "category":"Meals" },
3{ "date":"30-Aug-16", "title":"Rasoi", "currency":"EUR", "amount":"23.50", "category":"Meals" },
4{ "date":"30-Aug-16", "title":"Uber", "currency":"EUR", "amount":"7.00", "category":"Transportation" },
5{ "date":"30-Aug-16", "title":"Leon", "currency":"GBP", "amount":"4.90", "category":"Uncategorized" },
6{ "date":"30-Aug-16", "title":"Uber", "currency":"GBP", "amount":"13.61", "category":"Transportation" },
7{ "date":"31-Aug-16", "title":"Meals", "currency":"EUR", "amount":"13.00", "category":"Meals" },
8{ "date":"31-Aug-16", "title":"Uber", "currency":"EUR", "amount":"8.00", "category":"Transportation" },
9]


The next step is write a Greasemonkey script. This is the most difficult part since we need to understand what the target expenses web site expects and what are the identifiers of the various elements we have to interact with.

I’ll describe what I did to automate Oracle iExpenses which is my particular case:

First, I include the expenses variable and set up some constants such as my project id (cost centre) and so on:

1var expenses = [..]
2
3projectId    = "123456789";


Then I try to detect which page Oracle iExpenses is displaying since this appears to be maintained in the session and I cannot assign a different script for each page by using a URL pattern which is the default way of using Greasemonkey.

1if (document.title.indexOf("Details for Line") > 0) { detailsForLine(); }
2if (document.title.indexOf("Allocations") > 0) { allocations(); }


The first case is for populating the expense claim form whereas the second is for the cost centre and task allocation screen. I now proceed to describe the contents of the detailsForLine() function which is the core of the form population mechanism:

1function detailsForLine() {
2...
3}


This function populates the expense form “one at a time” as opposed to using the “bulk” editing form since the bulk editing mode skips mandatory fields.

First, I extract the line item number from the title. If the line item number is greater than the number of items that I have imported from Expensify (held in my expenses array), then there are no more expenses to enter:

1title = ""+document.title;
2item = title.replace( /^\D+/g, '');
3
4if (item > expenses.length) {
5  document.title = "You've reached the end of your expenses"
6} else {
7  // I need to populate this form
8}


So we will look into the contents of “I need to populate this form”. To begin, we match the current Expensify line item with the Oracle iExpenses line item:

1expense = expenses[(item-1)];


Now that we have our matching Expensify record in the expense variable, we map the Expensify’s categories to those defined in the Oracle iExpenses’s dropdown menu. For example:

1expenseType = document.getElementById("ExpTypeChoice");
2if (expense.category == "Transportation") {
3   expenseType.selectedIndex = 26;
4} else {
5   expenseType.selectedIndex = 28;
6}


We then populate other fields such as date, amount and justification:

1startDate = document.getElementById("StartDate");
2startDate.value = expense.date; //"26-Jun-2016";
3
4amount = document.getElementById("DetailReceiptAmount");
5firstAmount = amount.value;
6amount.value = expense.amount; //("15.50");
7
8justification = document.getElementById("Justification");
9justification.value = (item + "-" + expense.category).substring(0,10);


The Expensify currencies must also be mapped to those defined in Oracle iExpenses:

1currency = document.getElementById("ReceiptCurrencyChoice");
2if (expense.currency == "EUR") {
3  currency.selectedIndex = 23; // EUR
4} else {
5  currency.selectedIndex = 25; // GBP
6}


There is also an extra justification field that hasn’t got a stable identifier so we have to look for it every time:

 1var allElements = document.getElementsByTagName("input");
2var justId = "";
3for (var i = 0, n = allElements.length; i < n; ++i) {
4  var el = allElements[i];
5  if (el.id.startsWith("DFF_")) { justId = el.id; }
6}
7
8if (justId != "") {
9  document.getElementById(justId).value = expense.category + ": " + expense.title;
10}


Finally, entering a value in the amount field causes the page to refresh so we want to cause the same behaviour:

1if (firstAmount == "") {
2  currency.onchange();
3}


I haven’t automated the clicking of the “next” button since I am still tweaking the script and making sure that the form has been populated correctly. However, the script maps the line item numbers from Oracle iExpenses with those from Expensify so I don’t need to specify what record must be selected.

Other Steps

After all expense line items have been entered, there is an of extra bulk form to match each line item with a cost centre and a task. This particular screen is annoying because it has pop-up windows that send refresh updates to the underlying page. After completing this screen, I attach the PDF report generated by Expensify which has all the scanned receipts. This is pretty much “it”.

Conclusion

At the cost of frappuccino per month and less than 100 lines of code, we can free up weeks worth of time in a year that can be utilised for more productive endeavours.

Automation is not some buzzword to sell to customers but a way of seeing the world through a single question: “Why should I do myself something what the machine can do for me?”