>> 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)
Sapu's awesome auto-update mod:
Originally Posted by Sapu94
I figured this out with @AzTweeter on IRC just now...you can set excel to automatically refresh the TUJ data every X minutes or every time you open the spreadsheet. I am using excel 2007. No clue how to do this in 2003 but it should be roughly the same in 2010 at least. Here's how:
Open up the spreadsheet and click on the "Data" tab at the top. If you haven't already, click on "From Web" (2nd from left in 2007) and then type in your TUJ URL as described in the original post into the "Address" box and let it import (as described in the original post).
In the Data tab click on "Connections". The TUJ one should be called "Connection". Click on it and then click on "Properties"
Click on the "Definition" tab and make sure it's the correct connection (check the "Connection string").
Click back on the "Usage" tab and check the first checkbox ("Enable background refresh") and the 2nd or 3rd according to what you want. Remember that TUJ only allows you 5 updates per 24 hours so set your time interval accordingly if you choose that option.
Let me know if you have any questions / issues with this.
<SNIP> ppl know about how the shuffle works from the complaints in thread, so im snipping the explanation to get to the Spreadsheet info <SNIP>
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 "XML API". Scroll to the bottom where it says "Market Data XML". Paste this address into your browser and download the XML file to your desktop.
3. Switch back to the spreadsheet and go under the "TUJ data" tab. With the top left cell selected, go under "Data" -> "Import XML". Select the file you saved on your desktop. This step will differ based on the version of excel you are using. All of the pricing should now be filled in.
4. 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.
5. Look underProfit - Estimated. These numbers show the profit/losses at your server's prices.
6. Gold Per Hour shows your earning efficiency. I consider 3000 GPH a safe bet.
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!