• Home
  • About Us
  • Add-Ins
  • Templates
  • Pricing
  • Contact
  • FAQ

    Writings, news, and opinions from your friends at Scott's Add-ins.

    Partner Profile – PennyBooks

    What to do, when off-the-shelf forecasting software doesn’t deliver what your client’s need?  This is the situation U.K based, PennyBooks found themselves in.  Existing Xero marketplace forecasting solutions, either didn’t provide the level of accuracy client’s needed, or required way too much data entry on the client’s part.

    Enter Scott’s Add-in for Xero.  PennyBooks used the add-in to develop an Excel forecasting template that can be customized to the individual forecasting requirements of each client.  James Watson with PennyBooks explains further,

    “The add-in allows us to report actual monthly figures against their business plan in robust Excel formats. So ultimately helps them keep track of their performance. It helps us turn around their monthly reporting quickly – it’s also easy to refresh drafts when the numbers are still moving”

    Hat’s off to you, PennyBooks for Creating Financial Art™ !  PennyBooks would like as many people as possible to improve their forecasting via Scott’s add-in.  To that end, they are making available the forecasting template they have developed.  You can download it here.

    More from James about PennyBooks:

    • We like simplicity – that’s in the processes we’ve set up for our clients, client reporting, only using Xero, everything really!
    • We’re trying to provide a better user experience for our clients, with a mix of tech and good client service
    • We’ve put a lot of effort into our bookkeeping processes. We’re big believers that you need to get the basics done quickly and accurately before you move on to reporting
    • We’re not boring accountants – so we should be good to work with!

    Xero multi-currency considerations

    First off, the punchline:  The add-in works well for reporting on Income Statement accounts in a Xero multi-currency environment, not so well for Balance Sheet accounts.

    Why is this?

    The add-in works by summing up Xero journal lines that meet the criteria you specify in the =SCOTT custom function. Contrasting this approach with how Xero does the math to present balances for certain Balance Sheet accounts affected by foreign currency transactions.  To accommodate the unrealized currency gain, Xero uses certain “system” accounts to adjust the balance of certain Balance Sheet accounts.  Unfortunately, these adjustments are not recorded as journal lines, so the add-in is powerless to adjust the balances.

    Here is a deeper dive into the Xero accounts affected by multi-currency:

    810 Bank Revaluations

    A  Xero “system” account that Xero does not give us access to.  A “zero” balance will be reported by the add-in.

    815 Unrealized Currency  Gains

    A Xero “system” account that Xero does not give us access to. A “zero” balance will be reported by the add-in.

    820 Realized Currency Gains

    Works just fine with the add-in.

    Bank accounts

    Xero will show bank accounts that have been “revalued” for changes in currency exchange rates. However, the add-in will always report the Bank balance, using the sum of reconciled transactions in the home currency.

    Accounts Receivable & Accounts Payable

    Accounts Receivable / Payable –  Xero will adjust  the balance of these accounts on a Balance Sheet for transactions in different currencies, when unpaid items exist.  These adjustments are made by Xero, “behind the scenes”, using the system accounts mentioned above. The add-in will always report the account balance in your home currency, which will show a different number than the balance sheet if you have outstanding invoices rendered in a foreign currency.

    So, what to do?

    If your organisation is making use of Xero multi-currency, we recommend you only use the add-in for Income Statement accounts.  Optionally, you can use the add-in for Balance sheet accounts, if you are fine with showing the balances in your home currency, not reflective of unrealized currency gains.

    Orphans: solving for Xero bank accounts with no account code

    When adding a bank account to Xero, the user is not required to assign an account code to the account.  The user can, in the future revisit this decision and assign an account code at a later date.  However, the Scott’s Add-in database does not recognize any historical transactions made to the account PRIOR to the adding of an account code.  We call these orphaned transactions.  This makes pulling accurate balances for these bank type accounts impossible.

    No more!  We have issued an update to the add-in that marries up the orphaned transactions with the new account code that has been assigned.  Now, you can use the add-in to pull in balances for these bank accounts.  So to get started on this:

    1. Make sure you have account codes assigned to all of your bank accounts.
    2. When building your formula, be sure to use a Start Date that is equal to or older than the first transaction date you have in Xero.  This tip is applicable for any Balance Sheet account you are accessing.

    Now you can setup some nifty spreadsheets that show your bank balances by day, week, month, you name it!  

    Create financial art!

    New function: =SCOTT.XTRACKM

    We are pleased to announce the release of a new function:

    For those of you that are new to these pages, Scott’s Add-in for Excel and Xero, is a series of new, custom Excel functions that enable users to dynamically sum up Xero general ledger transactions into a single Excel cell.

    This new function builds on the capabilities to report on Xero journal lines that have Tracking Categories / Options assigned to them.  Our venerable function, =SCOTT.XTRACK , allows the user to select ONE Tracking Category and Option.  The function returns the balance of all transactions that have been coded to that Tracking Category / Option.
    However, Xero allows for up to two Tracking Category / Options to be assigned to a journal line.  For users of Xero that have exhausted the reporting capabilities associated with multiple Tracking Categories, help is here! Take the example of a company that uses (2) Tracking Categories, Region and Product Class to categorize journal entries.

    allows the user to select journal lines that have BOTH of the Tracking Categories / Options you specify.  Here’s the syntax:

    (Organisation ID,Account Code,Tracking Category 1, Tracking Option 1, Tracking Category 2, Tracking Option 2, Start Date, End Date)

    This function will return the sum of all journal lines that have BOTH of the Tracking Categories assigned to them.  Journal lines that meet only one of the Tracking Category criteria will not be used in the calculation.  You would of course use the =SCOTT.XTRACK for this purpose.

    More tips on how to use Scott’s Add-in for Xero Tracking Categories:

    =SCOTT.XTRACKM(org id, account code, category 1, option 1, category 2, option 2, beginning date, ending date)

    will show all transactions that have BOTH of the specified categories .

    If we omit category 2,

    (org id, account code, category 1, option 1,,, beginning date, ending date)

    the function will return “0”.  BOTH tracking categories must be specified

    use =SCOTT.XTRACK for finding transactions that meet have ONE Tracking Category assigned

    If we omit BOTH tracking categories

    (org id, account code,,,,, beginning date, ending date)

    the function will find all transactions with NO tracking code assigned.  This is the same behavior found by using =SCOTT.XTRACK

    *same rules apply for =SCOTT.XTRACKR
    To your success,


    What am I missing

    How to find a missing account code in your spreadsheet.

    When using our add-in to create financial art, sometimes we may inadvertently omit an account. Here’s a quick tip video on how to find that missing account using the =SCOTT.XRANGE function.

    The P&L is Dead

    Traditional financial reports have lost their appeal (if there ever was any) to entrepreneurs. The P&L and Balance Sheet are artifacts created by the accounting profession, designed to be the main outputs of a double entry accounting system.

    While well intended, these financial statements don’t satisfy the entrepreneur’s desire for financial information. The business owner, always pressed for time, and challenged by the format of traditional financial statements, finds it too easy to skip the time it takes to fully digest and interpret the numbers.

    What’s needed is a new financial statement: the Operating Report. The Operating Report has the following characteristics:

    • contains both P&L and Balance Sheet information.
    • contains graphical representations of the data.
    • is customized by the accounting professional to the exact needs of the entrepreneur.

    Operating Reports, are not a one size fits all. It is the job of the accounting artisan, to thoroughly understand the client’s business and ambitions, then create beautiful, financial art, to bring the numbers to life.

    New function =XTRACKR is here!

    =XTRACKR extends our add-in for Xero, by allowing you to specify a range of Xero GL account codes, tracking category, and tracking option. The syntax looks like this

    =XTRACKR (Begin Account Code, Ending Account Code, Tracking Category,Tracking Option,Start Date,End Date)

    Some “use cases” of =XTRACKR include

    • building a matrix P&L with departments or stores in one column, and varying financial periods in the other columns.
    • percentage comparisons from one Tracking Option to another.

    Here’s a two minute video that walks you through the new function.

    Give it a try, and create some beautiful, financial art!

    Our Stand

    Accounting and finance professionals need to rapidly evolve their service offering to small business clients.  Why?  Technology.

    Technology will automate and commoditize the historical service offerings that professionals have relied on to earn income, and live a good life.

    Tax prep, bookkeeping, and more, will become the domain of machines.  Machines, powered by AI will do the work.  The machines will be fed, directly by cloud-based accounting systems.  The machines will make recommendations to the business owner. 

    “Pay this estimate for your taxes.  Would you like me to setup a payment for you?”

    “Keep an eye on this customer’s AR.  Would you like me to text a payment reminder?”

    “Recommend you establish a budget for next year.  Would you like me to generate one?”

    Accounting and finance professionals will need to invent new offers for the entrepreneurs they serve.  This will require the professional to adopt an artisan mindset.

    “What can I do, that a machine cannot?”

    As accounting artisans, we will create valuable offers for the marketplace, by:

    1. Leveraging the data the machines provide us with.
    2. Creating bespoke (custom) financial interpretations (“reporting” or “dashboards” are our current distinctions for this). We offer a new distinction – Creating Financial Art™.
    3. Delivering verbal and written interpretations of the financial concerns of the business.

    These new and powerful offers will need to be customized for each entrepreneur / business.  Adopting the mindset that no two businesses or entrepreneurs are the same, is critical to being a successful accounting artisan.  Looking deep, into the ambition of the entrepreneur, her business model, and marketforces, will allow the professional to deliver a unique and powerful interpretation.  This interpretation will, in turn, allow the client entrepreneur to be successful and live a great life.

    To your success.


    New function =XTRACK is here!

    During our launch of Scott’s Add-ins at Xerocon, we heard from a number of Xero advisors about the importance of having an Excel function that addresses Xero’s Tracking Category capabilities. With this in mind, we have developed our newest function


    =XTRACK extends our add-in for Xero, by allowing you to specify the Xero GL account code, tracking category, and tracking option. The syntax looks like this

    =XTRACK(Account Code,Tracking Category,Tracking Option,Start Date,End Date)

    • if you wish to select journal entries that do NOT have an assigned Tracking Category, simply leave the Tracking Category blank. For example, =XTRACK(Account Code,,,Start Date,End Date)

    Some “use cases” of =XTRACK include

    • building a matrix P&L with departments or stores in one column, and varying financial periods in the other columns.
    • percentage comparisons from one Tracking Option to another.

    Here’s a two minute video that walks you through the new function.

    Give it a try, and create some beautiful, financial art!