Downloading your historical data from Xero

Getting started with new systems is always tricky, we know! This is why we have created this quick guide on preparing your historical data. Please set aside 10-15 minutes to get through it. And remember – this only has to be done once – participating in KPI Works in subsequent months takes mere minutes.

By uploading this historical data into your KPI Works dashboard, you’ll be able to see the trends that have been taking place within your organisation – and within your wider industry – right away. Also, some KPIs that we show you (e.g. insurance spend as a % of revenue) can only be calculated on an annualised basis – which is the second reason we ask for this historical data upfront.

Let’s get started!

Skip to:

Historical Profit and Loss
Historical Balance Sheet
Historical invoice count (a few invoices each month)
Historical invoice count (lots of invoices each month)
Providing access to your Xero account
Why we ask for historical data

Historical Profit and Loss

Once you’ve logged into Xero, click on the Accounting tab (1) and select the Reports option (2)

Select the Profit and Loss [New] report (3) – please don’t use the standard Profit and Loss report!

Click on the Date range triangle (4) and select Last Month (5)

In the Comparison period(s) window (6) click on Other (7)

In the period/s window (8) type 36 – even if you have less than 36 months of data in Xero, still use 36 here, as it’ll export the maximum number of months of data – then click on Select (9)

Click the blue Update (10)

Once the page refreshes to reveal multiple months worth of data (11), click on the Export (12) button on the bottom-right of your screen, and click on Excel (13). Once prompted, save your file somewhere convenient and give it a good, descriptive name – this will help you in the following steps.

Feel free to click here to download a sample file to see if yours looks similar.

Historical Balance Sheet

(back to top)

Once you’ve logged into Xero, click on the Accounting tab (1) and select the Reports option (2)

Select the Balance Sheet [New] report (3) – please don’t use the standard Balance Sheet report!

Click on the Date triangle (4) and select End of Last Month (5)

In the Comparison period(s) window (6) click on Other (7)

Click the blue Update (10)

Feel free to click here to download a sample file to see if yours looks similar.

Historical invoice count (a few invoices each month)

(back to top)

Download our invoice-counter template, and follow the sequence below to export your invoices from Xero, and easily count how many invoices you raised for each historical month. If you raise a lot of invoices each month, follow this manual process instead.

Once logged into Xero, click on Business in the top menu (1), then click on Invoices (2)

Assuming you have less than 500 invoices for all of the historical months you exported your data for, you can immediately click Export (3), and when prompted, open the Excel file (4).

If you have more than 500 invoices, Xero will show you an error message. To work around it, click Search (top-right of the invoices table), and restrict the date range to reduce the number of invoices Xero will try to export to under 500, and then repeat this export as many times as needed to cover your date range.

Select all of the invoice data in the exported Xero file by clicking and dragging, and “copying” the data once highlighted (right-click and “copy”, or, Ctrl+C). Start in the first top-left cell with data in it (5) and end in the very bottom-right cell (6)

In our invoice-counter template, paste (right-click and “Paste”, or, Ctrl+V) the invoice data into the top-left cell (7)

Once pasted, the data will fill the columns. Please double-check that your invoice numbers are under the green heading InvoiceNumber in column S (8) and that the date the invoice was created is under the green heading InvoiceDate

Finally, scroll to the right – and you will see the Invoice count by month that you need for KPI Works in row 2 (9)

Historical invoice count (lots of invoices each month)

(back to top)

When you have hundreds or more monthly invoices, the template described above will not be practical, as Xero can only export 500 invoices at a time. The below manual process will be a lot faster, and will simply show you the invoice count for each of your historical months.

Once logged into Xero, click on Business in the top menu (1), then click on Invoices (2)

Click on the Search button (3) on the top right

Inside the Search Within window (4) select Transaction date option (5)

Inside the Start Date window (6) choose the first of the month you wish to count invoices for (7)

Inside the End Date window (8) choose the last day of the month you wish to count invoices for (9)

Click the Search button (10)

The Invoice count for the month (11) – in this case 11 invoices – will be displayed at the top of the invoice list

Simply repeat this process for as many months as required by changing the Start Date (4) and End Date (6) to define a new month, and running a new search by clicking the blue Search button (10)

Providing access to your Xero account

(back to top)

We can help you get started with KPI Works faster by extracting your historical data out of Xero, and using it to pre-populate your KPI Works dashboard. This is an optional and complimentary service to all KPI Works users.

If you would like us to help you, please “invite” us as a user within your Xero subscription, using the following details (please refer to the “how-to” screenshots below, if needed):

Name: Dima
Surname: Ivanov
Email: support@kpiworks.co.nz
Access required to: Business and accounting (Standard access)

Once logged into Xero, click on Company name in the top-left corner (1), then click on Settings (2)

Click on Users (3)

Click the green Invite a user button on the top-right (4)

Use Dima Ivanov (support@kpiworks.co.nz) as the user details (5)

From your “Give them access to” options, only check the bottom value Business and accounting (6), and leaving the default selection on Standard (7) – you will not need to make any changes to the options below the Standard tab, please leave the default selections.