RECALC

Updated: January 7, 2023

You will note that we added an additional step to the authentication process.  After the connect window, users are now able to select the specific organizations that will be used with the Excel workbook.  After selecting the organizations, you will only see those organizations under the add-in Organizations tab.

When you save, close, and reopen the workbook,  the previously selected organizations will be maintained.  So, when you subsequently share the workbook with a colleague or client, they will only be able to access data from the selected orgs.  

If you do not wish your client or colleague to be able to recalc the workbook with fresh accounting data, you will need to disconnect the orgs (Organization tab), save the workbook, then share it.

You may have just a few hundred cells in your Excel sheet that contain =SCOTT functions.  Or you may have 400,000. Yes, we do have a user who has that many!  As your use of the add-in grows, and your sheets become larger, you may experience longer recalculation times.  

Here a 5 tips to decrease your recalculation time.

1. When first building your sheet, in Excel Preferences, set calculation to manual.  This will prevent the =SCOTT functions from recalculating as you add new formulas.
2. Close other sheets.  When you trigger a recalc using the add-in, Excel performs a recalc on ALL currently open spreadsheets. Closing sheets that you don’t need access to, will decrease the calc time.
3. Eliminate unneeded cells.  Take some time to review your sheet and see if there might be some =SCOTT cells that are no longer needed.  Delete those where possible.
4. Judiciously use =SCOTT.RANGE functions.  This function requires a lot of CPU processing power due to the initial searching and sorting to determine which account codes fall into the range.  If you just need to specify a few cells in a range, consider using =GL functions and the =SUM command to total those up.
5. Be sure to have a stable, relatively fast Internet connection.  Even though most of the recalc processing is done on our servers, sending the results back to your sheet does require some bandwidth.

Happy recalculating!

To allow multiple QuickBooks users to be connected via the add-in to the same QuickBooks company (organization), simultaneously, you will need to deploy this workaround.  Please create a new QuickBooks user ID with QuickBooks admin privileges.  Your add-in users will use this ID to connect to QuickBooks from within the add-in. For example, create a QuickBooks user addin@abcompany.com .

Not following this process, will result in users getting disconnected from QuickBooks in the add-in, as new users connect.  The disconnected users will receive #VALUE errors in their spreadsheet as they recalc.

We are publishing this workaround, due to the fact that QuickBooks limits connections to one admin user at a given time.

We are pleased to announce the release of our newest custom function for Excel and Xero, =SCOTT.XBUDGET. As you might expect, this function allows you to populate a cell in your Excel sheet with budget data you have entered in Xero. Here’s the syntax:

=SCOTT.XBUDGET (OrgID, budget name, account code, beginning date, end date)

A few things to note:

Xero has you enter budgets in periods (ex. June 2021). We convert the date range you enter in =SCOTT.XBUDGET to include all budget data for the periods the date range covers. For example, if you enter a date range in your sheet of January 1 – February 15, we will sum up the budget entries you have entered in Xero, for both January and February periods.

Be sure to copy the Xero Budget Name into your sheet, exactly as it appears in Xero.

For those of you new to budgeting in Xero, you will notice, budgeting by Tracking Category requires you to create distinct budgets (names) for the various Tracking Category / Options you wish to budget by. For example, if you wish to budget by Department (Tracking Category) and Retail (Tracking Option), you would create a distinct budget for that and give it a unique Budget Name.

You will need to restart Excel to get this function. If the function does not appear after an Excel restart, please remove the add-in, restart Excel, and add the add-in back in.

Happy budgeting!

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!

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

Works just fine with 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.

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!

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