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.