Accounting Export

Currently, only the Quick Books, Simply Accounting and Vadim file formats are supported. File formats for all exports are text files that may be imported into other General Ledger systems. If your accounting software is not any of these three but accepts entries from a text file, the Simply Accounting format may work.

Warning: For Simply Accounting, you must have a version higher than “Entrepreneur” to import text file transactions.

After all revenue account numbers have been set up in the Product Table the procedure for exporting transactions is just 3 steps:

Enter a date for “Export All Transactions On or Before Date” field. This field get automatically filled with the current date.
Press the “Print/View/Export” button and print the report. Check this report to be sure all accounts are accurate.
Press the “Finalize and Create Export File” button. This will create the file you need to import into your accounting software.
Start up your accounting software and import the file created in step 3.

WARNING: The FIRST time you run this export, the report and the Export file WILL contain ALL previous receipt records in your receipt history file. This is normal. The reason is because the system sees all these transactions as not being processed. You should produce the report and create the export file. This process will flag the transactions as being processed and they will not appear on future exports.

This first file should NOT be imported into your Accounting Software unless it contains only valid transactions. Delete the file if these are old transactions.

Some preliminary work MUST be done prior to using this program. This need only be done once to establish your accounts.

Before using the Accounting Export utility for the first time you must go to the “Payment Types” table on the Tables menu and review ALL payment types. All that needs to be changed or edited is the “Accounting Fund Type” field. The payment method codes and descriptions do NOT need to change because these are not sent to your accounting software.

A field called “Accounting Fund Type” MUST be verified/changed for each payment type you have. Each payment type is categorized as either “Un deposited Funds” or “Deposited Funds”. Credit Card and Direct EFT bank deposits are usually “Deposited Funds”. All others, such as Cash, Checks and CD’s are “Un deposited Funds”. Your Accounting Software, (Quick Books), may distinguish these two types of fund payments and therefore each payment type must be set. If your Accounting Software does NOT distinguish between these two types of funds, you can flag them all as “Deposited Funds”.

Accurate spelling of your account assignments is very important. The account names or numbers MUST match exactly with the account names/numbers in your Accounting Software. Pay attention to UPPER and lower case letters and spaces when typing in the account descriptions. If you print out the Account Listing and any sub account listing from your Accounting Software this will serve as a reference for entering your account names.

Quick Books uses account names instead of numbers. With Simply Accounting Premium or higher you can use 4 digit accounting numbers followed by a 4 digit department number (eg. 4080-0200).

You may prefer to set up a completely new set of accounts in your Accounting Software and use these within the Fitness software. This is not necessary, but is an option that would separate all these new transactions and allow you to easily trace any problems within your accounting system.

The Vadim Accounts are made up of FOUR segments. FUND(00), CLASS(0), CATEGORY(0000) and OBJECT(000). Each segment may vary in length depending on the VADIM setup.

