Scottʼs Add-in for Xero and Excel extends reporting capabilities for Xero users that have outgrown the financial reporting that Xero offers, but want something with greater flexibility than is offered by out-of-the-box reporting apps.
The add-in takes an approach that is uniquely different from other apps that integrate Xero data with your Excel spreadsheet.
Whilst other apps add raw data into the Excel sheet for further analysis, Scottʼs add-in provides custom functions for Excel that can perform calculations on data within a single cell.
Once you’ve connected your organisation you get a helpful sidebar with guidance on what each of the available functions does.
The custom functions, such as =SCOTT.XGL which returns an account balance for a specific date range, allow the user to build bespoke financial models, uniquely suited to individual clients using the flexibility offered by Excel.
For those that love their Excel spreadsheets for reporting, Scott’s Add Ins removes the need to repeat the process to account for new data. Just build your spreadsheet one time using the custom functions, then recalc the sheet as your Xero data changes. In seconds, the workbook is refreshed with the most current Xero data!
The add-in is great for building customized P&Lʼs and Tracking Category analysis for high level overview, but is less suitable for analysis that involves investigation of individual transactions, projects, inventory, or contact data.
It is possible to create an Excel workbook that has multiple sheets assigned to different organizations, to facilitate comparisons and consolidations.
We’ve had exclusive access to the development roadmap for Scottʼs add-ins. The two most exciting features to come are Budgets, and Drill Down.
When Xero completes work on their new Budgets API, Scottʼs will add a new function =SCOTT.XBUDGET. Using this function, users will be able to pull Xero budget figures into their sheet by ledger account and Tracking Category / Option.
The Drill Down function, a much requested user enhancement, will allow users to drill down to Xero journal detail to see the specific journal entries that comprise the sum total showing in the Excel cell. Great for auditing your work!
Due to limitations within the Xero platform, the add-in cannot report balances for the Xero accounts; Bank Revaluations and Unrealized Currency Gains. Realized Currency Gains are reported fine by the add-in.
So, to sum up, Scottʼs add-ins provides an easy to use solution to get real-time Xero data into Excel to be used in any way the user sees fit!
Here is a link to a short video that shows the add-in in action.
The Scott functions:
=SCOTT.DESC – Returns the account description for a given account code.
=SCOTT.XGL – Returns the sum of all transactions for a user specified date range.
=SCOTT.XRANGE – Returns the sum for a range of account codes.
=SCOTT.XTRACK – Returns the sum of an account code for a Tracking Category / Option.
=SCOTT.XTRACKR – Returns the sum for a range of account codes for a Tracking Category / Option.
=SCOTT. XTRACKM – Returns the sum of account transactions that have BOTH Xero Tracking Categories assigned.
As an example, the syntax for =SCOTT.XGL
=SCOTT.XGL (Organisation ID, Account Code, Start Date, End Date)
Sign up to our newsletter - don't miss out on all the good stuff.
Latest news, events, and updates on all things app related, plus useful advice on app advisory - so you know you are ahead of the game.
Connect with us
