Thread: Consortium Cataclysm Shuffling Spreadsheet v4.5

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:

The Shuffle in a Nutshell

You purchase Obsidium Ore, Elementium Ore, and Pyrite Ore. You can one of two routes here (or a combination of): Heavenly Shards or Dust/Essences.

Heavenly Shards: Obsidium and Elementium are smelted and Obsidium Bars and made into Folded Obsidium. You use the Folded Obsidium and Elementium Bars to create Stormforged Shoulders. You disenchant these and end up with Heavenly Shards. You can either use the shards for enchanting scrolls, or sell them raw at the AH. This method is generally a tad less profitable BUT its cycle time is very short. You can AFK most of it.

Dust/Essences: Ores are prospected and you're left with rare and uncommon gems. The rare gems are cut and sold at the AH. Jasper, Nightstone, Carnelian, Alicite and Hessonite are made into Jewelry: Jasper Rings, Nightstone Chokers, Carnelian Spikes, Alicite Pendants and Hessonite Bands. 10% of the time, you will get rare quality Jewelry that can be sold at the AH. Rare Hessonite Bands can be disenchanted into Heavenly Shards. Zephyrite is vendored or sold when the Jewelcrafting daily requires it. The remaining Jewelry is disenchanted into Celestial Essence and Hypnotic Dust. From here, you can either sell everything, or keep branching off into enchanting scrolls and Embersilk Bags (to get rid of all that excess Hypnotic Dust).

If you're doing this on a large scale, you will need to diversify to avoid flooding markets. Uncommon gems can be transmuted into rare gems, and into Shadowspirit Diamonds. Pyrite Ore stocks can be part prospected, and part smelted and made into belt buckles (along with some smelted Elementium).

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.
• Gives a complete breakdown of time allocation for both methods.
• Attempts to find GPH (gold per hour) for a basic prospect, cut, DE and sell cycle.
• 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, Bountiful Bags, and gem cutting.
• Provides a recommendation for gem transmutes, Embersilk Bag crafting, and Heavenly Shard crafting/disenchanting.

Future developments:

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

Basic Instructions

1. You can fill out the beige boxes with your server prices manually, or have it imported automatically. To have it done automatically, go to The Undermine Journal and create an account. The site uses OpenID, so don't worry about getting hacked or whatever.
2. Click on your server, and under "Site" click Market API.
3. Copy and paste the CSV link into your browser.
4. Open the file in excel, and copy paste its contents in the uppermost left corner of the TUJ tab.
5. Fill out the number of stacks you've purchased, or wish to purchase. and input the bonus you predict from selling scrolls (if applicable). Use 100% if you foresee no bonus. Select "Yes" or "No" for Bountiful Bags and Transmute Bonus.
6. Look underProfit - Estimated. These numbers show the profit/losses at your server's prices.
7. Gold Per Hour shows your earning efficiency. I consider 3000 GPH a safe bet.

Troubleshooting

If your CSV data appears to aggregate into 1 column, see these posts: Fix 1, Fix 2.

Notes about the spreadsheet

• Max shows the best value obtained for any given gem, whether it's vendored, auctioned raw, or cut and disenchanted.
• 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!

Originally Posted by Unregistered

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!).

Originally Posted by 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.

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.

Originally Posted by 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.

The sheet already does this.

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!!

Originally Posted by dumpdump

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.

Originally Posted by Unregistered

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.

Originally Posted by Unregistered

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.