Menu

How to get a near-realtime feed of transactions from your bank, without giving up your credentials or making them unhappy.

If you spend any amount of time trying to automate your life, then getting a feed of your transactions into your automation seems like a worthwhile step. This enabled me to keep track of transactions in a structured database, and keep a realtime view of how I was spending against my budget.

How not to do it

  • Use a browser automation framework to scrape your banks website
  • Reverse engineer your banks app/site and immitate their requests to get the data
  • Use a 3rd-party app which does budgeting or itemization, and scrape that service/site
These approaches have roughly the same problems:
  • They put your credentials at risk - Scripting a login into your bank implies providing your script (or worse, a 3rd-party app) with your login details. If your script/computer/the-3rd-party ever get compromised, expect your money to disappear.
  • They violate your banks TOS - Going against your bank means incurring all the liability if things go wrong. Each to their own, but I want to stay on good terms with the people protecting my money.

The safer + TOS-friendly approach

Most banks (indeed Wells Fargo) have a 'transaction alert' feature, where they will send you an SMS/email when a transaction over a certain amount is made against a specific account. We leverage this feature to have the bank give us a heads up with each transaction, without compromising login credentials or breaching the terms-of-service.

I did the following:

  1. Use Gmail filters to forward alert emails from Wells Fargo to my Nexus (think of it as a personal lambda) instance.
  2. Parse the emails to extract description, credit card suffix, date, and amount. Write this into a database.
  3. Match descriptions against hand-written rules to categorize them.
  4. Render everything in a pretty dashboard.

Email parsing

The following is is my lambda function to handle the forward alert emails. 
function contains(str, substr) {
    return str.indexOf(substr) !== -1;
}

String.prototype.startsWith = function(search, pos) {
	return this.substr(!pos || pos < 0 ? 0 : +pos, search.length) === search;
};

String.prototype.replaceAll = function(search, replacement) {
    var target = this;
    return target.split(search).join(replacement);
};

function findTrailingValue(lines, prefix) {
    for (var i = 0; i < lines.length; i++) {
        if (lines[i].startsWith(prefix)) {
            return lines[i].substring(prefix.length).replace('\r', '');
        }
    }
    return null;
}

function commitTransaction(acc, desc, amt, meta) {
    var insert = datastore.insert("Transactions", {
        account: ''+acc,
        timestamp: Date.now(),
        amount: amt,
        description: desc,
        category: '',
        subcategory: '',
        metadata: JSON.stringify(meta || {source: 'email'}),
    });
    if (insert.success) {
        console.log("Successfully saved transaction: ", desc);
        console.log("rowID: " + insert.rowID);
    } else {
        console.error("Insert failed.");
        console.data(insert);
    }
}


if (context.run_reason == "EMAIL") {
  if (message && contains(message.subject, "Wells Fargo card purchase online")) {
      var spl = message.text.replace('\r', '').split('\n');
      var acc = 'wf-card-x'+findTrailingValue(spl, "Card ending in ");
      var desc = findTrailingValue(spl, "Merchant details at ");
      var amt = parseFloat(findTrailingValue(spl, "Purchase amount ").split(" ")[0]);
      commitTransaction(acc, desc, amt);
  } else if (message && contains(message.subject, "Wells Fargo card purchase exceeded preset amount")) {
      var spl = message.text.replace('\r', '').split('\n');
      var acc = 'wf-card-x'+findTrailingValue(spl, "Card ending in ");
      var desc = findTrailingValue(spl, "Merchant details at ");
      var amt = parseFloat(findTrailingValue(spl, "Purchase amount ").split(" ")[0]);
      commitTransaction(acc, desc, amt, {source: 'email', card_present: true});
  } else if (message && contains(message.subject, "Wells Fargo credit card purchase exceeded pre-set amount")) {
      var spl = message.text.replace('\r', '').split('\n');
      var acc = 'wf-card-x'+findTrailingValue(spl, "Credit card XXXX-XXXX-XXXX-");
      var desc = findTrailingValue(spl, "Purchase location at ");
      var amt = parseFloat(findTrailingValue(spl, "Purchase amount ").split(" ")[0]);
      commitTransaction(acc, desc, amt, {source: 'email', card_present: true});
  } else if (message && contains(message.subject, "Wells Fargo credit card online, phone, or mail purchase")) {
      var spl = message.text.replace('\r', '').split('\n');
      var acc = 'wf-card-x'+findTrailingValue(spl, "Credit card XXXX-XXXX-XXXX-");
      var desc = findTrailingValue(spl, "Purchase method at ");
      var amt = parseFloat(findTrailingValue(spl, "Purchase amount ").split(" ")[0]);
      commitTransaction(acc, desc, amt, {source: 'email', card_present: true});
  }
}

Rule-based categorization

In this step, another lambda function processes the database entries, and matches them against a structured set of categories + match rules. I can't show you the full code with expense_classes.json unfortunately; transaction descriptions are not anonymous enough to put on the internet. 
function categorizeTransactions() {
    var q = datastore.query("Transactions", [{value: "", column: "category", condition: "=="}]);
    if (!q.success)
        throw "Datastore query failed";

    var rules = JSON.parse(fs.read("/minifs/expense_classes.json"));

    for (var i = 0; i < q.results.length; i++) {
        applyTransactionRules(q.results[i], rules);
    }
}

Pretty dashboards!

Combine the data with a sneaky Angular frontend, and voila! Easy tracking of recent spending against budget goals. (Before you ask, this is fake data - who spends $55 a week on hobbies? :P)

Post Tags scripting automation money