Running Xero reports from Excel links

When you do data analysis in Excel it is useful to have a link to the source data.
The parameters used to produce the last set of data are also very useful.
In Excel you can insert a hyperlink directly to a Xero report.
So the next time you want to regenerate the data, you simply click on the link rather than having to search for the report.

Hyperlink

In Xero navigate to Accounting - Reports and click on the report that you want to run.
Set the parameters that you want to use and then click on the Update button.

From the address bar in the browser, copy the URL.
Open Excel, on the Insert menu, select Link.
Enter the report name into Text to display field and paste the copied URL into the Address field.
Click on the OK button.

A direct link to the report will be created.

Parameters

Unfortunately Xero does not use a URL scheme for its reports to pass parameters.

The best that you can do is insert a screenshot into Excel of the parameters used.
Go back to Xero and take a screenshot of the report parameters.
Press Shift + Windows Key + S, then left click and drag the mouse to highlight the area you want to screenshot.
The screenshot will be saved to the clipboard when you release the mouse button.

Go to Excel and paste the screenshot near to the link.

Using the link

Next time you want to run the report, click on the link.
If you are logged into Xero the report will be opened.
Set the parameters, using the screenshot as a guide to what was used before.
Click the Update button and the Export to Excel button.
Finally copy the downloaded data into your analysis workbook.

Using with multiple Xero Companies

The URL contains a unique identifier of the company.
In the example below it is AAAAA

https://reporting.xero.com/AAAAA/v1/Run/1005

In Excel we can build the URL in order to make it easy to substitute in another company identifier.

Split the URL into three separate cells:

• the value before the company identifier
• the unique company identifier
• the value after the company identifier

Use the HYPERLINK and CONCAT functions to join the cells together to form a valid URL.

=HYPERLINK(CONCAT(B3,B4,B5),B6)

This way it is easy to reuse with other Xero companies by changing the unique company identifier.