Working with QuickBooks Desktop and other accounting systems

NOTE: Little Green Light offers a direct integration with QuickBooks Online (see help article). 

If you do not use QuickBooks Online, please continue below to see other options for coordinating your work between LGL and your accounting system. We describe each scenario as being for QuickBooks Desktop, but the same general approach can be used with any accounting system.

In this article (4 approaches):

1. Enter transaction details in LGL and lump sum entries into QuickBooks Desktop

Enter individual transaction details in LGL and enter lump sum totals in QuickBooks. Why re-create all the same details in QuickBooks that you have in your donor management database?

You will need to figure out with your accountant how to group your lump sum batches. Most likely, you will want to enter totals according to the LGL Gift Category, since those should match up with your QuickBooks Chart of Account line items.

By Gift Category
Here's an example of a report you could run to show the aggregate totals for a set of gifts by Gift Category:

You can generate the above report in the Fundraising tab.

1) Set the date range you want. As a best practice, you should stay consistent with your date ranges. For example, always run your reports through Sundays. That way you can easily establish a sequence of lump sum entries that are "mutually exclusive and collectively exhaustive" (a fancy way of saying that you won't miss any entries and that you won't have any duplicates).

2) Set the view to "Giving by category". This will produce a roll-up report with each line representing a category.

3) The columns you should focus on for your Accounting entries are:

a. Gifts - monetary contributions you've received 
b. Pledges - you'll need these if you enter invoices in QuickBooks
c. Other Income - other payments you've received

By Fund and Gift Category

While Gift Category should align closely to your Quickbooks Chart of Accounts, you may also be using Classes in Quickbooks to tracking giving (and spending) by the programs you run. The equivalent to Classes in LGL is Funds. In LGL, Funds is used to designate where money is going (ie., which program is it funding).

In LGL you can view giving by a combination of Fund and Gift Category. This will be the View you want to use if you need lump sum entries for each combination of Class and Chart of Account (or in LGL terms, Fund and Gift Category).

More complex options

If you need to create a more intricate report for your lump sum entries, see the Knowledge Base article Generate a lump sum report to record donations into Quickbooks.

Entering lump sums in QuickBooks

Create a customer name of "LGL Constituents" or something along those lines. Use this customer for all of the lump sum entries you make. Enter each lump sum entry with the appropriate Chart of Accounts and or Class in QuickBooks.

2. Enter transaction details into both LGL and QuickBooks Desktop (double entry)

With this approach, you enter gift data into both LGL and QuickBooks (double entry), and you reconcile your donor management database (LGL) with your accounting system on a regular basis (most likely monthly).

To help with reconciliation, you can produce a report of gifts for any time frame you specify. We recommend using "Deposit Date" (which is set by default to equal your gift date), since that will most closely tie to the dates you have in your accounting system for donations. In your LGL report, you’ll get back the name of the Gift Category, Campaign, and Fund to help tie out your LGL data to your Chart of Accounts and Classes in QuickBooks.

Corresponding fields from Little Green Light to QuickBooks:

LGL Field QuickBooks
Gift Category Income Chart of Accounts
Campaign or Fund Classes (sub-classes)

Examples of Gift Categories in LGL (which should map to your Income Chart of Accounts):

  • Donation
  • Event Fee
  • Corporate Gift
  • Foundation Grant
  • Government Grant

Sample "Deposit Report" from LGL:

Comments by column on the report shown above:

  1. Batches close each day in LGL (unless you choose to close them earlier)
  2. The LGL gift ID is a unique identifier for each gift
  3. The LGL constituent ID is a unique identifier for each constituent
  4. Constituent name (your format in QuickBooks may vary)
  5. Gift date is the date you received the gift in the mail (or online, etc.)
  6. Deposit date is the date the money went to the bank
  7. Amount is the total gift amount (this might differ from "Tax Deductible Amt")
  8. Payment type: note that payments typically get processed differently depending on the payment type (i.e., how you handle checks vs. online gifts)
  9. Gift type: you should only deal with 3 gift types in reconciliation reports: Gift, Pledge, and Other Income
  10. Category: this equates to your Chart of Accounts
  11. and 12. Campaign and/or Fund: equates to your Classes

3. Enter data into QuickBooks first, and then export a file from QuickBooks Desktop to import into LGL

This approach is designed to reduce the double entry of gift data. To use this approach, you'll need to enter the gifts into QuickBooks first and then run an export of those gifts and upload them into LGL. The reason you need to go from QuickBooks to LGL is that QuickBooks can export files in standard formats (such as comma separated value files and Excel files) and LGL can import those file formats. But QuickBooks can not import standard file formats for donations; they use a proprietary file format (.iif or .qif) and LGL can not export in those formats.

