Getting started with this book isn’t quite easy for someone who hasn’t worked too much in setting up a machine in these databases. I’m going to log my steps so that in the future people (or myself) can quickly reference my steps and see what worked for me!

Using Ubuntu 12.04

First step is to install the postgresql packages which comes in two pieces, the client and the dev package with the additional add-ons that the book requires:

sudo apt-get install postgresql-client
sudo apt-get install postgresql-contrib

Postgresql creates a postgres user which manages the server if you run it locally. Ideally you should create a user for yourself:

# Opens psql as the postgres superuser
sudo -u postgres psql
# Creates a user for yourself
createuser --superuser [user]
# Create a password for your user

Exit the psql dashboard and return to terminal. Now I can create and log into my server by using the psql command and install the extensions:

# Create DB
createdb book
# Open the newly created DB
psql book
# Install the extensions needed for the exercises in 7 Databases in 7 Days
create extension tablefunc
create extension dict_xsyn
create extension fuzzystrmatch
create extension pg_trgm
create extension tablefunc

Another link which was quite helpful for getting psql setup and running:


In its simplest form, prospect theory, originally pioneered by Kahneman and Tversky, explains why people make decisions to minimize loss rather than to achieve the highest expected value.

In a classic example consider two problems:

You are given $1000 and asked to pick one of two gambles:
A) 50% chance of winning $1000
B) $500 for sure

You are given $2000 and asked again to pick one of two gambles:
A) 50% chance to lose nothing
B) Losing $500 for sure

For the majority of survey respondents, in the first event they chose to gain $500 for sure, but when the event is framed as a loss, they chose to gamble. This is because of loss-aversion that is exhibited once you have a set reference point. The chance to avoid a loss becomes more tempting because we are so averse to losing once we have the $2000 in our possession even though the expected values of the gambles are identical.

Some day-to-day observations regarding leadership decisions while working in a public company seem to be neatly explained with prospect theory. Of these observations is the cultish obsession with revenue and EBITDA at my company. While financials reflect some measure of success, upper management has come to worship them as harbingers of life and death. One of the challenges that I have faced is the battle against upper management on whether to prioritize long-term goals vs short-term gains. In the losing battle I wage, I have seen time and time again a shift in prioritization from long-term progress to short-term goals. The idea of reference points and loss-aversion within prospect theory provide a possible explanation for these sub-optimal decisions.

Prior to an IPO, a company is often in a mode where they may be pre-revenue or unprofitable. A monetary loss may not be important and can be offset by a more intangible resource gain within the company.  Without public reporting, the reference point for the company is more fluid and changes with the needs of the company. The challenge of an IPO is that once a monetary reference point is set, these intangibles which may help the company in the future may become undervalued as they are not counted in the bottom line. The correct time for an IPO often comes when companies are growing the fastest and striving to become more profitable, exacerbating the possibility of missing projections and entering the realm of “loss”. In a public company, the focus is completely different with forecasting and quarterly announcements of targets. These promises become reference points set in stone for leadership and, when faced with the danger of missing the numbers, loss-aversion pushes decisions that will sacrifice anything to avoid losses. These initiatives tend to be ones that prioritize short-term revenue and padding while ignoring long-term impacts.

A second symptom of this creates an obsession with quantifying initiatives with a revenue/margin impact. This focus often de-prioritizes long-term initiatives because they often do not have immediate revenue boosts and are more resource intensive. Thus, by avoiding the losses based on a public reference point in a public company, leadership will trade the long-term gains for short-term value.

I believe that strong leadership is able to avoid these pitfalls, whether by understanding the economic concepts that drive their decisions, or by having a broader sense of what is good and bad for the company. As of yet I am still on the lowest rung of the corporate ladder; however, I do wonder if understanding these models can make us immune to their pitfalls. Perhaps that would be an interesting experiment to conduct!

Through the years, I’ve come to develop a basic set of rules that tends to hold true for most game economies. Understanding general aspects of real world markets and combining that knowledge with that of a game economy allows for a good understanding of what to do and avoid in game markets.

Tenet 1:

In a factor economy understand the entire supply chain.

Frequent and dedicated trading is almost always easiest at the lowest level of a supply chain. The liquidity of these goods and the amount of available supply and demand is almost always highest at the lowest levels of the supply chain because these goods must be combined to create higher level parts. When trading these commodities, the entire chain of production becomes a possible exit point because you have to ability to undertake the crafting to convert that good into a product. This ability to convert the good as well as trade it allows for greater reward because of the higher possibility of arbitrage between the purchase and the many exit goods, as well as reduced risk because the good can be converted to another.

