1. Excel: How to Keep Leading Zeros

    As a guy who uses Excel to open CSV (comma-separated values) files pretty often, I’d like to offer a pro tip:

    Excel will always drop leading zeros in a column of data unless you *import* the CSV file!

    In my Day Job, I often have to work with CSV files which contain job codes which sometimes start with zero — like “08124500” and similar. If I just double-click that file and open it in Excel, well, those leading zeros will always go bye-bye.

    If that’s not what I need (and usually it isn’t), then the only way around it is to open Excel on its own, and then import the CSV file the, uh, slow way.

    (I’m using Excel 2016 currently, but instructions here will be similar for most versions from 2010 and later.)

    1. Open Excel; i.e., a new blank Excel file.
    2. In the Office ribbon, choose the DATA tab.
    3. Look for a section of the ribbon named “Get External Data.” Click the “From Text” button.
    4. Button - From Text

    5. A file selection window titled “Import Text File” appears. Navigate that to your CSV file. Click IMPORT.
    6. Excel will walk you through the multi-step file import. The important thing here is Step 3. Click the column with the leading zeros, and set its Data Format as TEXT, rather than GENERAL.
    7. When Excel imports your CSV data, the leading zeros will still be in place!

    So remember: When bringing CSV data into Excel, you have to account for the fact that Excel will always try to figure out what type of data you have — and to Excel, “08253641” sure looks like a number. Which means the leading zero will be dropped. And if Excel guesses wrong, you might have big problems!




     

     

  2. Study: Federal Aid Makes College More Expensive

    A basic tenet of economics holds that the more money you make available for something (of finite supply), the more that “something” is going to cost. In this case, that “something” is college tuition.

    Enter a new study from the New York Fed. Researchers found that increasing the availability of student loans and grants has — shockingly! — caused college to become more and more expensive. (Somewhere out there is a guy who’s mentioned this before.)

    New York Fed: Credit Supply and the Rise in College Tuition…

    The fun part of the PDF, for me, is Section 5 (page 21), where this little tidbit is found:

    Changes in sticker-price tuition have a coefficient of 0.40 on the change in Pell Grant amounts (column 1) and this effect is significant at the 5% confidence level. The economic magnitude of this coefficient is large and implies that a dollar increase in Pell Grants going to an institution is associated with a higher sticker price tuition of about 40 cents. The effect of an increase in subsidized loan amounts is higher, at about 63 cents on the dollar, and this effect is estimated to be statistically significant at the 1% confidence level. Finally, we see the effect of a change in unsubsidized loan amounts on sticker price tuition to be smaller at about 25% but still highly significant.

    All this talk of coefficients makes my head hurt, but I think I get the drift: The more money that FedGov throws at colleges, the more colleges will find ways to take it. Ergo, annual tuition increases of 5 or 10 percent, and more. (Current and future higher-ed students “take it,” too, but in a very different, X-rated manner.)

    The point estimates suggest that the pass-through of increased student aid supply to tuition is around 50 cents on the dollar, on average, although with some heterogeneity. …We find a sensitivity of changes in tuition to changes in subsidized loan amounts on the order of about 60-70 cents on the dollar, with estimates that are highly significant in essentially all of the specifications considered.

    So if you want to see college tuition rates get jacked up even more, year after year, just make more loans and grants available. Especially subsidized loans — those things are the ooey-gooey candy that just never runs out. (If you’re a college, anyhow.)




     

     

  3. Make Your Debt Known

    NOTE: This article was originally published on Money Musings in May of 2006. I’m republishing it now to update some info and links, for one thing, but mainly because I think the idea here — to tell others about your debt, so you can begin to overcome it — is worth repeating. Loudly.

    I have become a big proponent of an idea I first heard from Suze Orman: If you’re in debt, and want out, then you must tell others about your debt.

    I bring this up because this week my wife purchased a newsstand copy of All You magazine. I’d never heard of it before. Because “what I read” is not nearly as important to me as the fact that I just read SOMETHING, I have no problem tossing aside my manhood and flipping through a publication whose self-described audience is “Value-conscious American Women.”

    (Yeah, I’ll read articles from stuff like Cosmopolitan and Woman’s Day, too. Sometimes there’s personal-finance stuff in there, which I’ll read no matter where it comes from. Other times, it’s more like reconnaisance. Us guys can get an interesting look behind enemy lines that way.)

    Anyhow, in this issue of All You, there was a one-page article about a 25-year-old woman who’d piled up $20k in credit-card debt. Not much about the story (soapishly titled “I Was Hiding a Huge Secret”) caught my attention, until I hit this little snippet:

    …I joked to my friends about being in the poorhouse, but because I’d landed a great public relations job, no one guessed what was going on. I never told because I was afraid people would see me as irresponsible.

    One day, though, I was with my best friend, Mandi, and I just blurted out the truth. For the first time, I actually said aloud the amount I owed. It was a huge relief. Mandi, who was a loan processor for a mortgage company, reassured me that I could get my debt under control, and she offered to show me how.

    I don’t know about you guys, but in my experience, folks with money problems almost always get to that point in secrecy. Read that again: Folks with money problems tend to get there in secrecy. I would contend that this is a powerful and dramatic “common feature” in today’s financial world.

    We could spend days talking about why this secrecy, damaging as it so often is, plays out. It’s a cultural thing; money simply isn’t an open topic in social (and, often to a larger degree, familial) circles.

    It’s human nature. Acquiring debt can hint at shortcomings, and who in their right mind wants to advertise his or her shortcomings if they don’t have to do so?

    It’s a “commercial presentation” thing. Ever notice how credit-card bills arrive in discreet white envelopes, while credit advertisements can usually be spotted by your mayor’s son’s future parole officer from two blocks away? Think about the possible implications. If you’re in debt, it’s a “privacy thing,” and you’ll probably want to keep it quiet. If you’re debt-worthy, though, you want the world to know.

    I could probably come up with another 10 or 12 reasons, but I’ll save the wear ‘n’ tear on your browser and eyes, and move on to the good side of all this. More from our spendthrift 25-year-old:

    I’m so glad I opened up about my debt problem. Today I’m 29, and my credit rating’s stellar, my debt’s entirely gone and I’m considering starting my own public relations business.

    Here’s where we recite that old tenet about facing your problems head-on: You pretty much have to. No one else will do it for you, right? For me, a big part of facing my debt head-on was making it a public affair. (In the case of It’s Your Money and this here blog, well, that’s about as public as you can get.)

    I needed the opportunity for learning that this site provided me. I darn sure needed the accountability. I needed the responses from readers, some who’d already been where I was, and who years ago had found the EXIT sign, and some who were right there with me, practically in lock-step, from Day One.

    Not everyone needs these things, of course. Waging a successful war on debt can be done entirely in private. But given the situations I’ve seen in my life, and the people whose lives I’ve seen ruptured by debt, I sure don’t like the odds.

    This is why it’s always made me happy to see new blogs popping up — the ones where the authors have made a decision to move forward in their money lives, to vanquish their debts and whatever other baggage they might have, and to generally get on with kicking life’s butt. I am here to proclaim that it’s a worthy mission.

    There are a host of other goal-oriented bloggers who see fit to make their debt paydowns public, and I commend each one of them. I do still try to keep up with as many as I can. Because I’ve been where they are. By making their debts, their goals, and their actions public, they’ve turned vague “ought-tos” into unmistakable, concrete targets.

    They’re asking for an audience. They’re asking for accountability. This goes against pretty much everything our society preaches regarding money and debt. And by gosh, it takes big-time courage.

    If you’re thinking about doing something like this, about starting up a WordPress blog or LiveJournal or something like that to help you get your money straight, I say “Fire it up.” There’s a great deal to be gained when you open up the windows and let some light in.

    Make your debt, and your task, known.




     

     

  4. Excel or Access?

    It was a hard truth to learn, and it took years for me to learn it, but facts is facts:

    There are times when Excel just isn’t the right tool for the job.

    Now, Excel and I get along swimmingly. I’ve devoted hundreds of hours to learning its “ins and outs,” and I regret none of that. To this day, I still get a little giddy when I discover a new function that’ll be useful to me somehow.

    But darn it, Excel just isn’t the right tool for managing data … especially if it’s relational data. (“Relational data” meaning that data in this table over here has a specific relationship to data in that table over there … and maybe even more tables of other data beyond that.)

    Excel Was My Best Friend.
    Access Was a Weird Guy Across the Street. (Nobody Talks to That Guy.)

    I can’t remember when I was first introduced to Excel — probably the mid- to late-1990s — but I was a fan from Moment One. I also can’t remember when I was first introduced to Access (the database program Microsoft bundles with its Office suite), but it was probably ten years later, if not more. It took me a long while to figure out why anyone would bother with Access. Excel was SO much easier to learn and implement for whatever tasks I had at hand. In fact, at my Day Job, while Excel licenses were utilized by just about every employee in the place, it wasn’t until a few years ago that Access licenses were made available — and even then it was by request only.

    So without knowing it, early in my career, I was trying to shoehorn Excel into doing data-management jobs for which it wasn’t really built. One particular spreadsheet I created, maintained, and added data to over 15+ years, across multiple worksheets … only to find out last year why it should’ve been exported to an Access (or similar) database long, long ago.

    (When you need to search for, say, multiple part numbers across about 20 separate worksheets, you figure out pretty fast just why a good relational database is as valuable as it is. And why Excel isn’t good for that AT ALL.)

    So now I have this Excel spreadsheet with 15 years of work-related data crammed into it. It’s not unusable by any stretch, but depending on the searching that needs to be done, it can be, uh, ungainly. At best.

    For THAT data-management job, I should’ve used Access. I’d be much better off now.

    Excel vs. Access: Which Tool for the Job?

    So, having lived and learned, here are MY thought processes for deciding whether to use Excel or Access for any given job:

    • If it’s a numbers task, and you want pretty charts and complex calculations run on the data, then Excel.
    • If it’s a flat-file, one-table sort of database, then either Excel or Access might do the trick.
    • If it’s a multi-table database with no relationships between tables, then Access is probably a better choice. But Excel could be workable. (Especially if complex searching is not required.)
    • If it’s a data-driven task, which will rely on text, numbers, text and numbers, or other file types being accumulated, modified, and analyzed over time, then Access.
    • If you need to implement strict controls on non-numeric data which users enter and/or modify, then Access.
    • If your data needs to have relationships (e.g., this table shows part numbers, and that table shows work operations, and the two tables are somehow “related” in use and/or search-ability), then Access.
    • If the end result of your data requires complex queries, searches, and filters, then Access.

    Note that these aren’t hard/fast rules. They’re just how I approach the problem now. You see, I’m all the time watching folks try to make Excel handle jobs which would be much better suited for a database program of some sort. But suggesting such a thing (“What? But I already know Excel!”) is almost always dismissed out-of-hand. At some point, I’m afraid, they’ll be sorry.

    Hey — just because you CAN whack a nail into a two-by-four with a heavy Swingline stapler doesn’t mean that’s the way you OUGHT to do it.

    ASIDE: Hey, people look at me like I’m nuts when I tell them that Quicken is 80 percent database, 5 percent math and calculations, and 15 percent bloat and unnecessary features. But it’s troof. Quicken’s database back-end is the workhorse!

    Here, by the way, are Microsoft’s thoughts on the issue:

    Office.com: Using Access or Excel to Manage Your Data

    Much wordier than mine, but very thorough, and worth a read-through.




     

     

  5. Excel: Create Drop-Down Menus

    Thanks to my popular Excel page, I’ve gotten several questions recently about how to create drop-down menus in Excel. Yes, a quick Google search for “Excel drop-down list” or similar would likely educate most folks well enough, but I’ve haven’t posted in a long while … and this seems like a fine little tutorial to add to my collection.

    NOTE: I’m using Excel 2010 for this tutorial. Instructions would be pretty much the same for Excel 2007 and later versions.

    Excel Feature: Data Validation

    The feature in Excel that allows for us to use drop-down menus in cells is called Data Validation.

    Data Validation

    In truth, Data Validation allows a spreadsheet creator to control all kinds of things about what the user might enter in cell or group of cells. But when it comes to creating a drop-down list for users to choose from, well, that’s where lots of questions tend to arise.

    So let’s pretend that we have a spreadsheet in which we want our users to enter, in Cell B6, one of four choices:

    • Visa
    • MasterCard
    • American Expresss
    • Discover

    First, we need to put those options somewhere in our spreadsheet for Excel to find. So I’m going to enter our “list” of options in Cells A1 through A4:

    Data Validation - Create a List

    Note that I don’t have to create my list of options in the same worksheet; it could be in a different worksheet, too, and in any group of consecutive cells you like. The list can also be in hidden rows; that’s what I end up doing most often in my own spreadsheets.

    Now that we have our list, and with Cell B6 as our active cell, we’ll do the not-so-hard part:

    1. In the Excel ribbon, select the DATA tab.
    2. Click the DATA VALIDATION button.
    3. In the SETTINGS tab, find the “Allow:” box, and select “List.”
    4. Now, below that, in the “Source” field, we’ll tell Excel where to find the list of choices we want users to have. In this case, our list is in the area defined by $A$1:$A$4. (The dollar signs tell Excel that these are absolute references to those rows and columns.)

    5. In the “Source:” field, type “=$A$1:$A$4” and then click OK.

    And just like magic, you’ll see the drop-down menu indicator next to Cell B6. Click that, and our four choices appear in a drop-down menu:

    Data Validation - Drop-Down Menu

    And that, folks, is all it takes to create a drop-down list in any cell (or cells) of your Excel spreadsheets!




     

     

  6. My Third Computer Build

    It’s been almost three years since I built my first custom PC and discussed it on this here money blog. In recent time, that Intel i5-2500K-based desktop has been used almost exclusively by our middle-school-aged daughter (as our old Dell PC, circa 2003, eventually went bye-bye). Should’ve seen that coming, right?

    Lisa and I have our own laptops, so we’re covered if either of us want to do basic home and office stuff. But if we want to game, we’re again relegated to the PS3 or PS4 … unless we feel like pushing Dear Daughter off the PC for a while. (Never fun for anybody, really.)

    The obvious solution, if you’re a geek like me? Build a new PC! Merry Christmas to me!

    Why Build? Why Not Buy Premade?

    Because it’s fun, mostly. And because there are certain features I want on (and not on!) my PC. And, yes, because sometimes your inner geek needs to come out and play.

    But beyond that, let’s consider my First Rule of PC Building:

    The more performance-heavy you want your PC to be, the more money you’ll save by building it yourself.

    Put another way: If you’re someone who’s only into light gaming, office apps, and watching videos on the ‘net, then odds are good you’d be better served (if price is your main consideration) to simply buy a Dell refurb unit and perhaps add a low- or mid-range graphics card to it. (That’s assuming you need a discrete graphics card at all. The integrated graphics in today’s Intel processors are pretty good.)

    I built a budget PC like this for my mother-in-law earlier this year, and when all costs were tallied up (she had no need for a discrete graphics card), it cost her a smidge under $460. (That’s parts only; my handful of hours of labor to research and build were gratis. She also reused her current monitor, keyboard, mouse, and printer.)

    Could she have gotten a Dell refurb more cheaply? Yes.

    Would we have known the quality of the parts Dell used? Nope.

    Would it have come with a bunch of useless, memory-clogging bloatware? Yep.

    So that $460 got my MIL:

    1) Good, quality PC components;
    2) A known level of expandability down the road; if required;
    3) A crapware-free PC; and
    4) The knowledge that if something goes haywire with the machine, or she just has a question, she can contact the dummy who built it and he can be on site pretty quickly if needed.

    If she’d gone the Dell route, it wouldn’t have hurt my feelings. But she seems quite pleased with her custom-built, budget PC. And I think she got a pretty solid Intel-based machine for her money.

    Building My New Gaming Machine

    New BuildAlas, because I want to play current games at high settings, building a sub-$500 PC was not in the cards for me. I also needed new peripherals (monitor, keyboard, and mouse) and had pretty specific desires in those areas. Which means more costs to tack on.

    Here’s my build list, as completed, with my prices in parentheses:

    CPU: Intel Core i5-4690K 3.5GHz Quad-Core Processor ($184.99)
    Motherboard: Asus Z97-PRO ATX LGA1150 Motherboard ($152.99)
    Memory: G.Skill Sniper Series 8GB (2 x 4GB) DDR3-1866 Memory ($56.99)
    Storage #1: Samsung 840 EVO 500GB 2.5″ Solid State Drive ($209.99)
    Storage #2: Western Digital Caviar Blue 1TB 3.5″ 7200RPM Internal Hard Drive ($49.99)
    Video Card: Asus GeForce GTX 970 4GB STRIX Video Card ($339.99)
    Case: Phanteks Enthoo Pro ATX Full Tower Case ($89.99)
    Power Supply: Rosewill Capstone 450W 80+ Gold Certified Semi-Modular ATX Power Supply ($34.99)
    Optical Drive: Asus DRW-24B1ST/BLK/B/AS DVD/CD Writer ($16.99)
    Operating System: Microsoft Windows 8.1 – 64-bit (OEM) (64-bit) ($84.99)
    Monitor: Dell S2340M 60Hz IPS Panel 23.0″ Monitor ($119.99)
    Wireless Network Adapter: Gigabyte GC-WB867D-I 802.11a/b/g/n/ac PCI-Express x1 Wi-Fi Adapter ($32.99)
    Keyboard: Razer Blackwidow Ultimate Wired Gaming Keyboard ($99.99)
    Mouse: SteelSeries Rival Wired Optical Mouse ($54.99)
    Freebie: Far Cry 4 Game (Bonus w/Video Card; Value $45)
    Total: $1,530

    All parts except the keyboard and mouse came from Amazon and Newegg, as I have free shipping from both. Prices shown include all rebates, including the mail-in variety. (I despise mail-in rebates with every fiber of my being, but money’s money. When it’s there, you take it.)

    Importantly, I was able to take my time with this build. I spaced out the purchases over several weeks to take advantage of Black Friday and Cyber Monday sales. I also used other offers which came my way during this time. (Newegg, in particular, is all about email coupon codes, if you didn’t know. So if you’re going to build a PC, get on their email list pronto. And be sure to use PCpartpicker.com to line up your components, compare prices, and sniff out any incompatibilities. It’s an insanely valuable resource.)

    Notes and Comments

    Space wasn’t an issue, so I had the luxury of choosing whatever size case I wanted. The Phanteks Enthoo Pro is about as big as PC cases come. It has no fancy, colored LEDs to make it look like a blinged-out spaceship (as my Cooler Master Storm Enforcer case did, which I used in my 2012 build). But the Enthoo Pro has crazy-good fans, terrific cable-routing tools (like built-in Velcro straps), and best of all, it has four easy-to-remove, easy-to-clean air filters. Since this PC will sit in the same room as our kitty-litter boxes, such filters are VITAL.

    Having a solid-state hard drive wasn’t in my plans originally. However, I took the leap, thanks to the many positive reviews I read on PC-building message boards. For those who care about such things, with Windows 8.1 and other startup software on the Samsung solid-state drive, this PC goes from power-on, to BIOS, to Windows 8.1 log-in in a brisk 16 seconds. Desktop is ready immediately thereafter.

    While I did purchase a Wi-Fi network card for this machine, that turned out to be unnecessary. In the week leading up to my build date, I worked up the guts to go crawling in our attic, where I wired several rooms in our house with Cat 6 cable. So my new PC’s connection is land-line fast, and the wireless card’s only job is to take up a small bit of space on the motherboard. There’s another $33 I might’ve saved.

    No, It Ain’t Cheap

    Fifteen hundred bucks is a bunch of money, to be sure. But drop the monitor, keyboard, and mouse (which lots of less-picky folks would reuse from old systems), and that’d shave the total down to $1,255.

    When I used these same parts to piece together a built-for-you machine at a couple of online places, their prices for a near-copy build never got below $2,000. And it still wasn’t an even comparison, as those places offered only no-name, cheaper monitors, and lower-quality power supplies. And no freebie Ubisoft game.

    At Dell/Alienware, it took $1,999 to get a PC with the same graphics card (GTX 970) and a slightly better CPU (i7 at 3.8GHz, vs. my i5 at 3.5GHz). But even at that $1,999 price, the Dell/Alienware machine was without a solid-state hard drive. It also had no monitor, no gaming keyboard, and no gaming mouse. Though the Alienware machine’s appearance certainly was, uh, nonstandard. I’ll give it that. If you’re into that sort of thing, maybe it’s worth the markup.

    In the end, I once again had a great time building a Windows-based PC for our household. Barring breakdowns, I feel pretty strongly that my $1,500 or so outlay will keep me in gaming bliss for years to come … and that I got quite a bit more for my money than if I’d gone the prebuilt route.




     

     

  7. Survey: Many Expect Student-Loan Forgiveness

    Well, can’t say I find this to be any surprise:

    NBC News: 25pct of Millenials Expect Student-Loan Forgiveness

    Note to those of you who run political campaigns: If a quarter of those Millenials with student-loan debt expect it to be dumped off on the taxpayer, well, I’m pretty sure they’ll line up to vote for anyone who promises to make it so.

    I’m also fairly confident that there’s a word for folks who borrow money without the intent to repay.

    (Doesn’t apply to governments or super-large automakers, of course; we’ve established that. But you know what I mean.)




     

     

  8. Still Here!

    Wow. Just checked in this here blog to find that I haven’t made a single post since June. Ack. How the time flies.

    Well, rest assured that you haven’t missed anything exciting in my world. And also that yes, I’m still here!




     

     

  9. Survey: 25pct Have No Emergency Savings

    In the latest non-shocking financial survey conducted by Bankrate, we find that roughly a quarter of respondents said they had NO emergency savings. Zero, zilch, zip, nada. A further 25 percent had less than three months’ worth of expenses saved, so altogether, about half of respondents had less than three months of expenses saved up.

    Bankrate: Financial Security Index, June 2014

    A cursory glance of my “Statistics” category of previous posts tells me that the dismal figures Bankrate reports here aren’t far off from what we’ve seen from other sources lately.

    Also from the poll: Among those earning $75k or more per year, only 46 percent have at least six months’ worth of expenses saved.

    Readers know that I love polls and stats like these, but gosh darn, how come these polls never seem to ask the ancillary question: For those with any savings, how much are they also carrying in revolving credit-card debt?

    Because something tells me that if these same respondents divulged THAT as well, then their “true cash savings” would emerge … and savings rates would be significantly worse.




     

     

  10. College Debt Comes Home to Roost

    A really fun article which I stumbled upon late Saturday evening:

    NY Times: Boomerang Kids Won’t Leave

    ShacklesI could go on and on about the scam that college debt has become, but I promised myself I wouldn’t do that again. Fact is, this country just moves from one bubble to the next, and Today’s Bubble™ is college debt.

    The article itself is a hoot. But I also highly recommend that you take a look at the 14-photo slideshow which accompanies it, because there you’ll unearth gems like this one from Alexandria, 28 years old, the proud owner of a $90k student-loan chain around her neck:

    I feel like I had no idea what I was doing when I took out those loans. They didn’t really sit us down and talk to us about financial aid or what our options were. I wish they would’ve had a class before you graduate high school, or like the first semester of college: ‘Let me teach you about basic student loans, math, finance, anything that you’re going to need now that you’re 18.’ I wish somebody would’ve been like, Alex, it is not a good idea to take out a loan that has 12.5 percent interest.

    Oh, Alexandria. As Upton Sinclair so succinctly put it, “It is difficult to get a man to understand something when his salary depends on him not understanding it.” The same goes for education: Don’t expect colleges and the rest of the “Big Education” industry to teach you all that basic financial and debt stuff, because their salaries depend on you NOT learning it.

    At least, not until you’ve run up student loans to the hilt and paid off all your bursar and textbook bills.