Inspiration

We were shooting the breeze with our accountants, FD Works, when it emerged that they had many ideas about how a progressive accounting firm could take advantage of the excellent Xero api, with a bit of technical know-how and impetus. Bookkeeping requires diligence and consistency, but nobody's perfect - we identified a partnership opportunity to build a platform that allows Xero pros to quickly identify errors and inconsistencies, and take action. Xavier Analytics was born.

What it does

Xavier guides accounting teams through Xero best practices and encourages data consistency across clients. Each client can have an Account Manager assigned for quick navigation to the clients you are in charge of.

There are four key elements to Xavier's process:

Automated Syncing

Import all of the data for all of the Xero organisations that your team needs (we call them clients) and their data will stay in sync with what is in Xero automatically using the Partner api flow. Due to the sizeable data transfer requirements we've put a lot of effort into optimising the performance and stability of our Xero integration.

Deep Insights

Your client's data is passed through Xavier's Insights Engine to produce three actionable insights, with more in the pipeline.

Duplicate Contacts

As transactions come into Xero and are reconciled, it is quite common for duplicate contacts to be mistakenly created. Xavier uses fuzzy logic to surface possible duplicate contacts, and links through to Xero where these can be resolved. It shows the strength of the match, the number of transactions each contact has, and allows you to "dismiss" the suggested match if it is incorrect.

Mis-Coded Transactions

Xavier analyses a contact's transactions over time to check for consistency. In a lot of cases, the invoices or bank transactions for a particular supplier or customer should be coded with the same account codes - Xavier will highlight instances where this is not the case, allowing you to correct the codings and ensure consistency for a contact's transactions.

Unreconciled Transactions

Keep track of unreconciled transactions, and sort and filter using Xavier's grid. You can also quickly toggle to see all Bank Statement lines in the same view. Xavier indicates if any records are in locked periods, and deep-links straight through to Xero for reconciliation.

We go into more detail on each insight in our help documentation.

Slice and Dice Reporting

Xavier's in-browser sandbox is a pivot table playground. Starting with Xero's Journals (the base of the general ledger), these are augmented with specific data from the source of the journal (e.g. Bank Transaction, Invoice, Manual Journal) such as Contact, Type, Status, etc. Columns can be added, removed, sorted, filtered and rearranged. Your final configuration can be saved as a custom report, made available to the rest of your team and used across all your clients. Xavier also ships with some pre-made reports that highlight the power of the sandbox and help you get started.

Data Control

Xero's lock dates give teams the ability to prevent unwanted changes in periods that have already been reported on. Xavier tracks changes to existing lock dates and raises warnings where lock dates were changed to older dates, warranting further investigation. Configurable warnings can also be raised if lock dates are not kept up to date with reporting periods in Xero, encouraging active maintenance and best practice amongst your team.

Xavier also queries the UK company register, Companies House, to compare filing dates with the lock dates in Xero, and raises a warning if lock dates have not been updated to prevent changes on dates that have been filed.

Xavier checks all transactional records as they are imported from Xero, and flags them as "Locked" in Sandbox and other Reporting views, which serves as a handy indicator as to what records can be changed in Xero. Records that are in locked periods can easily be filtered out of reporting views, so the user can work with records they know can be adjusted.

How we built it

The Core

The core of the project is built using the Laravel PHP framework, which handles the communication with Xero and manages all the jobs and queues. The Laravel back-end provides an API that is consumed by our Vue.js single page app front end, utilising elements such as Vue-Router and the Vuex store for state management.

Reporting

The base of the pivot table reporting utilises the ag-Grid framework, with schema setup to work with Xero data. Custom report schemas are saved as JSON.

Queues

Long-running tasks such as Xero-imports are placed into Redis queues. Messages such as import progress are broadcast to the front end using Laravel's broadcasting system and Pusher, and picked up by event listeners employing the Laravel Echo javascript library.

Database and Processing

Basic project structure (users, teams, clients) is held in a multi-tenant MySQL database. Each client's Xero data is placed into their own individual MongoDB NoSQL database instance. As the data comes in from Xero we mould it into the format we need via a number of aggregation pipeline functions - we found this was the fastest and least memory-intensive way to transform the data.

Insights

Taking a micro-services approach, once data has been imported from Xero we hand over to a number of python modules for processing Xavier's insights - we call this service "Xavier AI". Communication between Xavier and Xavier AI is performed over redis channels for simplicity and speed. Xavier AI utilises libraries such as pandas and numpy for its processing and writes the results of its individual insights back to MongoDB.

Supporting Sites

As part of the Xero App Partner certification process, we needed to ensure we provided a marketing website that informed our users about our product (with a Xero landing page) and sufficient help/support documentation. To try out some new technologies, our marketing site uses the Jigsaw static site framework, and our help site uses the Vuepress static site framework (using markdown).

Challenges we ran into

Pulling so much data from Xero meant the chance of hitting API rate limits was guaranteed. We needed to implement a throttling solution that worked across queued jobs, and as we couldn't find a library that did exactly what we needed we ended up modifying one to our needs.

Processing large amounts of data required consideration and optimisation across the board:

  • Long-running processes had to be split up into multiple queued jobs so as not to hit query execution timeouts
  • Aggregation pipeline operations had to be optimised so as to not hit in-memory limits in Mongo
  • Data had to be stored and retrieved using cursors and streaming to avoid out of memory exceptions
  • The front-end user interface had to be designed so long-running processes didn't impact usability, and progress was clearly indicated to the user

Accomplishments that we're proud of

The thing we are most proud of from this project is that we have managed to get it released live, and some accountants are already using it on a regular basis, with positive feedback.

From starting the project in June to achieving Xero App Partner certification in August, we're pleased with the speed at which we managed to meet the certification requirements and convince enough beta testers to provide some decent test results.

Looking back at our initial Xero integration to what it is now, we are proud at the huge performance gains we've achieved across the app, from working with changes-only in the Xero API, optimising mongodb aggregation pipelines through to streaming data to the front end. All of these things combined have increased the usability of the app by several orders of magnitude.

What we learned

The team have tackled a number of technologies in this project that are new to them, including:

  • Separate Python microservices architecture
  • Use of docker containers for the python insights and local development environments
  • Working with MongoDB in earnest and especially aggregation pipelines
  • Static site generation using two new frameworks: Vuepress and Jigsaw

What's next for Xavier Analytics

Following our recent certification approval, we are soon to be listed on the Xero marketplace - an exciting milestone for us. We plan to continue on building out Xavier as a product, working through a feature list longer than Inspector Gadget's go-go-gadget arms and adding more and more insights and tools for accountants as we go. Now that the initial build is released we're starting to ramp up marketing activities. Eventually we hope to expand across all territories that Xero operates in.

If you want to have a play around with the app we have a full demo environment available here: Xavier Demo

Share this project:
×

Updates