Export "All Transaction" to Excel - split categories are formatted incorrectly

mjbank

When I go to All Transactions and do Export to Excel, it isn't a "real" spreadsheet. For rows that have Split categories, you get a full row first with all the fields, but each additional split category has a partial row that does not include Date, Account, Num, or Description.

THIS SHEET CANNOT BE SORTED (all the additional splits end up as orphan rows).

And, to make matters worse, the original order that you are looking at in the register (i.e., sort by date) is not preserved when the excel export is produced (it ends up sorted by account).

mjbank

I am guessing you did this to help visually "group" the related rows together under the one transaction they are associated with (not a good excel practice).

You could, theoretically do that visual grouping if you changed the "S" num code to "S1", "S2", etc. "S1" would be used on all the rows for that first transaction that had split categories, "S2" for the next one encounterd, and so on.

Quicken_Natalie

Hello @mjbank,

Thank you for bringing this to the attention of the Community!

I went ahead and turned this discussion into an Idea thread so that other users are able to vote on it. Our developer's review Idea threads to get an idea of what people would like Quicken to offer.

Be sure to add your vote by clicking on the gray triangle right above the current vote count, located in the blue box at the beginning of this thread:

Thank you,

Quicken Natalie

Jim_Harman

You might want to consider the clever approach described here
https://community.quicken.com/discussion/7126704/exporting-to-excel-lots-of-blank-cells

Or this approach to fill in the blanks after exporting (thanks @mshiggins for finding this)
https://thesoftwarepro.com/excel-tips-how-to-fill-blank-cells/

You can Google "Excel fill blank cells with cell above"  (wthout the quotes) for more ideas on how to do this in Excel. 

mjbank

Jim,
Thank you! I decided to write a macro to do this in a way that preserves the ability to sort. (For one thing, I wanted the split rows to remain together under various sorts - For another thing, I wanted to get rid of blank rows and summary rows to achieve an orthodox/traditional spreadsheet.)

For the Stout-of-Heart excel maniacs, I will attempt to post the code here...

Sub RePopulateRegister() ' Created 20200214
' License/Copyright: None = Totally free use/copy/modify/share, but USE AT YOUR OWN RISK
' If you feel like it, you can credit author, www.jfkelley.com
' Purpose: Fill in partially blank, orphan "sub-rows" on Quicken Register Excel export.
' Why: So this will be a proper spreadsheet that can be sorted on any column
' without orphaning the splits/sub-rows that only have content in the last few columns.
' It endeavors to keep the split/orphan rows together with their parent rows.
' It also allows the user to "pad" the data section of the grid with extra rows and columns at top and sides (i.e., for formulas).
' What: Will populate Date, Account, Num, Description on the blank cells in the orphan sub-rows with data from the parent row.
' The Num column on split/orphan rows will populate with "S*".
' The Description column on the orphan sub-rows will have " SPLIT <NN>" appended to the end (for sorting)
' (<NN> will be like 01, 02, 03, etc.)
' The Description column in the parent row will have " SPLIT 00" appended to the end.
' How: This is not elegant code, it is verbose to make it easier to understand what's happening (see "USE AT YOUR OWN RISK" above!)
' Note: THIS MACRO WILL DELETE THE SUMMARY ROWS BELOW THE DATA ROWS! They would break any sorting/summing you'd want to do.
' Tip: To sort by Date, first sort the Description column (Ascending) and then sort the Date column (in either direction).
' Tip: To enable sort, select header columns Date through Amount and click Excel menu: Data -> Filter
' Caveat: This macro is "brittle" in that it is based on the format of Quicken Register excel exports as of Feb 2020.

'First, find the header row within the top 20 rows with "Date", "Account", "Num", "Description" as first few labels...
Dim r As Integer, c As Integer, nDone As Integer, rowHdr As Integer, i As Integer
Dim stVal As String
hdrRow = 0
nDone = 0
r = 1
c = 1
stVal = Cells(r, c).Value
Do While (r < 21 And stVal <> "Date")
If c > 10 Then
c = 1
r = r + 1
Else
c = c + 1
End If
stVal = Cells(r, c).Value
Loop

If Cells(r, c + 1).Value = "Account" And Cells(r, c + 2).Value = "Num" _
And Cells(r, c + 3).Value = "Description" And Cells(r, c + 4).Value = "Memo" _
And Cells(r, c + 5).Value = "Category" And Cells(r, c + 6).Value = "Tag" _
And Cells(r, c + 7).Value = "Clr" And Cells(r, c + 8).Value = "Amount" Then
' We found what appears to be the valid header row; set the column numbers...
Dim colDate As Integer, colAcct As Integer, colNum As Integer, colDesc As Integer, colMemo As Integer, colAmount As Integer
colDate = c
colAcct = c + 1
colNum = c + 2
colDesc = c + 3
colMemo = c + 4
colAmount = c + 8

hdrRow = r
Else
MsgBox ("Couldn't find a header row with Date, Account, Num, Description, etc.")
Exit Sub
End If

r = hdrRow + 1

