Quicken Utilities For Excel
The idea for this week's tip came from conversing with Dr. M, author of the great weekly Quicken tips newsletter. If you are a Quicken user, subscribe to the free Dr. M's Quicken Newsletter for great weekly tips. Send an e-mail to firstname.lastname@example.org with SUBSCRIBE in the subject line. I highly recommend it.
I love Quicken, but it certainly has its annoyances. I have a certain memorized report in Quicken with categories down the side and months going across the top. Quicken offers the opportunity to print this report, but of course, I always just use the Copy command to copy the report to the clipboard and then use Edit > Paste in Excel to copy the report to the clipboard. This feature is much faster than the older (and still available) print to a .prn file option.
Here is where the annoyances pop up. First, Quicken did not bother to copy the column headings with the report. So, I manually must enter the month names in Excel. Simple enough. Second, the categories that get copied to the clipboard include an annoying outline format for categories and sub-categories.
Quicken allows you to use categories and sub-categories to classify your expenses. In the report at left, the automotive expenses are further broken out by insurance, gasoline, repairs and license plates. Perhaps I did a poor job of setting up my categories, but I find that I have some categories where I want to see the subcategory detail and other categories where I would prefer to just see the category total.
I would also like to be able to sort this report in Excel. It would be useful to sort it by total expense, then sort it back by category. Sure, I could use undo, but I would like categories that are alphabetic in nature. In short, I don't care for the outline format used by Quicken.
Long time readers will remember my disdain for the outline format used by pivot tables in Fill in Pivot Table Blank Cells Using Go To Special tip. We have the same situation here. If the Quicken report is merely an intermediate step and you want to be able to sort by category, the outline format is horrible. After sorting by totals and then by category, the Auto:Insurance category will be mis-sorted into the "I" section of the report. For the categories where I keep only the total, they will be incorrectly sorted into the "T" section of the report.
There were two utilities that I thought would ease this situation. Utility one is called collapse. When invoked, this macro will collapse a sub-category into a single line with a proper category name. In the example above, running the macro while the cell-pointer is anywhere in rows 34 through 38 will replace the category in A38 with " Computer" and delete rows 34 through 37.
Utility two is for the categories where I would like to see the subcategory detail, but do not need the heading, the dashed subtotal line, nor the category total. This utility is called Fill. It will find the proper category name, and prefix each subcategory with the cateogry. In the example above, running the macro while the cell-pointer is anywhere in rows 24 through 30 will result in cells A25:A28 being changed to a format like " Auto:Insurance". Rows 24, 29 and 30 will be deleted.
At the right, is my improved version of the report. By assigning Collapse and Fill to hot keys, I was able to make these changes with just a few keystrokes. It is now easy to sort the report, knowing that the report can return to it's original sequence by sorting the category.
If you are new to macros, review Introducing the Excel VBA Editor.
Once you copy the macro, you can assign a hot key by following these steps:
- From the Tools Menu, Choose Macros then Macro
- Highlight the Fill macro. Click Options. In the Shortcut field, enter any letter. I use f for Fill. Click OK
- Highlight the collapse macro. Click Options. Pick a letter for a shortcut, but stay away from c, as Ctrl+c is the common shortcut for Edit>Copy. Click OK
- Close the macro dialog with Cancel.
As part of his quest to design an add-in a day, MrExcel's summer intern, Anhtuan Do created the following macros.
Option Explicit Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows Dim Counter As Integer 'Counter to ensure searches are relative to activecell Dim TempString, TempTest As String 'Strings that are used as temporary holders to compare Dim CategoryName As String 'Name of the category currently in Sub Collapse() 'To collapse the rows, run this macro Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Replace "TOTAL Category" with "Category" Cells(TotalRow, 1).Value = " " & CategoryName 'Deleting sub-category rows Rows(HeaderRow & ":" & TotalRow - 1).Delete Shift:=xlUp End Sub Sub Fill() 'To add the CategoryName to each of the types, run this macro Dim i As Integer Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Adding CategoryName and colon to the start of each sub category For i = HeaderRow + 1 To TotalRow - 2 TempString = Trim(Cells(i, 1).Value) Cells(i, 1).Value = " " & CategoryName & ": " & TempString Next i 'Deleting Header and Total Row Rows(TotalRow & ":" & TotalRow - 1).Delete Shift:=xlUp Rows(HeaderRow).Delete Shift:=xlUp End Sub Sub FindHeader() Dim i As Integer TempString = Cells(ActiveCell.Row - Counter, 1).Value 'Checking to see if in first row If (ActiveCell.Row - Counter) = 1 Then MsgBox "You are not in a collapsable row" End If 'Checking to see if in a valid row 'If it reaches a TOTAL before reaching a colon, then error If Left(Trim(TempString), 5) = "TOTAL" Then MsgBox "You are not in a collapsable row" End If 'Checking the String for a colon For i = 1 To Len(TempString) TempTest = Mid(TempString, i, 1) If TempTest = ":" Then CategoryName = TempString Flag = True Exit For End If Next i End Sub Sub FindTotal() Dim i As Integer 'Finding the TOTAL Row TempString = Cells(ActiveCell.Row + Counter, 1).Value TempString = Trim(TempString) If TempString = "TOTAL " & CategoryName Then Flag2 = True End If End Sub