It's been a couple months since I took this Spreadsheet down. I hope you'll forgive the long absence, but Mithrildar and I have some new goodies inside that should make up for it. There were, of course, various bug fixes here and there, but the biggest feature we've added is the ability to import your realm's wowuction data simply by clicking a button in the Spreadsheet. No more copying and pasting bs. Just don't get too trigger happy on the import button or WoWuction will throttle you and you'll have to wait.
Also, thanks to Mithrildar and all the help he provided, we've now solved the issues users were experiencing with EU-localized versions of Excel. No more jumping through hoops guys - if you have Excel 2010, all you have to do is open the file. Oh, and the last bonus is a quick tutorial I did using bandicam and Youtube:
New Youtube Tutorial
If you want to leave rep, be sure to hit Mithrildar, as well as myself - again, without his help, we would not have been able to support all the EU realms (Russian realms are not supported - you may find the english name of a russian realm listed and can use that, but I did not go out of my way to support russian realms).
You Will Need
#1 - Microsoft Excel with Macros Enabled (I can no longer confirm that the SS works in Excel 2003. I use 2010 to create and edit it, but it should be fine with 2007+)
#2 - Your WoWuction static Download token. Click the link to go to WoWuction, and log in and choose a server - any server. You will find your token as illustrated in the highlight portion of the screenshot below:
#3 - The Spreadsheet
It's Excel only guys - I highly recommend using Excel 2010 or higher. Java is still out there in the wind somewhere and webapp is an idea, but this is it for now.
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 enter your WoWuction static download token in the top right
#2 - Set your options, your ore prices (if you'd like), and the # of stacks you're going to shuffle
#3 - Select your realm and click the Import button
#4 - ZERO OUT YOUR ALLOCATIONS - there's a button that with do it for you with a single click. Now, Allocate your gems
#4 - Click the Calculate button to account for your allocations
#5 - Profit!
* Uses the latest information from your Auction House with an Hourly Resolution
* Single button import of data from all realms (except russian)
* 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 that accounts for the difference in the rate of sales of each gem to ensure that your profit per stack is actionable
* Option to only sell cuts that are at a higher price than uncuts added.
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. The first thing you need to do is grab your WoWuction download token and enter it in the yellow cell in the top right of the shuffler. The 2nd screenshot in this post shows you where to get your token. Then select your server and faction from the dropdown boxes and click the import button. Give it about 15-30 seconds to download your data - crack a cold one; take a sip - ah, your data is ready now, sir / madam - refreshing, no? Let's continue!
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.
@<a href="http://stormspire.net/member.php?u=5304" target="_blank">Mithrildar</a> 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.
The newest option is the ability to choose to not make or sell any cuts that are currently below the value of an uncut gem. Oftentimes, shufflers want to move as much inventory as they can, so all cuts are sold, regardless. Other times, if you're not having trouble with sorting though backstock, you might consider this option to boost your profits a bit.
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.
Moving on - Below and to the left, you see the server prices. Here, we carry forward the old tradition of "Only Mess with Yellow Cells". The price of ore is pre-populated with the market value of ore when you import new data. You can enter in your own threshold price, if you like. 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.
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. You can zero out your allocations easily by clicking the button on the left side of the shuffler. Once you have your allocations set, click the Calculate! button on the upper-left of the sheet.
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.
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.
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!
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. Testing has been limited, but 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 lets us know.