One tool that I hadn’t seen before but have since come to rely on was an innocuous looking button with the label ‘Tidy Array’ that would resize an Excel range with an array formula in it. This is probably the biggest PITA in Excel: entering a formula that returns several values. However, unless you know exactly how many values the formula is going to return, you’re left either getting only part of the result back, or with unsightly #N/As framing the result. Tidy Array would evaluate the formula internally, check the size of the returned result, resize the range in which the formula had been entered, and then re-evaluate the formula to fill that resized range with the result.

Since leaving DKIB, I’ve been playing around with ExcelDNA, Govert van Drimmelen’s fantastic library for building XLLs in managed code, to build an Excel-based interface for my trading system. I missed Tidy Array, so I put something similar together (link below). It’s a little different in that after you run the function (from the ‘Pandora’ menu), it just resizes the selected range to the correct size rather than filling that resized range; the reason for this is that this prevents neighbouring cells from being inadvertently overwritten. (Note: GB’s version had logic to keep it from overwriting stuff, but I prefer to limit function behaviour rather than add behind-the-scenes logic.)

I tried to add a keyboard shortcut for it too (Ctrl + Shift + T) but I haven’t managed to get it to work yet.

To use:

1. Unzip to some convenient directory. Note the location of the TidyArray.xll file.
2. Start Excel and add TidyArray.xll as an Excel Add-in (In 2003, it’s under Tools -> Addins; in 2007, it’s under Excel Options -> Add-ins -> Excel Add-ins

TidyArray.zip

Given the background, and how nearly I didn’t run, I’m very happy with how yesterday’s marathon went, even though my finishing time (over 5 hours) is more than half an hour outside what I expected.

The first thirteen miles were incredible: my knee (which I was trying to protect by running on the outside edges of my feet) didn’t twinge even once, my lungs felt strong and I was hitting or bettering my pace at every mile. Bizarrely, I started getting blisters about 9 miles in but even though I did stop for a breath at mile 12, after that I didn’t really have any problems till mile 17. That’s when both quads cramped so hard that I could barely lift my legs. I started over-compensating with my calves, which soon tightened up too. The end result was that between miles 17 and 23 I had to stop no less than 3 times to stretch and massage my legs (more on that later), which altogether totalled about 20 minutes of wasted time. By mile 21, my knee started to give too, with the muscles tightening up to the point that I couldn’t bend my leg (no sharp pain like before, though). Needless to say, I was doing a lot of walking at this point. Just after mile 23, though, excitement got the better of me and, with the adrenalin flowing, I managed to start running again. My lungs still felt strong, so the only thing that was holding me back were my legs. And even they couldn’t stop me from making a dash for it when we turned on to The Mall for the final stretch :)

Around mile 19, I pulled up to stretch my quads. One of the St. John’s Hospice workers saw me and told me there was a massage spot a little further down so I stumbled down and had my calves massaged for five minutes. That made my legs feel so much better that when I saw another massage spot around mile 21, I decided to stop there too. This one was different though: they made me lie down on the grass and insisted on starting near my shoulders even though I had clearly explained the pain was around my knee. And when the lady started, instead of really kneading the muscle, she just lightly passed her hands over my shoulders and back. I had no clue what she thought she was doing but lying down felt so wonderful I let her do whatever she thought she was doing for a good 10 minutes! When I finally got up (no better for the ‘massage’), I asked what organisation they were with. That’s when the lady reached into her pocket and pulled out a card that said ‘Scientology Volunteer Minister’. Umm, OK that explains a lot.

I have to say, the event was brilliant. The logistics of just providing adequate security and water is mind-boggling in itself, but on top of that the masseuses, the energy gel stations, the vaseline (note: being hirsute means not having to worry about chafing – ’nuff said!), and everything else was available in abundance and arranged perfectly. The only gripe I have is that Lucozade ran out of energy gels at their last station, which is what led to me finally giving in and taking whatever sweets and jelly beans the crowd was offering.

Oh yeah, the crowd. I honestly remember only one 100-yard stretch where the sidelines weren’t absolutely packed with people. It was remarkable, and their support was invaluable. And by support I don’t just mean the constant ‘Come on, Spinal Research, mate!’ but also the orange slices, jelly beans and sweets many of them offered. And the live bands all the way along were great for morale too.

