Fun With Microsoft Excel
October 18th, 2007I use Microsoft Excel a lot. If you’re reading this, there’s probably a good chance you do too.
(Quick refresher: If you’ve been living under a rock with no internet access for the last decade and haven’t heard of Excel, it’s Microsoft’s full-featured spreadsheet tool for Windows. It crunches numbers, makes charts, stuff like that. If you’re not familiar with it, you should probably read some about the program first. Also, since I’m all for learning by doing, I highly recommend having access to Excel for this post. You can probably get a fair amount out of this post without it, but it’ll greatly enhance your reading.)
In my experience, most of the time Excel is used for fairly mundane tasks. Not always, though. If you read books on Excel (and I’ve read so many I’ve honestly lost count), there’s sometimes a section in the back of them called “Just for fun” or “The lighter side of Excel” or whatever. There you’ll see crazy spreadsheets that play everything from craps to Tetris. I’ve seen even art made out of hundreds of Autoshapes, which gives a new meaning to the phrase “modern art”. The things people make in Excel often blow my mind. Most of them are made possible by the use of Visual Basic for Applications (VBA), the scripting language for Microsoft Office. VBA basically sits on top of Excel as a full-blown programming environment, if you need it. (If you want to learn more about VBA in Excel, here is an excellent link to get you up and running in no time.)
One of the coolest spreadsheets I’ve seen generates hypocycloid charts, made by John Walkenbach, Excel guru extraordinaire. What exactly IS a hypocycloid chart, you ask? It’s a chart of hypocycloid curves, of course. *drum fill* OK, so that’s not really a definition. Here it is in layman’s terms: imagine yourself inside an old tire at the top of a hill, curled up in a ball getting ready for the ride of your life. It’s pitch black outside and your hand is holding a brightly colored glowstick. When your friend pushes you off the top off the hill (some friend!), the visual trails that your glowstick leaves would make a (small) hypocycloid curve.
But really, the best way to see what hypocycloid charts are is to check out that spreadsheet Walkenbach made. It’s cooler than it sounds, trust me. I think the animated charts (with markers) are especially neat-looking and Walkenbach himself has added 50 of his favorite charts for you to check out. The fact that this is all done in Excel is just icing on the cake. (If you end up liking this spreadsheet, Walkenbach has a whole page full of nifty spreadsheets he’s made. I’ve checked out a lot of them myself and I really enjoyed many of them.)
That’s just the tip of the iceberg, though. I think the games people make in Excel are probably the most hardcore stuff I’ve seen. The best game I’ve seen is a full VBA macro implementation of Puerto Rico. Not the US territory (though I’m sure some wacky economist has tried) - I mean the wildly popular German board game of the same name. (Puerto Rico is my all-time favorite German board game, and it’s been ranked #1 at BoardGameGeek for some time.)
You can download the “Puerto Rico Evolver” (as the Excel version of the game is called) here. Just scroll down until you see the “Files” frame. Click on the “Hot” setting in the upper right of the “Files” frame and the Puerto Rico Evolver should be there on the first page. (If BoardGameGeek’s site changes layout in the future and you click that link, just dig around till you find it. It’s been available there for years so you shouldn’t have a problem.) If you’re not familiar with the game, I recommend reading the Puerto Rico rules on BoardGameGeek first. It’s not strictly necessary, but it will probably enhance your understanding of the game and give you a chance to try it out.
Anyway, though the Evolver is written in VBA, the code behind it is fairly lengthy and involved. The whole workbook runs off the text strings each player has. (See tabs “P1″ through “P5″.) Different text strings control different parts of the game at different times. The code just peels off whatever part of whatever string it needs to play the game for a particular player.
The game can be played one of two ways: to compete or evolve. If you compete, you (and up to 4 other people) face off against however many computer players you want. (5 is the maximum number of players, so if you have that many humans there aren’t any computer players.) The game is ideally set up for you vs. 4 computers. And if you start playing immediately, don’t get too cocky starting out - the basic random players the Evolver starts off with aren’t very good.
However, if you evolve the computer players first, you can face much better competition. If you read the notes on the “start” tab of the Evolver, you can see how the whole process works. You specify the number of games to be played during each “cycle” of evolution, and how many times you “evolve” the computer players. The larger each number is, the better the computer gets.
Basically, there are 2,000 players for each of the 5 possible players (10,000 total). When you evolve, you randomly choose 1 of those players each for the number of people playing (usually 5). Then each of those computers faces off against each other. This continues until you play the number of games in that cycle.
After that, you see who the best 360 players are (out of 2,000) for each player tab and keep them. You get rid of the rest. Then the notes say that each of the best players “breed”, or mix and match their strings, 3 times. However, I think that has to be a typo because the math doesn’t work out. My guess is that each of the best players breed 4 times. (Note: A player can breed with itself. Would you call that “string-cest”?) That gives you 4*360 = 1,440 players. Add the original 360 best players and you have 1,440 + 360 = 1,800 players. Then 200 new players are randomly created. 1,800 + 200 = 2,000 players, or the amount you had before that evolution cycle. Pretty cool, eh? (I thought so, anyway.)
If you specified more than one evolutionary cycle, then the computer keeps playing games until the requisite number of evolutions are finished. Hopefully, by that time you have the best of the best computer players remaining and ready to play against you. Darwin would be proud. In fact, I think the evolution metaphor is particularly apt (except for that string-cest bit I’m trying to block from my memory) - for example, the computer players have their own “DNA”, which consists of 13 separate “chromosomes”, or bits of a player’s DNA that tell it how to act in any given game situation. Evolution is a quick, intuitive, and deep way to think about how the computer players get better.
Even better, instead of theorizing about what works best (a frequent pet peeve of mine), the Evolver just tinkers with itself (minds out of the gutter please) until it eventually finds some great Puerto Rico players. That seems ingenious to me. And the fact that the Evolver fully implements a legion of abstract rules in succinct conditional code and a series of simple strings is a pretty clever trick. I was certainly impressed by the whole thing, at least. (That’s one reason I’m not posting my Evolver. It’s fun enough to Evolve some yourself or download some players from BoardGameGeek. And they’ll be just as good as mine, probably.)
Besides the amazing technical achievement of coding a perfect computer implementation of Puerto Rico, it’s also a really solid game. I have played tens, if not hundreds of games against the Evolver (and against people in real life) and the challenge is extreme but rewarding. I found myself cursing at my screen as I got outfoxed again and again. It’s a little insulting to be outwitted by a few lines of text, I must admit.
The Evolver is not without its limitations, however. You really should play it 1 vs. 4 and evolve using 5-player games for the best experience. Anything else makes the game substantially easier. However, I’ve found that if you add another human player or two into the five-player games, the computer still puts up a good fight. And as the author of the Evolver admits, the computer players evolve to play specific strategies. Since the computer players are chosen randomly each game, you won’t know what specific strategy that is (without some hard-core cheating, anyway), but it still means the computer players are fairly inflexible.
On top of that, since the players are constantly evolving vs. each other, they tend to adopt strategies that play off each other, in effect raising all of their victory points at your expense. (The player with the most victory points at the end of the game wins.) Unless you know how to play off them as well, you can easily find yourself at their mercy. (I am not responsible for any injuries you may sustain by kicking your disk repeatedly as a result. Not that I’ve done that or anything…)
And there are other weak points. As the author notes, the shipping code is “quick-and-dirty” (the sexual metaphors are getting a little ridiculous at this point) which means that players all ship the same and independently of each other. This is not always optimal. In fact, most of the computer players’ actions are independent of each other, in a sense. Maybe from game to game they interlock (because of evolution), but there is a sizable lag there since Puerto Rico is constantly changing, not just from game to game, but turn-to-turn as well.
That means the computer can handle simple rules well, namely, the ones that don’t have a lot of player-to-player feedback. Shipping goods is like this. You can mostly ship what you want in Puerto Rico, given a few limitations. It’s the buying and using of buildings, and choosing roles, that have a lot of volatility. That’s why the author admits that most of the “decision-making code” (and hence the biggest part of the artificial intelligence) lies in choosing a role.
Thus, what comes out is not always the best decision for a given player. Most of the Evolver code uses a “greedy” algorithm, which just means the computer does the best thing for itself on any given turn. It doesn’t take other players into account (much) and it doesn’t look very far ahead. All the computer players can do is switch from early to middle to late game strategies. That’s as much planning as you’ll see.
When I beat the computer (I’d say I do so about 20 percent of the time), it’s because I built and used better buildings and chose better jobs, usually. I get outshipped each and every game. There’s no getting around it, because the other 4 computer players basically collude to ship as much as possible at your expense. (Kind of like the mob, really, but you get to keep your kneecaps.) In virtually every game the best you can do in shipping is tie.
Sometimes, because the endgame conditions can vary so much, you can also outwit the computer players by ending the game early. That is the easiest and most consistent way to really screw up the computer, actually. Your typical win against the Evolver involves a lot of victory points from buildings, a fair amount of shipping, and a very short game that ended somewhat unexpectedly for the computer players. Though even that method doesn’t win much, any other strategy is playing with fire, really. Even worse, if you don’t know the game inside and out, you don’t stand much of a chance. Luck helps, but to beat such highly evolved players you mostly have to directly outwit them. That’s one of the things that makes the Evolver so fun (and frustrating). You’re constantly going against random, highly-specialized, ruthless automatons. (Sounds like a pretty cool sci-fi flick, actually. “Puerto Rican Robots of Death”, anyone?) So you can’t just stick to a rut that might work against human opponents. They force you to always discover and play the best strategy available to you, or you’ll lose. Can’t ask for better training than that. Ask my friends, who now hate to play me in Puerto Rico. Except for my other buddy who plays against the Evolver. Maybe he’s secretly a Puerto Rican Death Robot now, I dunno.
Like I said, there’s a lot of really cool and crazy stuff people have done in Excel that I think it was never intended for. Intense visualizations and in-depth games on a humble spreadsheet? We’ve come a long way from the days of ledgers and accounting paper. Now all I need is a spreadsheet that can write blog posts…
| | | del.icio.us |
October 19th, 2007 at 1:26 am
How dependent upon Excel are some of these things? I imagine many of the functions are rather Excel-specific, but personally I use OpenOffice at home to avoid spending lots of money on a software suite I would use infrequently.
Greg, most of the stuff I posted about uses VBA, which is specific to Excel. In fact, last time I checked, one of the big advantages of Excel over OpenOffice is that Excel has a scripting language. It’s only something that matters to power users, but it’s a deal breaker to me if I don’t have it. VBA is only useful for certain kinds of things (like heavy duty automation), but when you need it it’s really useful and nothing else in a spreadsheet program will get the job done.
OpenOffice is great because it’s free. Excel is great because it’s more widely used and robust. Which one you prefer depends on your needs. Also, if you run Linux, OpenOffice is your only choice (unless you use Wine or something).
- Dave