Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Tutorial: Create a Sales Daybook for QuickBooks in Excel

by
17th Oct 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

One defect of QuickBooks is that it can’t print a proper sales or purchase daybook. In this first of two tutorials David Carter shows how to get data out of QuickBooks and into Excel via Excel’s Data – Import External Data command. In the second he uses a pivot table in Excel to create the sales daybook

One of the regular questions we get on Any Answers about QuickBooks is how to get a proper daybook report out of it. Go into Edit – Simple Find (or just Ctrl-F) and select a Transaction Type. The list of invoices always gives just one Amount value and it’s Gross – there’s no breakdown into Net and VAT.

Why can’t QB provide a simple daybook showing Gross–VAT–Net on a single line for each invoice?

It comes down to QB’s file structure. QB has transaction detail records, but no transaction headers So a sales invoice never appears as a one line header, but is always split up into at least 3 transaction lines – Accounts Receivable, VAT and Sales.

To get a proper daybook out of QuickBooks, you have to bring the transaction lines into Excel, then summarise them in a pivot table. Click here to see a sales daybook created in Excel.

In this two-part tutorial we’ll create this sales daybook out of QB sales invoices and credit memos. Note that you can also create a Purchase Daybook in the same way, except that in that case you bring over Bills.

In this first part we’ll create a Customised Detail report in QB, then import it into Excel via Excel’s Import External Data command.

In the second part next week we’ll create the above Sales daybook with a pivot table.

Tip: Towards the end of this tutorial you will be asked to enter some new transactions into QuickBooks, then import them automatically into Excel. So before you start, get hold of a couple of sales invoices that aren’t on the system yet and need to be posted onto QuickBooks.

1. Why use Data- Import External Data in Excel?
If you want to create a report in Excel, there are two ways of loading the data. The first is via File-Open, the second is via Data-Import External Data. Which one you use depends on what sort of report you wish to create.

If you want to create a one-off report that you won't use again, use File-Open.

If you want to create a regular report, use Data-Import External Data. To run the report at a later date, simply hit the Refresh Data button and it will be automatically updated.

In this case I want to make a permanent sales daybook report, and update it regularly. So we will route the data through Data – Import External Data.

2. Create the Import External Data report in QuickBooks
Start up QB. Reports - Custom Transaction Detail Report. Column headings are:

Type Date Num Name Memo Account Class Clr Split Amount Balance

We need to make some changes. In the Modify report box tick or untick the following columns (at bottom left):

Tick: Trans no (second from top), Account Type (second from bottom)

Untick: (left margin), Clr, Split, Balance

[It’s very important to untick (left margin) at the top, otherwise pivot tables won’t work. If you don’t use Classes, you can untick them as well. If you use the Sales Region and Rep fields for sales analysis, tick them.]

In the Dates box at top left, set it to This Financial Year to Date
(or This Month or This Quarter if you prefer)

Sort by:
Change Default to: Trans no – Descending order

This means that the most recent transactions will appear at the beginning of the report, not at the end.

Transaction number is also more reliable than Date. It is automatically generated by the system, whereas Date is typed in by the operator and operators make mistakes. [If you want to control a database, never use a field that is entered by the user; always use a field that is system generated.]

Filters
For a sales daybook, we only want to include invoices and credit memos. At the top click onto the Filters tab.

In the Filter box at left, click onto Transaction Type

Next to the Filter box it now says Transaction Type All

Click the down arrow and change All to Selected Transaction Types

In the Select Transaction Types box, tick Invoice and Credit Memo. OK.

OK. We’ve now finished the report. Click Memorise and name it as:

Import Sales Daybook into Excel

3) Export the data as a Tab delimited file
Click the Print button (NOT the Excel button). In the Print Reports box:

Change Print To from Printer to File.

Change File: from ASCII Text file to Tab delimited file

Print. Save it as QBsales.txt. Keep a note of the folder you’ve saved it in.

4) Import the data into Excel
Start up Excel.

Open a new worksheet. Click onto cell A1.

Select Data – Import External Data – Import Data
(Excel 2000. Get External Data – Import Data. Excel 2007. Data – from Text)

In the Select Data Source box, find the folder containing QBsales.txt.

Open QBsales.txt. You see the Text Import Wizard, Step 1 of 3

Step 1: choose Delimited. Next

Step 2 choose Tab. Vertical lines separate the fields. Next.

Step 3: Finish

The Import Data box appears. Make sure it is aimed at $A$1.

The data is imported into Excel

Make the top Row bold. Freeze panes.

Make the Trans No and Name columns narrower.

Right click for menu. Select: Data Range Properties – untick the Adjust Column Widths box. (this means that when you Refresh, the column widths will stay as they are)

Format the Amount column and make the data tidy. When you have finished, save this workbook as QBsales daybook.xls. Close.

5) Add new sales invoices in QuickBooks
Add a new sales invoice to QuickBooks.

In QB, go into Reports, Memorised Reports. Run Import Sales Daybook into Excel.

In the report, the new sales invoices are displayed at the top of the screen.

Export the report via: Print - File - Tab delimited file. Save it once again as QBsales.txt and in the same folder, replacing the previous version.

6) Refresh the data in Excel
Go into Excel. Open QBSales Daybook.xls.

Right click again. At the bottom of the menu, click on Refresh Data.

The Refresh Data box appears. OK.

The Import Text file appears, with QBsales.txt as the default file name.

Click on Import. The Excel file is refreshed and the new sales invoices appear at the top of the screen.

The report looks exactly the same as the QuickBooks report, with several lines for each invoice. But we can use the power of Excel pivot tables to summarise each invoice into a single line showing Gross, VAT and Net, as well as produce sales analysis reports showing which customer is buying what product, what margins are we earning etc, etc.

We’ll do that in Part 2 next week.

END OF SESSION

Replies (2)

Please login or register to join the discussion.

avatar
By axw001
19th Oct 2007 07:37

How's that for ease of use?
David - Did you say Part 2 next week???

I know that accountants have a long standing love affair with Excel but could i be cheeky and suggest as an alternative using a product like Liberty Accounts: Simply select menu option Reports--Other--Sales Day Book and you have your report. Same for Purchase Ledger, Bank Receipts and Bank Credit Card Payments.

Alan Wright
MD

Liberty Accounts Online Accounting Software
[email protected]

Thanks (0)
avatar
By perfectcover
25th Oct 2007 08:21

What about Access too?
We keep our database in Access and issue invoices from Quick Books.

At the moment we have to double key all contact information and sales history - this is both time consuming and obviously liable to error.

Only this week we contacted a customer, in error, trying to sell him a product he had recently purchased.

Any suggestions please?

Clive Steward - MD Smith's Taxation

Thanks (0)