Tip: How to Export Quickbooks Multi-year P&L

Background

We often ask for multiple years of financial statements from our clients.  Here is the easiest way to run one simple report to send over.


 

1. Start by running a report in QuickBooks

(the ‘Profit and Loss Standard’ & ‘Balance Sheet Standard’ are suggested formats)

1QB PL Standard

2. Customize the Report columns

To make the import process more efficient its best to import a number of periods at once, PlanGuru will allow you to import up to 36 periods of financial data in a single import.   Your QuickBooks report can also be modified to include multiple columns of results by month or year.    Do this by clicking “Customize Report”, next with select month (or year) from the “Display columns by” drop down box, and finally update your date range (note QuickBooks will allow you to create a report with 36 or more columns)

2Customize QB Report

The result is a QuickBooks report with one column for each month or year of the selected date range.

3QB Report by month

3. Export the Report to Excel

You will need to get the report in a format that can be opened in Excel.  To do this click the Excel tab and select ‘Create New Worksheet’

4QB Create new Worksheet

4. Choose csv format, this produces the best results.

The default selection sends the file to an xls format, which provides account information in inconsistent columns, this is no good for importing into PlanGuru.

5QuickBooks - xls inconsistent columns

Instead choose to the .csv output, this will put all of your account information into one column, making it ready to import.

6QuickBooks - csv consistent columns

 

 


IF YOU ARE SENDING FILES TO US… YOU CAN STOP HERE!  We’ll make them pretty once we get them.  But if you are using this for your own purposes… go ahead and read on.  This is a great way to get useable Excel reports!

 


5. Use Excel’s Text to Columns to separate account numbers.

If your QuickBooks company includes account numbers this feature will allow you to parse out both portions of the account number and description into separate columns.  With your csv report open in Excel start by inserting a blank column into the report.

7Insert Space column

Next highlight the combined account number & description string and click on the ‘Data’ tab, and ‘Text to Columns’

8Text to Columns

 

In the Text to columns wizard select ‘Delimited’ in the first dialog.  In the second dialog select ‘Other’ and copy and paste the character QuickBooks uses as a delimiter into the text box to the right of other.  Then click finish to complete the text to columns process.

9Text to columns options

 

The result is a QuickBooks export with account numbers and descriptions in separate columns. NOTE: rows with that are still mixed, or that don’t have account numbers are parent account and total rows.  Its fine that these aren’t formatted the same, because we don’t want to import these into PlanGuru (they will result in double counting). These rows will get filtered out during the import process.

10Text to columns finished product

 

For more questions on the Excel “text to columns” functionality see this Microsoft forum post:  http://technet.microsoft.com/en-us/library/ee692872.aspx

X