' Now delete any entirely blank rows at the top of the data section under the header row...
Do While Application.CountA(Range(Cells(r, colDate), Cells(r, colAmount))) = 0
Cells(r, 1).EntireRow.Delete
Loop


' Next, find the last true data row...
' The last data row of the original report is followed by a row with just a date range in the Date column and a total amount in the Amount column.
' So, a valid data row has either a valid Date in the date column or has a blank Date but values in later columns (i.e., not a blank row)
Do While isdate(Cells(r, colDate)) Or (Cells(r, colDate).Value = "" And Application.CountA(Range(Cells(r, colDate), Cells(r, colAmount))) > 0)
r = r + 1
Loop

' Delete summary rows (assume there won't be more than 20!)
For i = 1 To 20
Cells(r, 1).EntireRow.Delete
Next

' Now we're ready to proceed...
Dim stDate As String, stAcct As String, stNum As String, stDesc As String, stMemo As String
Dim stLastDate As String, stLastAcct As String, stLastNum As String, stLastDesc As String, stLastMemo As String
Dim dtDate As Date, dtLastDate As Date
Dim nSplit As Integer

stLastDate = "": stLastAcct = "": stLastNum = "": stLastDesc = "": stLastMemo = "": dtLastDate = 0: nSplit = 0 ' start "clean"

' Walk remaining rows looking for partially blank "orphan" rows...

r = hdrRow + 1
Do Until Application.CountA(Range(Cells(r, colDate), Cells(r, colAmount))) = 0
' Collect values of appropriate columns for this row...
stDate = Cells(r, colDate).Value
stAcct = Cells(r, colAcct).Value
stNum = Cells(r, colNum).Value
stDesc = Cells(r, colDesc).Value
stMemo = Cells(r, colMemo).Value

dtDate = Cells(r, colDate)

If stDate = "" And stAccount = "" And stNum = "" And stDesc = "" Then
' For orphan sub-rows, copy in values from the previous parent row...
nDone = nDone + 1
nSplit = nSplit + 1
Cells(r, colDate) = dtLastDate
Cells(r, colAcct).Value = stLastAcct
Cells(r, colNum).Value = "S*"
Cells(r, colDesc).Value = stLastDesc & " SPLIT " & Format(nSplit, "00")
If nSplit = 1 Then
Cells(r - 1, colDesc).Value = stLastDesc & " SPLIT 00"
End If
If stMemo = "" Then
Cells(r, colMemo).Value = stLastMemo
End If

Else
' Otherwise, set the previous values in case the next row is an orphan sub-row...
dtLastDate = dtDate
stLastAcct = stAcct
stLastNum = stNum
stLastDesc = stDesc
stLastMemo = stMemo
nSplit = 0
End If

r = r + 1

Loop

MsgBox ("We re-populated " & nDone & " orphan sub-rows.")
End Sub

EmKay

I appreciate the responses attempting to provide workarounds but how can this be put to Quicken as something that they, after many years of suffering this problem, need to fix. This problem is long-standing, and just (really) stupid. Who writes data to the rows and columns of a spreadsheet but leaves out data so that the data is unfilterable and unsortable? It's ridiculous.

Quicken - fix this!

Netpog

Quicken's ill-conceived and utterly sloppy Export-to-Excel feature serves as a constant reminder that those developers don't understand how actual financial analysts do things. (Quickbooks, similarly, has limitations that are laughable, if you've encountered professional-grade accounting software. It literally refuses to let you run reports on some of your own data.)

So the blank rows are there because the developers don't grasp that pretty PDF reports (with, say, ONE instance of the category labeling dozens of items) are NOT THE SAME as useful spreadsheets, which should NEVER use a blank cell to indicate "same as above".

Actually-numerate software developers know how spreadsheets and tables and databases work: If I've 100 items under a given category or date, then I need that category or date value in each and EVERY row. If I've a split, I need ALL the data for each subitem. OF COURSE I DO.

I can write a formula to fill in the blanks, and remove the subtotal-category labels that are idiotically inserted into the same column as the data (really!?), and so I can make the exported data ready for processing in a table or pivot report or dataset, but that work is only necessary because the Quicken developers aren't competent to get it done.

The company (as I know from digging into their Quickbooks process) simply has no internal systems for gathering and escalating systemic bugs and user-experience failures. (While YOU are always welcome to leave feedback, the support reps are powerless to file bug reports. And the usability testing is provably absent, or at best defective.

Sorry if I sound bitter, but this is the sort of bug that other companies fix within a few months. These problems are YEARS old.

made_in_ga

this is definitely something i need to have implemented...

Jim_Harman

This change would make the Excel export much more useful. As others have noted, users export to Excel so that they can manipulate the data there, not to make a spreadsheet that duplicates the appearance of the printed report but needs extra work before it can be filtered or sorted

Providing an option to export the underlying query results rather than the formatted version would resolve this problem.

Jim_Harman

The issue of blank cells also affects the Investment Transaction report, where compound transactions such as Sells and Reinvests are split into two lines, one for each underlying transaction.