‍Why do accounting and finance professionals love Excel?  Because Excel allows us to always say “yes”.  Yes, I can:

• Add that chart to your P&L.
• Show how all of our business units are performing.
• Build a custom dashboard, unique for our industry.
• Yes, yes, and yes!

Excel also allows us to channel our inner artist.  To be creative with financial design.  To convey our interpretation of past financial results, or how the future might turn out.

Excel allows us to paint on a blank canvas, not constrained by pre-built dashboards or software.  Excel is an opportunity to build our identity with others:  clients, owners, shareholders, board members.  To build our identity in the marketplace as a finance professional.

So why don’t we use Excel more for financial reporting and analysis?

Excel simply doesn’t play well with accounting system data.  Accounting system data is structured to serve the needs of the accounting system, not Excel.  Sure, we can export, import, change columns widths, remove unwanted data, get the spreadsheet looking good. Then the accounting system data changes.  Rinse and repeat. Painful.

What If

What if you set about to change how Xero and Excel work together?  What if you set about to design the best way to seamlessly integrate Xero ledger data with an Excel spreadsheet?

This was the journey we embarked upon twelve months ago.  Here’s an insider’s look at how we thought about the project, and our solution, Scott’s Add-in for Excel & Xero.

The Vision

Empower accounting and finance professionals to be creative, and build their identity in the marketplace, as innovators.

Must Haves

• Integrate with Xero at the journal / transaction level, allowing for date driven calculations, and future functionality such as transaction drill down.
• Zero user training required (pardon the pun).  Users should be up and running in minutes, not hours.  The integration should feel like a logical extension of Excel.
• Fast.  When recalcing a workbook, new Xero data is reflected in seconds, not minutes.
• Support for multiple Xero organisations within the same Excel workbook.
• Date sensitivity.  Pull balances for any date range the user specifies. A day, week, month, quarter, year.
• Extensibility.  Structure the code such that future functions ( ex. budgets) and new spreadsheets (GSheets, SmartSheets) could easily be spun up in the future.

The Solution

Build custom functions for Excel, that are intuitive for accounting and finance folks.
=SCOTT.XDESC - Returns GL account description
=SCOTT.XGL - Returns GL account balance
=SCOTT.XRANGE - Returns sum of a range of account balances
=SCOTT.XTRACK - Returns GL account balance for a tracking category and option

Optimize Xero journal transactions for a super fast recalc.

Hello Xero ecosystem.  We’re here.  It’s nice to meet you!