A typical account may look like this: 01|1|0032|015 (Each segment separated by a “|” (Bar) character.

This is how the accounts MUST be defined in the Product Table and Accounting Export screen.

NOTE: Failure to insert the “|” character into the full account will result in invalid export files. No “|” character should be at the beginning or end of the account.

Before using the Accounting Export utility for the first time you must go to the “Product Codes” table on the Tables menu and review ALL Product Codes. All that needs to be changed or edited is the “Revenue Account” field.

A field called “Revenue Account” must be filled in for each Product Code. A default drop down list is provided with some common account codes for you to choose from. You can select from this list or enter your own Account Descriptions. Select a Revenue Account for each of your Product Codes. Many will probably be the same.

For Quick Books users, you MUST select a description from your Quick Books “Account Listing” report. Account names must be entered using the EXACT spelling that Quick Books has on the Account Listing report. (eg: “Sales:Merchandise” or “Sales:client/member Fees” Any accounts that are NOT spelled correctly, will be created as NEW accounts in Quick Books.

If you enter an account that is NOT on the drop down list a message will ask if you want to add it to the list. Added accounts will be available from the drop down list.

To DELETE an account from the drop down list; select the account and press the GARBAGE icon button at the end of the field.

NOTE: If the Account does not exist in QuickBooks or is spelled wrong a NEW account will be created.

Some preliminary fields MUST be set up on the Accounting Export screen prior to the first export.

Accurate spelling of your account assignments is very important. The account names MUST match exactly with the account names in your Accounting Software. Pay attention to UPPER and lower case letters and spaces when typing in the account descriptions. If you print out the Account Listing and any sub account listing from your Accounting Software this will serve as a reference for entering your accounts.

Accounting Software: Only Quick Books, Simply Accounting and Vadim accounting software are supported at this time.

Undeposited Funds Account: This is the Account Description found in your Accounting Software. Usually used for Cash, Checks and CD’s. If your Accounting Software does not use this category, enter your Bank Account Description or number.

Deposited Funds Account: This is the Account Description found in your Accounting Software. Usually a “Bank Account” or “Current Account” description or number will be used here. This would be for Credit Cards and Direct EFT deposits.

Membership Revenue Account: This is the default revenue account found in your Accounting Software for Membership Sales. This will only be used if your Product Code table for client/member Fees does not have an account assigned or if the receipt does not have a product code entered.

Product Revenue Account: This is the default revenue account found in your Accounting Software for Product Sales. This will only be used if your Product Code table does not have an account assigned for the product code used or if no product code is used in the receipt history.

Level One Tax: This is the First tax payable account found in your Accounting Software.

Level Two Tax: This is the Second tax payable account found in your Accounting Software. This account may be the same as Level One Tax Account.

Export Folder Location: This is the folder location on your hard disk drive, where the export file will be located. The system will enter a default location of “C:\Tmp” for you. You can leave this unchanged or change it to something else. You will need to remember this or write it down so you can locate the export file from within your Accounting Software. You can use the “BROWSE” button to locate the folder you want to use, if you want to change the default folder.

Export File Name: This is the file name used when the system creates the export file. For Quick Books the file name MUST end with “.iif”. The system will enter a default file name of “Fitness_trns.iif” for you. You can leave this unchanged or change it to something else. You will need to remember this or write it down so you can locate the export file from within your Accounting Software. The Simply Accounting and Vadim file extension is “.txt”.

Previous Export Date: This is the date of your previous export. The first time the export is run, this date field will be blank. You cannot change this field; it is only for information purposes.

Export All Transactions Before: This is the cut off date for the transaction export. All receipt transactions that are dated before this date will be exported to the Accounting Export file. This date will default to the current system date. You may have to change this date if you want to update only up to a prior date, such as the end of a previous month.

This first file should NOT be imported into your Accounting Software unless it contains only valid transactions.

Print/View/Export Button: This button will prompt you for a printer selection where a report of all transactions will be printed. Two reports are produced. One is in order by Payment Method. The other is in Account Number order. Check the report to be sure it has what you expect to be exported. You MUST print the report or the system will not allow you to create the Accounting Export file.

Finalize and Create Export File Button: This button will create the Accounting Export file in the location specified and mark all the receipts as being updated so they cannot be updated twice.

If a previous Accounting Export file exists in the Export Folder, a message will ask if you want to delete the old file and create a new one. The normal answer to this, is the “YES” option. If you answer “NO” a message will ask you if you want to add transactions to the existing Accounting Export file. If you answer “NO”, the program will terminate without updating.

A message that the file was created will be displayed after the update is complete.

The Accounting Export file is now created and ready to be imported into your Accounting Software. You now can start up your Accounting Software and import the file.

If your Accounting Software is on a different computer, copy the Export file to a USB memory device and take it to that computer. If you use a communication utility, like PcAnywhere, upload the export file to your accounting system computer and import it there.

It is good practice to MOVE the export file to another archive folder or delete it after you have imported it into your accounting software. This will prevent overwriting the file by mistake in future exports.

This button will start up your spreadsheet application, such as Excel, and load the Export file(Fitness_trns.iif) so you can review it.

WARNING: DO NOT SAVE THIS FILE AS AN EXCEL FILE OR QUICK BOOKS MAY NOT BE ABLE TO IMPORT.

If you get a Windows message that the system doesn't’t know how to open this file, select the option to “Pick from a list of program”, then select “Excel” or your regular spreadsheet program. After you do this once, windows should remember this for the next time.

The button allows you to reset the update flags in the receipt history so you can do the previous update again. This would only be used if you lost the Export file from the previous update or you reset some of the accounts and want to generate the same export file again.

The Reprint button allows for the repirnt of any previous accounting export report, based on the Export Date.

The Quick Books Export file that is created is a plain text file with tabs between each field. This means you can open it with any spreadsheet application, like Excel, and view the contents. The file name always ends with “.iif”.

Using My Computer or Windows Explorer locate the Export file, usually called “Fitness_trns.iif” and double click to open. Your system may ask for the program to use to open this file; select Microsoft Excel, or some other spreadsheet program and you will be able to see and/or modify the contents.

Warning: If you do modify this file, be sure to save it as TEXT and not in Excel format, or Quick Books may not be able to import it properly.

For the first several times you use this export you may want to review this file prior to importing it into Quick Books, to verify that everything looks OK and the accounts are accurate for each transaction.

To import this file into Quick Books start up the Quick Books application and select Sales Invoice/Receipt, then from the File Menu button at the top of the screen, select the Import option. Then select “Import iif Files”. This will bring up a file selection window where you can navigate to where the import file is located. The default location is the “C:\Tmp” folder. When you find the “Fitness_trns.iif”, highlight it using the left mouse button then press the OPEN button. The import process will start up and take between 30 seconds and several minutes to complete. After this completes you will get a message that the import is complete.

If Quick Books cannot import the file an error message will be displayed and should be resolved by either editing the import file or reviewing the accounts setup in the Fitness application and recreating the export file. Depending on what the error message is, Computerease support may be able to assist or advise a course of action.

All transactions are coded as “CASH SALES” in the iif file. This is to ensure that Inventory is updated properly without corrupting the data.

If a credit entry (reversal) receipt is updated the Bank Account is changed to UnDeposited Funds. QuickBooks will NOT let a CASH SALE go to a Bank Account. For accounting purposes a journal entry may need to be posted in QuickBooks once a month to move the balance in Undeposited Funds to your Bank Account… but this depends on how your books are set up.

“Warning: Error on Line 7: Can’t record invalid transaction (same warning for line 14, 21, 28, 32, 36, 40, 44….)”

The Federal/GST Tax Account field and the State/Prov Tax Account field on the Accounting Export screen MUST match your Quick Books tax account description? It cannot be left BLANK.

If you want to fix the import file you created, load it into Excel and replace all the entries labeled “Sales Tax Payable” with a valid description in your Quick Books.

If you get an error message stating: “Warning: The tax field cannot be left blank, even for non-taxable customers and sales which have no taxable items…”

This is caused by an extra “ENDTRNS” line in your import file that doesn’t contain any data. If you load your import file into Excel and see any lines that have “ENDTRNS” followed by “ENDTRNS” on the next line, delete ONE of these. There MUST only be ONE “ENDTRNS” between each transaction. This will fix the file.

The Simply Accounting file that is created is a plain text file ending with “.txt”. This means you can open it with Notepad and view or modify the contents.

Using My Computer or Windows Explorer locate the Export file, usually called “Fitness_trns.txt” and double click to open. Your system may ask for the program to use to open this file; select Notepad, or some other text display program and you will be able to see and/or modify the contents.

For the first several times you use this export you may want to review this file prior to importing it into Simply Accounting, to verify that everything looks OK and the accounts are accurate for each transaction.

In the Simply Accounting application select the “File” option from the top menu bar. Then select “Import Export”; then “Import Transactions”. If you do NOT see this selection you may have an older version of Simply Accounting or a version that does not support this type of import.

On the “Import Transactions Wizard” select “Import general journal entries into Simply Accounting” and press the “Next” button.

A Backup Warning will be displayed. Press the “Next” button, if you already did a backup.

Select the import file. Press the “Browse” button and navigate to the folder where the Accounting Export file is located. (eg. C:\Tmp) and select the “Fitness_trns.txt” file and press the “Open” button.

If there are any problems with the import file, a description of the problem and the line will be displayed.

If the transaction file is OK all transactions will be imported and a verification message will be displayed showing how many were imported.

The Vadim Accounting file that is created is a plain text file, bar delimited, ending with “.txt”. This means you can open it with Notepad and view or modify the contents.

For the first several times you use this export you may want to review this file prior to importing it into the Vadim Software, to verify that everything looks OK and the accounts are accurate for each transaction.

The Vadim Accounts are made up of FOUR segments. FUND(00), CLASS(0), CATEGORY(0000) and OBJECT(000)

A typical account may look like this: 01|1|0032|015 (Each segment separated by a “|” (Bar) character.

This is how the accounts MUST be defined in the Product Table and Accounting Export screen.

NOTE: Failure to insert the “|” character into the full account will result in invalid export files. No “|” character should be at the beginning of end of the account.

To test the import option select the “Process Type “Report Only”. This will provide a report of any error and will not update the GL.

When importing transactions choose the option to “Import with Detail Description”. This will update the detail description from the Receipt History. This description includes the Receipt Date, Receipt Number, Member Name, client/member Number and Receipt Line Description.

How do I import transaction data into QuickBooks 2008 and later using IIF files?

Caution: Make sure all accounts in your IIF file are created in QuickBooks prior to importing. If you import a transaction with an account that is not already created in the QuickBooks data file, that account will be created as a new bank account.

As of QuickBooks 2008, QuickBooks uses a new database format and a new sales tax system. These allow for several benefits, including greater flexibility in your company setup and more efficient access to the records in your company file.

With current and previous versions of QuickBooks, you can create transaction data in an IIF file (typically with a spreadsheet application), then import that data into your company file. However, because these data fields are coded differently from previous versions of QuickBooks, there are significant differences in how sales tax information is processed from imported IIF files.

· In previous versions of QuickBooks, sales tax codes could be assigned to each SPL line item in the IIF file for an exported transaction.

· In QuickBooks 2008, sales tax codes assigned to line items in this fashion will not be imported. Instead, the IIF file must include sales tax items as separate line items in the body of the transaction.

When creating an IIF file for a bill, the bill must be item-based. That is, SPL entries in the IIF file must specify the item name in the INVITEM column. If you use an expense account, sales tax amounts will be posted as an uncategorized amount, since no sales tax item is associated with them.

Then, list the sales tax items on additional SPL lines, identified with AUTOSTAX in the EXTRA column. You do not assign tax to accounts any more: you instead assign it to sales tax items.

Make sure you correctly specify the NAME (sales tax agency vendor name), ACCNT (account name), and INVITEM (item name) for the sales tax item. These must exactly match existing entries in your company file. For example, you must have an account with the name of ACCNT, and a sales tax item with the name in INVITEM.

As long as columns for sales tax item SPL lines are correctly indicated — and the EXTRA column has AUTOSTAX — the sales tax amounts will be correctly imported. The amounts posted to the sales tax items will carry through to all reports and the File Sales Tax window, and will not result in uncategorized transactions.

For example, the following IIF file (illustrated as a table) is for a simple bill for an item named "PurchItem", bought for $100 with $5 GST from a vendor named "ItemVendor":

When creating an IIF file from scratch, you would typically create a table like the above in a spreadsheet application, save the spreadsheet as TAB-delimited text file, and rename the file to have an .IIF file name extension. For more details on creating an IIF file for importing transactions, search the in-product help for the "Creating an import file".

The following items were incorrectly specified within the description of Transactions ( TRNS and SPL) IIF files in the in-product help. If your IIF file does not import correctly, check the following:

· Dates (such as in the DUEDATE and DATE columns) use the format MM/DD/YYYY, not DD/MM/YYYY.

· When entering the EXTRA column in an SPL line, using AUTOSTAX identifies the item as a sales tax item.

The IIF file illustrated above can also be imported into QuickBooks 2007. However, in QuickBooks 2007, sales taxes on bills show up as a negative tax on sales, rather than a positive tax on purchases. This can be fixed by editing the general journal entry for the transaction.

If you have any questions, email us at support@computerease.ca or call Computerease at 1-800-201-2996 (International: 1-902-465-1122)

!TRNS	TRNSTYPE	DATE	ACCNT	NAME	AMOUNT	CLEAR	TOPRINT	DUEDATE	TERMS
!SPL	TRNSTYPE	DATE	ACCNT	NAME	AMOUNT	CLEAR	QNTY	INVITEM	EXTRA
!ENDTRNS
TRNS	BILL	7/16/2008	Accounts Payable	ItemVendor	-105	N	N	8/15/2008	Net 30
SPL	BILL	7/16/2008	Inventory Asset		100	N	1	PurchItem
SPL	BILL	7/16/2008	GST/HST Payable	Receiver General	5	N	1	GST (ITC)	AUTOSTAX
ENDTRNS