Contents


File Formatting


Calxa provides the ability to Export and Import budgets from CSV or Excel files, as noted in the Import and Export Budgets help note. The easiest way to get the correct format for an import template is to export the required budgets from Calxa. The template can then be edited to suit your needs within some guidelines. 


Calxa uses the import template's column headings to determine the format used and the data held in each column. In general, the only section of the import template you should edit is the monthly or annual amounts. You can delete rows and monthly columns as noted in the What else can you Edit? section below, however.


Business Unit Number/Name and Account Number/Name

  • These column headings are required and should not be deleted. 
  • Accounts and Business Units must exist in Calxa first (synced from your accounting data).
  • Both the name and number need to match exactly to what is saved in Calxa for the import to run successfully. Note these fields are case sensitive.
  • CSV export files will be exported with brackets {} around the account numbers (to avoid date formatting issues when opened in Excel), however the brackets are not required to successfully import. 


Business Unit Path and Account Path

  • These column headings are required and should not be deleted even if the columns are empty.
  • In most cases, these columns can be empty. Data is only required in the following instance: 
    • Required when accounts or business units can't be identified by number and name alone.
    • QuickBooks users who don't use account numbers might have multiple accounts with the same name, for example.
  • See the Format Business Unit and Account Path section below for how to format the path. As always, the easiest way to get the correct format is to export first. 


Entering Budget Data

  • Enter budget data into each monthly/annual cell as desired. Note blank cells will be imported as zero. Non-numeric entries will cause the import to fail. 
  • In Excel you may use formulas and number values. Calxa imports what is visible by value (not formula). 
  • In Excel you may use the General, Number, Currency or Accounting for number formatting. General and Number formats are the most reliable as they won't break the format if saved in CSV format. 
  • If you enter budget amounts for header accounts in the import file they will be ignored during the import
  • If you enter budget amounts for nominated Financial Setting accounts in your import file they will be ignored during the import
    • Examples of these types of accounts may include GST Paid/Receivable, PAYG Withholding, Bank & Trade Accounts, Superannuation Payable, Accumulated Depreciation, Company Tax payable.


What else can you Edit in the Rows/Columns?

  • You can add columns to your file without affecting the import as long as the columns are added after the last column you want to be imported. Note you must leave one empty column in between your import data and any additional columns.
    • When reading column headings from left to right, Calxa will stop reading when it reaches an empty header. 
  • Rows can be added below your import grid as well provided you leave one empty row before adding additional content. 
    • Once again, when reading rows from top to bottom Calxa will stop reading when it reaches an empty row. 
  • Only the rows and columns included in the spreadsheet will be updated in Calxa. 
    • Other rows will remain unchanged, therefore the import process can be used to do a partial update. For example, you could update just Wages budgets while leaving the remaining budgets untouched by only including wages in the spreadsheet.


Import Format - example images (Excel)


Monthly Amounts 


Business unit import:


Monthly Business Unit import example



Organisation import:


Monthly organisation import example



Note: CSV export files will be exported with brackets {} around the account numbers, however the account number column can be imported to Calxa with or without the brackets.


CSV export account number format



Annual Amounts 


Business unit import:

 

Annual Business Unit import example



Organisation import:


Annual organisation import example



Error Reporting


The error reporting in Calxa will help identify errors in your budget import template. If you attempt to import a budget that is not in a recognised format or has accounts that cannot be matched you will receive a message that the budget import has failed.


If an error occurs, no rows will be processed and no budget data will be updated in Calxa. You will need to resolve the errors and import the full spreadsheet again. 


Click Download error log to view the import errors. The log file will help you to resolve issues in your budget template.


Budget import failed message



Sample Error Log

Error log



First Error

The first error described above refers to the account name-number pair 4-1000 – sales. To reconcile this error you would open the budget in both your import file and Calxa budget screen to compare the two budgets and diagnose the problem.


Error 1 highlighted


In this case, the error was caused by the word Sales not being capitalised in the import template. To resolve this issue you would simply update sales in your import template to Sales.

Calxa budget screen:

First import error Calxa budget screen


Import template:

First import error - import template



Second Error

The second error relates to the account name-number pair 5-1001 – Cost of Sales. 


Error 2 highlighted


This error was caused by an incorrect account number in the import template. To resolve this issue you would correct the account number in your import template to 5-1000.

Calxa budget screen:

Second import error - Calxa budget screen


Import template:

First import error - import template


Format Business Unit and Account Path


In most cases you shouldn't need to populate the Business Unit Path and Account Path columns for a successful import. These columns are used to identify Business Units or Accounts that are not considered unique based on Number and Name alone. For example in QuickBooks you may not use account numbers. In this case, it is very possible that you may have two accounts with the exact same name. In Xero there is an Unallocated Business Unit name in each tracking category. In these situations you would need to provide a Business Unit Path or Account Path. Note this is only required for the rows that can't be identified as unique.


Once again, the easiest way to get the required format is to export the budget from Calxa first. The Business Unit Path or Account Path will be populated if it is required. But if you would like to add this content manually you can use the information below as a guide. 


For example, lets consider the following account structure where account numbers have not been used.

Motor Vehicle Expenses
    Vehicle 123 ABC
        Fuel & Oil
        Repairs & Maintenance
        Insurance
        Registration
    Vehicle 456 XYZ
        Fuel & Oil
        Repairs & Maintenance
        Insurance
        Registration


In the above example 'Fuel & Oil', 'Repairs & Maintenance', 'Insurance' and 'Registration' are all accounts that can't be identified by the name alone. Therefore to identify them in an import we must provide more information about the full path to each of these accounts. You would represent these accounts in the spreadsheet's Account Path column, with each level separated by a hyphen as illustrated below. 

Expense-Motor Vehicle Expenses-Vehicle 456 XYZ-Fuel & Oil

Expense-Motor Vehicle Expenses-Vehicle 456 XYZ-Repairs & Maintenance

Expense-Motor Vehicle Expenses-Vehicle 456 XYZ-Insurance

Expense-Motor Vehicle Expenses-Vehicle 456 XYZ-Registration 

Expense-Motor Vehicle Expenses-Vehicle 123 ABC-Fuel & Oil

Expense-Motor Vehicle Expenses-Vehicle 123 ABC-Repairs & Maintenance

Expense-Motor Vehicle Expenses-Vehicle 123 ABC-Insurance

Expense-Motor Vehicle Expenses-Vehicle 123 ABC-Registration