My birthday was this past week, and as birthdays go it was not spectacular. That's fine with me, as I grew up without most of the pomp and circumstance that is normally associated with kids' birthdays here in the United States. Still, as birthdays go, it wasn't bad. Jennifer and I had planned on going to the fitness center (*it's officially the fitness center to distinguish it from the gym, which is the big hardwood-floored room where basketball and volleyball are played, but since "gym" is colloquially used to mean 'a place where you go to lift weights, do cardio and sweat', all references to "gym" will refer to the weight and cardio place). I got up early, and after a few phone calls and a chat with tree trimmers who showed up unexpectedly to do some utility easement cleaning for the electric company, we were off to the gym. In retrospect, I could have slept in. However, we did have a very good workout.
The epiphany was the night before. Part of Mr. T's education this year is going to be about investing. He reads the business section of the Chicago Tribune every day, so this is really a logical progression. I figured the best way to do this would be to make a fantasy stock portfolio and track its performance in terms of profits and losses. He has Excel 2007 on his PC, so I whipped up a spreadsheet that he could use as a sort of master ledger for the exercise. I've done these before, so it came together pretty quickly- I even color-coded the cells with formulas so he wouldn't break anything.
The whole "whipping up" process took about ten minutes, and it was nearly perfect and complete. Of course, nearly == almost, and in this case there was a little issue which I had never had to deal with before.
The rules of the exercise include a provision for the paying of commissions on the virtual purchase and sale of equities, so I have a column for commissions- for the purpose of the exercise, this is set to == 10 USD. I did not build the commission directly into a purchase formula because this spreadsheet can be repurposed to do real world investing, and the ability to change the commission price should be available to the user. So, the grand total for a transaction is the purchase price ((price * number of shares) + commission). The problem is that this column ("total") appears as part of another formula which shows total available funds. As there are initially twenty-five rows for transactions, the user automatically starts off with a 250 USD deficit in their available funds, as 0*0+10==10. So, I embarked upon a quest to find the function that would take care of this.
I knew what I wanted, and a simple IF/THEN script would have done the trick, but VBA in newer versions of Microsoft Office (2007 and on) have apparently also had the developer tools revamped, as the Expression Builder I had hope to see was nowhere to be found.
Bummer.
On to Plan B: the library. My personal collection, that is. I spent a few hours pouring through my books, and came close to a solution on several occasions, but as they say, "close only counts in horse shoes and hand grenades". I found the definitive solution (and syntax) on an Excel site. (For those with inquiring minds, the function is IFSUMS.)
This made me happy. See, I have a smiley :)
As always, I am hochspeyer, blogging data analysis and management so you don't have to.
No comments:
Post a Comment