Oh, something cool occurred to me while I was running: if the most you’ve ever run before is 11 miles (as I had), then by running 26.2 miles you’ve effectively established uncountably infinite personal bests! The proof follows from a simple application of Cantor’s diagonal argument. Yay!

In conclusion, I’m very happy with how things went: despite injuring my knee and not being able to run at all for the month before the marathon, and despite being stranded in continental Europe the week before the actual run, I finished. I think I’m going to run another one — soon — but for now, I’m going back to bed to nurse my aching legs and the inch-long blisters on my feet.

Faced with the choice of nominal gains or no gains at all, investors have opted for the former, in the hope that they’ll come out ahead once the economies have finished resetting. In the euphoria of the last few months it can be argued many investors have actually been unable to distinguish between real and nominal gains — blame the short attention span a pile of cash earning 0% causes — and have bought into the rally with commitment, ignoring the naysayers’ warnings of a repeat of the whipsawing markets of the Great Depression. The central banks, of course, are just relieved the naysayers are being ignored, regardless of what’s going on: they’ve run out of tools so the perception of confidence and control are all they can offer. Anyway, who’s to say the rally is unsustainable or that this is a fool’s rally — aren’t markets the only authority on themselves?

I claim no expertise at interpreting the wider economic implications of what’s going on, but it’s becoming apparent that what’s happened is that inflation, the beastie we were all fearing has been replaced by devaluation, it’s more subtle twin. The FT had a good article examining how cross-currency investors were possibly the best positioned for last year’s meltdown (though clearly the right combination of currency and asset was crucial). The traditional safe havens, US treasuries and gold, have become less attractive, both because inflation has been under control and because of their own problems (US treasuries are dollar-denominated and don’t offer a yield, and gold has reached a psychological ceiling).

So, my view? Nothing you haven’t heard already, I’m afraid: Inflation in the mid-to-long term, and whipsawing markets in the short term. Great time to be trading currency :)

The Constitution only guarantees the American people the right to pursue happiness. You have to catch it yourself.

- Benjamin Franklin

I don’t claim to know what Mr. Franklin was thinking, but I find it telling that the quote works equally well if you leave out the second sentence. That, my friends, is what American exceptionalism is actually about.

With that in mind, it’s interesting to read J.A. Adande’s recap of Game 4 with a scorecard on hand. What we see is that Kobe Bryant, who scored 38 points on 66% shooting in 39 minutes, ended the game with a +4 rating, the same as Luke Walton, who chipped in 9 points on 50% shooting in 19 minutes. Sasha Vujacic had a +9 for his contributions of 9 points on 33% shooting in 17 minutes. The guys who fare the best are Lamar Odom (10-15-6 line on 40% shooting in 41 minutes, for +20) and Pau Gasol (13-10-1 in 41 minutes, for +15). Clearly, those numbers are all over the place, but what’s weird is how Kobe ended up with just a +4, given how much everyone insists he dominated the game. The following excerpt from Adande’s article helps to explain this:

Bryant came out firing. … He scored the Lakers’ first 11 points and 13 of their first 15, hitting 6-of-8 shots in the first quarter. … But the rest of the Lakers went 2 for 10 and Utah led, 25-20. The turning point actually came when he was on the bench at the start of the second quarter and the Lakers fielded a lineup of Shannon Brown, Sasha Vujacic, Luke Walton and starters Lamar Odom and Pau Gasol. It was the reserve players who hit three consecutive 3-pointers to take the Lakers from a seven-point deficit to a two-point lead.

A-ha! Kobe, may have scored a full one-third of the team’s points over the course of the game, but he was sitting out during a key stretch. Worse, while he was lighting it up in the first quarter, everybody else’s ineptitude was costing him in the +/- stakes.

This situation captures almost perfectly the beauty and the shortcomings of +/-: on the one hand, it zooms in on what matters most — winning — without ascribing arbitrary weightings to individual statistics like points, rebounds and assists while still incorporating the value of intangible contributions. On the other hand, the value of the individual is so tightly tied to who is on the court with him that +/- as a comparative tool becomes worthless, since it reflects more the overall quality of the team and the ability of the coach to construct effective rotations. It’s sort of the classic curse of single-number metrics.

