Saturday, February 12, 2011

MS Excel: Should I Use VBA or Not?

I've been using Excel for years but only recently started learning VBA. Now when I want to do something in Excel I have to decide whether to write a VBA module to do what I want or just hammer out a quick and dirty solution using formulas. I am by no means a programming expert (if you can't tell already), but I have gleaned a few pointers that may be useful.

Using VBA

Pros:
  • You can make turnkey products - everything is under the hood, reducing the likelihood that your user will break something.
  • You can customize everything - write new functions, change formatting, etc.
  • Many things are just not possible without VBA: loops, for example.
Cons:
  • Programming is time intensive.
  • If it does break, your user is in much more trouble than if your spreadsheet were non-VBA.
  • The end product is less flexible. If you want to change something once you're done, you might need to spend hours rewriting all your modules. (How many times have you created something and they didn't request changes?)
  • If you hard-code formulas, users can't tell what is going on unless they pop the hood and understand your coding.
Let's look at two examples. I just built two versions of Boggle -- the board game where you use letters on dice to spell words. The first version has almost no VBA, and the second is mostly VBA.


You'll quickly notice that the VBA version is very clean - only one tab; just press a button and go. It also has functions that are very difficult without some coding, like the button that rotates the board.


Now look at the non-VBA version (these were made at different times -- sorry the formatting is slightly different). You can peek at the "Data" tab and see exactly what is going on. I'll admit I patted myself on the back for figuring out how to do all of the calculations. You'll notice that the spreadsheet doesn't work right unless you go to "Formulas -> Calculation Options -> Automatic Except for Data Tables". This enables the scoring formulas to work but forces you to manually recalculate the spreadsheet when you want a new board. Normally, changing the formula calculation settings on a product is a big no-no. There's a high likelihood that the client will forget and probably won't be able to figure out what is wrong.

In the end both version work, but I think we can find a useful way to categorize features:
  1. Features that can be easily done without VBA (e.g., formatting)
  2. Features the client will want to tinker around with (e.g., decision-making formulas)
  3. Features that could easily break and cause headaches
  4. Things that can only be done with VBA
One common pitfall is when a developer (who probably learned programming first and spreadsheets second) does some basic requirements gathering and jumps right into the coding. They often don't know that there are Excel formulas for many of the things they normally do in VBA. If you are an end user requesting a product, you may want to mock up a prototype of the user interface, with all of the formatting and formulas you will use, and then draft your requirements document outlining the modules you will need and where they tie in. If you do this you will probably get something closer to what you want and the developer will thank you.

Thursday, February 10, 2011

Using Excel to Mimic STATA Regression Output

I got bored yesterday and was messing around with some Excel commands. I knew that you could use the LINEST command to do simple ordinary least squares on a scatterplot, but I didn't realize that you could actually use it to regress multiple variables and output a number of test statistics.

I love STATA and use it every day, so I thought it would be really cool if you could output STATA-quality information on OLS regression in Excel.

So here it is. I think I got everything right. Let me know if something looks weird.

First, here's an explanation of STATA regression output: http://www.ats.ucla.edu/stat/stata/output/reg_output.htm

And here's an explanation of the Excel LINEST array function:

Thursday, February 3, 2011

Review of Malcolm Gladwell: Outliers

"Lift up your heads," Robert Winthrop told the crowd many years ago at the unveiling of a statue of that great hero of American independence Benjamin Franklin, "and look at the image of a man who rose from nothing, who owed nothing to parentage or patronage, who enjoyed no advantages of early education which are not open—a hundredfold open—to yourselves, who performed the most menial services in the businesses in which his early life was employed, but who lived to stand before Kings, and died to leave a name which the world will never forget." (Outliers p.19)


In a forward to the autobiographical “Winners Never Cheat” Larry King labels Jon Huntsman as “the American Horatio Alger”. While most people enjoy a good “rags-to-riches” American Dream, Malcom Gladwell believes that there is a deeper story behind the simple explanations of success.

