Register
  • Consortium Cataclysm Shuffling Spreadsheet v4.5 Released

    Hey folks,

    I just made several improvements to our shuffling spreadsheet. As usual, you can download the 2007 and 2003 versions from the centralized download page. I'd like to thanks @Simca, @calif94577, @Kiken and many others for the help!

    Improvements:
    • The spreadsheet now makes a quick comparison of smelting profits.
    • Rare gem values were out of whack. Previously, the spreadsheet would take the market price value for rare gems, and multiply them by a user-defined "cut bonus". This often resulted in massively inflated/deflated gem prices. The spreadsheet now takes an average of the most popular cuts available.
    • @Simca's changes are also included in this version, as seen here.
    • Several bugs have been fixed:
      • Item price cells now point to the correct TUJ Data cell, this was broken by a TUJ XML update.
      • Selecting "No" to bonuses no longer breaks the spreadsheet (thanks @Kiken).
      • Several disenchant ratios have been corrected.
      • Profit/ore in cell S57 and S58 have been corrected (thanks @calif94577).

    Still to do:
    • Milling spreadsheet needs a huge overhaul.
    • Find more accurate values for enchant bonus by examining enchanting scroll values (big job, I know).
    • Merge some redundant sections of the spreadsheet.
    • Provide GPH for things like Heavenly Shards, Smelting, and perhaps even a full-blown, step-by-step optimal shuffle (another big job).
    • Find what % of blue jewelry sells well. Assume DE for pieces that don't sell.

    As usual I'm open to suggestions, keep them coming!
    This article was originally published in forum thread: Article: Consortium Cataclysm Shuffling Spreadsheet Updated started by Sterling View original post
    Comments 226 Comments
    1. Seoulseeker's Avatar
      Seoulseeker -
      I have to say that I do love the spreadsheet! Orecrusher was nice when the shuffle was simpler, but just doesn't cut it now.

      I do seem to be missing several cuts under the Rare Gem Analysis; did I miss something in the thread or is this a mistake, feature?
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by Seoulseeker View Post
      I have to say that I do love the spreadsheet! Orecrusher was nice when the shuffle was simpler, but just doesn't cut it now.

      I do seem to be missing several cuts under the Rare Gem Analysis; did I miss something in the thread or is this a mistake, feature?
      I tried to average out the cuts you would realistically sell, ignoring other cuts as they would just skew the average.
    1. Seoulseeker's Avatar
      Seoulseeker -
      Quote Originally Posted by Sterling View Post
      I tried to average out the cuts you would realistically sell, ignoring other cuts as they would just skew the average.
      Got it. Any insights on how the non-realistic cuts were identified?
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by Seoulseeker View Post
      Got it. Any insights on how the non-realistic cuts were identified?
      Wowpopular and personal experience.

      I'm open to suggestions though!
    1. wyze's Avatar
      wyze -
      What about for the people that farm the ore instead of buying it? What do you think the best way to implement something like that would be? I have started on it, but I think what I have is most likely wrong. Then I was going to factor that into the GPH formula and I just got lost. Lol. Would this be a good feature or not?
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by wyze View Post
      What about for the people that farm the ore instead of buying it? What do you think the best way to implement something like that would be? I have started on it, but I think what I have is most likely wrong. Then I was going to factor that into the GPH formula and I just got lost. Lol. Would this be a good feature or not?
      I don't think it would be a good feature. Farming is never better in terms of GPH. When there's a comparison to be made, it must at least compete with the other alternatives. Let me demonstrate.

      Suppose you buy 100 stacks of Obsidium and plug that into the spreadsheet. It spits out the total cost, profit and time. For me, ore is at 58.8g/stack, so the total cost would be 5883g, profit 13250g and time 74.5 minutes (for a GPH of ~10,670). Let's assume you can farm 25 stacks an hour (I haven't farmed anything in a long, long time).

      Total time becomes 4 hours + 74.5minutes = 5h, 14.5min = 5.2416hr
      Total profit = 19,133g (since you didn't have to buy the ore)

      GPH = 19,133g/5.2416hr = 3650

      As you can see, farming ore is a complete waste of time and only kills your GPH.
    1. wyze's Avatar
      wyze -
      That makes total sense, which is why I just wanted to throw it on the table and get your thoughts. I guess a good use for this would be only if someone was starting out with very little gold and farming would be a way to kick start them into the shuffle of buying the ore.
    1. Seoulseeker's Avatar
      Seoulseeker -
      Quote Originally Posted by Sterling View Post
      Wowpopular and personal experience.

      I'm open to suggestions though!
      That's good enough for me!
    1. calif94577's Avatar
      calif94577 -
      As Sterling said its not very profitable but lets say that's just what you do, you mine your own ore. I believe all you would have to do is change the price of ore per stack to 0. If you use TUJ data then you would go to F5-7 before putting in the TUJ data and delete the values already inputted there changing it to 0 at which point it wont pull from the TUJ data sheet and read what you have imputed which would be 0. I don't know if it would break any other calculations in the sheet as we doing want to try to divide by 0 and have your computer catch fire and be sucked in to a wormhole lol. So attempt at your own risk

      TLDR: Change values F5 F6 and F7 to 0 before inputting TUJ data. Do at own risk

      EDIT: so I when I went to try it i relized I didn't think about the GPH feature which would be inaccurate. So what I figured was either ignore it or in Q37 at the end of the formula type +"number of min spent farming" dont put the quotes or that sentence, just put the value so example, you spent 2.5 hrs farming you would add to the end of the formula +150

      Another option to make it simpler in the future is to expand the table and add to it. In S27 (or any unused cell) type "Time spend farming" then in T27 (or what ever cell is to the right of the first one as long as it is not currently used by the spreadsheet) type in the time that you spent farming in min, this is the cell that you would continuously update instead of constantly messing with the formula, and now to make the whole thing work, change Q37 to =SUM(Q27:Q36)/60+T27 (replace the T27 for what ever cell you decided to use to input your value if you decided to put it some where else.)

      And that should keep the GPH and profit accurate

      EDIT2: Lastly under misc parameters its possible to put in the 43 row a conditional for farming. Then changing the value of the ore to something like =IF(F43="Yes",0,insert original formula here) don't know how well it would work or if you would have to modify the formula I just typed as I'm not as proficient with creating spreadsheets like this, I can completely understand them but creating them is a bit different. lol And then adding the second option I typed above about creating its own cell for farming. Now I don't know how we could also tie in if someone selects No on farming if it could lock or change the background color from yellow to gray on the Farming time cell. That's beyond me lol
    1. ErolVingian's Avatar
      ErolVingian -
      Quote Originally Posted by calif94577 View Post
      As Sterling said its not very profitable but lets say that's just what you do, you mine your own ore. I believe all you would have to do is change the price of ore per stack to 0.
      Does not compute.

      Anything you farm hasn't cost you any physical gold BUT it has cost you the chance to sell the raw mats at market prices.

      If we farm 25 stacks of Obsidium ore then we can either sell the ore on the AH which nets us say 40g stack total profit 1000g. Or we can make the ore into stuff and then sell the stuff that we make.

      That ore has still cost you 40g a stack whether it's money you had to spend to get the ore or money you lost by not selling the ore the actual gold cost at the end of the day is identical.*

      [It's not *exactly* identical as when you buy you restrict the supply, even if only by a little, which will in some way affect the price. When you sell you increase the supply, which will also slightly change the price, but for the sake of argument they are close enough to the same.]

      Or the short version "Time is Money Friend."

      It's only a good idea to farm when the gph from farming exceeds what you'll make from other methods, or you actually prefer farming, or you don't have any/enough gold to do much else.
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by ErolVingian View Post
      Does not compute.

      Anything you farm hasn't cost you any physical gold BUT it has cost you the chance to sell the raw mats at market prices.
      Seems to compute just fine here. Zeroing out the price accomplishes just what you described.
    1. calif94577's Avatar
      calif94577 -
      Never said time wasn't money or the ore he got was not worth anything, but It didn't take an upfront cost besides time, in which i offered a solution to compute that. So yes the number should be 0 because we are talking about what the profit is, you didnt pay anything for that ore. If you end up selling it at 40g then you will be making a profit of 40g if you put in to the spread sheet that it cost you 40g cuz that is the going rate then you made 0 profit, in which case that is incorrect. So yes putting 0 in the price would fix that.
    1. calif94577's Avatar
      calif94577 -
      Question: Why is W13 =IF(G15=5,V13,0) I get the 0 and the reference to V13 but why the limit to, if G13=5? Same with W14 thru W18 they all say =5??? Its trying to figure out if cut&vendor the common stones are worth cutting and vendoring vs making jewelry & DEing. Is G15 supposed to say =MAX(5,F15,I73) vs =MAX(0.75,F15,I73)? Or is W13 supposed to say =IF(G15=0.75,V13,0)? Also I don't really use the GPH graph but where is the Transmute times? Its not under time allocated or estimate charts.
    1. ErolVingian's Avatar
      ErolVingian -
      Quote Originally Posted by calif94577 View Post
      So yes putting 0 in the price would fix that.
      doh.

      I should have read the entire post before replying.
    1. Sterling's Avatar
      Sterling -
      Quote Originally Posted by calif94577 View Post
      Question: Why is W13 =IF(G15=5,V13,0) I get the 0 and the reference to V13 but why the limit to, if G13=5? Same with W14 thru W18 they all say =5??? Its trying to figure out if cut&vendor the common stones are worth cutting and vendoring vs making jewelry & DEing. Is G15 supposed to say =MAX(5,F15,I73) vs =MAX(0.75,F15,I73)? Or is W13 supposed to say =IF(G15=0.75,V13,0)? Also I don't really use the GPH graph but where is the Transmute times? Its not under time allocated or estimate charts.
      You are correct, nice find.

      5 should be changed to 0.75 to reflect new uncommon gem pricing.

      The spreadsheet won't account for transmutes, smelting, embersilk bags, or stormforged shoulder shuffles. Maybe one day .
    1. calif94577's Avatar
      calif94577 -
      Quote Originally Posted by Sterling View Post
      You are correct, nice find.

      5 should be changed to 0.75 to reflect new uncommon gem pricing.

      The spreadsheet won't account for transmutes, smelting, embersilk bags, or stormforged shoulder shuffles. Maybe one day .
      cool thanks!!
    1. brandonh6k's Avatar
      brandonh6k -
      I was playing around with v4.5 of the Shuffler and added in some tweaks to the Milling tab. Mainly just tweaking it to use the TUJ numbers for herb, DMC cards, Blackfallow Ink and Volatile Life (cause I'm really lazy and I hate looking up numbers). I decided to see if an Inferno Only strategy would be more viable than the glyph cycle (this is mainly due to my llimited play time - I don't have time to stay on top of the cutthroat glyph market on my server), so I added in some more formulas around converting all the Blackfallow Inks to Inferno Inks at the ink vendor and just making DMCs out of everything.

      Seems like that's a more profitable cycle than either of the cycles listed (provided your server will buy the DMCs, of course). Maybe I'm just missing something though (or I've totally horked up the formulas, which is definitely within the bounds of possibility). Of course, doing just the DMCs would force you to invest a lot more into the cycle to average everything out and you'll fall whim to the RNG gods when they give you 6 200g cards in a row.

      Anyhow, screenies attached (sorry about the size). Shoot me an email if you want a copy of this version of the spreadsheet.

      -Brandon



    1. Nilaus's Avatar
      Nilaus -
      I feel sorry for you @Sterling... Now I started looking at the Excel sheet so now you will probably receive an endless stream of questions and feature requests for the shuffler.

      First of all, this is a monumental work and this deserves +rep more than anything else on this site

      I have a few questions:
      1. In the (very nice) diagram of the shuffle there seem to be missing a few arrows: From uncommon gems to rare gems (Carnelian, Hessonite and Nightstone primarily). From enchanting mats to gold. But the spreadsheet seems to take this into account, so it is purely cosmetic.
      2. I can't find any way to analyse what the GPH of Inscription would be if all BFI is exchanged to II for DMC exclusively.
      3. Also in the Inscription part you are assuming that 8 cards = 1 deck. That is a bit optimistic in my opinion. We discussed it on IRC a couple of days ago and some people estimated 10-12 cards/deck to account for all the doubles. Some of the doubles can be sold easily at 1/8 of a deck price, but not all.
      4. Inscription again. The "Create glyph" section includes a 5% AH cut of Glyphs and Cards, but "No glyphs" section doesn't take the same 5% cut from Cards. Fields J17 and J23 respectively
      5. Where are the MFC option? I can simulate it by setting the price of BFI as MFC-Resilient Parchment, but it still would be a nice option.

      EDIT: Looks like point 2 was the part @brandonh6kadded to his version.

      EDIT2:
      I just tried the prospecting part of the spreadsheet and I really don't like the fact that it requires TUJ data in order to get any information about GPH
      Example path: GPH (Q38) uses Revenue (P22) uses Max server price (G9) uses Possible max server prices (H46) uses Average server price per cut (N76) uses TUJ data (N72)
      I think the problem is using H46 in my opinion it should use TUJ if avialable and if not then use a parallel user input value.
    1. chelmo's Avatar
      chelmo -
      does anybody know if there is a way to import data from wowTrader (eu-Tuj)? would be encredibly helpfull!
      great sheet btw, spares lots of time for me! thx

      greetings
    1. Nilaus's Avatar
      Nilaus -
      TUJ is working on EU realms now (English only I think)