Register
  • Stede & Company's MoP Shuffling Spreadsheet

    28-November-2012 - Ran into a major SNAFU when I mixed Excel versions to try to update stuff, so I've rolled everything back to the old, slow version. The new version has some bugs, if it works at all, that I won't be able to fix until later this evening. Really didn't anticipate VBA completely breaking down when up/down converting the file like this.

    Major Update posted on 27-November-2012: I just pushed a revision that now uses a much faster computation method. Recalculation will only bog down when you change the data in the Prices Worksheet, and a message box will usually alert you within 5 seconds when its done. This makes altering your allocations lightning-fast. On top of that, I've added a Reset Allocations button that will zero out all your gem allocations. Lastly, I moved some of the allocations columns around to better line up with the analysis sections above. ((Having soem Tech problems with the upload atm - might be awhile)).


    Well, it's been a long time coming, through a very iterative revision process - but today I'm releasing this for everyone. 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 Contributor 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.

    If you leave rep - be sure to hit @Mithrildar (add rep) and @quietstrm07 (add rep), too.




    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 at the bottom of this post!

    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.

    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!

    One of the more common technical problems I see users having is that all their data is getting copied into one cell. If this happens to you, @Mithrildar has a solution, below:
    Quote Originally Posted by Mithrildar View Post
    I've had this problem too.

    1. Select the whole column the data is in.
    2. Go to the 5th tab in Excel, should be called something like "Data"
    3. Click on "Text to Columns"
    4. Select Seperated and click on Next
    5. Now select comma
    DONE!

    (if there still is a problem you might have to switch the comma and dot around, US use comma for 1000s and dots for decimals, but like here in the NL we use it the other way around, this can be done at the end of the text to columns menu).

    Also, from Silhantcat, one of our Ethereal Contributors:
    Quote Originally Posted by silhantcat View Post
    Something I noticed too, is that when updating manually, I need to clear the prices from the prices tab (ctrl+a and Delete) before pasting the new prices to remove the existing data as sometimes I have old data (eg from US-Illidian) when there are less rows in the new data.


    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 lets us know.



    @j311yf1sh 's Guide To Automatic Updates

    Quote Originally Posted by j311yf1sh View Post
    Automatically updating Stedes Spreadsheet with Wowuction and Python


    Required:
    Python 3.2
    Python will be used to create a program which will perform one part of the automation process.
    You will need to download the Python 3.2 Installer here:
    http://www.python.org/download/releases/3.2/

    Wowuction account
    Just going to go out on a limb and say you have one.

    Windows 7 task scheduler
    If you wish to update the file at certain times.

    My python script
    Go down to the raw paste down bottom.
    highlight it all and copy it.
    create a new text file.
    paste the information in it
    Save the text file as "Whateveryouwant.py"
    http://pastebin.com/Dnr7Nupu

    Method:

    Step 1: Login to wowuction and go to your data location and http://imgur.com/2EHCk

    Step 2: Add the copied link into the first location inside of the python file we created.(also follow instructions inside if needed)

    Step 3: Save that file and run it.

    Step 4: Open the spreadsheet that stede has created from here on we will be working inside of that.

    Inside of spreadsheet:
    Inside of the spreadsheet go to the prices tab and data tab at the top of excel.
    Click the top left cell in excel.
    Up top click the "from text" button under Get external data.



    Locate your file that downloaded from running the python script and click import.

    On the first page check the box "Delimited" and click next



    On the second page uncheck "tab" and check "Comma" and as you guessed click next.



    On the third page, Click finish.

    Go to properties on the page that comes up and uncheck "Prompt file on refresh" This will make sure it downloads the same file that you first linked it to. Also if you would like it to update everytime you open the spreadsheet then click on "refresh data when opening the file".



    Click on ok, then make sure in the box you have "=$A$1" and click ok.

    Now When ever you open the spreadsheet it will import the data from your file from Wowuction.

    Automatically run the script with Windows Task scheduler

    If you would like for windows 7 to automatically run the python script then please follow
    http://windows.microsoft.com/en-US/w...chedule-a-task

    If any other help is needed please feel free to quote me.

    References:
    http://stormspire.net/official-forum...magically.html

    Cheers j311yf1sh