Stede & Co's MoP Shuffling Spreadsheet

[Stede]

Latest Updates: Pushed a new update on 28-November-2012 with bug fixes to a major updated I pushed at the end of 27-November-2012 (US time).

Figured I'd throw this up here before others did it for me. This was originally posted on The Consortium forums. If you have questions or comments, you'll probably get a quicker response if you post in my thread over there. I'm a busy guy, and I don't get to OC as much as I'd like to.

I know we're paranoid about downloads here, and I don't have much rep - here (I've plenty at The Consortium). I guess - if you don't know me - first of all - welcome to the party. Second of all - Unholyshaman is a good buddy of mine.

The shuffle in MoP is different from Cataclysm. Both green pieces of jewelry take 3 total gems, but there's no wasted color, like zephyrite. On top of that, a large amount of the profitability potential is locked behind meta cuts - primarily because these patterns are rare JC-only soulbound world drops - which means people who actively play a JC in MoP have an advantage over crafting alts. We can also up-convert and down-convert enchanting materials. On many servers, we're also seeing that a short-shuffle is feasible - where you just vendor green jewelry instead of DE'ing it. That's just a few interesting points. Lots of people have asked me about Blacksmithing in the shuffle - it's just not efficient right now - algebraically. Next patch / tier we may see that change and there would be a dramatic shift in the shuffle. But for now, here you are.

((Unholyshaman tells me bitches love pictures, which I usually don't do, but here's one anyway))





Original Thread

First off, I just want to make it clear that I didn't do all the work on this myself. I had a major amount of help from Mithrildar and some great feedback and discussion from quietstrm07. Beyond that, this has been a community effort - the Ethereal Contributors that have used this have made some very good suggestions, which I've done my best to incorporate.

One day, we'll be able to get all this sort of thing online in a webapp, but for now, I hope you'll give Excel a shot.

I wanted to make a youtube tutorial video, but I just haven't found time. I wanted to do something more with perfect cuts, but offering a better solution was rather untenable in this format. But there's tons of super-cool stuff in here that I hope everyone appreciates.

You Will Need

#1 - Microsoft Excel with Macros Enabled (confirmed works in Excel 2003)
#2 - WoWuction Data For Your Realm
#3 - The Spreadsheet

I'm not doing an XML version - the files are needlessly large. I'm not doing a Google Docs version - the computation is epochly slow. Sorry guys.

I never thought the previous sticky did the old Spreadsheet much justice, so I'm going to actually spend some time discussing the different aspects of this spreadsheet. But for those of you who want to just jump in and kind of know the routine, here's a quickstart guide and a list of features.

Quick Start Guide

#1 - Get the Spreadsheet and import your WoWuction data (Instructions in the Info tab of the SS)
#2 - Set your options, your ore prices, and the # of stacks you're going to shuffle
#3 - ZERO OUT YOUR ALLOCATIONS. Now, Allocate your gems
#4 - Profit


Major Features

* Uses the latest information from your Auction House with an Hourly Resolution
* Allows you to decide how to use the materials you prospect in an endless combination of ways
* Allows you to toggle Bountiful Bags and Transmute Bonuses
* Automatically Optimizes Enchanting Materials to their most Profitable Form - Should you choose.
* Visually re-mastered for ease of use
* Straightforward analysis section gives up-to-the-hour, bottom-line advice for how to use your gems, including transmutes, jewelry, disenchanting, cutting rares, and even fishing for perfects
* Valuation of cut gems uses a robust, conservative algorithm to ensure that your profit per stack is actionable
* j311yf1sh has developed support for automatic updates from wowuction - instructions in Consortium OP.

Long Guide

Intro
Let's start with the general concept - who uses this? when? This SS is built to use data from your AH via WoWuction's last hourly data pull from Blizzard. Given a user-defined shuffling path, it will tell you how much profit you can reasonably expect to make, on-average.

You can use this in conjunction with the current market conditions, as it is currently setup, or you can enter your own values to simulate different scenarios. The SS was originally built to give shufflers a concept of how large their margins were to empower continued materials stockpiling and prevent shortages during price spikes in ore. That's a finer point of auctioneering - buying up supply to a price that your competitors aren't comfortable with and depleting their inventory and competitiveness - but it's one that, when executed well, works very well.

To that end, I wanted to make the SS as comprehensive as possible, but also as concise and as possible. There's a lot going on, but the visual flow was something we were keen on keeping polished. Before we talk much about that - the instructions for load in your realm's wowuction data is easily located on the Info tab. Go ahead and load up your data, and then we'll play with it a bit.

Options
Good, now check out the Shuffler tab. And note the Inscription tab is something I'm just playing with now - it's not release-quality. Those of you familiar with the old SS won't feel too far from home. The layout is different, but intended to make more sense. The first place to start is the Options section in the upper-left corner. Here you can set your bonuses, whether you'll Disenchant Blue 415 ilvl Jewelry, and the coolest option of all - whether you want to optimize your enchanting mats.

Mithrildar and I developed a custom optimization algorithm that plainly shows how you would transmute your enchanting materials from the shuffle into their most profitable form for resale. Remember - 5 dust make 1 essence; 5 essence make 1 shard; 5 shards make 1 crystal (on a daily CD). Likewise - 1 crystal makes 2 shards; 1 shard makes 3 essence; 1 essence makes 3 dust. Optimizing your enchanting materials lets you get the most out of your shuffle - try toggling between 'Y' and 'N' and you'll see for yourself.

In addition, it can sometimes get tricky when you optimize as to whether or not it's worth disenchanting your blue 415 ilvl jewelry or not. There's an advice pane just beneath your options that is programmed to account for the optimization, if it is toggled, and tell you what the most profitable choice is.

Analysis
Below that, we have the Gems analysis. We reworked the presentation of this section. You'll see the raw value of the gem displayed first, followed by the additional profit gained through subsequent actions like cutting, transmuting, or disenchanting. All this is standardized to a per-gem basis, to make it very straightforward to understand.

We've added the ilvl 450 jewelry and the cuts for uncommon gems. Cutting uncommons yields a perfect gem about 10% of the time, and in Mists, these have the same stats as their equivalent rare cuts. I want to explain a bit about how the spreadsheet clculates the profitability of cut gems. I takes into consideration all the cuts of that color, and computes an average value (we expect supply and demand to operate in something near equilibrium so you won't sell only unprofitable cuts), and ssigns that average value to the price of cut gems. For perfects, it looks at the Market Value of the other perfect cuts that have been posted on your AH - it uses market conditions, rather than assuming that perfect cuts sell for the same price as rares (which is infrequent, in reality).

