Quickbooks Sales Imports 01

Do you want to import Quickbooks sales information into EasyRoyalties? Its easy. To do this you will need to export a Sales by Item Detail report to Excel. In Excel we sort the report by the date column and delete all rows where the date cell is empty (i.e. all non-header rows without sales information).

Before you get started you need to enter the Quickbooks Item number into the EasyRoyalties Edition SKU or other product code field if you are not using the ISBN as your product code. The list price of each book (i.e. edition) also needs to be entered into the Edition record.

NOTE: The instructions below assume that your royalty rates are not based upon the sales type (i.e. customer type). If they are, you will need to add a sales type field to the excel export file.

NOTE:If you have a rule where you pay no royalties on Below Cost sales then you need to make sure that the unit cost of each item is in the Edition record, as the standard Quickbooks Sales by Item Report does not have a field for cost of goods sold.

The 4 Steps

Step 1. Sales by Item Report Modify Button

Click the Modify Report button and make sure that the following fields are listed;

  • Type
  • Date (the invoice date)
  • NUM (the invoice number)
  • NAME (the customer name)
  • NAME Account # (the customer account number)
  • ITEM (the product code)
  • MEMO (this is the title of the book)
  • QTY (the quantity sold)
  • Amount (this is the amount invoiced)

Deselect the balance field, as this field shows the running total of the sales records.

In the Filter tab deselect all headers.

After you have made these changes then select the Memorize button to save this report layout. The next time you need to access the sales report go to the memorized report section of the reports menu.

Step 2. Sales by Item Report Export Button

 In the Basic tab select Export to a new Excel workbook

 In the Advanced tab under Printing options select Send header to screen in Excel

Then press the export button.

Step 3. In Excel remove all blank rows and columns.

NOTE: With Version 3.4.45+ you do not need to execute Step 3 as Easy Royalties will automatically delete all sales file rows without a date during the import process.

Sort the excel worksheet by the date column after selecting the header rows check box. Then delete all rows without a date.

Then delete the empty columns at the start of the worksheet.

The end result is a worksheet with a header row containing the field names followed by rows of sales records.

Step 4. Save the file as an Excel 2003 file

You can import an Excel 2003 (not excel 2007) file from the main menu via File > Import > Sales entries menu.

How it works

The imported quantity and invoiced amount along with the retail price stored in EasyRoyalties are used to calculate the discount percentage.

The imported quantity multiplied by the unit cost stored in EasyRoyalties is used to calculate the cost of sales for each sales record.

The import will create a new customer account automatically if it cannot find a matching customer record. The created customer record will contain the Quickbooks Name and the Quickbooks Name Account # (if any). If you want a more complete record you need to export the customer address and customer type information (along with any other fields you want) from Quickbooks and import it into EasyRoyalties.