King writes that Huntsman was “afforded nothing but an opportunity to compete on the field of dreams and that the rest—his vision, determination, skill, integrity, a few lucky breaks, and ultimate success were up to him.” It’s hard to disagree with the commonly held belief that in order to be successful you need to work hard, plan a vision, and be the talented negotiator, but Gladwell believes that success might be determined more by choice opportunities and cultural legacy than individual abilities. It just turns out that Jon Huntsman was given such an opportunity.

Huntsman grew up in rural Idaho, the son of a school teacher. Jon was lucky enough to receive a scholarship to study at the Wharton School of Business at the University of Pennsylvania. While Jon had the work ethic, the grades, and the vision, Gladwell would point out that this special educational opportunity and the thousands of laboratory hours that came with it enabled Huntsman to become one of the first experts in chemical product design for commercial manufacturing. He would go on to start Huntsman Chemical and use the unparalleled training he received creating simple plastic egg cartons to form a multi-billion dollar corporate empire.

The American Dream

“In the autobiographies published every year by the billionaire/entrepreneur/rock star/celebrity, the story line is always the same: our hero is born in modest circumstances and by virtue of his own grit and talent fights his way to greatness. In the Bible, Joseph is cast out by his brothers and sold into slavery and then rises to become the pharaoh's right-hand man on the strength of his own brilliance and insight.” (Outliers, p.18)

The conventional view of success states that people with natural talent will rise to the top. In a world full of scarcity, we must measure highly on generally-accepted tests of ability and compete for the best positions in the best companies. The common theme is convergence: everyone is aiming for the single seat at the top, and there is one right answer to get there.

This leads to a common fallacy: that those at the top must be the best. In a meritocratic society that values the most-accomplished, you would be labeled a heretic to claim that the system gets it wrong. In a perfectly efficient free-market system with rigorous competition, people receive resources according to their potential, and the best and most worthy will succeed regardless of where they start in life.

Turning Success on its Head

In order to test this assumption, Gladwell first turns to sports. He cites researchers who examined successful hockey players and identified a puzzling common factor: the best hockey players are most commonly born in the first three months of the year. After further study the researchers realize that since the age bracket cuts off at December 31st, athletes born in the beginning of the year are older for their class, and at the age of 12 or 13 may be much more physically developed than other peers in their division. This distinction leads them to receive better coaching and more rink time than others, and they rise to the top. This finding is startling: it means that arbitrary rules lurking your organization may be severely limiting your success. Are certain simple hiring standards preventing you from reaching the best talent? Maybe it’s time to examine the incidental rules within your organization and make sure you aren’t artificially limiting yourself.

While many organizations focus on becoming the “best of the best” in their particular field, Gladwell recommends another approach. While it’s always possible to pick the most popular market and rise to the top through competition, divergent organizations will break new ground and become experts in fields for which there is not yet a demand, and over time can accomplish much more than their more traditional counterparts. Gladwell describes how as hostile corporate takeovers became more commonplace, the mid-tier law firms specializing in that subject overtook the “white-shoe” firms on Wall Street. We see this on a global scale today, with traditional corporate auto giants crumbling as they compete for the business of the same billion people, while India’s TATA Motors is figuring out how to make automobiles for the other five billion. In today’s age where ideas outnumber people, the divergent thinkers may win out over the Harvard grads.

Apparently, outliers sometimes come in clusters, instead of sprouting up randomly through time. Gladwell lists the richest men in history and points out the fact that a disproportionate number of them were born in the United States between 1831 and 1840. He also shows that billionaire software developers were born predominantly in the mid 1950’s. Gladwell’s reason for this is that the generation immediately preceding a period of rapid change is poised for big opportunities, and that the experts in their field will be positioned just right to take advantage of circumstances beyond their control.

