Journal imports: how do I specify the GST?

Clément Nedelcu
Clément Nedelcu Member Posts: 7
Hello,

I apologize in advance -  I am just an IT guy with no understanding of Quickbooks, Reckon, or accounting whatsoever. I have been tasked with a seemingly simple operation: importing journal entries from invoices and receipts coming from a third party application. The version is Reckon Accounts Premier 2016. I have learned everything I know about Quickbooks over the past few weeks but my knowledge is still very limited.

I have been trying for the past few weeks to import journal entries to achieve a result that would be similar to the example on the screenshot below:

image

- a debit line of 10
- a credit line of 9.10 with GST amount 0.90

This transaction was imported into Quickbooks by another application using the Quickbooks XML SDK. I am trying to replicate the same behavior but so far, I have failed.

Unfortunately, no matter what fields I use or what data I import, I am unable to get Quickbooks to import a "Tax Item" for my transactions. I can set a tax amount of 0.9, but the tax item is always ignored, and I am forced to enter a credit amount of 10 (instead of 9.10) otherwise Quickbooks says my transaction is not in balance. I assume both issues are related: if my tax item was properly recognized, I would probably be able to enter a 9.10 credit amount and the 0.9 would be added into the calculation.

image

This is my import data:

image

I have also tried to import two separate credit lines:
- one credit line of 9.10 assigned to the sales account (4200)
- one credit line of 0.90 assigned to the tax account (2201)

When I do this, I get an error message from Quickbooks:

image

I am not sure what this means or where to look. This is what the tax settings window looks like:

image

For the credit line of 0.9 going to the tax/GST account (2201) I also tried inserting a "TAXCODE" column to make sure Quickbooks knows that the credit amount is going to the GST. If I do this, Quickbooks returns a different error message: "The taxable setting in tax code GST has been changed. This cannot be imported". No idea what this means either.

I am starting to feel like this is a dead-end. I must lack basic understanding of how taxes are handled in Quickbooks, but none of my colleagues are able to help out. I have tried reaching customer support at Reckon and they started by repling "sorry, journal entries cannot be imported" (I kid you not). Then after insisting they said they could not assist.

I have tried the following methods and all have failed:
- CSV and IIF imports within Quickbooks
- 3rd party tools such as Transaction Pro Importer
- QODBC - inserting journal entries straight into database

None of them have worked. Does anyone have any idea what I'm doing wrong? :-(

Comments

  • Adrian_6974554
    Adrian_6974554 Member Posts: 191 ✭✭
    edited December 2017
    Looks like you are missing the tax code.

    The Tax Item is a code not the GST amount.
    Income codes
    GST
    FRE

    Expense codes
    NCG
    NCF
    CAG
    CAF


    Adrian

  • Cl√©ment Nedelcu
    Clément Nedelcu Member Posts: 7
    edited December 2017
    Hi Adrian,

    Thanks for the help. I have tried the following:

    image

    It is producing exactly the same result, the taxcode and taxitems are both ignored. The journal entry produced still does not have GST.

    image



  • Cl√©ment Nedelcu
    Clément Nedelcu Member Posts: 7
    edited December 2017
    Hi Kevin, thanks, I have emailed you just now.
  • Cl√©ment Nedelcu
    Clément Nedelcu Member Posts: 7
    edited December 2017
    HOLY COW it worked. Right now, I have no idea what's different, I just see a bunch more fields in the sample import file you sent me, and I don't really know what they mean, but I did it ! I will do some further testings to see what column I was missing in my original IIF file, but thats not important right now. IT WORKS!

    image

    Kevin you have no idea how thankful I am right now. This is like a Christmas miracle. Thanks Santa! Been at this for weeks!
  • Cl√©ment Nedelcu
    Clément Nedelcu Member Posts: 7
    edited December 2017
    Just a quick extra question. I have no idea if this matters, but in the transactions imported by the old software, the debit line came before the credit. So you would have: +110 on the first line (debit) and  -100 and -10 (tax) on the credit line. 

    On the above screenshot, you can see that I have been able to generate a similar transaction, but with the credit line coming first. The reason is because in the sample file you sent me, Kevin, the credit line came first and all I did was edit the amounts & the destination GL accounts.

    This is what I'm able to import:

    image

    Now, I tried swapping the debit & credit lines. This is what I did:

    image

    Basically, I changed the first colum (TRNS/SPL) and the second one (49/50); left everything else as is. Also swapped the IIF header lines appropriately (I think).

    But if when import this, Quickbooks says that my transaction is not in balance (going back to my original issue, tax is probably being ignored).

    I think it probably has something to do with the extra columns - I dont really know what they mean or how to use them. Do you have any clues?
  • Cl√©ment Nedelcu
    Clément Nedelcu Member Posts: 7
    edited December 2017
    OK, I have simplified the import file down to two scenarios

    THIS WORKS:

    image

    THIS DOES NOT WORK (transaction not in balance)

    image

    Any ideas? Am I missing something?

    I could be happy with the first one, but then how is it even possible to invert the order of the lines ? how could the old software import those two lines in reverse order?

  • Cl√©ment Nedelcu
    Clément Nedelcu Member Posts: 7
    edited December 2017
    Well, we're almost there. I dont know if my client is going to complain about the order of lines. I hope not... but still, I wonder what I'm doing wrong!
  • Virginia Buttula
    Virginia Buttula Member Posts: 1
    edited January 2018
    Hi Kevin

    I have also been trying to import a data feed off a broker and struggling with the GST issue as well.  With 10,000 lines of trades I am desperate to find a solution.  I do not want to manually adjust each journal manually for the GST.

    Are you able to help.

    My email address is vbonner@collinscorporate.com

    Regards

    Virginia
  • John G
    John G Reckon Staff Posts: 1,570 Reckon Staff
    edited January 2018
    Hi Everyone,

    There are KB articles for the errors mentioned here...

    The transaction is not in balance when using an item recently imported through an IIF file.

    You must specify a tax agency for the tax account


    The Tax Agency is set in the settings of the Tax Supplier (Australian Taxation Office)

    image


    The taxable setting in tax code GST has been changed


    You may also find this article helpful in some situations:
    Consolidating / merging multiple Tax Agencies - Australian Taxation Office, Australian Tax Office, ATO etc



    Hope this helps.


    regards,
    John