Register
Welcome to the Stormspire.net WoW Gold Forums!

Greetings, guest! Welcome to Stormspire.net, your #1 resource for WoW and Diablo 3 Gold, Professions, and everything in between!


Enjoy what the site offers? Consider Premium Membership if you wish to get the most out of the site. Premium Membership benefits include the acclaimed Goblineer's World of Warcraft Gold Guide, private forum access, a complimentary high-quality avatar, and much more!


If this is your first visit, make sure to check out the Stormspire.net Survival Guide for a quick site orientation. You may also register to unlock all standard forum features, and to get rid of these lovely ads!


We proudly host the Official forums for TradeSkillMaster, The Undermine Journal, and WoWuction.

  • The Shuffling Spreadsheet That Killed Them All

    Folks, today is the day that you destroy all your Cataclysm shuffling spreadsheets.

    Up in the Stormspire, we've been hard at work producing a spreadsheet that we hope will be the final solution to ore shuffling. I'm happy to release it to the public today. A special thank you to Stede from Venture, LLT for extensively testing the spreadsheet (+rep clicky) and implementing several features. Other notable mentions include RavenC, Drrwho and Euripides. Take note, if you'd like to see improvements or features added to the spreadsheet, I'm listening!

    >> Download link, for the latest version of excel. <<
    >> Download link, for silly willies using excel 2003. <<
    >> Google Docs link, so that you don't get fired for downloading stuff. << (kinda messy, loss of formatting)


    I won't dwell too much on the shuffle itself. Truth is, it has been blogged about on multiple occasions. [Carnelian Spikes] DE ratios are still not 100% determined, so you can modify those if you want. The general idea of the shuffle is illustrated below:


    Spreadsheet features
    • Finds total revenue, total profit, profit per stack, profit per ore type based on user input.
    • Displays prospecting results, disenchanting results, and localized number of crafts.
    • Does the above for both "Quick Sale" and "Maximized Profit". The former being auctioning all prospected gems, and the latter being optimal.
    • Gives a complete breakdown of time allocation for both methods.
    • Attempts to find GPH (gold per hour) for both methods. Also finds relative GPH based on profit differential.
    • Shows the cost to produce a [Shadowspirit Diamond] based solely on the cost of ore (Stede). Standalone feature.
    • Adjusts max value for each gem through [Alicite Pendant], [Jasper Ring], [Carnelian Spikes], [Hessonite Band] and [Nightstone Choker]. Also considers [Shadowspirit Diamond] transmute.
    • Allows the user to input +profit % for transmute mastery, enchanting scrolls, and gem cutting.
    • The spreadsheet does not consider rare gem transmutes as I don't see this being profitable any time soon.

    Future developments:
    • Allow the user to manually input shuffling breakdown. Handy for high-volume shuffling where markets may become flooded.

    Basic Instructions

    1. Fill out the beige boxes with your server prices. Input the bonus you predict from cutting rares, and from selling scrolls (if applicable). Use 100% if you foresee no bonus.
    2. Look under Profit - Quick Sale and Profit - Maximized. The numbers in bold are what you're looking for.
    3. Gold Per Hour - Quick Sale and Gold Per Hour - Maximized shows your earning efficiency.

    Notes about the spreadsheet
    • Max shows the best value obtained for any given gem, whether it's vendored, auctioned raw, or cut and disenchanted. Increase % is a percentage figure for the server price -> Max amount. Abs is the absolute value for this.
    • Shadowspirit Diamonds are not taken into account for max value calculations. They are very illiquid and though they may provide extra profit, it's impossible to move every diamond you transmute.
    • Time breakdown:
      • Search & purchase: 20 seconds, plus 1 second per stack of ore purchased.
      • Loot from mailbox: 10 seconds, plus 0.5 seconds per stack purchased, plus 20 seconds per mailbox refresh.
      • Prospect stacks: 2.5 seconds per prospect.
      • Cut gems & vendor greens: 6 seconds per rare, 4 seconds per uncommon, 0.25 seconds per vendored green.
      • Craft jewelry: 4 seconds per craft.
      • DE jewelry: 4 seconds per DE.
      • Post auctions: 0.5 seconds per auction. Dust posted in stacks of 10, Essences in stacks of 5, uncut gems in stacks of 3
      • Loot mailbox: 10 seconds, plus 0.5 seconds per item, plus 20 seconds per refresh.
      • Repost: Assumes 25% of auctions don't sell and need reposting.
      • Overhead (20%): Adds a flat 20% increase to the entire shuffle for miscellaneous operations.

    Future versions will be posted and updated here. Again, if you find a bug, let me know and I'll squash it. Feature requests and recommendations and most certainly welcome too!

    Sterling beaming out!
    This article was originally published in forum thread: The Shuffling Spreadsheet That Killed Them All started by Sterling View original post
    Comments 380 Comments
    1. turbare's Avatar
      turbare -
      Let me be the first to thank you for this, this will be infinitely useful
    1. Unregistered's Avatar
      Unregistered -
      amount column should say 1.
    1. Z-Man's Avatar
      Z-Man -
      On my server, Solid Ocean Sapphires are still above 65g at times, so I've been transmuting Zephyrites quite regularly to keep up with the volume. My cost is nominally 45g*, so it's a bit better than vendoring my excess gems.

      * That's 27g lost vendor sales for the gems plus 18g for herbs.
    1. Unregistered's Avatar
      Unregistered -
      Could you add add a section to factor in the fees associated with the auction house? They should make a pretty big difference when you factor in posting fees and the AH cut off sale.
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by Unregistered View Post
      amount column should say 1.
      I'm not sure what column you're referring to, but the columns that read 1.5 are correct (later corrected, thanks unregistered!).

      Quote Originally Posted by Z-Man View Post
      On my server, Solid Ocean Sapphires are still above 65g at times, so I've been transmuting Zephyrites quite regularly to keep up with the volume. My cost is nominally 45g*, so it's a bit better than vendoring my excess gems.

      * That's 27g lost vendor sales for the gems plus 18g for herbs.
      Hmm, perhaps I should implement those transmutes after all. The sheet was built with that future implementation in mind anyway, so it shouldn't be too hard.

      Quote Originally Posted by Unregistered View Post
      Could you add add a section to factor in the fees associated with the auction house? They should make a pretty big difference when you factor in posting fees and the AH cut off sale.
      The sheet already does this.
    1. Namssob's Avatar
      Namssob -
      WOW - I love it! The amazing thing for me is realizing the GPH difference between the quick method and Maximized. The GPH in Quick is 10k, GPH Maximized is 7k. I always thought of myself as a GPH freak, calculating everything that way, and this is one I missed because I've been using the Maximized method for so long.

      THANK YOU!!
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by dumpdump View Post
      Am I correct that this sheet assumes that cut gems will never sell for less than uncut ones and even assumes a whopping 20% increase in revenue by cutting? This is certainly not the case on my server, where cut rares often sell for less than the uncut ones
      You can adjust the cut revenue (as per spreadsheet instructions). On my server, 30% would be a more accurate figure.
    1. dumpdump's Avatar
      dumpdump -
      ah thanks, just found the parameter indeed. Wanted to remove my post, but you already made a response Thumbs up for reaction speed!
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by Unregistered View Post
      Any way to set the cut values per rare gem type? They vary wildly on my server.
      I could add this, but honestly prices are all over the place and you can just assume an average.

      Quote Originally Posted by Unregistered View Post
      Is the elementium and obsidum percentages reversed? I thought you got more gems from elementium but id this was the result of testing then great.

      Also, thanks for all the hard work. This is nice.
      I'm using wowhead data. You can double check it if you'd like, but obsidium yields more green gems than any ore, and less blues.
    1. diophan's Avatar
      diophan -
      Your hessonite band recommendation formula got shifted by one. I believe the formula should be =IF(W15>0,"Vendor",IF(X15>0,"DE","Sell Raw"))"\ rather than =IF(X15>0,"Vendor",IF(Y15>0,"DE","Sell Raw")). Thanks for all the work!

      It does seem like there's a decent discrepancy between what your spreadsheet and flyinfungi's says for the value of pyrite ore. Any idea if I'm just imagining this or what the cause it?
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by diophan View Post
      Your hessonite band recommendation formula got shifted by one. I believe the formula should be =IF(W15>0,"Vendor",IF(X15>0,"DE","Sell Raw"))"\ rather than =IF(X15>0,"Vendor",IF(Y15>0,"DE","Sell Raw")). Thanks for all the work!

      It does seem like there's a decent discrepancy between what your spreadsheet and flyinfungi's says for the value of pyrite ore. Any idea if I'm just imagining this or what the cause it?
      You are correct about the formula, that should be fixed in the next release.

      I just tested pyrite ore on both sheets, both returned very similar results. Remember, mine takes into account a 5% AH cut.
    1. kenshunter's Avatar
      kenshunter -
      What would take this from awesome to "must have" is if you could download the prices from one's particular server (similar to what what UMJ used to do for all servers) just like one downloads stats from wowarmory into spreadsheets... simply list low price, high price, median price, and average price; as a default use average price for your calculations and allow the user to edit from there....
    1. Slaghed's Avatar
      Slaghed -
      Holy crap, thanks a ton for this, it is awesome.
    1. Malhavoc's Avatar
      Malhavoc -
      How can it recomment Find Hessonite Value - Hessonite Band to vendor, gems are 16g at ah vendor for 5g ? i dont get it
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by Malhavoc View Post
      How can it recomment Find Hessonite Value - Hessonite Band to vendor, gems are 16g at ah vendor for 5g ? i dont get it
      Please read the whole thread before commenting.

      The spreadsheet never recommends to vendor jewelry. The typo you see is a single cell that was mistyped as discussed 2 posts above. Otherwise the entire spreadsheet is probably telling you to craft, DE + AH the gems.
    1. Unregistered's Avatar
      Unregistered -
      Quote Originally Posted by Sterling View Post
      I'm not sure what column you're referring to, but the columns that read 1.5 are correct.
      As I understand you are modelling Alicite prospecting 18% of time for 1-2 gems (using elementium as example).

      Based on my prospects it appears that it is actually prospecting 12% of the time producing 1-2 gems giving an overall 18% drop rate. I am getting on average only 67% of the total gems your spreadsheet is indicating from Elementium prospects
    1. Unregistered's Avatar
      Unregistered -
      couple of things i noticed
      for example if you input 1 stack of obsidium you supposedly average almost 8 total uncommon gems from the prospects. Those numbers are way off. it should be about 6 gems. so you need to either use a 1 multiplier instead of 1.5 or something like 100/6 (16-18%) instead of 24% with the 1.5 multiplier.

      i'm not familiar enough with elementium right now but i'm pretty user you want to change that 1.5 multiplier to 1 as well. That will take you from 6.5 uncommon gems per stack of ore to 4.3


      It would be great if someone that has been keeping track of this stuff can chime in. I've gone through at least 3000 stacks of ore this xpansion but haven't really taken notes on numbers.
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by kenshunter View Post
      What would take this from awesome to "must have" is if you could download the prices from one's particular server (similar to what what UMJ used to do for all servers) just like one downloads stats from wowarmory into spreadsheets... simply list low price, high price, median price, and average price; as a default use average price for your calculations and allow the user to edit from there....
      I'll try and implement this in the future... might need some help though.

      Quote Originally Posted by Unregistered View Post
      As I understand you are modelling Alicite prospecting 18% of time for 1-2 gems (using elementium as example).

      Based on my prospects it appears that it is actually prospecting 12% of the time producing 1-2 gems giving an overall 18% drop rate. I am getting on average only 67% of the total gems your spreadsheet is indicating from Elementium prospects
      Quote Originally Posted by Unregistered View Post
      couple of things i noticed
      for example if you input 1 stack of obsidium you supposedly average almost 8 total uncommon gems from the prospects. Those numbers are way off. it should be about 6 gems. so you need to either use a 1 multiplier instead of 1.5 or something like 100/6 (16-18%) instead of 24% with the 1.5 multiplier.

      i'm not familiar enough with elementium right now but i'm pretty user you want to change that 1.5 multiplier to 1 as well. That will take you from 6.5 uncommon gems per stack of ore to 4.3

      It would be great if someone that has been keeping track of this stuff can chime in. I've gone through at least 3000 stacks of ore this xpansion but haven't really taken notes on numbers.
      The drop ratios were taken from wowhead... it takes a large sample size to understand the correct ratios, but if you are correct I'll fix the numbers. This will show much lower GPH... what a shame.
    1. Unregistered's Avatar
      Unregistered -
      i wish you were right but i just think that we're estimating a bit high.

      im about to do 50ish stacks of obsidium, ill post my numbers in here in a bit.

      ok 1740 obsidium ore, which is 87 stacks or 348 prospects


      This is what I got. We'll ignore the blue quality stuff right now since the percentage on those is low and can seem unstable sometimes.


      Gem Number
      Carnelian 87
      Hessonite 86
      Nightstone 93
      Zephyrite 86
      Alicite 80
      Jasper 89

      which is a total of 521 gems.

      which gives us 5.988 green gems perstack of obsidium ore. Pretty safe to say that this then is supposed to be 6 gems a stack or 1.5 per prospect.



      The blues for what it's worth were

      Demonseye 5
      Ember Topaz 0
      Amberjewel 2
      Dream Emerald 6
      Inferno Ruby 7
      Ocean Sapphire 4

      for a total of 24 gems.

      which gave me .275 gems per a stack of ore or about .06 or .07 per prospect for a blue gem. Again these blues are volitile and I would not use my numbers to determine much unless we combined my stats with several others.

      But for the green quality stuff that sample size is more than enough to show you that your sheet is estimating high.


      we should not worry about color as we can always assume that blizzard will make all the colors drop randomly at a 1/6 chance and no one color has a built in advantage.
    1. Z-Man's Avatar
      Z-Man -
      I also think the numbers are high. I'm seeing closer to 6 uncommons from Obsium and 4.4 uncommons from Elementium. Obsidium rares are also near 7.5% as mentioned above. I'm prospecting a large batch today and will post numbers soon.