Syncing Data between SharePoint & QuickBooks Desktop – Using IIF

There are basically 2 ways to sync data between Office SharePoint 365 and QuickBooks Desktop:

  1. Intuit Interchange Format (.IIF) files.
  2. Microsoft Excel and CSV files.

We review the IIF method in this post. Here is more information from Inuit on IIF Files.

Generally, you will use IIF files to update QuickBooks from SharePoint when there is no option to copy and paste list data into QuickBooks. You will use IIF files to update SharePoint from QuickBooks when you can’t use QuickBooks to format data into a report for exporting to Excel and then into SharePoint.

Update QuickBooks from SharePoint

We’ll use SB2O Customer Type List to update the QuickBooks Pro Customer Type List using the following steps:

A. Go to QuickBooks Desktop Sync Library under Setup on the SB2O SharePoint Site.

B. Download and Save the file to a convenient location. Keep it as a txt file. Open the downloaded SB2O Customer Type IIF in Excel.  We only need to add the Client Types to the Name column.  The rest of the cells are left blank.  We are not adding REFNUM and TIMESTAMP since we let QuickBooks use the default values.

C. Go to the SB2O Customer Type List under Setup.  Click on Export to Excel.  Open in a new Excel Workbook and Copy the Customer Type Names that need to be added to QuickBooks and paste them in the spreadsheet in B. Save the txt file.

D. Go to File>Utilities>Import>IIF Files in QuickBooks and Import the txt file.

E. Check that the import is correct.

Update SharePoint From QuickBooks Desktop

Let’s reverse the above process to see how to go from QuickBooks Desktop to an Office 365 SharePoint List.

A. Go to Files>Utilities>Export>Lists to IIF Files.  This shows the Lists that can be exported as an IIF file.  Check Customer Type List, hit OK and save the file to a convenient location.  You get a message telling you that the data has been successfully exported.

B. Open the file in Excel. Select the cells you want to Copy and Past into the SB2O Customer Type List.

C. Go to SB2O>Setup>Customer Type List.   Open it in “Classic View” and click on Edit this list.

D. Highlight the first cell you want to paste the data into and hit Control V.  The copied data is pasted into the List. Stop editing the list.

Note: 3rd party vendor software exists to sync data between SharePoint Online and QuickBooks Desktop, it is fairly expense to buy, implement, and maintain.   Here at SB2O we decided that it is not worth the additional cost to implement it in our offering. Mainly because List data is not that volatile after it has been setup.

Wave Accounting & SharePoint Synchronization

I’m adding a short post on Wave since it has gotten good reviews and deserves to be discussed with the other accounting systems.

Wave is based in Toronto CanadaWave’s Accounting, Invoicing, and Receipt Scanning are free. Wave charges for Payroll, Credit Card and Bank payment processing.

Here is a Wave Review 2018 by Cloudwards that provides more information on the product. The reviews I have read give Wave high marks and classify it as very good for single owners/very small businesses. I believe that if Wave is used in conjunction with Office365/SharePoint the combination could meet the needs of many small business and save those businesses money on their accounting software.

Customer Synchronization

Customer are set up under Sales in Wave. Wave provides the capability of importing clients via a CSV file. The customer data in Wave is limited but if you are doing your customer management in SharePoint this would not be a factor. See my post on Customer Management for further information.

Project Synchronization

The post “The Case for a Project List” – I suggest you should set up projects in both SharePoint and QuickBooks. I also point out that although you can import from QuickBooks to SharePoint, there are multiple steps involved. It is easier to go from SharePoint to QuickBooks.

There is no concept of Job or Project in Wave. You can set up a product or service, but they are single items. Bottom line — this is not a job/project oriented application. However, you could do your project management and bill preparation in SharePoint/Office365 and record the invoices and payments in Wave.

Task Plan Synchronization

As in Xero, there is no concept of Task. You could use the product/service field to track tasks but then you can’t track a project if you use those fields for that purpose. Again, this is not a drawback if you are using SharePoint Task Management capabilities.

Time Tracking

The post “SharePoint Task Plan – Tracking Time” discusses time capture alternatives. Wave has a timesheet capability as part of payroll but there is no import capability. Entering the data in two places is necessary if you are tracking hours for payroll and project or customer management.

Billing

Wave’s Estimates and Invoice features do not support importing of data. Their related reports can be exported into Excel, so you could update SharePoint with Estimate and Invoice information. As mentioned above, you could decide to undertake these functions in SharePoint.

Employee Synchronization

The posts “Example Employee List from QuickBooks” and “Setup Users by Importing” discuss synchronization between QuickBooks and SharePoint. I did not look at Wave’s employee data capabilities.

Document Management

Wave has no document management capabilities.

Overall Import/Export Capabilities

Wave has limited import capabilities but does provide report export capabilities. Unlike FreshBooks and Xero it does not have an Application Program Interface (API) that can be used by third parties to provide integration.

Bottom Line

Like Xero and Billy, if you decide to run your business mostly using SharePoint and Office365, you can use Wave to do your basic accounting, financial statement preparation, payroll and collections. Its added advantage is that the software is free.