From XU Magazine, 
Issue 24

Creating Financial Art

An Insiders look into Scott’s add-in for Xero and Excel

This article originated from the Xero blog. The XU Hub is an independent news and media platform - for Xero users, by Xero users. Any content, imagery and associated links below are directly from Xero and not produced by the XU Hub.
You can find the original post here:

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!

Why leave it there?

To give Scott’s Add-in for Excel & Xero a try

Straight to your inbox

Subscribe to our newsletter for updates as they happen
We hate spam too. We NEVER sell our mailing list.
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.