Quickbooks and Excel

A great combination !

Importing excel Purchase Orders/Requisitions into Quickbooks desktop

It is often the case that the buying department of a company have their own system for making requisitions and sending orders out to suppliers. They then feed the accounts department with copies of the requisitions and POs which they have prepared in excel or Word.  The accounts department then checks these against invoices from suppliers. Alternatively, they enter all excel POs on QuickBooks and end up duplicating  work done by the buying department.

To avoid this scenario , I have designed a Purchase Requisition template in excel. This template can be completed by the person ordering the goods and then emailed to the accounts department. Once the accounts department receive this , all they need to do is click on the macro button at the buttom right and an IIF file will be generated. The IIF file can then be imported into Quickbooks as a Purchase Order. This can then be printed and  sent to the Supplier (or Vendor , if you are American)

See clip below.





December 15th, 2016 Posted by | IIF imports | no comments

Importing Purchase Requisitions

I have recently been reminded of a tool I wrote back in 2012 which has not been featured on this blog. The tool converts an excel Purchase Requisition into an IIF file that can be imported into Quickbooks Desltop as a Purchase Order . It can be then printed out and sent to the Supplier.

The excel Purchase Requisition has a macro button at the button of the form. When clicked , an IIF file is generated which readily imports in Quickbooks.

July 31st, 2016 Posted by | IIF imports | no comments

Importing stock parts, with Cost and Sales price – QB2012

I have quickly put together a simplified IIF template for importing stock parts. This is tested with QB2012 UK and is for experimentation only. Some headers may need to be altered for other versions of QB.

You need first to open the IIF template with excel. You must keep the headers as they are (Row 1)  and  change the the following  :-

1)Name of you stock part (in Name Column – col B)

2)Income account associated with item (ACCNT column – col H). This must already have been created in QB

3)Expense account associated with item (COGS – Col J). This must already have been created in QB

4)Price – under PRICE column

5)Cost – under COST column

When you have finished , save  and accept the text  delimited format. This is important.

Then, on the Quickbooks menu, go to File – Utilities – Import – IIF files and browse to the saves IIF template. Please make sure that you close and save the template first.

If you do it correctly, then you could import hundreds of items this way.

Please note that, before experimenting,  you must do a backup.


Stock import IIF file – simplified


June 23rd, 2013 Posted by | IIF imports | no comments

Importing Day Books

Many enterprises like to maintain a day book that logs all supplier invoices that come in. A sequential number is given to each document and the original document reference is also noted. I see this as an ideal scenario for automating the posting of supplier bills.

Provided all the information is on the Day Book (eg date, invoice ref, amount, details,nominal code, etc) and the Day Book is maintained on Excel, it is possible to customise a Day Book that would be used as an IIF import.

I have created such a template and it really does save time. I will write more on this




May 2nd, 2013 Posted by | IIF imports | no comments

IIF import – journal example

If you are trying to import transactions  through IIF for the first time, I would recommend the import of a journal as a first step. Intuit provides guidance in the Quickbooks help file and have templates for download.

If we look  at the template provided in the link below, we find a rather complex text file with plenty of column and row headers. The reason it is complex is because the template is designed to cater for all eventualities.

Download Intuit’s typical journal template

If you are a beginner, you might be put off. Therefore I have trimmed to template to basics only. My simplified template can be downloaded from the link  below. It will work on QB UK 2010 and 2012 .

Download my trimmed journal template

Steps are as follows :-

1)Extract zip file and open with excel

2)Substitute the nominal accounts with your own accounts which already exist on your Quickbooks . Make sure the spelling is exactly the same.

3)Put in your figures . Do not forget the minus sign for credit. Total must be nil

4)Save as a text delimited file, keeping the workbook in that format

5)Open Quickbooks and go to File – Utilities – Import – IIF Files and browse to the saved file. If QB cannot see it , change the file type to “all files”

6)Select the file and click ok . The data should be imported in a fraction of a second.

If successful, you can experiment with more rows, etc.


March 19th, 2013 Posted by | IIF imports | no comments

Demystifying IIF imports

Quickbooks’s IIF import feature is one of the utilities which is very often under-used. The reason is due to its apparent complexity. Yet it is a very easy and reliable way to import transactions.

Most users are quite comfortable with importing excel data. However, such excel imports are limited to lists such as stock items, customers information, etc. You cannot import transactions through the excel import feature. You need to use the IIF import when it comes to transactions.

IIF stands for Intuit Interchange Format but it is no more than a text format . In fact it is a delimited text file. It can be created in excel and saved as such. I will elaborate on this in my next post in this category.

March 18th, 2013 Posted by | IIF imports | no comments