A caveat to trading low level goods is that there is a hidden value of most commodities in games that have crafting systems that is priced into the market. When crafting requires repeated creation of products using raw materials, the experience gained from the commodity is often priced into the material. Often this will result in the products created by these materials to sell for less than the inputs because of the value of experience. Being aware of the usefulness of the outputs and extra careful about the dollar value of experience will help in avoiding costly trades.

Tenet 2:

The more liquid the good, the smaller the margin.

Just as demand and supply are the highest for the low level commodities, the trading activity within these goods creates the most efficient markets. Because of the constant activity, spreads between the bids and asks tend to be small and thus the margins for trading these goods tend to be thin. One must be very careful when trading with low margins because the often overlooked costs of commissions and trading fees can often turn a profitable trade into one in the red. On the other hand, slow markets tend to have high spreads and thus offer the most opportunity for profit. One tactic when keeping this tenet in mind is that when one has time to dedicate to trading, they can pick highly liquid goods to trade frequently and increase profits; however, when one is not actively in the market, one can often invest in a few highly illiquid goods to effectively maximize the profit off of a single trade during the time that you are away from the market. One caution with this technique though is to be sure to pick markets with which you have a strong understanding. With illiquid markets, often the prices in market are very volatile because of the nature of players posting outrageous prices. One must have a lot of caution in picking their investment because this volatility usually regresses to the mean and thus profit opportunity will be lower than expected.

Tenet 3:

Always understand the origin of supply and the reason for demand.

Understanding supply for goods can help you foresee and take advantage of supply shocks or booms. In games, all goods can be traced to a specific origin. Understanding the amount of the good available and the relative rarity of the good can help with judgement of price and maximize your profit. Equivalent is understanding the reason behind demand. If demand is artificially inflated by some circumstance, investing in a good can be dangerous when the market crashes. This particular note is very salient in real world markets as well.

An example of this in action is an early demand spike for a highly rare item On the supply side, take for example a rare dungeon drop that is found early on in the game release. It is almost always good to sell that good immediately because supply will be severely constrained. As the game progresses, the rarity of the item will drop and the value of the good will stabilize at a lower value. The best course of action in this circumstance would be to immediately sell the rare good at a very high price (take advantage of the anchoring effect ;)) and then quickly reinvest the money into the market to beat inflation. Inflation early on in a game economy is a very real issue because of low currency saturation. As money is collected by the game population, money supply will grow quickly, and inflation will be high and unstable. In an established market, the urgency of these trades is much lower because of the relatively lower risk of inflation.


These are just a few basic aspects that I keep in mind when thinking about a trade. After a while, most of these considerations become second nature and you will automatically understand when each of these tenets apply and what they might tell you about the good. Most game economies are only mildly sophisticated because of the nature of the market with many participants that are not engaged in actual trading and use the market purely as a tool to sell and buy. This gives the traders of any game a great opportunity to accumulate returns and ultimately those who are the most aware come away with the best earnings!

I was told in high school the shower is the best place to think of ideas. As I mulled over my ideas about the GW2 market, an idea popped up…

Guild Wars 2 introduced an interesting new type of guild system. The ability to join and stay in multiple guilds while selectively representing guilds has some great aspects. I’ve always found in MMOs that I enjoy having a few of my alts be in different guilds to interact in different player communities. The interaction between players is always one of the central aspects of the game and is personally one of the largest staying powers for online gaming. Having multiple guilds gives great ease to joining different communities just as you would have different friend groups in real life. It also opens the door for more specialized types of communities. PvP, WvW, and PvE are all vastly different experiences in GW2 and the option to have specialized communities will greatly improve the experience for all players.

One caveat for this new system is the relative lack of anonymity with your alt characters. The fact that you can always be seen in guild screens and friend lists on different characters makes it so that you are always visible to the players you are connected with. While this is a great convenience most of the time, with accounts locked to purchases, it isn’t easy to get another account to play privately in contrast to F2P MMOs. However, overall my experience with the guild and party system keeping you connected to your party across characters and play sessions has been very positive.