Gladwell examines the conventional wisdom that inborn ability will win out, by studying popular figures in history. He finds in each case that while all of his examples had some inherent skill, they also happened to become experts in their field before anyone else. For example, by the time Bill Gates founded his first software company in his early twenties, he had logged more time writing code than almost anyone else on the planet. Mozart was a precocious youth, but didn’t write a groundbreaking piece of music until he was twenty years into his career as a composer. Gladwell highlights other, more recent musicians as well: by the time The Beatles signed their first record deal they had logged almost 10,000 hours playing live shows in Hamburg’s new rock-and-roll club scene. Through this and other studies Gladwell concludes that 10,000 is the magic number: work at something for 10,000 hours and you will be world-class, regardless of your inherent ability at the outset.

This throws conventional wisdom on its head: do you hire the best and the brightest, or just the hardest working? Gladwell claims the answer isn’t so simple. Regardless of intelligence or ability, the upper and middle class are born with a sense of entitlement: they are trained to “customize” their surroundings to better suit their needs: to negotiate, banter, and challenge authority, while lower-income children are often raised to fear authority. Middle-class parents also see natural abilities as something to build upon and invest in, while lower-income parents see them as static character traits. As a result, certain “privileged” groups of children are raised with what Gladwell calls “practical intelligence.” They know how to make things happen.

This principle translates well to an organization: not only must you work toward being an outlier through developing world-class skills, you need to experience a series of outlier events that come from cultivating a “practical intelligence” within your institution. Often the ability to fill your organization with people who know how to navigate through red tape, network, or think ahead of the crowd means as much as having an amazingly-skilled workforce.

Reaction

While reading Malcolm Gladwell is always slightly exhilarating (“how could the world run so counter to my intuition and yet be so understandable?”), somewhere near the halfway mark of the book most readers begin to question how he is able to make such tidy conclusions. Why is it that researchers never see eye to eye on things, but the journalists who report on them understand the world perfectly? As in Gladwell’s other books, the reader has no glimpse into the actual methods used in the studies, and Gladwell has no incentive to declare whether the case studies he uses are generally accepted and supported in the academic literature. This is “social science lite”: not necessarily empty calories, but not JSTOR material either.

Another criticism that will sound familiar to those who read his previous books is that while Gladwell tells a compelling story, he fails to do it in an actionable way. Readers will walk away probably feeling inspired, but a little puzzled as to what they should do to be successful themselves. This is possibly acceptable for a social science crowd[1], but when writing about a topic as pointedly “self-help” as Success, you should throw in some how-to points to satisfy your audience. Gladwell has found a significant following in the business world, so it’s surprising that he hasn’t made his books more instructional. Outliers does appear to have some solid insights that can be applied to organizational strategy, but they’re not packaged in a way that is readily accessible.

The last chapter of Outliers describes how Gladwell’s distant maternal ancestor was purchased by a slave owner in Jamaica, and that their son, John Ford, happened to be born with whiter-than-normal skin that spared him a lifetime of slavery. John Ford’s descendants subsequently inherited an upper-class Jamaican lifestyle and the education that accompanied it, which eventually enabled Gladwell to receive the opportunities and experience he needed to become a successful author. This narrative brings the book full circle and hopefully leads the author to reflect on the fortuitous circumstances that enable today’s and tomorrow’s successes, and the concentrated efforts necessary to achieve excellence.



[1] for example, “Freakonomics” seeks to answer a random assortment of questions and show the general public that economists don’t just predict GDP and unemployment rates all day.

The Wisdom of Decision Analysis

Economists frequently characterize humans as “rational beings,” capable of subconsciously weighing the costs and benefits of every situation and taking appropriate action whenever possible. This assumption allows the construction of sophisticated models of human behavior used to predict your demand for Cheetos, how likely you are to speed on the way to work, or the likelihood that you will die from python-inflicted asphyxiation.

The economist Herbert Simon pointed out some glaring lapses in the rationality of mankind, like the continued existence of Las Vegas casinos, and the fact that mankind needs economists to point out principles that everyone supposedly knows and already uses. He posited that people weigh the costs and benefits of even their information gathering, and choose a level of “bounded rationality” that suits them. In other words, we don’t have enough time and energy to be completely informed, so we determine how informed we can afford to be and accept the consequences.

