cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Level 1

.iif format question

I am writing some code to convert some transaction records to an .iff format for Quickbooks import. The imported amounts are a daily summary of the transactions for each account, so things will always balance, however it results in transaction overlap i.e. two or more cash deposits returns a single amount. Because of this the only way I can export to .iff is have a single transaction per day. 

 

Will Quickbooks Premiere accept an .iif file that, for example, has positive values for AR and Cash, and the other balancing negative values?

 

ex.

!TRNS   TRNSID  NAME    ACCNT   AMOUNT    
!SPL    SPLID  NAME ACCNT   AMOUNT    
!ENDTRNS
TRNS    145940  <none>  Accounts Receivable 1000    
SPL 145941  <none>  Sales   -1000
SPL 145942  <none>  Service Provided    -500
SPL 145943  <none>  Cash    500
ENDTRNS
Solved
Best answer 11-17-2017

Best Answers
Highlighted
Level 15

.iif format question

Remember: this Coding is a transaction and not a Bunch of Lines.

 

"The imported amounts are a daily summary of the transactions for each account,"

 

AR = unpaid sales by specific customer names. If you are trying to manage Open balances generically, that is an Other Current Asset and not AR. AR requires this to be an invoice or statement charge, for that specific name and any payment needs to also be for that specific name and only the interface can be used to Apply them to each other.

 

"so things will always balance,"

Things Have to balance, or the coding is not valid.

 

"however it results in transaction overlap"

Your Snippet doesn't even include Transaction Type at all. Did you not include that here, or overlook it entirely?

 

"i.e. two or more cash deposits returns a single amount. Because of this the only way I can export to .iff is have a single transaction per day."

 

You can have Multiple Transactions. Each transaction has a Date.

 

"Will Quickbooks Premiere accept an .iif file that, for example, has positive values for AR and Cash, and the other balancing negative values?"

 

The coding you showed is Invalid.

 

You want to go back to your .iif kit and look at the "TRNSTYPE" Header options. You seem to want to do Daily Sales Summary = Sales Receipt = "CASH SALE" on the TRNS line, which is the Ending Amount (perhaps to Checking, as a Positive Number). Then, Split lines SPL to sales account = a Negative number for amount, assuming you are not also including qty and price. Otherwise, QTY is negative, Price is positive, and Amount ends Negative (is that math). Sales tax is negative, as well.

 

Other Asset is treated as a Payment, showing that Sales was not totally paid; all Payments would be Positive, if you are listing them to control the data flow manually.

 

Examples:

$600 of sales (listing negative for the SPL = Credit to income)

$400 Put into a Charge Account (other asset) to be Paid later = positive to Other Current Asset (debit)

+ $350 as payment on account to clear earlier Other Current Asset = negative to Other Current Asset (credit)

Should = a Total on a sales receipt TRNS line of (600 minus 400 + 350) = $550 Positive on the Very First Line = TRNS, posting to Checking.

 

If some of that is still in the Safe, then you make a SPL line with Negative to "safe bank" for the amount Held out of this deposit to checking.

 

And if that is the Only activity you ever had in this file, then the Other Current Asset account now shows a running balance of $50:

$400 in

$350 paid

 

I hope that helps.

View solution in original post

7 Comments
Highlighted
Level 15

.iif format question

Remember: this Coding is a transaction and not a Bunch of Lines.

 

"The imported amounts are a daily summary of the transactions for each account,"

 

AR = unpaid sales by specific customer names. If you are trying to manage Open balances generically, that is an Other Current Asset and not AR. AR requires this to be an invoice or statement charge, for that specific name and any payment needs to also be for that specific name and only the interface can be used to Apply them to each other.

 

"so things will always balance,"

Things Have to balance, or the coding is not valid.

 

"however it results in transaction overlap"

Your Snippet doesn't even include Transaction Type at all. Did you not include that here, or overlook it entirely?

 

"i.e. two or more cash deposits returns a single amount. Because of this the only way I can export to .iff is have a single transaction per day."

 

You can have Multiple Transactions. Each transaction has a Date.

 

"Will Quickbooks Premiere accept an .iif file that, for example, has positive values for AR and Cash, and the other balancing negative values?"

 

The coding you showed is Invalid.

 

You want to go back to your .iif kit and look at the "TRNSTYPE" Header options. You seem to want to do Daily Sales Summary = Sales Receipt = "CASH SALE" on the TRNS line, which is the Ending Amount (perhaps to Checking, as a Positive Number). Then, Split lines SPL to sales account = a Negative number for amount, assuming you are not also including qty and price. Otherwise, QTY is negative, Price is positive, and Amount ends Negative (is that math). Sales tax is negative, as well.

 

Other Asset is treated as a Payment, showing that Sales was not totally paid; all Payments would be Positive, if you are listing them to control the data flow manually.

 

Examples:

$600 of sales (listing negative for the SPL = Credit to income)

$400 Put into a Charge Account (other asset) to be Paid later = positive to Other Current Asset (debit)

+ $350 as payment on account to clear earlier Other Current Asset = negative to Other Current Asset (credit)

Should = a Total on a sales receipt TRNS line of (600 minus 400 + 350) = $550 Positive on the Very First Line = TRNS, posting to Checking.

 

If some of that is still in the Safe, then you make a SPL line with Negative to "safe bank" for the amount Held out of this deposit to checking.

 

And if that is the Only activity you ever had in this file, then the Other Current Asset account now shows a running balance of $50:

$400 in

$350 paid

 

I hope that helps.

View solution in original post

Highlighted
Level 1

.iif format question

Thanks for the help.

 

I was able to find the quickbooks permiere demo and with that all the issues that you mentioned with the iif file were brought to light.  I am not an accountant so the numbers/acocunt amount may not look right; so ignore them.

 