This new guild system brings me to my new idea. With the ability to switch and participate in many guilds on the same account, an interesting possibility opens up for my market escapades. I haven’t examined the new guild system closely enough, but the great flexibility with guild ranks allows for a very fine control of the actions in a guild. Combined with the functionality of the guild warehouse and bank, it is very possible to open a type of banking system. Managing a shell guild (The Bank of TopGun!) to act as a central depository hub and actively managing the deposits in the game could become a great way to establish a more complex and robust player-run economic system. I’m sure that I’ll be forming a bank for members of my guild to deposit and withdraw from. The money in the fund could then be leveraged to trade and participate in the market.

A deposit only format for depositors of the bank would be necessary to manage the books and keep track of “share” purchases in the bank. The books would have to be kept clean and ownership of the fund adjusted as deposits are made, however this can all be done with relatively simple bookkeeping. The traders would be granted withdraw permissions and be allowed to manage the fund. A transaction log would make this process complete with the ability to track performance and finely tune the deposit and withdrawal times to reflect proper trades in the fund. With the free market system in GW2, being able to leverage a depository institution would likely grant much more market power to the traders and result in the ability to maximize returns. Working out a withdrawal system that is monitored but available while traders aren’t online is another task to think about.

Pending the release of the game I’m already excited about my first plans. Mapping out the guild system and trying to create possibly the first GW2 bank will be exciting!

The end of BWE3! Guild Wars 2 has been one of the most exciting new releases this year and the only milestone left now is release! This weekend I finally got around to testing out the market system in GW2. Having played a lot of MMOs with needlessly complicated or tedious markets, I was greatly refreshed with the simple market system in GW2, very reminiscent of the futures market. I quickly got drawn into the market as my day-trading experiences reincarnated within me.

The market operated on a simple bid/ask market platform. Players could place bids or set items up for sale or place market orders at will. Probably by combination of the fact that this was the Sunday of the last BWE event and the relative infancy of the game, I found the market in GW2 to be amazingly inefficient. In almost all high volume commodities the spread between bids and asks were almost always 5 copper. With a base price of 20 copper and a listing fee of only 5%, the opportunity for arbitrage was ripe and I instinctively began trading copper ores.

The liquidity in the market was impressive for such a young economy. My bids and asks were filled within seconds and I turned around my savings multiple times within the span of 10 minutes. In the short hour that I played with the market I gained a return of almost 200% and a trade profitability rate of 100%. If only I had those odds in the real world.

What struck me about the system is that the ease with which I participated in arbitraging opportunities indicated that not many were yet acting in the markets. Furthermore I began to wonder if the construction of the system and the slight lag that it imposed upon placing orders created this artificial 5 copper spread. The game limits buying goods to a single stacks and, although this may not hold true for others, I found the typing of orders to be relatively fickle and my typos often delayed my orders. I wonder if given the relatively small amount of traders, the inability to insert orders fast enough caused the spread in the bid and ask. I know that before the days of HFT and electronic trading became the norm, arbitrage definitely existed in real-world markets. Today the markets are extremely tight and any slight opportunity is immediately acted upon by some server in New York.

It would be quite interesting to study the shift in the GW2 market as more and more traders come in to close these gaps. The real market doesn’t provide the stability and ease that a more unsophisticated market such as GW2 provides because the volatility and spread of contracts is so small which magnifies risk and increases the amount of leverage needed to gain a small profit.

My short foray into GW2 trading has ignited a spark within me that I intent to fan into flames once the game is released. I plan on forming a trading fund with guild members and create some for of banking/fund management system within the confines of the game. Perhaps there is even room for a third-party to step in and become a broker-house. If the market becomes large enough and one becomes reputable enough, it could be conceivable that an entity could step up to act as a mediator and issue complex securities such as options.

Either way, I’m sure that a large enough crop of sophisticated traders will participate in the GW2 markets to greatly increase the skill required to stay profitable in the market. I plan to stay on the top and I look forward to trading with you all… May the best man win. 🙂

While experimenting with the nuances of vlookup, I noticed an interesting pattern in the way that Excel conducts searches. The searches begin from the bottom of a list and work upwards sequentially. The byproduct of excel using this method is that vlookups in excel become exponentially slower as the lookup table grows. As I brainstormed for ways to alleviate this issue, the common solution to all sorts of computer science problems turned out to do the trick. My experimenting produced a mish-mash of excel formulas that can use a piece-wise approach to drastically improve the performance of large lookups.

