Converting Quicken Report to Excel Pivot Table

Jay Gourley

Quicken exports its report pivot tables to Excel as flat spreadsheets. Has someone documented a step-by-step procedure to reconstruct the pivot table in Excel? It's not simple, and I don't want to waste time reinventing the wheel.

NotACPA

By having asked the question, it sounds like you're familiar with Excel Pivot tables, is that correct?

Why/how is Q's export (once loaded into Excel) any different from any other Excel spreadsheet?

Jay Gourley

Thanks, NotACPA. I don't understand your question. As I described the issue in my post, I believe the expandable/collapsable rows in Quicken reports export to flat spreadsheets with no nesting structure in Excel. Your question makes me think that might be wrong. Excel is capable of managing spreadsheets with the same expandable/collapsable rows that Quicken reports can have. Excel calls them pivot tables, I believe. But the Quicken reports I've exported to Excel do not make use of that. All the nested structure in Quicken is lost in the Excel spreadsheet. Can you make your question more precise?

NotACPA

Just so we're on the same page, let's talk about a specific Q report, and what you want to do with it.

Name any Q report, I'll create it in my file, and we can proceed from there.

Jay Gourley

Thanks, that would be great. Some of my budget categories have three levels of nesting. So either "spending by category" or "current budget" would be a good choice.

Scooterlam

@Jay Gourley

If you are looking to export the Current Budget report to Excel and preserve the nesting only, here is what I do. Note that this approach does not preserve the expand / collapse feature of the Current Budget Report. I've never had the need to put this budget in a format for use with Excel's pivot table functions Perhaps this will help you.

1. Launch the Current Budget Report. Hide the Graph. Modify the Display>Organization and select Category Groups.

2. In the Current Budget Report, open Print Dialog and select Print To>Export To> tab-delimited (Excel compatible) disk file and save to disk. Saving the report this way will preserve the formatting.

3. Open this saved budget file with Excel and select Finish in the Text Import Wizard.

4. Excel will parse the text file into a format that preserves the nesting structure (but without the collapse/expand capability).

Essentially this is now in mostly the same format as the Current Budget Report.

5. From this "raw report" I launch a macro that further refines and formats the report structure to my liking. I have not played around with subtotal nor pivot table functions in Excel. I haven't the need. I will extend the macro if I want to do some further forecasting.

6. Final formatted output in Excel from Current Budget Report. Start to finish is approximately 20 seconds.

Jay Gourley

Thanks, NotaCPA. That's helpful. It still doesn't get a transportable file that's easy to evaluate. A better alternative would be to print .pdfs straight from Quicken of the report at various levels of expansion. The most digestible and transportable solution is still a pivot table structure (rows that expand and collapse) in Excel. Hopefully, someone has already done the work documenting the easiest way to create that in Excel.

NotACPA

@Jay Gourley it was actually @Scooterlam, not me, who provided that info. I've been working on YE tax planning, and hadn't gotten back here until just now.