The trouble here is, of course, that you usually only choose a select few cuts of a certain color to go 'fishing' for perfects. You lookup The Undermine Journal's Jewelcrafting page for your server and check for opportunities, then set about seizing them. It is a very targeted process of production, and so the average value of all perfect cuts of a single color can be a diluted value. On the other hand, if the SS indicates perfects are profitable, there is very good evidence that there are one or more very good opportunities for that color gem. It is presumed that non-perfect cuts are dumped somehow, and the options allow you to set that dump price (default is 85 silver - vendor price).

Some other quick notes here - You'll notice that the +Profit from Disenchanting will change with your optimization option settings. You'll also notice that the +Profit from transmutes presumes thos transmuted gems will be cut and sold.

Server Prices
Moving on - Below and to the left, you see the server prices. Here, we carry forward the old tradition of "Leave Orange Stuff Alone" and "Enter in #s for Yellow". Once again, you will enter in your own price for ore. This is usually the price at which you either currently buy your ore or that you would like to buy ore at. A further ways down, you also see that once again, you get to set a single price for the blue proc 415 jewelry. There wasn't an effective way to do this when we were building the new SS, and I've found that most shufflers have a pretty good idea of what these sell for. Use your best judgement.

Gem Allocations
To the right, You'll see the famous allocation section. This is where you decide, given the information above in the analysis section, how you want to 'spend' your raw uncommon and rare gems that you got from prospecting. You want to make sure your allocations make sense, so read #4 in the Instructions at the top of the Shuffler. Your Balance should always be non-negative.

The Balance Sheet
From here, you're done - it's all accounting and bells and whistles. You'll see below the Allocations is the Enchanting Materials Conversion Matrix. It shows the per-cast profitability of each conversion. To the right of the Allocations, you will see a full simple-style drop rate table for prospecting and disenchanting. Should these ever change, the SS can be very easily updated here and the changes will automagically carry throughout the sheet. At the Top-Right, you see the Final Items for Sale, based directly on your allocations. Below that are your costs - ore, settings, golden lotuses (for some transmutes).

If you chose to optimize your enchanting mats, you'll see a section on the right that shows the side-by-side of the original materials you start with and the final materials you end with. Then you have your Final Profit along with the Average Profit Per Stack.

Notes
I did remove the time calculations. I felt they were too susceptible to inaccuracies and not too useful. They took up visual space from more informative aspects of the SS, and so they were dropped.

One of our Ethereal Contributors noted that it's a good idea to clear out your old server pricing info before copying in a new set of data.

That's the long guide - I really hope you enjoy playing with it, and I appreciate you suffering through this wall of text in lieu of a video. Good luck!


Closing Thoughts

As always, I've done my best, within reason to stay active and continually update my Spreadsheets, and I plan to continue to do this. Mithrildar and quietstrm07 have extensively tested and developed this with me, and I feel it is in a good spot with regards to bugs, but inevitably these things can happen, so if you find one or have any other suggestions or comments, please let us know - we usually respond quicker to posts made on The Consortium.

[TheQt]

Pictures attached via the site's attachments system aren't the way to go, upload via another site (imgur/postimage/imageshack/ whatever else) , for it will take made a day for it ot be approved :< But still, thanks for sharing =)

[Unholyshaman]

What a noob :P

This is a pro spreadsheet though, I've been using it for a while and it's made me some awesome amount of gold.

[quietstrm07]

I figure I finally should stop lurking the forums here and actually register for once. I'm another one of the people that have helped create and polish this spreadsheet. I hope you all enjoy it and if you have any questions or advice, feel free to let us know. We're easier to reach on the Consortium, but will try and pop by here to help out as well. (Also, Stede is a nub for not realizing the picture thing, lol)

