Process credit/debit cards and ACH (E-check) all from within QuickBooks. Installation is quick and easy!

Free QuickBooks Reports and Data Export

| July 24, 2008 | 36 Comments

Your QuickBooks company file contains a large part (if not all) of your important company data, but our understanding of that data is limited by the views that QuickBooks provides to us. They have a lot of “canned” reports that are very useful, but every business has its own unique situation that isn’t covered by those standard reports. Today I’m going to talk about a resource that a lot of people don’t know about, the QuickBooks Library Reports. This is a wonderful source for reports as well as another way to export your data, and it is free!

Reports, Reports, We All Like Reports

Follow this link to the QuickBooks Reports Library. There is a long list of reports that you can download. Most of these are in the “QBR” format. To use one:

  1. Save it to a folder in your computer
  2. In QuickBooks, select Reports, then Memorized Reports, then Memorized Report List.

  1. Click the Memorized Report button and select Import Template.

  1. Locate the report and “open” it. It will be saved to the memorized report list.

MAKE SURE that you save the “QBR” file that you downloaded, because QuickBooks sometimes has a problem where it loses memorized reports.

Take a look at the list of reports – it is very extensive. I found a number of reports that would have solved problems that people have been asking about in the QuickBooksGroup user forums.

Some interesting examples:

  • Unapplied Customer Payments and Credits: listing all payments from customers that are unapplied.
  • Unbilled Mileage by Job: Unbilled mileage marked as billable, subtotaled by customer job.
  • Contacts for Pending Invoices: A list of your pending sales for each customer, showing the customer’s contact name, phone and email address.
  • Customer email list: Customer email address and current balance for each customer.

This is just a sample. The list is very extensive, and worth time exploring.

Exporting Transactions

One way to get a look at your data in your own way is to export it into another format that you can manipulate with Excel, Access, or some other database/reporting tool. QuickBooks by itself doesn’t do a great job in letting you export data – you can get list information (item list, customer list, etc.) but it is harder to get transaction information (invoices, checks, etc.). Normally when I talk to people about exporting transactions I tell them to use one of the following methods:

  • Purchase the ODBC driver (www.qodbc.com) and use a tool like Access, Excel or Crystal Reports. Note that if you have QuickBooks Enterprise you already have this. It takes a bit of work to use this tool.
  • Purchase another export/synchronization tool from the QuickBooks Solutions Marketplace – there are several that work with various database/reporting tools.
  • Get the Programming SDK and write your own program (very complicated), or hire a custom software developer to write a custom export for you.

For the most part these approaches cost money (the SDK is free, but complicated to work with and you may need a programming tool). The QuickBooks Report Library provides another alternative if you have Excel. If you scroll down the page a ways there is an Excel section that contains a QuickBooks Workbook with Wizard and a QuickBooks Workbook. These tools provide you with a way to extract transaction information from QuickBooks into Excel. You can use them as they come, or you can play with them to modify what information you can extract. This is still a bit complicated, particularly if you want to modify what you can get. If you are comfortable with playing with VBA (Visual Basic for Applications) then you can get a lot out of this. The nice thing is, it’s free (if you have Excel).

Download the worksheet and open it with Excel. Most likely you will run into some security issues – Excel will not let the program run fully unless you take care of some permissions. There is a Security Guide worksheet that will explain what to do.

Right off the shelf – enter some dates in the Update worksheet and click the button (have your QuickBooks company file open first):

When it is done, look at the Data worksheet, and you will see your transactions:

As you can see, this pulled out inventory adjustments, invoices, and assembly builds (this is just a sample).

If you are trying to get transactions out of QuickBooks this can be a simple and cheap way to start.

Let Us Know What You Try

If you try this spreadsheet, or if you use any of the reports from the library, post a comment here about what you found that you like (or don’t like)!

Category: General Tips, Import/Export

About the Author (Author Profile)

Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70′s, focusing on inventory and accounting software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor. Look for Charlie’s articles in the QuickBooks and Beyond blog, as well as his California Wildflower Hikes blog.

Connect with Charlie at Google

Comments (36)

