• Blog / Cloud Bookkeeping Blog /

• How to create an Audit Report for Repeating Invoices and Bills in Xero

The Problem

We love Xero repeating invoices, however, it has a number of downsides when you are trying to manage them on a large scale. Imagine having 100 + clients that are set up for monthly invoicing. The job of managing it is a nightmare because you cannot:

• Bulk edit repeating invoices
• Generate any sort of a report to check repeating reports
• Export repeating invoices to a .CSV or Google Sheets
• Import repeating invoices
• Copy from existing repeating invoices
• Easily check what email template you have set up

Current Situation

If you open Xero Repeating Invoices screen, you can only see:

• Contacts used
• Truncated reference
• Total Amount charged
• Repeat Frequency
• Next Invoice Date
• End Date
• Invoice status

For all other information, you need to click into each of the invoices individually, which is extremely time-consuming when you are auditing or changing setup data on the large scale.

Workaround Overview

We have created a workaround to help with this arduous task. It is a little complex however, it works when you are dealing with a large number (I would say over 50) recurring templates. This workaround will allow you to quickly check the following:

• Full reference field used
• Correct Branding Theme is applied
• Can see invoices that have been deleted (maybe by mistake)
• Consistency of the Line Item Descriptions
• Number of items
• Detailed breakdown of pricing calculations
• Correct GST code is used
• Inventory ID used
• Currency
• Schedule period and method (eg. every 1 month or every 2 weeks and etc.)
• Schedule Due Date and Type (eg. 14 days after the bill date, or 1st day of the month)
• Start date of every recurring template
• If any of the templates have discounts applied and what they are...

Workaround Process

For this workaround to work, you will need to have:

• Advisor access to particular Xero file
• Access to Xero API as a developer

Login into Developer’s system and click Previewer:

Click Connect to Xero:

Login into Xero (if you are not logged in yet), then choose Xero file you want to get data from and click Allow access:

Now change Endpoint from the drop down to Repeating Invoices:

No need to specify any parameters. Just click Run:

After few seconds it will return you all the data in XML format:

This will probably result in thousands of lines of XML code that needs to be converted into a more readable format. Copy all the content into a TXT file and rename the file with extension .XML. Now you can use any online converter to convert the data into .CSV. The following converter will work fine: ConvertCSV.com

Next open the CSV file with Google Sheets (you can try using Excel, but usually Excel converts fields of data to its own formats and causes problems.

Now you can dive into your data:

Converting Branding Theme codes

Some data, like Branding Templates and Inventory Templates, is represented in the format of the XeroID (32 character alphanumeric code):

Just create another sheet in Google Sheets and specify what these codes represent (there will be a particular Branding theme responsible for this code which you can find in your Xero file). Then just run VLOOKUP formula to insert the real values:

Assistance

If you need assistance in performing extraction of the data from the file aVers offer this service. Depending on the amount of data that needs to be processed, it takes on average 20 min to extract. Just add nick@avers.com.au (Nick Dorogavtsev) as an Advisor to your file, so we can connect to it and extract data for you. Then use the chat on the right hand side of this blog to contact me so I know you are waiting for data.

Subscribe by email and instantly get FREE Illustrated eBook. Adequate ‘positive’ cash flow is essential for the survival of any business, yet this is something that over 50% of small business owners struggle to manage.