Daily Dose of Excel

Recently, I had to submit a fixed width text file to a state tax agency in lieu of sending them physical copies of W-2 forms. Fixed width files contain a number of columns and each column starts at a particular position on the line. If the data in that column is shorter than the width of the column, the column is padded with spaces so that the next column starts in the correct position.

I had the data in Excel, I just needed to get it to a text file. As it happens, I only have one employee for this state, so it would probably have been just as quick to type it manually. But I think we all know why I didn’t do that. The spec for the file is this:

Here’s the monster formula in F1:

=A1&REPT(" ",A2-LEN(A1))&SUBSTITUTE(B1,"-","")&REPT(" ",B2-LEN(SUBSTITUTE(B1,"-","")))&C1&
   REPT(" ",C2-LEN(C1))&D1&REPT(" ",D2-LEN(D1))

The basic structure is cell value & repeat a space to fill. The exception is B2 where I had to remove the dashes from the social security number. If I had more than one row to do, I probably would have put the field lengths above the data. Then I could fill down column F. Finally I would copy F1 to a blank Notepad file, and there’s my fixed-width text file.

Mathew asks:

Where do you find the field names for Outlook, i.e. FirstName. Yours are practically about the only ones I can get to work. Strangely, I can’t get Business Phone (s) or faxes to work.

The answer: Use the object browser. Here’s how. First, make sure you have a reference set to the Outlook Object Library.

If you’re looking for something inside Excel or VBA, those references are set automatically, so you can skip that last step. Next, choose Object Browser from View menu (or hit F2).

From the Project/Libraries drop down, choose Outlook

This will show only the classes from that library. In the classes list, choose ContactItem. Then browse the Members list to see what’s available.

The box just below the Project/Libraries drop down is a search box. When you have an inkling of what you want, but don’t quite know the proper terms, you can use search to narrow things down.

Worst. BlogPost title. Ever.

Jon Peltier of Peltier Technical Services (and world renowned charting expert) has started the PTS blog to:

…show off some of the projects I’ve worked on, share some Excel and Charting tips and techniques, show how to make some tricky charts that people ask about in forums and newsgroups, and talk about the various utilities I’m working on.

I had already subscribed to the RSS feed for his site, so his first blog post showed up automatically for me. I love RSS. Needless to say, I’ll be reading every post and if you have any interest in charting in Excel, you should too.

As I mentioned in a recent post, I’ve finally installed Excel 2007 (Office Ultimate, actually) on my home computer. I had it on a virtual machine back when it was in beta, but now it lives as a first-class citizen on my hard drive right next to Office 2000, Office XP, and Office 2003.

I’m still using Office 2003 at work, so I’ll be relearning the UI every morning. When I was working with 2007 pretty intensively, it took about two weeks for me to be proficient with the ribbon. From my standpoint as a user, I actually preferred the ribbon because it offered keyboard shortcuts for all its commands. As I commented in one of Simon’s latest rants discussions, applying a style or border via the keyboard was not easy in previous versions.

I don’t mean to imply that style and border access is some sort of panacea of Excel UI evolution. It’s just something in the plus column. In the minus column, there’s the two weeks it took me to figure out where everything is. Two weeks isn’t a lot of time, but the question remains whether the ribbon provides enough benefit to overcome that cost. Also, I was a motivated user. I wasn’t being dragged begrudgingly into 2007, I was a willing participant.

Another minus, I’ve heard, is programmability. I have not attempted to program the ribbon, but at a minimum there will be a learning curve. Whether it proves worth it remains to be seen. I’ve recently purchased RibbonX: Customizing the Office 2007 Ribbon, so I should be on my way.

Screen real estate seems to be a hot ribbon topic. During the beta, I kept my ribbon minimized and it would magically appear when I began using the keyboard shortcuts. Mouse-centric people would probably prefer to have the button in plain view. I can’t speak for them, but for me the ribbon took up less space. I do agree that the button size is ridiculous. I’ll bet you dollars to donuts I haven’t clicked a paste button in at least five years. Even my grandma knows Control+V. But someone must have been clicking those paste buttons and sending that data to Microsoft.

I don’t use tear-off menus very often, being the keyboard guy that I am. But I do see the value in them for others. It was a big miss not having them in 2007. I predict that if nothing else changes on the ribbon, it will have tear-offs at some point.

Finally, a word from the field. A fellow accountant and friend emailed me today about Excel 2007. I repeat his email in its entirety:

This new Excel is driving me crazy. Trying to find where everything is a pain in the A$$

I asked if I could quote him on that, and he responded, in part:

Back in November, I took a laptop home that had new Excel and I was trying to finalize a worksheet for Budget meetings the next day. I was trying to do a save as so I could save the file under a new name, and I swear it took 10 minutes before I accidentally hit that windows button and saw the save as command. I was about ready to chuck the laptop out my bedroom window.

The difference between this guy and me? I was trying to learn Excel 2007 and he was trying to actually do something with it. Fortunately, I have the luxury to ease into it. Others won’t be so lucky and they have my sincere sympathy.

I read my retirement account statement today. It wasn’t pretty. A lot of low, single digit returns lately. I know people who complain when the market tanks, but I’m not one of them. When it comes to my retirement, I want the market to keep going down until I’m 59 1/2, then shoot up ten-fold in one day. That would suit me just fine. As long as the market goes down, it means I get to buy stock cheaper tomorrow than I did today. Assuming there are no underlying problems with that stock - and there won’t be if I’m properly diversified - then I am likely buying something for less than its true worth.

Is that flawed? It could be that the lost compounding negates the cheaper price. In other words, if my small initial investment quadrupled the first day, I wouldn’t even need average returns to do well because my base was established so early. Those are the extremes, I guess.

Here’s how I set about testing my hypothesis: In my example, I invest $200 per month in a $1 stock. In a steady market the stock price increases .8% every month like clockwork. In a volatile market, the stock price swings wildly, but the final stock price is the same as the steady market.

In the volatile market, I have about $700 more in my account than if the market was steady. Either way the stock is $1.24, I was just able to buy more of it in the volatile market. It’s the underlying fundamentals of a company that determine its stock price over time, but the utter wackiness of investors that determine it in the short term.

I believe investment folk call it dollar-cost averaging when you invest the same amount of money periodically. What do you think? Should I rejoice at each downturn?

PS To get the volatile market returns, I used this formula: =RAND()/10*IF(RAND()>.5,-1,1). Then I hit F9 until I got close to the correct final stock price. Finally I fiddled with some percentages haphazardly to make the two final stock prices match.

PPS Yes, that’s Excel 2007 you see there. More on that later.

MSDN has some nice articles on the basics

The Application Object
The Range Object