How to import tax codes when importing invoices or sales receipts

Lisa Moulden

I am using an IIF file to import invoices into Accounts online and the only issue I seem to have is with recording GST.  Is there a way to record the tax code against each disbursement line or the header line in the import file?
Thanks for your assistance.

Mirko

Hey Lisa,

Thank you for reaching out and welcome to the Reckon Community!

RA does offer an in help menu, if you type IIF, in the search it will provide you with information regarding import files and accepted formats.

Regarding Tax Codes it would be best to speak with one of our Accredited Partners, who are Reckon Product experts as well as Tax Professionals.

Locate a Partner: http://home.reckon.com.au/support/Home/ReckonSupport/FindAReckonAccreditedPartner.aspx

If someone in the community has any experience, their wisdom would be greatly appreciated I’m sure!

Thanks,
Mirko

Graham Boast

Hi Lisa

I suspect that you need to add an additional column TAXABLE in the !SPL header.  The data value for this column on all SPL lines of the transaction is Y , irrespective of the taxcode.used.  

Here is an example iif.  It is for Invoice #9994, dated 31/3/2014, due 30/4/2014.  The item used is Cakes that is linked to Income Account "Sales".  The invoice is for $220 = $200 + $20 GST.  I have set TOPRINT = Y so they can be printed after import, but this can also be set to N .  You can also set a "TOSEND" variable (Y or N) if you want to email the Tax Invoice after import.

!TRNS,TRNSTYPE,DATE,DOCNUM,NAME,DUEDATE,AMOUNT,ACCNT,MEMO,TOPRINT
!SPL,TRNSTYPE,DATE,DOCNUM,NAME,DUEDATE,AMOUNT,ACCNT,MEMO,INVITEM,TAXABLE,TAXCODE,TAXAMOUNT
!ENDTRNS
TRNS,Tax Invoice,3/31/2014,9994,Customer Name,4/30/2014,220,Accounts Receivable,Invoice Memo,Y
SPL,Tax Invoice,3/31/2014,9994,Customer Name,4/30/2014,-200,Sales,Line Description,Cakes,Y,GST,-20
ENDTRNS

If you can't get it to work, email me the file and I can have a quick look at it for you.

Graham
graham@reckonhelp.com.au

Sally McIntosh, Accredited Consultant

Hi Lisa,

Graham is an expert at importing files, so definitely contact him if you are having issues.

Kind regards,

Sally

Lisa Moulden

Hi Graham

Thank you, thank you, thank you!  Ensuring each line had TAXABLE set to Y and adding in the TAXCODE and TAXAMOUNT headings to the SPL lines has worked.  It was these 2 headings that I couldn't find any information on in the help sections online.

You have saved me from much frustration in searching for an answer to this problem I was having and many hours of data entry.

Thank you again!!

Kind regards
Lisa

Inigo

Hi Lisa,

Great to see a solution from Graham that works for you.

For other users not as confident with creating their own import text files, the can consider using some 3rd party utilities that may be better suited to their needs and technical proficiency.

Zed Systems - Axis Importer
http://www.zed-systems.com/products/axis.aspx

Baystate Consulting - QuickBooks Transaction Pro Importer
http://www.sleeter.com/blog/2012/02/quickbooks-transaction-pro-importer/


-IM

Lisa Moulden

Just wondering do those 2 different 3rd party utilities work with Accounts Hosted Online?  

- Lisa

MichMulder

Hi Inigo - we are having a similar problem with Tax codes not loading - but it's with uploading of Credit Card purchases - once loaded the transactions default to only the 'taxable code' instead of retaining the various tax and tax free codes as per the spreadsheet.  We are using Transaction Pro, and they have said it's a QuickBooks problem.  Obviously the transactions are sometimes multiple lines, so it's important that the tax code loads correctly. 

Michael Damico

OMG - thank you thank you thank you thank you

I have been trying to bulk upload cheques with part GST on split lines and had no luck from the documentation

!TRNS TRNSID TRNSTYPE DATE ACCNT NAME AMOUNT MEMO
!SPL SPLID TRNSTYPE DATE ACCNT AMOUNT TAXABLE TAXCODE TAXAMOUNT  MEMO
!ENDTRNS
TRNS 101 CHECK 01/01/2014 ANZ Cheque Vendor Name -300 MemoDetails
SPL 102 CHECK 01/01/2014 Loan:Account1 200 N   MemoDetails
SPL 103 CHECK 01/01/2014 Interest 198 N   MemoDetails
SPL 104 CHECK 01/01/2014 Direct Debit Fees 2 Y NCG 0.18 MemoDetails
ENDTRNS

I have written a Excel macro template that will export a list of cheques to iif files - perfect for importing amortisation schedules

if anyone wants it email me - mickdamico at Hotmail dot com

Jacqui Allen

Graham I'm hoping you can assist
Thanks to you I have successfully  imported invoice's however the third party  can only export net amounts + tax, this is causing us grief as generally line entries are rounded ie gross $16, net being $16.5454 and Tax $1.45455 no matter what I do I can NOT import msg say's out of balance.
Can you help!!
 

Graham Boast_8683921

Can you calculate the tax? So for a given net of 14.5454 then tax = round (net * 1.1,2) - net. That will return 16 - 14.5454 = 1.4546. Graham

Graham Boast_8683921

Also, try changing the tax from 1.45455 to 1.4546 as a test. Remember also that Debits are positive, credits negative and the sum of the amount + taxcode columns for TRNS and SPL lines should equal zero. If this is a Tax Invoice, the TRNS line should show 16.00 I'm AMOUNT and the SPL line -14.5454 and -1.4546 in AMOUNT and TAXAMOUNT respectively

Jacqui Allen

I'm trying to minimize any manipulation for the client as there is over 147 invoices many with multiple line entries.  I think the problem is with the third party software and what they are exporting. The client has always used amount included tax in RA however the other party requires the net price?? and of course initially they were importing GST  as one line entry being 1/11 of the total invoice however this doesn't sit well with GST reporting and or QuickBAS in RA as it treats the net income as GST free income and the GST is not reflected at all.  I've attached a screen shot of the transaction journal.

Graham Boast_8683921

Can you s not me the import file and I can look at it for you. graham@reckonhelp.com.au