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

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

    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:
    =SCOTT.XTRACKM

    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.

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

    (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,

    Scott

    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.

    Scott

    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

    =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!