The practice of Decision Analysis allows an individual to become more functionally rational by providing tools that can be incorporated implicitly into ones thinking, or used explicitly on paper or a spreadsheet, to make more informed decisions. The fundamental process in decision analysis is to (1) clearly define your problem, (2) specify your objectives, (3) generate alternatives, and then (4) evaluate them in a way that allows you to more intelligently judge which option is best. This may require you to combine qualitative criteria (e.g. how exciting, obnoxious, or colorful something is) with quantitative variables (e.g. the money and time you have to give up). You may also need to account for the fact that some benefits occur now while others occur later, and some alternatives may involve more risk than others. The thought of evaluating all these potential factors and boiling them down into one final verdict may seem daunting, but these skills can actually be learned in a relatively short period of time[1].

Qualitative criteria can be evaluated in a straightforward manner. After determining your alternatives and objectives, simply form a matrix and use a 1-9 scale to score each of your alternatives on each objective. You then need to determine how important each objective is (i.e. split up 100% into weights for each one). Then you can calculate a weighted score for each alternative and compare them easily. The accompanying examples show how universally-applicable this technique can be: you can determine which language to learn next, which projects to incorporate into your work schedule, or (if you’re feeling Napoleonic) which country to invade. You can also build an instrument that tells you, based on your mood, what books you should read. In a more global application, Thomas Saaty, a pioneer in the field of decision analysis, even developed a more sophisticated version of this method to evaluate policy strategies for the Israel-Palestine conflict[2].

When we make decisions, we are often unsure about assigning definite numbers to fuzzy situations. That is perfectly fine—you can incorporate fuzzy numbers into your decision analysis by specifying a range of numbers and utilizing a Monte Carlo simulation to test many scenarios at once. This allows you to see an overall pattern and still make clear decisions even in the face of uncertainty. You can also use Excel to simulate how the results will change as one or two of your factors vary, which allows you to more effectively evaluate the complex relationships between different unknowns and determine your strategy.

Quantitative data such as time and money require other unique methods to evaluate. For example, say you are trying to decide between three investment opportunities: one is a sure bet, but doesn’t pay very much. Another pays much more, but requires you to wait for a few years. The third might pay off very well, but involves some risk. Which should you choose? Decision analysis can help here as well. You can account for time differences by calculating your time discount rate and decreasing the value by that amount. You can also calculate how risk-averse you are and discount the value of an alternative for risk as well. This allows you to level the playing field and translate very different alternatives into net-present, risk-adjusted values.

Donald Adolphson teaches a Decision Analysis class at Brigham Young University. Every September, Professor Adolphson receives a new group of graduate students from widely-varying backgrounds, and walks them through basic Excel skills and problem statement construction. After a few months, they are completing advanced analysis projects for government, nonprofit, and private institutions. Students build a toolkit that combines business strategy, economic modeling, and even some actuarial methods. The projects this year were as varied as the students’ backgrounds: one project evaluated an array of insurance policies to minimize cost; another analyzed water usage in the university bathrooms; one team of students worked with the State of Utah to determine how best to decrease waste from plastic bags. After only one semester, it is hard to believe that some of these students studied Humanities, and had never created a spreadsheet before.

Until recently, scientists were convinced that the brain is like quick-set concrete: starting in the teenage years, neural connections become set in stone and are hard or impossible to alter. Recent research has unearthed the concept of neural plasticity, or the brain’s ability to profoundly change itself even in old age. Professor Adolphson’s course shows us that people from many different walks of life can internalize sophisticated Decision Analysis techniques and incorporate them into daily life.



[1] The principles referred to in this essay are found in the book Smart Choices: A Practical Guide to Making Better Decisions, by Hammond, Keeney, and Raiffa. The quantitative techniques incorporate the curriculum of Dr. Donald Adolphson from the Marriott School of Management at Brigham Young University.

[2] There is a substantial body of literature on the Analytical Hierarchy Process, largely developed by Mr. Saaty. An Analytical Network Process, involving more complex relationships, is also growing in use.