QuickBooks Import: 1099

QuickBooks Import: 1099 - QB Online

There are 4 simple steps that need to be done:

Step 1: Settings

Ø Choose a payor that the imported data will be for. The payors are available in a drop-down list. If you start typing out the name, you will see that there is an auto find feature.

Step 2: Extract

You will need to export two reports to Excel spreadsheets. We utilize the data from the 1099 Transaction Detail Report and the Vendor Contact Listing. You may wish to review the accuracy of these reports within QB Online prior to this task.

We designed the QB online import routine to be as easy as possible. All you need to do is run the two reports using their default settings and export to Excel. You do not have to open up the file in Excel. We will do it automatically for you and save it as a CSV file.

1099 Detail Transaction Report

This report is available under Reports in the Expenses and Vendors section. You must use the default report format in QB Online. It is important to check your date range and make sure that you are generating data for the correct year.

Trying to run the 1099 Detail Transaction Report for the first time requires a one-time setup where you complete a survey in QB Online. Intuit doesn't really tell you about this but it is necessary. You need to go to the Vendor Listing which is under Expenses / Vendors. On the top right there is a button to the left of "New Vendor". You need to select the down arrow on that button where you will see a choice to "Prepare 1099's". After you select that the survey begins which is 5 steps. Follow this outline to proceed:

w Step 1: Confirm your Company information

w Step 2: Categorize payments to contractors - Here you map which accounts in your chart of accounts go to which box on the 1099-MISC or 1099-NEC

w Step 3: Review your contractor's information

w Step 4: Check that the payments add up

After Step 4 there will be a button on the left to "Finish Preparing 1099's". After you click that button you will be brought to a screen which will try to sell you the services that Intuit offers for filing 1099s on your behalf. You do not want to select any of these. Hit the Back button. You have to do this procedure twice. Hit the button again for "Finish Preparing 1099's" and then the back button. After you have gone back to step 4, you can hit the button to "Save and Finish later" The survey has now been completed successfully in order for you to run the 1099 Detail Transaction Report. This is a one time setup so you will not have to do it again until next year.

It is important to note the following:

The report must be used in its default format. If you have made any customizations you will need to reset the report to the default format. The default format uses the following columns:

Date

Transaction Type

Num

Memo/Description

1099 Box

Account

Split

Amount

Balance

Tax ID

DO NOT hide, change or reorder any columns. Even though QB Online gives you the ability to do do this it will cause the import to not work.

If the 1099 data maintained in QB is very large then you will need to apply a filter and break up the report using date ranges. You will know that it is too large because when you pull up the excel file you created you will notice at the bottom that it has been cut off. QuickBooks has a 32.000 record limitation on export files. Alternatively you can check by pulling down the View menu and select Browse Temporary Working File. This is only necessary for very large amounts of data maintained in QB. If this applies to you, it will be necessary to export the first half of the year to 1 file and then the second half to another file. Run the import twice but on the second time, uncheck the box to "Delete all records for this payor prior to import" in Step 1 and select the box to "Combine with previous file imported" in Step 2.

To export to Excel, click the export icon in the top right which is next to the "gear" icon. A submenu will apear to either export to Excel or export to PDF. You must export to Excel. The file will be downloaded to your Downloads folder and be named automatically. You can leave the filename as is or optionally rename it. Do not make any structural changes to this file. You will point to that file when importing it into our program in Step 2.

Tip: Copy the file to another file where you always give it the same name (i.e. 1099Detail.xls). It is only a temporary file which serves as a vehicle for our program to import. If you always use the same filename, you do not have to keep on specifying a different filename for each client imported. You are always pointing to the same file.

Vendor Contact Listing

This listing is available under Expenses / Vendors. All you have to do here is click on the export icon in the top right of the list next to the gear icon. On this export, there is no customization available within QB online so you don't have to worry about whether or not you changed the columns shown on the listing. It always exports the same structure no matter what. DO NOT go to the reports section for this report. There is a vendor contact listing available there but it is not the same format as this export from the Vendors section.

We merge this listing with the 1099 Detail Transaction Report to give us the name and address of the vendor. QB allows you to setup a vendor with just the vendor name and optionally use a different company name. If you use a company name that is different than the vendor name, we will use the company name; otherwise we use the vendor name.

To export to Excel, click the export icon in the top right which is next to the "gear" icon. A submenu will apear to either export to Excel or export to PDF. You must export to Excel. The file will be downloaded to your Downloads folder and be named automatically by QuickBooks Online. You can leave the filename as is or optionally rename it. Do not make any structural changes to this file. You will point to that file when importing it into our program in Step 2.

Tip: Copy the file to another file where you always give it the same name (i.e. Vendors.xls). It is only a temporary file which serves as a vehicle for our program to import. If you always use the same filename, you do not have to keep on specifying a different filename for each client imported. You are always pointing to the same file.

Note: You are allowed to edit any of those export spreadsheets in Excel. It must be stressed that you cannot change the format or structure of the file. You can however edit any of the data. We have some clients who have a large amount of invalid Taxpayer ID#'s in QB Online. Every time you change a Taxpayer ID# in QB Online you are requested by email to verify this action. That can be quite time consuming. Sometimes it is easier just to change the exported spreadsheet. Or you may have to update the spreadsheet with data from an external source. Your are allowed to do this so long as after you are done, the format of the spreadsheet is the same as it originally was.

After indicating the names of the 2 Excel spreadsheets in Step 2 just execute the button labelled "Process XLS files holding your exported QB Online data".

Step 3: Verification

A recap summary of the data extracted from QuickBooks is automatically generated when Step 2: Extract is performed. You should review the totals provided to ensure accuracy. Any employees with invalid Taxpayer ID#s are not included in the recap totals.

The trick here is to examine your recap. If you do not see any totals then the mapping of data in the 1099 Detail Transaction Report has not been completed. You can access the mapping database by using the buttons labelled 1/2/3 below the recap list. First select Button 1 to see what boxes are being used on the 1099 Detail Transaction Report. Then go to Button 2 which is for mappings. For QB online we have taken care of these mappings for you but you may have an unusual one that you are using which requires you to setup the mapping. With Button 2 all you are doing is telling our program which box on the 1099 to use to put the description used under the column titled "1099 Box" in the Detail report. After mapping, you then select Button 3 which will recalculate the recap list at the top.

A list of those employees with invalid Taxpayer ID#s is also provided on the right. You can correct them within this screen. If you wish to make a correction to a Taxpayer ID#, either double click on the highlighted record or select the Change Record button. Corrected Taxpayer ID#s must be input with the dashes in the correct place. If you do not have a valid Taxpayer ID# on file for an employee, input an arbitrary SSN with 9 digits in it and the dashes in the correct places. Place an "X" at the end of the number. The "X" tells us that this is an arbitrarily assigned Taxpayer ID# and we will treat it accordingly in our program as a System SSN.

When finished updating any invalid Taxpayer ID#s select the button to Update Imported Data. This will write back the corrected ID#s to the temporary file holding your imported QB data. The recap totals on the left will automatically be refreshed. You will be alerted if any ID#s already exist in the program. Those corrections will not be implemented so you will need to use a different ID# in your correction. Any ID#s that are still invalid will not update the imported data and will be excluded from the recap totals.

Step 4: Import

Select the button to finalize the importing of your data. Throughout the first 3 steps, nothing has been written to the files within the W-2/1099 Filer. This button will handle the writing of the QB Online extracted data to our program