One is tempted to abandon +/- as the ‘holy grail’ statistic at this point, but it’s really a very good idea in as far as cutting out the pseudo-science of sports statistics and focussing on what actually leads to wins. One wonders if the problem is ’snapshotting’ +/- ratings at the end of the game, which necessarily throws out all the information that a real-time +/- score would have. Behold:

Utah-L.A. Game 4, Playoffs 2009 - Selected Plus-Minus Ratings

What you’re seeing is a graph of realtime +/- ratings for the teams (ie, overall) as well as for various players over the course of the entire game. The overall team rating is just the point differential between L.A. and Utah (I’m a Lakers fan); for the individual players, you can see how their +/- rating was related to that of the team through time. The flat regions (eg, Luke Walton from the beginning of the game until about 7:30 in) are periods when the player’s +/- didn’t change — ie, he was either off the court, or he was on, but neither team scored. You’ll also see some periods when everyone’s rating moves together — eg, the plotted players’ +/- ratings all fell between 10:30 and 14:30 — which implies that they were all on the court (in the 10:30 – 14:30 period, the falling ratings mean Utah made a run).

The plot makes it immediately apparent how, for example, Luke Walton ended up with the same +/- rating as Kobe: he simply sat out the last 10 minutes of the game, when Utah was cutting L.A.’s lead. This, of course, raises some interesting questions, such as how starting and ending games ends players’ +/- ratings; from this example, it looks like garbage time can hurt the winning team’s ratings (though conversely, Utah’s players’ ratings benefited from garbage time), while starting can be good or bad for one’s score depending on who starts the game stronger (ie, there’s no bias there, whereas there is for garbage time).

Of course, since one now has a nice time-series describing the players’ contributions (as encapsulated by +/-), one can try computing a single-number metric of performance (though, obviously, YMMV*) by simply computing the correlation between the team’s overall +/- and the players’ individual ratings. Note that this number really only makes sense in the context of the team’s performance — ie, a player with a perfect score on the Kings is clearly worse than a player with a perfect score on the Lakers, since correlation doesn’t differentiate between covariance that leads to team success or failure. That said, let’s take a look at what this ‘+/- correlation’ metric looks like for Game 4:

Utah-L.A. Game 4, Playoffs 2009 - +/- correlations

I’ve shown both team’s players on this one chart, so what you’re seeing is who contributed to what ultimately proved to be a Lakers’ win as a normalised score based on players’ full real-time contributions. So if you were to say a dude is absolutely indispensable to a team’s success (ie, the MVP), you would expect him to have, over the course of the season, a +/- correlation of almost +1. We see that, for example, for Pau Gasol, implying he was on the court for almost every significant period of the game (even if he didn’t contribute as much as Kobe in raw numbers). The implications for Andrew Bynum are also interesting, since a correlation that low means he basically didn’t matter much as far as the team’s ultimate success is concerned. I’m really itching to see what it says about Shane Battier ;)

Unfortunately, it’s not the end-all, be-all of metrics because by construction the amount of playing time a player receives will bias it, and because it’s basis in the overall winning ability of the five players on the court means it doesn’t break down how much of that success is attributable to any individual player. However, it does roughly put players where we expect them in the pecking order, and gives a rudimentary sense of how much responsibility a team’s results individual players should be ascribed. And there are ways to improve this metric, a topic which really deserves another post (although here’s an obvious extension: define the overall team +/- to always be in favour of the team that wins).

In summary, what complaints we have against +/- ratings might be resolvable by analysing players’ +/- ratings as they evolve through time. In abstract terms, +/- seems a much more objective metric than the weighted-average metrics in more common use (eg, PER, Wages-of-Wins), which make assumptions that borne out only by a couple of decades’ history.

I’m attaching the spreadsheet containing my data as well in case anyone’s interested in taking a look at it. If I get time — and if there’s interest — I might compile this sort of data for all the games next year and put it on the web. Let me know what you think.

Utah-L.A. Game 4, Playoffs 2009 – Data

Utah-L.A. Game 4, Playoffs 2009 – Scorecard

• I have trouble using this phrase in polite conversation now, but hopefully we’re all on the same page as to why I’m using it!