Monitoring Past Due AR with Xero and Slack

Monitoring accounts receivable (AR) is something that all companies do. Generally, this is a function of the accounting department.

The challenge is: Who is responsible for following up when AR is past due?

Atomic’s Approach

Like many consultancies, our invoice monitoring is a separate function from our normal project management practices. This separation of concerns makes good sense. The team is responsible for producing exceptional client value and tracking its overall budget, and our business layer (accounting department) is responsible for sending invoices and monitoring payment.

Luckily, at Atomic, most of our clients consistently pay on time. However, we do occasionally have invoices that go past their due dates. When invoices are past due, it’s a collaborative effort to coordinate communication with our clients. This effort is generally led by a combination of our office-level managing partners (MPs) and our internal accountant.

As we’ve grown in size, the frequency of past due invoices has also increased at a linear rate. The amount of people that need to collaborate in order to follow up on past due invoices has also increased. We currently have two offices, two MPs, one accountant, one business manager, and tens of active clients.

Our historical model of relying on our accountant to raise AR issues to MPs using tactics like word of mouth or email at variable intervals was no longer cutting it. We needed a more consistent interval and collaborative model.

Leveraging Slack

Since we were already using Slack internally, I decided to create a simple Ruby script that programmatically connects to our accounting system (Xero) every week and publishes a past due AR report into a Slack channel. This allows everyone that needs to know about past due AR to see the report once a week.

Having the data in Slack has allowed us to keep the conversation about how to handle the past due AR in once place—Slack. Everyone sees the messages, everyone agrees to the plan, and everyone can go back and review the written transcript.

This integration has been a powerful enhancement to our AR process.

The Implementation

This is how I created the script.

First, I leveraged two open-source Ruby gems.

  1. Xeroizer – This is a wrapper that connects to the Xero API. I used the private applications authentication process that’s documented on Xeroizer’s GitHub page.
  2. Slack Poster – This is a wrapper that allows you to post to Slack. I followed the instructions on Slack Poster’s GitHub page and created a webhook for our Slack instance in a matter of minutes.

Next, I wrote the following script, installed it on a server, and configured Cron to run it once a week. The script simply connects to Xero using Xeroizer, queries all the past due invoices, and then posts them into Slack using Slack Poster.

The currencify method is a handy utility method that I found online. It helps to nicely format floating point numbers.

To use it, just store your secrets in the environment variables. Alternatively, you can encode them in the following files: consumer_key.txt, consumer_secret.txt, and slack_web_hook.txt.


#!/usr/bin/env ruby
# encoding: UTF-8
# frozen_string_literal: true

require 'xeroizer'
require 'slack/poster'

def currencify(number, options = {})
  # currency_before: false puts the currency symbol after the number
  # default format: $12,345,678.90

  options = {
    currency_symbol: '$',
    delimiter: ',',
    decimal_symbol: '.',
    currency_before: true
  }.merge(options)

  # split integer and fractional parts
  int, frac = format('%.2f', number).split('.')

  # insert the delimiters
  int.gsub!(/(\d)(?=(\d\d\d)+(?!\d))/, "\\1#{options[:delimiter]}")

  if options[:currency_before]
    options[:currency_symbol] + int + options[:decimal_symbol] + frac
  else
    int + options[:decimal_symbol] + frac + options[:currency_symbol]
  end
end

# App Starts Here

consumer_key = ENV['CONSUMER_KEY'] || IO.read('consumer_key.txt').strip
consumer_secret = ENV['CONSUMER_SECRET'] || IO.read('consumer_secret.txt').strip

client = Xeroizer::PrivateApplication.new(
  consumer_key,
  consumer_secret,
  'privatekey.pem'
)

late_invoices = client.Invoice.all(
  where:
   {
     type: 'ACCREC',
     amount_due_is_not: 0,
     due_date_is_less_than_or_equal_to: Date.today
   }
)


slack_webhook_url = ENV['SLACK_WEB_HOOK'] || IO.read('slack_web_hook.txt').strip

late_invoices.each do |invoice|
  poster.send_message("Past Due Invoice\n" \
                      "\tEngagement: #{invoice.contact.name} - #{invoice.reference}\n" \
                      "\tInvoice Number: #{invoice.invoice_number}\n" \
                      "\tAmount: #{currencify(invoice.amount_due)}\n" \
                      "\tDue: #{invoice.due_date}\n" \
                      "\tDays Late: #{(Date.today - invoice.due_date).to_i}")
end

This approach has improved our ability to track AR issues, and I hope it’s useful for you.