1. Excel: Switching Rows & Columns

    Every so often I find the need to swap (or transpose) rows and columns in Excel.

    Take a report generated in Quicken, for instance. I like to see how my spending categories change from month to month. Getting Quicken to generate a Spending Report that shows this is really easy. In the Quicken menubar, REPORTS → SPENDING → SPENDING BY CATEGORY will get you there. Generate the report, then select your Date Range. Add a column for “Month” and you’re set:

    Spending-By-Category Report

    Exporting this data to Excel is a simple matter, too. In the Quicken report menubar, choose EXPORT DATA → REPORT TO EXCEL-COMPATIBLE FORMAT, then give your data a save location and a name, and save it.

    One problem, though: When opened in Excel, Quicken reports usually have the date periods as columns, and spending categories reside in rows. What if you want your categories in columns, and your dates in rows? (This is usually my preference.)

    Thankfully, Excel makes such a switch very easy to do.

    Transposing Rows & Columns in Excel

    As an example, I created a “Spending by Category” report in Quicken which shows my auto expenses for a portion of 2010 (March 1 thru July 31):

    Spending-By-Category Report

    Exporting that to an Excel-compatible format gives me a text file, which I named “Data2.txt” and saved on my desktop. I then opened a blank Excel spreadsheet, and from within Excel, I then opened Data2.txt.

    Importing a text file to Excel like this is quite easy: In the Excel menubar, select FILE → OPEN. Navigate to the text file you wish Excel to import. Excel opens its Import Wizard, where you can change column breaks and ignore rows as necessary. When you’ve finished this, click OK to close the Import Wizard, and your text file should now be converted into Excel.

    Readers who wish to follow along with the files I’m using can get them here:

    Excel File: Sample Data (ZIP file with Data2.txt and Data2.xls inside)

    Just download and extract that ZIP file, and you’ll have the files I use below. Play with them as you wish!

    Once the text file has been opened in Excel (I’m using Excel 2010), it’s time to work some magic. We want our date headers to be in rows, rather than columns, and our categories to be in columns, rather than rows.

    First, select the area of data to be transposed. In this case, that’s B5 thru H13:

    Select the data to transpose.

    Now right-click inside that area, and select COPY:

    Right-click and select COPY

    Now place your cursor in the spot where you want the data to be moved (and transposed) into. For this example, I’ll select Cell B15. In that cell, right-click again, and choose PASTE SPECIAL:

    In the PASTE SPECIAL menu that appears, select TRANSPOSE:

    PASTE SPECIAL -- TRANSPOSE

    Our data rows and columns have now been switched (transposed)!

    Data is now transposed.

    I can’t tell you how many times that this feature of Excel — being able to swap rows and columns with a few clicks — has saved me TONS of work!




     

     

  2. Quicken Users: What Do Tags Do For You?

    Reader Kelsey emailed me with a Quicken-related comment a few days ago. Buried in the middle of it was a question that intrigued me:

    Categories I get, but there’s these tag things … what would anybody even do with those?

    Personally, for my household, I haven’t really come up with a good use for tags in Quicken. To this point, categories have taken me everywhere I need to go. (I’m currently using Quicken 2010 Deluxe, and have reviewed it previously.)

    Quicken Tags: What’s the Point?

    Basically, tags give Quicken users a way to “categorize” transactions outside of, and across, categories. I guess you could call tags a “second level” of categorizing goodness.

    Suppose you wanted to sort of “sub-track” your grocery spending so that you could see how much of your grocery spending was attributable to unhealthy food. You could do something like this…

    … and then run a report as necessary to see how much you’ve been spending on foods that will kill you. But in reality, such a usage of tags wouldn’t be all that novel. After all, you could do the same thing with categories. Simply have a subcategory of “Junk Food” in your main “Grocery” category, and you’d be set.

    However, say you wanted to track all your “Nonessential” spending. That’s a “tag” that could span across categories because, after all, “nonessential” could apply to Groceries, Entertainment, House Repair & Remodel, and just about any other category you could think of.

    So keeping an eye on “Nonessential” spending, via a tag named “Nonessential” or something similar, is more along the lines of what Quicken intended tags to accomplish.

    Possible Use of Tags: Tracking Your BMF

    One “big picture” idea for tag-use that comes to mind — but which I’d be way too lazy to implement — would apply to anyone who wanted to follow Elizabeth Warren’s Balanced Money Formula, as described in her book All Your Worth (review).

    Warren advocates that folks classify their outflows as one of three types: “Must-Haves,” “Savings,” and “Wants.” Then track where your money’s going, and aim for the following percentages:

    BMF Targets: 50% Must-Haves, 20% Savings, 30% Wants

    I’m good with using those three “types” to track spending and saving, and to create a plan for such, but I’m a Certified Data Dork, too. I would also want to know what I was spending on, say, groceries, household consummables, mortgage debt, and so on.

    So, in Quicken, I’d categorize my spending normally as regards the groceries, dining, and so on. But then I’d also give my spending “tags” of Must-Haves, Savings, and Wants as applicable. That way, I could quickly generate a Quicken report (utilizing those tags) to show me how my BMF-style money plan was working out.

    Possible Use of Tags: Monitoring Use-Tax Expense

    For a while, I really thought I could make great use of Quicken’s tagging feature by assigning specific tags to my use-taxable online purchases throughout the year. By assigning a tag of something like “Use Tax” to all my online purchases on which I hadn’t paid sales tax at the time of purchase, I could, at tax time, fire up a simple report and see how much I needed to remit in use taxes to my state’s taxing authority.

    In the end, though, I decided to treat my use-tax liability as what it really is — an ongoing “debt” that I owe to the state, and which I pay off in April of each year. So I accrue for it in its own Quicken liability account, as detailed in my Quicken: Handling Use Tax tutorial.

    What Have You Made Tags Do?

    I’m sure lots of people have put Quicken tags to work for them — I’m just not one of those folks. To date, I’ve been able to make categories do ALL my heavy lifting.

    So what about you? Have you come up with a great use for Quicken tags that I’ve overlooked?




     

     

  3. Quicken’s Register Calculator

    As readers probably know, I’m a big fan of Quicken. It’s my finance-tracking tool of choice, and has been since the mid-1990s.

    Last week, I received an email from reader Dennis, who apparently has caught on to something of a shortfall in Quicken’s internal register calculator. For those of you who aren’t sure what that is, here’s a screenshot:

    Quicken's internal register calculator

    If you have numbers to add or subtract, you can do it inside the SPEND and RECEIVE columns in Quicken’s register. Anyhow, here’s what Dennis had to say:

    In your review of Quicken 2010 you revealed that you have been a Quicken user for a number of years. Personally, I’ve been using it since the second release.

    I’d like to alert you to a bug in Quicken that has been for many years. I submit a bug report on this issue with every new release, but apparently one user isn’t enough to promote change. Maybe you would have better luck.

    The Quicken Register Calculator does not perform calculations in the mathematically standard priority sequence of Parenthetical, Exponential, Multiplication, Division, Addition and Subtraction operations. Using the Register Calculator, the key strokes “2+3*3+5*3″ are evaluated as “(((2 + 3) * 3) + 5) * 3″ resulting in 60 (the wrong answer) instead of “2 + (3 * 3) + (3 * 5)” which results in the right answer of 26. Anyone accustomed to using any standard calculator will experience this error.

    To avoid this error, it’s necessary to add repetitive values individually when using the Register Calculator (2+3+3+3+5+5+5 = 26). It’s a lot easier (and more accurate) to use any external calculator and copy the result into Quicken; however, since that last step creates an opportunity for transcription error, so it would be better if Intuit would simply fix the Register Calculator. Of course they could rename it to “Register Adding Machine” which would at least alert the user that it doesn’t function as a ‘Calculator’.

    Dennis’ note caused me to think: I can’t remember the last time I used any Quicken calculator. I almost always have Excel open, so it’s kind of become my default “quickie” calculator. And now that the calculator in Windows 7 shows you all the numbers you’ve entered …

    Windows 7 Calculator

    … as you input your formula, I have even less reason to utilize any “calc-ability” inside Quicken.

    I spent some time trying to think of a situation where having Quicken calculate this way — computing what is effectively a formula, and doing it without standard mathematical priorities — would be an issue for me, but I couldn’t come up with one. Again, I’ve grown accustomed to using Excel as my calculator for pretty much everything.

    I suspect that Intuit programmers never really intended this feature to do much more than add or subtract a string of numbers. Don’t guess I can really fault them for that, either. It’s awfully tough to program software that can do everything for everybody!




     

     

  4. How to Know You’re a Dork

    This past week, I replaced my laptop (a 5-year-old Dell Inspiron) with a fresh ‘n’ shiny Toshiba Satellite A665-S6050.

    Inherent in this upgrade was my switchover from Windows XP Home, which I loved, to Windows 7 Professional, for which I don’t yet have a verdict.

    (No, I never so much as bothered with Vista. In fact, a couple of years ago, I purchased a second Dell laptop for my wife. My first out-of-the-box change? Wipe the hard drive and its Vista operating system, and replace it with a clean, non-OEM-cluttered full version of XP Home.)

    However, based on something I just discovered, Windows 7 just got big bonus points in my book. If you’re a power user of the calculator in Windows, as I am, you just gotta love this revamp:

    Windows 7 Calculator

    Giggity.

    And yes, I am a complete dork.




     

     

  5. Online Personal Finance Tools

    The answer to Jim’s question is “No, I don’t.”

    Great. That’s out of the way.

    So what was the question?

    Bargaineering: Do You Use Online Personal Finance Tools?

    Call me crazy. Call me “stuck in the 1990s.” Call me whatever you like. But I’m very much a guy who feels better when my personal-finance software, and the vast amounts of sensitive data therein, reside right on my hard drive.

    Now, when I say “personal finance software,” what I’m talking about is stuff like Quicken. Spending, saving, and account-balance tracking software. And I know there are lots of super-cool online-based tools out there these days — heck, Quicken and Quickbooks offer online-access versions, but even here, Intuit is way late to the game. I, however, am not yet sold on the concept.

    I like knowing that my household’s financial data is stored locally. “Locally,” as in, in my laptop. On my kitchen table. (And in a backup hard drive that’s also “locally” stored.)

    Actually, Jim’s (the Bargaineering author) feelings on the subject very much mirror mine:

    I trust all the services to do the right thing and to protect my information and privacy, but I know that sometimes mistakes are made and things can happen. Maybe I’m a little too old school; I’m about the age where I am comfortable telling people where I am via Twitter but not comfortable telling a third party my banking credentials. Mint and the like will treat it with the sensitivity it deserves but … you never know.

    Precisely. It could be, though, that I’ll rethink this stance in the coming years. Since I don’t ever use Quicken’s “transaction download” features, and thus would never store any account-access credentials with Quicken/Mint or whomever, the only info I’d be leaving on their servers would be account-balance and spending data. And who’d give a crap about that, aside from some data-mining, consumer-research enterprise?

    (Note to any data-mining, consumer-research entities who read this post: My life is boring. My spending patterns are boring. Steal my data, and you will be underwhelmed. I promise.)




     

     

  6. Simply Money and Windows 7

    Kiplinger's Simply Money

    Ready for a trip back in time? If so, grab a copy of Kiplinger’s Simply Money. And get ready to see 1995 all over again.

    I reviewed Simply Money back in 2006 after receiving a free copy in the mail. The program takes you back, for sure. If you’re looking for financial-software simplicity — and don’t give a flip how it looks on your computer screen — then Simply Money is your huckleberry.

    Simply Money Desktop

    However, there may now be an issue for those folks who use Windows 7. As noted by reader Dick, via email:

    I found your article on the subject through Google. I’ve used this software since 1993 (latest version is 2.08, 1995) and have found it to be most useful. I just (inadvertently) moved up to a 64-bit Dell desktop running Windows 7 and the software will not load (even though it was fine through Windows 3.1, 95, 98, and XP). Do you know of any patch or fix that will let me continue to run my old friend? I’d really hate to move to Quicken or one of those other packages with the unnecessary bells and whistles.

    Well, if we’re to believe what we read at SimplyMedia.com, Simply Money doesn’t appear to play well with 64-bit systems. They write that a new version/patch is in the works…

    [Simply Money] Works on Vista up to 32 Bit. 64 Bit Vista not stable enough yet. Stay tuned for that improvement from Microsoft–and then from us with Simply Money to be compatible with their 64 bit Vista.

    I’m not sure exactly when 64-bit Vista hit the ground, but it’s been a while. And now we have 64-bit Windows 7 out there … and still (apparently) no fix for Kiplinger’s Simply Money to make it usable on such systems. The same site has a small tech support page for Simply Money, but alas, no answers are to be found there, either.

    In any case, Simply Money still deserves a place in my list of Quicken alternatives, but for those of you who are using 64-bit operating systems, you may (for now) wish to look elsewhere.




     

     

  7. Quicken Tip: Set Up Monthly Transfer Reminders

    It’s a bit of knowledge of which some folks are still unaware: You can set up recurring account transfers in Quicken the same way you set up recurring bills.

    Created this way, such transfers will appear on your BILLS screen, along with all your other recurring expenses and deposits. For my household, I’ve set up our monthly Freedom Account deposit this way, as well as my transfers to a less-used checking account outside of our normal ING Electric Orange account. (The less-used account handles our annual life-insurance premium payments, our monthly MCC tax credit fees, and our child’s medical-insurance premiums — as these must come from an account at an in-state financial institution, which ING Direct is not.)

    What’s the Advantage?

    If you’re anything like me, you’re a busy soul. Money and bills aren’t the only things you have to worry about. And regardless of how much coffee you drink, or how many gingko biloba pills you pop, you still go through periods where anything that didn’t get written down, gets forgotten.

    Quicken’s “recurring bills” feature is a great failsafe against just such memory lapses — those that affect your financial life, anyway.

    Quicken BILLS Screen

    Every month, your recurring bills, deposits, and transfers appear as a list of items that “check off” as you complete them. Actually, these days, all decent financial software programs offer this feature in some fashion or another. So it isn’t as if this feature is specific to Quicken. Quicken happens to be my software of choice, so that’s where I focus.

    (In Quicken 2010 Deluxe, you can set the program to always open to your BILLS desktop. If you’ve set up all your recurring transactions correctly, this makes it darn near impossible to EVER forget one.)

    Setting Up Recurring Transfers in Quicken

    I’m currently using Quicken 2010 Deluxe (review), so screenshots and instructions will be applicable to that version. Recent Quicken versions have offered similar functionality.

    Let’s say you make a $371 transfer to your Freedom Account savings each month. To set this up, you could go to the Quicken menubar, and select…

    BILLS → ADD REMINDER → TRANSFER REMINDER

    Alternately, from the BILLS tab (desktop), click the ADD REMINDER button on the right side:

    Both methods pull up the EDIT TRANSFER window.

    From there, simply fill out the data fields as required. Most are self-explanatory. Note that I label the “PAYEE/PAYER” field with my nickname for the transfer itself, rather than a “payee” name in the usual sense. This makes the transaction more recognizable when viewed in your BILLS list … which is where it will appear from this point on!




     

     

  8. Quicken Tip: Use Account Number in Account Name

    If you have a lot of accounts to track in Quicken — as my household does — you might notice that keeping track of which ones are which can be somewhat difficult.

    When I’ve helped others set up and use Quicken, what I’ve seen is that most of them will use pretty standard account names in their Quicken sidebars. “Jane’s Mastercard” and “Chase Sapphire Visa” are examples of what I commonly see.

    Those account names are fine so far as they go. But one thing I’ve discovered over the years is that it’s also very helpful to put the last four digits of the account numbers at the end of your account names. Why?

    So that these digits show in the Quicken sidebar, too.

    Why I Started Doing This

    Since my household uses a variety of credit cards — gotta maximize those cash-back rewards! — it can be a challenge to determine which cards were used for which purchases. This is especially true when we have a handful of receipts waiting in our Cash Flow Box to be logged into Quicken.

    However, since most retailers’ receipts show the last four digits of the debit- or credit-card account used, having the “last four” also show in Quicken’s sidebar makes this task dead simple to accomplish. It’s a snap to see which card or account was used for that week-old Red Lobster receipt!

    Changing Account Details in Quicken

    To make this change (plus a host of others) to your account names in Quicken, simply right-click the account’s name in the sidebar. Select EDIT ACCOUNT from the drop-down menu that appears. That should bring up your Account Details window. These days, I’m using Quicken 2010 Deluxe; yours may look a bit different than this:

    Quicken 'Account Details' Window

    Simply make your changes in the “Account Name” text box, and you’re good to go. You can also change account-balance limits here, and enter credit limits for your credit-card accounts, among other things.

    All in all, using the “last four” in my Quicken account names has been fantastically helpful more times than I’d like to admit!




     

     

  9. Review: Quicken Deluxe 2010


    Quicken  Deluxe
    I’ve just added my review of Quicken 2010 Deluxe on the main IYM site.

    After a few days’ use, I’m hopeful that this version will be even better than 2008 Deluxe (review), which I found to be outstanding for my needs.

    I’ll say here that the 2010 version, while it took longer to install, does seem to perform day-to-day operations faster than did 2008.

    I’ve not yet experienced any of the glitches that other users have discussed on the ‘net, but we’ll see how it goes…




     

     

  10. How We Manage Our Money

    It’s been a while since I discussed how my household manages its money; the last time was in October of 2006. Some things have changed since then, and since readers continue to ask my opinion on ways to keep funds running smoothly at the ol’ homestead, I’d like to cover the topic again.

    Receipts, Receipts, Everywhere

    This, inevitably, is Issue Numero Uno for many readers: How can I keep track of my spending as well as my spouse’s? It’s impossible to know where the money’s going!

    Actually, it isn’t. Or, perhaps more correctly, it hasn’t been for us. Oh sure — it was a challenge for a while. Back when we were paying bills from our checking accounts (more on that later), we ran into a few obstacles. But once we became debt-free and were able pay our card balances in full each month, things got easier.

    Cash Flow in a Box

    So how to handle all those receipts? Well, we do it with a box.

    This invention, I call our Cash Flow Box. Whenever either of us spends money, we tuck the receipts into our wallets RIGHT THEN. Later, once we get home, we toss the receipts in our Cash Flow box. Mail and bills go here, too.

    Since I’m the guy who handles bill-paying and money-tracking for our household (gee, can’t imagine why), I sit down every couple of days and enter the receipts into Quicken. (You can tell I’m a sicko, because I actually enjoy this part. Then again, I’ve found that being in control of your money tends to have just this sort of odd, Twilight Zone effect on people.)

    If any receipts need to be kept for tax purposes (or some other reason), I have a set of manilla folders right next to the box for just this purpose. Think flexible-spending account receipts, small-business expenses, and large-item purchases (where warranty might be an issue) here.

    The rest of the receipts get File Thirteen’d as soon as I enter them in Quicken.

    Easy peasy.

    Joint Checking … Times Four

    For starters, our household has multiple joint checking accounts — four of them, in fact. And a host of savings accounts (online variety, mostly) on top of that.


    Click here to start saving with ING DIRECT!

    I primarily use our ING Direct Electric Orange checking, while Lisa uses a local credit-union checking account. Due to its extreme ease of use, ING Direct also holds most of our savings at present.

    Since ING Direct isn’t exactly a “local” banking entity for us — if you need to see someone face-to-face, whatcha gonna do? — we also have two joint, no-fee checking and savings combos at local institutions. We generally keep only a few hundred dollars in these “just in case” accounts.

    Pay It All By Plastic

    Here’s the caveat to all these checking accounts: We rarely pay for anything by check. Every expense than can go on plastic OR can be paid electronically will be handled that way. We use two cash-back, no-fee cards for this. We pay these cards in full every month.

    Because of this, we typically write no more than one or two paper checks per month.

    Spending and Account Balances


    I am a Quicken devotee. It is my Ultimate Money Security Blanket, and I’m not ashamed to admit that. I depend on Quicken like snow depends on cold.

    Right now, my laptop runs Quicken 2010 Deluxe (review), which I believe is one of the best Quicken versions yet.

    Quicken tracks our spending, our account balances, our net worth, our bills and recurring payments, and about a thousand other things that are only important once or twice per year. (Use taxes would be one!)

    And oh yeah — I now use Quicken for our…

    Budgeting!

    Honestly, we don’t need much of a budget these days. With no debt (other than our mortgage) and a definite aversion to long-term financial commitments, we just don’t have that many bills coming through the door. Savings-building is our goal now, and I can accomplish it just fine, thank you, with Quicken’s recently-added Cash Flow Tab.

    Cash Flow - Click to Enlarge

    What’s coming in? What’s going out? The Cash Flow Tab tells me what I need to know. Once I got our recurring bills and deposits set up, and designated the correct “spending” accounts for Quicken to monitor, I no longer had any need for my Spending Plan spreadsheet at all.

    I love my Spending Plan spreadsheet. But having my budgeting tool contained within Quicken makes things oh so simple.

    And simple is good.

    Download Transactions? Nope!

    I have never once used Quicken’s ability to download transactions from banks and other financial institutions. As noted elsewhere, I enter all Quicken transactions by hand.

    Keeps me “closer” to our spending, ya know? (Plus I’ve heard too many horror stories about transaction downloads going horribly wrong!)

    The All-Important Freedom Account

    I believe that the discipline to save up for future expenses — rather than relying on the kind-heartedness of Visa and Mastercard — is a hallmark of successful personal finance. Heck, it may be THE hallmark.

    In any event, we do such saving in our Freedom Account, which resides with the rest of our savings at ING Direct. Why?

    Because it’s darn easy (and immediate) to transfer funds to our Electric Orange checking, where the vast majority of our transactions land at some point. (We pay our credit cards electronically via Electric Orange.)

    This is one area where Quicken falls short. Since it doesn’t allow for subaccounts, I track our FA subaccount balances with ExcelGeek’s Freedom Account spreadsheet.

    Emergency Fund

    I don’t have a specific spreadsheet that I use to track my Emergency Fund. We’re currently keeping most of our E-fund (say, 90% of it) at ING Direct. Any transactions which affect our Emergency Fund get logged/tracked in Quicken, as noted above, and I can always see our E-fund’s balance right there in my Quicken toolbar.

    Small-Business Stuff

    Lisa and I both have our own small-business ventures. I utilize QuickBooks 2009 Pro to manage these tasks.

    Credit Monitoring & ID Safety

    I monitor our credit reports and scores monthly. I do this with TrueCredit 3-Bureau Credit Monitoring . (Here’s my TrueCredit review, if you’re interested.)

    Whew … that should pretty much cover it!