The only requirement for method is to have a sortable data-set and lookup table. The idea is to shrink the lookup area as you exhaust your search so that, with an inversely sorted data set, you continuously help excel find a match in the first few rows it checks, and then eliminate rows that would never contain the value in the first place. A more detailed explanation is below for those who are interested.

The Process:

The first step is to setup the data correctly so that Excel can find values as quickly as possible. Sort the Dataset in ascending order and the Lookup table in descending order. (Make sure the sort area covers the entire table to avoid scrambling the dataset)

Create empty columns for the values you are looking up as well as two extra blank columns.

In the first extra blank column write the following match function to get the row number of the object in the lookup table:

=IFERROR(MATCH(<Dataset value>,<lookup-table value array>, 0),1)
E.g. =IFERROR(MATCH(A2,Sheet1!$A$2:$A$100,0),1)

Once that formula is written, we want to repeat it once every few hundred rows. You can either use a macro of use the nifty auto-fill options in Excel. To use the autofill write the formula in the first cell, select that cell and a few hundred blank cells underneath it, then double click the bottom corner of the selected region to auto-fill the entire column. The last step is to add a 1 to the last row of the column.

Once this is complete, we have a reference column for our dynamic lookup. In the second extra blank column we need to populate the row numbers that appear every few hundred rows into all the blanks. This can be done with an if statement which takes the match value if it exists in the first column, or else takes the value from the cell under it (which should have the next match value):

=IF(<returned match> > 0, <returned match>, <cell directly under>)
E.g. =IF(A2> 0, A2,B3)

You should now have two columns, one with a value every few hundred rows which pulls the matching row number (or a blank if it doesn’t exist), and a full list of row numbers.

Now we can create the vlookup and link it to a dynamic reference that shrinks as we search. The INDIRECT function in excel allows you to create a reference out of a string.The idea is that, if we’re searching for the value “5” in a list of numbers from 1-10, once we get to 5, we can skip searching through items 1-4 and start at 5-10 to find a match.

In the column write a normal vlookup:

=VLOOKUP(<lookup value>, <lookup table>, <column to fetch>, FALSE)
E.g. =VLOOKUP(A2,Sheet1!$A$2:$B$100,2,FALSE)

Now replace the array reference with the dynamic reference:


Now as we copy the vlookup, the reference for the lookup table will resize according to the row number in the previously populated column and aid excel in shrinking the area it needs to search to find a value.

This hack increases the speed of excel lookups by 10x-100x! (A speed test with a 500,000 row to 500,000 row lookup improved times from 1+ hours to 1 minute 13 seconds)  The same principles can also be applied to an Index Match carry have the same advantages!

Optional Explanation:

Take for example two tables with values 1-10 for which we want to use a vlookup.

The reason this works is that excel  vlookups conduct their searches with a simple row by row search starting from the bottom and moving up. By sorting the columns in opposite directions, the first object you look up will be the last object in the lookup table which means that the lookup will complete instantly. Excel won’t have to iterate through the entire lookup table to find the value. However, merely sorting the datasets won’t alleviate the problem because no matter what, as we iterate through the lookup there will be values that take the worst-case time to find because they are at the top of the lookup list. This contributes to immense slowdown in the process.

This optimization works by shrinking the table as we do the lookup and making sure that the value that excel is searching for is one of the first ones in the lookup table. This example illustrates my logic:

We have two lists A-Z we want to lookup against. We search for the first value, A, in the lookup table from the bottom up. If we sort the lookup table A-Z then we will have to look through 26 values (Z, Y, X, W, … etc.) until we get to A to find it. If we sort it in inverse order we will get the A match on the very first search. Now we move on to B. Because we know that we’re on B now, we no longer need to check A to see if it matches. Therefore if we shrink the lookup table to Z-B, we will find B on the first try as well. This continues with every iteration and ensures that we always find the value quickly in the lookup table.

Although the usage of INDIRECT in the lookup adds another calculation to the normal lookup, it adds to the number of calculations linearly and reduces total calculations exponentially. The larger the data-set the better the improvement.

One piece of advice to note is that, even after developing this method, I’ve refrained from using vlookups whenever possible. What I’ve discovered in my time handling large datasets and doing analyses is fundamental skills such as query building reduce troubles along the way for every task. As I work with larger and larger datasets, the ability to craft a query and use SQL to manipulate the data as much as possible has dramatically reduced my dependence on excel and increased my ability to work with any dataset.