Trackback URL | Comments RSS Feed

  1. Dave says:

    I need a report that I can not find in standard QB or in the Quickbooks Report Library. In QB, when running a customer list, and using the Modify Report button, the available fields to select for the report does not include the “Exp. Date” field. I need a report of all customers showing their credit card expiration date. This date is stored in a standard field on the Payment Info tab. The issue I have is that I don’t find out that a customers credit card has expired until after I have attempted to process payment through my Merchant Services account. I need reporting on this so I can get updated credit cards from my customers before we process orders and billing. How can I extract this data?

  2. Charlie says:

    Dave, credit card information can be a bit tough to extract from QuickBooks because they protect it, for obvious reasons. I believe that you could access this information via the ODBC driver (although I haven’t checked on that particular field), so you could buy that and use Excel or some other ODBC compliant tool to get the information. Alternately someone who uses the programming SDK should be able to get that information and build a report. For more details on that particular approach you can contact me at the address in the “about this blog” page listed at the top.

  3. Nancy says:

    I used the transaction list by customers for year end accounts receivable reporting. First, the total does not match my account receivable on the balance sheet, and second it does not pull in the discounts given. Why?

  4. Charlie says:

    Nancy, I’m not sure which exact report you are referring to. Is it from the Report Library they have? If so, what is the exact title?

    Off the top of my head, without seeing the report or your data, the first thing I’d check is to make sure that the dates on the report matches the date of your balance sheet – if they dates don’t match, the values won’t match.

  5. SPO says:

    We are trying to generate Payroll reports using Quickbooks 2009. However, we do not Quickbooks’ PayCycle. Are there any reports out there that can help us get our payroll information?
    Thank you!

  6. jerry baugh says:

    I am using a “positive pay” service at my bank. When I issue checks, I upload a file to them that contains bank account number, check number, amount etc. I need to do this in a csv format. I now have to recreate this file each time. Is there a way to automate this?

    • Charlie says:

      Jerry: I don’t have a way to do that from QuickBooks directly. I suggest that you look in the Intuit Marketplace for export utilities – perhaps the one from Bay State Consulting…

  7. Ryan says:

    Thanks for the website. I’m trying to make a report to show all invoices closed for a day by any method (payment, applied credit memo, applied credit from JE, etc.) Any thoughts? Thanks.

    • Charlie says:

      Ryan, I haven’t looked through the list of reports to see if there is something that covers that. That would be, off the top of my head, a tough one. My first thought would be to look for all invoices with a “last updated” date of your date, with a balance of zero. That wouldn’t be perfect – and it requires using a tool that can see that date, which means the programming interface or the ODBC driver probably. But that is just off the top of my head…

  8. Alex says:

    Hi Charlie, I am looking for a report of all my BOM assembly items. I have sought but with no success! I don’t need to see multi-levels, just each BOM assembly item with its parts and quantities listed, so I can review and correct. Appreciate any help! thanks

  9. Melvyn says:

    Hi,I downloaded the Quickbooks_Linked_Reporting_Workbook.xls file but when I put in the dates I got an error message.
    Compile error Cant find projext or library
    It stops on a VBA page that says
    Private Sub getQBData_Click()
    Would you know whats wrong?

    I also downloaded the excel file Quickbooks Linked Reporting Workbook With Wizard. In this case I get a pop up window that says
    Unable to Connect to QuickBooks
    Could not connect to QuickBooks

    An error occured while attempting to use the connection to Quickbooks
    Error: -2147221000 : Method ‘GetModule’ of object ‘Irunninginstanceinfo’ failed

    • Charlie says:

      Melvyn, that sounds like an incomplete installation of QuickBooks – or an older (or non-US) copy of QuickBooks that doesn’t support the interface. If you have a relatively current copy, try doing a “repair” of the QB installation. What version/edition/year of QuickBooks are you using, what operating system? And if it is Vista, do you have UAC enabled?

  10. Richard Yee says:

    I would like to export transaction into excel then audit and update transaction then import back into quickbooks, will your product do this?

  11. Charlie says:

    Richard, that is a bit complicated, if you are MODIFYING an existing transaction. Talk to the people at Baystate Consulting, http://www.baystateconsulting.com – not sure if an off-the-shelf product can do what you want but this is probably your best bet.

  12. Daniel says:

    Hi Charlie,

    Where can I download the “Quickbooks_Linked_Reporting_Workbook.xls” from?
    You mention : “QuickBooks Workbook with Wizard and a QuickBooks Workbook” but I can not find any link to it.

    Can you help me please?

    Thanks,
    Daniel

  13. Charlie says:

    Since this article was written Intuit rearranged the “Library”.

    Go to http://community.intuit.com/library

    Look for the “Reports” section. Under that is a link to “All Reports”. Click that.

    In the “Search” dropdown select “Others”.

    You will get a list of three options, “QuickBOoks Linked Reporting Workbook with Wizard”, “QuickBooks Linked Reporting Workbook” and another.

  14. Mike says:

    Hi Charlie,

    Quick question – if one exports quickbook backup file (gives it to colleague) to be imported to another computer (in this case Quickbooks Pro 2010 was just installed), will the original exporter computer’s QuickBooks Pro 2010 preferences be imported to the 2nd machine as well?

  15. Alicia says:

    I need to export invoices (report format is fine) to a csv or excel spreadsheet. I have Quickbooks Pro 2009.

    Thanks,
    Alicia

  16. Charlie says:

    Alicia, you can use the ODBC driver (included in Enterprise, or purchased from http://www.qodbc.com) with Excel. A bit tricky if you don’t have experience with ODBC.

    You can use an export tool like the Transaction Pro Exporter (reviewed at http://qbblog.ccrsoftware.info/2009/06/exporting-quickbooks-transactions-with-transaction-pro-exporter/)

    You can use the “Letters” feature to send it to Word, and then transfer from there…

  17. James Thomas says:

    Alicia,

    Found a free export utility for QuickBooks Pro at http://www.data-devil.com. Doesn’t support every transaction type, but it’ll work for your invoices.

    Regards,

    Jim

  18. Nelson Croes says:

    Hi,

    I need a report that can give me the breakdown of my inventory with its GL balance.

    Anyone can help me?

    Nelson

  19. CH Cohen says:

    Having trouble finding the very simplest reports.

    Report #1: If 7 clients each pay me per month, I need a report that sinmply states date of payment, amount of payment, cash or check, or credit card, and totalling the payments at the bottom of the report.

    ie:
    J Jones $100 Mar3
    D Smith $150 Mar 4
    J Doe $200 Mar9
    Total: $450

    Can anyone help me with generating a report like this? Or is there a resource on the Internet that has a report like this that I can download?

    Report 2:

    A simple check registry showing only date of check, amount of check, payee, and total by date.

    • Charlie says:

      CH Cohen: Several ways of getting this kind of stuff. I’ll give you some general direction to get you started.

      One approach is to get an addon product. I like the reporting capabilitys of QQube – see the review at http://www.sleeter.com/blog/?p=480. There are other reporting tools that can work also.

      Another way is to play with the “Custom Transaction Detail Report” in QuickBooks. This is one of the more customizeable reports. You can set filters, and add fields. “Pay Method” is a column you can add. You can filter for payment transaction types. You can sort by “Name”. If you play with this report you may find that you get close to what you are looking for.

  20. CH Cohen says:

    Report 3:
    If I pay via check, I would like to have a report that gives me the following: date of payment, reason for payment (Memo or Account) amount of payment, payee, and totalling the payments at the bottom of the report.

    USPS $100 Mar 3 Postage
    PSEG $300 Mar 5 Utility
    Associate $500 Mar 10 (Associate Attorney/Salary)
    Total: $900

  21. Mary says:

    Where can i buy a practical workbook for quickbooks

    Thanks

  22. I have Quickbooks simplestart edition. Can I export my customer contact list into excel?

    • Charlie says:

      Lindsay, I don’t have Simple Start around any more so I can’t be sure. I don’t think that you can – but that depends on how old a version you have (that is no longer supported in the US market). It also might depend on the national version you are using, as non-US versions differ from the US versions.

  23. georgie says:

    https://workplace.intuit.com/db/be3484f36/gl/PapyrusApp.html#

    This is the link on community.intuit.com under Reports, beside “all Reports”

    Generates cannot display webpage.

    Many years ago, I got a template from this resource which did EXACTLY what I wanted.
    Removal of that resource leaves me with a bad taste in my mouth about Intuit, at worst, at best, I’m very disappointed.

    • Charlie says:

      Ah, that link took people to the old “Papyrus” site, which was something they were trying out to provide collaborative reporting. They’ve replaced that with the “contributed reports” feature in QuickBooks 2012 (see a reference to it here: http://www.sleeter.com/blog/2011/09/quickbooks-2012-wrapping-up-the-rest/ ). That new feature contains many more reports than the old Papyrus site.

      Looks like they forgot to update that web page.

      “Papyrus” is gone, it was a program in the Workplace App Center. Before Papyrus there was a static site that had reports. I’m not sure if that is around any more, probably not.

      I think that in the rush to provide shared reports with their new feature they forgot that older products can’t use the new site.

      I’ve asked some people about this, but I don’t know if I’ll get a good answer. The most likely answer will be that they’ll change the web site to remove the link, but we’ll see. In the meantime, I’ll update this article.

  24. Charlie says:

    Following up on this, Intuit fixed the link on that page so now you can find the reports. They didn’t realize that the link was broken, until I pointed it out to them (based on Georgie’s comment above). Thanks, Intuit!

  25. Eisa says:

    Dear Mr. Charlie

    I do have knowledge of using VBA in Excel, but where i can look at what other people write their VBA to get more experience

Leave a Reply