How To Build Spreadsheets

One of my many New Year Resolutions was to be a kinder, gentler hedge fund shark.

As such, I have realized that my last post was unnecessarily grumpy. Instead of merely complaining about the problem of jobseekers’ lack of ninja finance skills, I have decided to contribute to the solution. With this humble aim in mind, I present the second part of my guide to living the hedge fund life: How To Build Spreadsheets.

Building spreadsheets in Excel is an essential skill for any hedge fund manager. Indeed, it is one of the ways we distinguish ourselves from lesser mortals. I often think that you can determine how high up on the totem pole any particular individual or industry is, by looking at how much they use Excel.

Hedge fund managers live and die by Excel; and of course they are at the top. Investment bankers and private-equity types occasionally work in Excel, but they don’t harness its full power; I can’t really condone such sloppiness. Management consultants spend their entire lives building Powerpoint presentations, which tells you all you need to know about how futile the consulting industry is. Programmers use Access, which is I suppose acceptable. One step below them come secretaries and personal assistants, who use Outlook. Finally, lawyers are clearly at the bottom of any social ordering you may care to construct, and it shows: they use Microsoft Word.

But just firing up Excel is not sufficient to make you a hedge fund hotshot. Heck, a mere accountant can open a spreadsheet and add up numbers. No; there are specific principles that you must follow in order to establish your credibility. I call these the Four C’s of Excel Mastery:

1. Control. Ask yourself this basic question: what is the purpose of your spreadsheet? Answer: the purpose of any spreadsheet you build is to confirm and buttress the trading position you already hold (or intend to hold), for the benefit of investors and senior management. Therefore, it is vital that you exercise total control of the output of your program. Anyone can build spreadsheets which give unpredictable (and hence unreliable) answers. But true mastery comes from knowing what your spreadsheet will do, before it does it. This is the first principle: Control.

Control can have different aspects. Elementary control involves delinking inputs and outputs: no matter what the inputs are, the outputs are the same (namely, what you want them to be). More advanced techniques of control involve complex logical pathways which somehow, magically, cancel each other out, so that the spreadsheet as a whole does nothing. Finally, Zen level control involves manipulating irrelevant cells in non-obvious ways to produce seemingly random (but – surprise! – highly desirable) results.

But mere control is not enough; it is useless without its counterpart, which is the next principle:

2. Cripple. If your control is not perfect – if your spreadsheet can be used to generate conclusions contrary to your own, then it is useless and in fact actively dangerous. The best way to ensure that a spreadsheet cannot be used to generate such conclusions is to prevent it from generating any conclusions at all, at least when used by other people. This is the second principle: Cripple.

There are different ways you can cripple your spreadsheet. The most effective way is to not disseminate it at all; instead, distribute hard copies (paper printouts) of all graphs, tables and other data. Printed graphs are unassailable and incontrovertible, and thus a boon for traders.

But occasionally you will run into a paperless-office-enthusiast, who insists on soft copies of all documentation. PDF snapshots of the output screens should do the trick here.

Rarer, but much more irritating, is the keen junior trader or diligent risk manager who wants your actual Excel source. Such people are easy to handle; before mailing them anything, simply delete all macros and paste-special-values everywhere. (If you’re feeling vindictive, paste-special-values in some places but not in others; this is guaranteed to keep the recipient busy for days if not months.)

Finally, rarest of all but also very dangerous, is the competent adversary: someone with Excel skills of his own who insists on a working version. There’s not much you can do here except move on to the next principle:

3. Conceal. If you find yourself compelled to share ‘working’ versions of your spreadsheets, make sure you conceal all the calculations. Bury crucial variables. Hide cells, rows, columns, heck, entire worksheets. Use white-on-white text judiciously. Stick critical calculations into cell HZ65000.

In short, make it as hard as possible for any user other than yourself to figure what’s going on. Remember, if your spreadsheet can be reproduced by a third party, then you have failed.

Now, you may say that I’m carrying things too far. Control, Cripple and Conceal – these principles are all very well, but surely nobody can get away with such blatant tricks forever? Surely even the most blinkered investors or incompetent managers will see through such obvious manipulation? This brings us to the last and most important principle:

4. Convert. Your ultimate aim is to get your target audience to have faith, blind unthinking faith, in the outputs of your spreadsheet. And the key to getting people to believe in something, is getting them to want to believe. In other words, you have to get them on your side before a single regression is run or graph is plotted.

How to do this? Through the trader’s classic weapon: psychology.

If your target is junior to you, overwhelm him with information. Include every possible data point as an input, from the phase of the moon to the GNP of Upper Volta in 1962 (deflated and adjusted for purchasing power parity, naturally). Have 8 different models running simultaneously, with 78 individual parameters to be fine-tuned. Print the results across 5 worksheets, using a random combination of cell values, scatter plots and time series graphs to represent the output data.

Overkill? Quite the contrary. Your target will be so awed by the sheer quantity of work that must (obviously) have gone into the spreadsheet, and so keen to make a good impression on you, that he will fall for your conclusions hook line and sinker. Even if he doesn’t know what those conclusions are, or how you arrived at them.

On the other hand, if your target is senior management, your strategy should be the exact opposite: simplify, simplify, simplify! The less nuanced, the better; senior management do not handle complexity well. I sometimes think the ideal spreadsheet for a CEO is one with a single cell, saying BUY or SELL as the case may be. No other distractions, if you please. CEOs like clarity of thought and directness of action; your job is to provide them with these qualities.

Aesthetics matter as well. If your target is a devotee of the classics, use antique Quotron colors – green numbers on a black background. If your target is a bleeding-edge techie, go for the brushed metal, glass and chrome look. If your target prides himself on being self-educated (not uncommon in a Wall Street where old-school seat-of-the-pants traders are increasingly being rendered obsolete by PhD-wielding droids) then make sure your spreadsheet has a home-baked appearance, with misaligned columns and clumsily-annotated graphs. And so on.

The possibilities for customization are endless. Everything you know about your target should be reflected in the design of your spreadsheet, such that you press all the right buttons. Pander to his prejudices, cheerlead his favorites, blackball his enemies. Your target will be so thrilled to see his own biases confirmed, that he will not dig deeply into your biases, which after all are what your spreadsheet is designed to verify. And then you’re home free.

Truly, Excel is a wonderful tool.