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

mjbank
mjbank Member ✭✭✭
edited October 2023 in Product Enhancements
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).
Tagged:
26
26 votes

Reviewed · Last Updated

Comments

  • mjbank
    mjbank Member ✭✭✭
    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
    Quicken_Natalie Moderator mod
    edited February 2020
    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
    Jim_Harman SuperUser ✭✭✭✭✭
    edited February 2020
    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. 

    QWin Premier subscription
  • mjbank
    mjbank Member ✭✭✭
    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
    EmKay Member ✭✭✭✭
    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!

    Quicken Classic Premier (Windows) R52.33

  • Netpog
    Netpog Member
    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
    made_in_ga Member
    this is definitely something i need to have implemented...
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    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.
    QWin Premier subscription
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭

    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.

    QWin Premier subscription