The instructions below are written using a desktop version of QuickBooks. You can use a similar process for QuickBooks Online, the online version of QuickBooks.

There are three steps here:

  1. Prepare the export in QuickBooks (running a query to get the transactions you want)

  2. Exporting that report from QuickBooks to Excel (or a text file)

  3. Importing that file into LGL

1: Preparing the export

Generate a report that has the transactions you want to upload into LGL. In this example, we’re using the report Transaction List by Date found in the Accountant & Taxes area. Do not use a report that creates subtotals; that will cause problems when uploading to LGL.

A few things to keep in mind

Time frame: You want to make sure you select a time period that covers the period since the last time you did this. If you’re doing this process on a weekly basis, you can always set your report to pull transactions from “last week” (the weeks defined in QuickBooks run Sunday through Saturday). Or you can run your reports day-by-day. The thing to avoid is pulling in transactions that are mid-day, because it will be harder to create a clean batch for your next upload.

Email addresses: When importing your QuickBooks file into LGL, the best way to match up records to existing records in LGL is via email address. You should populate email addresses as fully as possible in your QuickBooks account, and then make sure you export that field when generating a file to upload to LGL.

Types of transactions

LGL is a great place to track income, but it is not designed to help you track the payments you make. So, you will want to filter your report from QuickBooks to include just the income items. Also, you might not track income from sales of merchandise or program fees in LGL (some organizations do, some don’t). When you run your QuickBooks report to get a file of transactions to upload into LGL, make sure you filter by Class or Transaction Type or in some other way that distinguishes donations from other transactions.

You’ll see the results on your screen.

2. Generating the export

Click Customize Report and go to the Header/Footer tab and uncheck all the header and footer information QuickBooks tries to add. This information is helpful if you’re printing the report, but it’s not helpful for the file you want to upload to LGL.

Click Excel to export a copy to Excel (create a new spreadsheet).

The Excel export is in pretty good shape for uploading now into LGL. A few things to point out:

Make sure your transaction data is the first tab (sheet) in your workbook. If QuickBooks created a summary page on tab 1, delete that or move it so your transaction data is in sheet 1. This is critical because the LGL Flex Importer only looks at the first tab of the Excel file you upload.

The first two columns have no headers. If you upload as an xls or xlsx file, these will be ignored automatically; if you upload a csv file you will be able to tell LGL to ignore these columns.

The first row below the headers is blank except for column A, and the same for the last row. LGL should ignore these rows, but if not, you will be able to "reject" those records when reviewing your uploaded file.

3. Importing into LGL

In LGL, select the Flex Importer (on your home screen) for upload. You should also set your Record matching preferences to allow LGL to match on "Name" and "Email". LGL will always match on constituent IDs (i.e., "LGL Constituent ID" or "External Constituent ID"). Setting the Name and Email flag allows LGL to import the records coming from your QuickBooks file to the existing records in your LGL file if the combination of name and email match to an existing record.

During the import process, you will be able to review which records LGL matched to, and if you see a match that is incorrect, you can break that match (i.e., if your QuickBooks record for John Smith is incorrectly matching to an LGL record with the name "John Smith").

On the next page of the import process, you will map the fields from your upload to the LGL fields.

Note #1: By the "1" in the mapping above, you'll see that we are telling LGL to import the "Name" field as the External Constituent ID, setting the . This is a good option if you're not using the External Constituent ID field for anything else. You can use it to store your QuickBooks name field, and that will help make sure that future imports from QuickBooks will map to the correct constituent record in LGL. Note that QuickBooks ensures that each Name field is unique.

After you’ve completed the mapping one time, you can save that upload as a template and then re-use it on future uploads. So future uploads will go very quickly.

For more help with how the Flex Importer works, see our Knowledge Base article Using the Flex Importer.

4. Enter data into Little Green Light first, and then, using third-party software, import that data into QuickBooks Desktop

This is often the approach prospective customers most want to take. It's nice to enter gift data into your donor management system quickly so you can get your acknowledgment letters out within 48 hours. The issue with this approach, though, is that QuickBooks doesn't accept a regular Excel file or CSV file. The data must be in a very specific format.

Fortunately, there are companies that specialize in transforming spreadsheets into a format that can be imported into QuickBooks. The Intuit Marketplace lists a number of software providers.

One that looks quite good is Transaction Pro Importer. Look for the importer for either QuickBooks or QuickBooks Online.

In summary

  1. Enter transaction details in LGL and enter lump sum entries into QuickBooks (**recommended**)
  2. Enter transaction details into both LGL and QuickBooks (double entry), and then run reconciliation reports
  3. Enter data into QuickBooks first, and then export a file from QuickBooks to import into LGL
  4. Enter data into Little Green Light first, and then, using third-party software, import that data into QuickBooks

Related Articles