latenitewithstede at gmail dot com || @StedeUS on Twitter || Subscribe with RSS

Monday, January 6, 2014

Why Spreadsheet - The Sharp Edge of Aggregate Analytics

I talk a LOT about spreadsheeting.  It's one of the things that I tend to do a lot.  And I'm not alone in this.  My buddies Zerohour, Kathroman, and Zamboni - all three of them world-class goldmakers - all make liberal use of spreadsheets.  One of the things that we often take for granted are the reasons why we spreadsheet.  It's easy enough to say, "Because we're rich and know it works."  But how?  What is it, specifically, about spreadsheeting, that allows us to be so much more lucrative?

Well, there are a few things.  So let's get right into them.  The first advantage of a proper spreadsheet is that it is often, for many of us, our own custom-tailored TUJ Profession Page for all professions we have, at a single, condensed glance.  All of us have used TUJ on the web, and as big time goldmakers, we all use that same data in our spreadsheets - without the loading times and the extra fluff that's not part of our routine.  So, if you don't bother with Netherweave Bags - fine - don't include em in your spreadsheet.  In this manner, we build a spreadsheet that's restricted to the markets we're in.  We can fit much more of our interests on a page / sheet, and don't have to wait on loading times.

Some folks even draw on both TUJ and Wowuction data, when it's available, to look at not only the current market value and materials value of an item, but also to look at the 14-day median market value and standard deviation of the market value, to understand the degree of volatility involved.  Some even go so far as to setup conditional logic that generates a quick Go / No-go recommendation. 

There is a second advantage to all this: you catalogue all the possible markets you're able to enter.  A market that's not profitable this week might become profitable next week.  It's easy to forget markets like Jade Owls or Sapphire Cubs or Enchanted Lamps / Lanterns - but if they're there on your spreadsheet, staring you down every time you glance at your markets, they become very difficult to overlook.  In this way, a spreadsheet becomes an organized digital notebook.  And it's useful - even if you stick to just your factory professions or your niche markets and rarely venture outside them, a good goldmaker does not shun information - so it's very useful to see it, even if you don't act on it.

But the biggest reason why we spreadsheet is because it allows us to do things that we can't find on TUJ, in TSM, or on Wowuction.  It allows us to explore data in novel ways.  We see this a lot in aggregate markets.  It starts with base materials, like ore, and proceeds down several possible paths - like a shuffle.  Want to know what the most profitable transmute chain is for your Golden Lotus, including the option for making meta gems?  Want to include that transmute bonus?  Or maybe you'd have better luck using it to make flasks.  Or how about Ore?  Is it better to shuffle, make PvP gear, or Belt Buckles?  In that shuffle, is it more profitable to disenchant jewelry or to fish for perfect cuts?

We all have limited time to play.  We all have limited materials available to play with.  Being able to choose how we spend each in an efficient manner that balances them against the enjoyment we get from playing is an important part of being a well-rounded goldmaker.  Not everyone will nerd out to this degree, though.  These might not be things you think to do on your own.  I definitely hope that the blog and podcast inspire some new ideas, but there are a lot of times that others will beat us to it.

For instance, GoblinRaset, a past guest on the podcast, has a very under-rated post on The Consortium Forums about crafting green-level Blacksmithing gear as a way to fish for Blue procs.  If you're not familiar, there is a full set plate 384 gear that Blacksmiths can make, and 10% of the time, you'll make a rare-quality ilvl 415 version of the item.  These are ideal for folks who want to level their alts as painlessly as possibly while not breaking the bank.  GoblinRaset in that post, suggests how to setup TSM to handle this - crafting the greens, fishing for blues, and De'ing / vendoring the Greens that didn't proc.

And this is a cool idea.  On many servers, it's an untapped market.  But I'm gonna pick on GoblinRaset for a minute here because he and I bros, and suggest a potential improvement.  I call it demand-based rebalancing.  Often, in stable markets of items that share a set of materials, some of those items still have a higher market value than others.  Usually this is because these sell more often.  If we were to take a raw average of all these items to calculate our profit potential, then, we'd arrive at a conservative estimate.  This is because the higher profit items sell in greater volume than the lower profit items.  If you were crafting once and leaving the market, a raw average is fine.  But if you're there to stay, you'll be restocking, hopefully with TSM, as needed, up to a set stock threshold.  The items selling faster will be restocked more often - you'll make more of them because you'll sell more of them.

Then how do you account for this?  TSM has some neat functions that leverage your TSM accounting data, but that's only useful if this is a market that you've blanketed for some time.  But, since these recipes and items have been in the game since MoP launch, they're also within Wowuction's data.  There are, of course some limitations to using Wowuction's old auction data, but it is generally safe to use to evaluate the relative speed at which items in the same market sell.  With a spreadsheet, we could use the Demand #s from Wowuction to figure out what percentage of sales out of items in this market we can expect to see for each individual item.

For instance, we might see numbers like 0.10 for 7 of the 8 item slots, and 0.30 for the 8th (let's say - boots).  Then we know that boots are selling fast.  If those 7 yield a 40g profit, each, and the 8th yields a 160g profit, then we can figure the average profit for this market, rebalancing for demand is:

7*(0.10*40) + 1*(0.30*160) = 76

That is, you can expect to make 76g per piece sold, in the aggregate.  This may seem misleading as no individual piece will yield a profit of 76 gold, but aggregate profits are very useful when we consider advanced AH strategies, including vertical monopolies - a topic that I will address in detail in a future blog post.  In any case, this type of analysis is not available currently, outside of a spreadsheet.

Hopefully, this has given you an idea of the kinds of things you can do with a spreadsheet and why the best goldmakers use them often.  It's possible to arrive at the same actions blindly via luck, faith, gut feelings, or even stubbornness, but you can arrive at them more quickly, more efficiently, and more confidently - with a spreadsheet.


  1. EDITOR's NOTE: Kathroman USED to make liberal use of spreadsheets and now makes liberal use of spreadsheet-esque web-based resources.

    P.S. This has NO bearing on Stede's point and probably didn't even need to be pointed out...

    1. No it's really good to note because we've collaborated before on some of both. Ideally, a web-based resource eliminates the need for Excel, which is something a lot of folks don't have.

      In the meantime, I may spend a few hours playing around with Open Office to see what I can do there. But, if it looks too daunting, I will be sticking with Excel :)