- have more than one QuickBooks file?
- archive QuickBooks files occasionally?
- ever start a new company file to speed performance?
- store client files?
- frequently close one file and open another for yourself or clients?
The solution is:
Store QuickBooks transactions in date chunks in another application so you can access them when QuickBooks is closed.
In addition to transactions, this solution also works for accounts, vendors, customers, items, etc. Therefore, we will refer to this information as:
- source data: residing in QuickBooks files
- stored data: exported from QuickBooks and stored in another application
How the Solution Works for Historical Data
Since historical data rarely changes, you can rely on stored data as long as you refresh it when you change the source data.
For example, you may store transactions in blocks by year and have three tables:
The only data you need to refresh is data you change in the open QuickBooks file.
You cannot change stored data while QuickBooks is closed, therefore it remains accurate until you open QuickBooks and change it. The only exception is when using ODB connections and certain file permissions but even then you can only change one file and all others must be opened to change data.
After changing data and before closing QuickBooks, refresh the stored data.
Multiple Company Files
The same principle applies to multiple company files. You cannot change any company's QuickBooks data unless the file is open. Before closing the file, refresh the stored data and it remains accurate when QuickBooks is closed.
Store multiple company files by date but also include a higher level of identifier: Company Name. Your list of stored tables might look something like this:
ABC Company 2009Accessing Paperless Receipts
ABC Company 2010
ABC Company 2011
XYZ Company 2009
XYZ Company 2010
XYZ Company 2011
Another benefit of stored data is access to paperless receipts with QuickBooks closed. If you use the The Link Principle (Amazon, Barnes & Noble) to link transactions to paperless receipts, you can view any receipt in seconds from the data in your stored transaction list.
How to Store QuickBooks Transactions
A variety of methods exist and we'll begin with the easiest but least effective and end with the most effective that uses VBA. Copy and paste VBA is included so you need not know VBA to use the method.
The simplest way to store QuickBooks transactions is to export to spreadsheets. QuickBooks provides this feature for you. To include all transactions, use the Custom Transaction Detail report, unless you only want specific transactions like checks, bills or credit cards or variations of combinations of these.
To create the Custom Transacction Detail report, follow these steps (see video below):
- click Reports
- select Custom Reports
- select Transaction Detail
- select a Date Range
- select the Columns you want on your report
- click OK
- click Memorize
- name the Report so you can find it later
- save it in a Report Group or create a new Report Group
- click Excel
- create a New Worksheet the first time; thereafter, if you are saving the same date range,
select Update Existing Worksheet
- save the report
- if updating an existing worksheet, select the sheet from the drop-down box QuickBooks provides for you that lists all worksheets in the select workbook
How and where you store spreadsheets is up to you. You might store each company in a separate workbook and keep each date block (year, quarter, etc.) on a separate worksheet. The possibilities are many.
This is the easiest method as far as exporting; however, saving and accessing spreadsheets means remembering their locations. In addition, each date block will reside on at least its own worksheet if not its own workbook. You may need to open more than one workbook or worksheet to find the transaction you need. This is still faster than opening QuickBooks each time you need to view a transaction, especially if you have to close one file and open another.
We can improve on this method by using a database and this is covered in the next post.
Continue to the next post
Do you like this post? Please link back to this article by copying one of the codes below.URL: HTML link code: BB (forum) link code: