A common question from Accounting.BI users is:
How do I sync the Accounts Transactions report from Xero or Zoho Books into Excel or PowerBI?
For those users that are not familiar with downloading this report from the Xero or Zoho Books, the Account Transactions report is essentially the report that contains every nominal ledger transaction that makes up the general ledger of either bookkeeping system. To put it plainly, the Accounts Transactions allows you to download every transaction from the system, and reconcile this back to your Trial Balance, Profits and Loss and Balance Sheet reports.
The Account Transactions report data is obtained via the Journals (Account Transactions) report in Accounting.BI. For the avoidance of doubt, the Journals Report in Accounting.BI contains all transactions of any transaction type, not just Manual Journal transactions. If it is not already obvious, it is important to note that the Journals (Account Transactions) report contains are high volume of data. It is very important that you understand how your Excel and PowerBI file can consume this volume of data without crashing your reports.
Our explainer video below details how you can use Power Query to manage this data when building your management accounts:''
Connecting Your General Ledger from Xero or Zoho Books to Excel Using Power Query
First, make sure your Xero organisation is connected in Accounting.BI. This guide will walk you through obtaining the data and syncing it to Excel in a way that won’t overwhelm your file. Excel has limitations on the amount of data you can input into a single tab, but by using Power Query, which can handle larger datasets, you can manage the information effectively.
Here’s how to proceed:
- Connect to Accounting.BI: After connecting your Xero or Zoho Books account, go to the ‘Connectors’ section in Accounting.BI. You’ll need to take note of the bearer token, which functions as a password for your Accounting.BI connectors.
- Prepare Excel: Open a blank Excel file. Navigate to ‘Get Data’, select ‘From Other Sources’, and then choose ‘Blank Query’. This will open Power Query.
- Input Advanced Code: In Power Query, go to ‘Advanced Editor’. While this may appear complex, Accounting.BI has already created the necessary code for you. Simply replace the highlighted text in the code with the correct details: paste your bearer token beside "bearer", and insert the journal’s endpoint below the token.
- Fetch Data: Power Query will now access the Accounting.BI system and retrieve the required data. Convert this data into a table within Excel and expand it into columns. Given the volume, this step might take a while as it fetches all records.
- Load to Data Model: Once the data appears in columns, click ‘Close & Load To’ and select ‘Only Create Connection’, then ‘Add this data to the Data Model’. By doing this, you avoid overloading Excel’s tabs, while Power Query handles the large data set more efficiently.
- Sync and Refresh: With a live connection established, you can easily sync and refresh your data as needed. Accounting.BI allows for quick updates, grabbing only the latest changes from Xero, making subsequent queries faster. You can also set up auto-refresh options within both Accounting.BI and your Excel spreadsheet.
- Build Your Reports: Now that your data is in the Data Model, you can start building reports such as a Profit & Loss statement using a Pivot Table. Insert the Pivot Table from the Data Model into your existing worksheet, and begin selecting fields like date, account names, and amounts to shape your reports.
For further assistance, you can explore ready-made templates on our website at www.accountingbi. These templates can help you get started with constructing reports from your data if you're struggling.
Ready to start your journey? Contact us to get started!