[Stede]

Pictures attached via the site's attachments system aren't the way to go, upload via another site (imgur/postimage/imageshack/ whatever else) , for it will take made a day for it ot be approved :< But still, thanks for sharing =)

Ah, dang - thanks!

[Unholyshaman]

Just in an attempt to make this spreadsheet a little bit more understandable for now, here is a quick briefing on it. Stede and Co. are planning to release a proper video guide when they stop being lazy :P

After you have imported your prices, switch over to the main Ore-Shuffler page and find the Gem Allocations table in the center of the spreadsheet. I'm going to warn you guys now, this isn't one of those spreadsheets that tell you absolutely everything that you should do.

This spreadsheet is from the big boys in gold making and you actually need to put some effort in if you want the insane profits.

Inside the Gem Allocations table, grey blocks are uneditable, yellow blocks are editable. Make sure everything in the yellow blocks (Column E-I) are set to 0 like so:



You will see a Balance column on the right of the Gem Allocations table, this balance shows you how many of each gems you have to "spend" in your shuffle. If you change the number of stacks of ore you purchase, these numbers will go up and down according to how many of each gem you "should" statistically receive from prospecting it all.

The goal with the Balance column, is to make the numbers reach 0 by "spending" your gems throughout the Gem Allocations table.

Before we continue, set a number of stacks of ore to purchase. I'm using 200 Ghost Iron in this example.

To figure out how to spend your gems in the Gem Allocation table properly, you look up above it to the Gems Analysis table that shows where you get your best bang for your buck.



In this screenshot I can see that the "+Profit 450 Jewelry" is the most valuable for all of the rare gems.
I can also see that Roguestone's +Profit 450 Jewelry are the most valuable too. The rest of the green gems you can either +Profit Cut if you think they will sell, or just do like what most of us do and reduce your profits slightly but increase efficiency drastically by using Profit DE to disenchant them rather.

Now that you know which shuffling route is most profitable for each gem (450 jewelry, profit cut, profit de, or profit craft/vendor), you can start spending them below in the Gem Allocations table.

Go back and find your Balance column to see how many of each you have to spend. If I purchase I have an estimate of 33 of each rare gem so I enter 33 in the 450 Jewelry column with the yellow box for each gem. You will notice that my Balance then becomes 0, meaning I have none left to spend. I do the same for the rest of my green gems, using the Roguestone to craft 450 jewelry but choosing to DE the rest of the greens.

Keep in mind the Gem Analysis and Gem Allocation tables DO NOT line up. Gem Analysis 450 Jewelry column is in H, Gem Allocation 450 Jewelry is in G, etc. +Profit DE Gem Analysis is represented by 384/415 Jewelry in F where it expects you to craft jewelry to DE (and possibly sell the rare procs).

Now my Gem Allocations Table looks like this:



Now you can look over to the right to the Final Costs and Final Profit tables:



16,000g investment with an estimated 20,000g profit.

[Stede]

Just a quick note that I pushed a major revision at the end of yesterday that allows you to zero out your allocations simply by pressing a button. Also, the allocation columns now line up better with the analysis section. I'd like to get a youtube vtutorial vid up sometime in the next week, if I can figure it all out.

This spreadsheet isn't ez-mode. It understands that there's a lot of stuff you can do in the shuffle. The idea is that you buy some ore at a certain price and prospect it, then you have a bunch of uncommon gems, rare gems, and Serpent's Eyes to "spend". This spreadsheet lets you spend them however you want and shows you the profitability of it.

[Unholyshaman]

Also for anyone having a problem with things showing up like this:



Check your security warning:



And then enable macros:

[Darknight48458]

This looks great, and i'll be attempting this over the next 3 days, I will report back with numbers

Thank you for the contribution, will +rep once I see the profit

EDIT: Repped all 3 of ya, thank you for the work! Will give more rep once the profit is seen

[phannes1987]

+repped you guys for your spreadsheet guides!

love the work and the effort for contributing here

[baichii]

5+ Rep inc! Best shuffle sheet yet!

[Stede]

Thanks guys - I made a new revision last night, and then tried to push some bug fixes this morning and everything ****ed up on me. I originally wrote this in Excel 2003, but this morning, I made the fixes in 2010. Then I saw something else i needed to fix, and made that fix in 2003, again. And then it all went to shit.

Like dividing by infinity.

So, the download link is now rolled back to the original version - it's a bit slower, but it works fine. When I get home, I'm going to straighten everything out with the new version and re-upload it. Sorry for the inconvenience.

[quietstrm07]

And don't worry, I'll keep pissing Stede off while testing it by finding dumb little things that seemingly only I care about, lol

[phannes1987]

ok got one problem...when i download your spreadsheet and open it in Excel 2003 it just shows in strange signs...

and with office 2010 it won't let me acitvate those nasty macros and tells me the VBA Macros are damaged or broken -.-

maybe got a solution? ...

[Popocub]

What the hell is +450 Jewelry o.O