Xero is cloud-based accounting software with built-in reports and analysis tools. However, its standard functionality can be limited leaving users to look for 3rd party apps with the ability to aggregate your data over multiple Xero accounts or cross-reference your Xero data with other cloud services.
Everyone knows Microsoft Excel and it might have been around for a long time, but it is hard to beat from a functionality point of view. An excel spreadsheet allows you to organise data in columns and rows that can be manipulated through formulas, something most accounting software cannot compete with.
PowerQuery is a business intelligence tool built into Microsoft (or downloaded as an add-on) that allows you to import data from many different sources (including Xero, via Accounting.BI). PowerQuery may change, adjust or shape data to suit your data analysis needs - before loading it into your Excel workbooks.
Excel 2016 and above versions have built-in PowerQuery under the Data tab in the Get & Transform group. However, Power Query is available in Excel 2010 and Excel 2013 as a free add-on downloadable from Microsoft's website.
By using Microsoft Excel together with PowerQuery when working with Xero Data - accountants, bookkeepers, and businesses stand to benefit in the following ways:
Automatically syncing management account data
Power Query lets you automate your report by creating a query that pulls data from Xero to create a single data set. From this single data set, you can quickly create a PivotTable in excel to summarise the transactions by general ledger account and by month.
Merging excel data to online data
Power Query in Excel is programmed to import data into Excel from different popular data sources, including CSV, XML, JSON, PDF, SharePoint, SQL, and many more. Once data is in Excel from Xero and other data sources, it's ready to change shape to what you want using Power Query and other Excel data properties. The ‘shaped data’ can then be imported by different data sources, shared as reports, charts, or even exported to Power Apps.
Improve and optimise reporting with the power of Excel
When you pull Xero data into Excel using PowerQuery, you can adjust and customise your reports to your preference. Moreso, you can take advantage of the advanced Excel functionality - VBA and Pivot Table - to exceed the standard spreadsheet system’s capabilities and manipulate, visualise and transform information by integrating your Excel document with PowerBI.
Xero to excel live connection via Accounting.BI
PowerQuery exports Xero accounting data and reports to Excel in real-time. You can connect PowerQuery to your Xero data via Accounting.BI to shape your accounting data in ways that meet your needs - before loading your query into Excel to create your management reports. To get automatic data updates, you can refresh the data periodically to help your business operate on the latest insights.
Visibility into your business performance
Powerquery allows you to import Xero Invoices, Payments, Purchase Orders, Profit and Loss, Balance Sheets, and other reports to Excel. If you blend this information with data from other systems and visualise it with charts and graphs – you can review the outcomes - to get a complete picture of your company's results.
Automating business processes
If you have multiple Xero organisations and must manually export data at regular time intervals, it becomes hard to create the data correctly. However, if you set up PowerQuery via Accounting.BI - a query is stored in the system - every time the information needs to be imported and formatted similarly, the instructions are repeated using the refresh option. It eliminates repetitive manual work and makes the process more efficient.
Power Query then provides the option of combining different data sets. For example, if a single report needs to be made on the amount of revenue earned by each company over one year, data from each month is added to that from the previous months, and figures are correctly added up. The revenue reports from each month can be combined with those of the prior months - by specifying the necessary instructions in PowerQuery.
Even with its host of benefits, Microsoft Excel’s Power Query feature remains undiscovered and unused by most business professionals. When working with Excel - this tool offers business professionals unparalleled options for linking and transforming data and automating many reporting processes. Learning how Power Query can help you advance and automate your reporting processes is crucial.
To keep learning and developing your business intelligence and data analysis knowledge - we highly recommend visiting the AccountingBI website.