Unfortunately, my question still remains; can I merge all the daily account summary amounts into an .iff transaction block? i.e. as if the day is a single transaction.

 

As for the demo software I downloaded that comes with a Hockey School demo company, when I try to import the .iff content below, which was done successfully, but none of the transactions show up in the account pages...although that could be due to the numbers not *adding* up.

 

!TRNS	TRNSID	TRNSTYPE	DATE	NAME	ACCNT	AMOUNT	DOCNUM	MEMO
!SPL	SPLID	TRNSTYPE	DATE	NAME	ACCNT	AMOUNT	DOCNUM	MEMO
!ENDTRNS
TRNS    145940  GENERAL JOURNAL 07/31/2026  <none>  Accounts Receivable 1000    Order Batch # 20170803.m01
SPL 145941  GENERAL JOURNAL 07/31/2026  <none>  Accounts Payable -1000  Order Batch # 20170803.m01
SPL 145942  GENERAL JOURNAL 07/31/2026  <none>  Chequing -500   Order Batch # 20170803.m01
SPL 145943  GENERAL JOURNAL 07/31/2026  <none>  Cash    500 Order Batch # 20170803.m01
ENDTRNS
Highlighted
Level 1

.iif format question

To rephrase, in the previously posted iif snippet can all the items be made into one transaction, since I don't know where each dollar amount went, all I know is the final account change amounts.

Highlighted
Level 15

.iif format question

"can I merge all the daily account summary amounts into an .iff transaction block? i.e. as if the day is a single transaction."

 

You can make One Transaction or as many as you need. Any Transaction can have a bunch of different Split lines, and you can even end a Sales Receipt at 0, since everything else was controlled. I don't know what "merge" and "block" mean to you.

 

Yes, a method is called "Daily Sales Summary" = One Transaction. Example: A POS system has a Z-out tape and we want to turn that into a Single QB import. It is done as a "CASH SALE" = Sales Receipt, for Import.

 

Let me help:

Stop using Journal Entry like this. JE first line is called Source; split lines are Targets. Stop using AR; I already explained that Problem. You also cannot use AP like that, for the same reason that Names are required.

 

AR = someone owes you. Someone Specific. AP = you owe someone; that Specific Someone. AP and AR rely on the Realtional Database to include Names. You cannot use AP and AR Generically. If you want Generic like that, this is Other Current Asset and Other Liability. Not AR and AP. And even if you tried this with names, you cannot put Both in one transaction. Think of AR and AP as "reserved functions for those people using the program's Intereface" which you are Not Doing.

 

Using AP and AR means you would still need to have the Linked Transactions; you cannot have Linked transactions from the .iif process. Even if you list AR and "debit and credit" with the same one name, those are not Linked. Even if you use Bill and Bill Payment for AP, using JE or even the real transactions, they are not Linked from the .iif process. You are mixing your processes.

 

You have Checking as Negative, but Cash as Positive?

 

Let's start over: what are you trying to show? Type out the Function here, such as:

I sold $x, I paid for $y, I got paid $a, I transferred $c from checking to savings, etc.

 

Give us Words, please. We cannot tell you if the coding is what you want, when we don't know the End Goal.

Highlighted
Level 1

.iif format question

As I mentioned I am not an accountant, so any amounts that don't make sense, you need to disregard them.  I believe you answered the question by saying 'Yes, a method is called "Daily Sales Summary"'.

 

Thanks for the help

Highlighted
Level 1

.iif format question

My transactions are in other currency. I have setup my exchange rates by the I still cannot get my transactions translated. How do I set up my IIF file for multicurrency?

Highlighted
QuickBooks Team

.iif format question

Welcome and thank you for joining the thread, @Snderitu.

 

Allow me to share some information about importing bank feeds transactions in QuickBooks Desktop (QBDT).

 

Instead of starting from scratch, QBDT allows us to import/export data. You can transfer information to and from QuickBooks in case you need to convert, upgrade or create a new company file. Before importing, make sure to identify the transaction type and file format to ensure that QuickBooks will be able to handle them. QuickBooks allows us to import/export list to and from MS Excel. 

 

Importing of bank feeds transaction in QBDT uses the web connect (.QBO) files and not .IIF, you can only import transactions like invoices, bills, checks, and others using the .IIF files. Web connect (.QBO) files are online banking transactions from your bank's website, this can be downloaded and saved on your computer. Afterwards, you can import the transactions so they become available in your bank feeds. The multi-currency feature should be turned on in QBDT so you can import foreign currency bank transactions. I recommend backing up your company file before proceeding with the steps below.

 

To turn on Multi-currency:

 

  1. Click Edit at the top menu bar.
  2. Select Preferences.
  3. On the left-pane, select Multiple Currencies.
  4. Go to the Company Preferences tab.
  5. Select Yes, I use more than one currency.
  6. From the drop-down, select your home currency.
  7. Click OK to save the changes.

To import web connect (.qbo) files:

 

  1. Go to the File menu.
  2. Select Utilities.
  3. Click Import.
  4. Select Web Connect Files.
  5. Select the QBO file you saved, then select Open.
  6. When prompted to select bank account, select Use an existing QuickBooks account if the account you are importing the transactions into is already set up in QuickBooks and select Create a new QuickBooks account if the account you are importing the transactions into is not yet in QuickBooks.
  7. Select Continue. You should see a dialogue box telling you that the data has been successfully read into QuickBooks.
  8. Select OK.
  9. Go to the Bank Feeds Center to review your transactions.

You can check these articles for additional reference:

 

 

That would allow you to import bank feeds transactions. Please feel free to add a comment below if you have additional questions about importing transactions. Have a wonderful day!

Need to get in touch?

Contact us