Tagged: spreadsheet

My good buddy Rexor has been telling me about this fantastic way to flip cheap, Mists of Pandaria-level Enchanting materials for nice profits in World of Warcraft. I understood his fascination with this scheme: taking advantage of players that don’t understand how Enchanting materials — or the profession, in general — work in the new expansion. If you’re making gold, the two best markets to be in are selling items that are confusing to players, or selling items in high demand to lazy and/or desperate raiders. The Sha Crystal conversion covers both of these markets.

With MoP, we are introduced to a new tier of Enchanting mats just as we were in the previous expansions. The Sha Crystal is the epic-level Enchanting mat, currently used in five Enchanting recipes. The enchants produced when using Sha Crystals are only necessary when players reach end-game, or when they pick up an item that they probably won’t upgrade for some time. While most players are busy leveling alts, battling pets, or tilling their farm, you can spend a few moments with me and figure out whether or not you want to make some gold with Enchanting.

The Conversion

Before we can confirm whether or not the Sha Crystal conversion market is worthwhile to you, we need to understand the conversion itself. Basically, it takes the following quantities of materials to “craft” one Sha Crystal:

Spirit Dust Mysterious Essence Small Ethereal Shard Ethereal Shard Sha Crystal
Spirit Dust 1 5 25 125
Mysterious Essence 1 5 25
Small Ethereal Shard 1 3 15
Ethereal Shard 1 5
Sha Crystal 1
Reading the table from left to right, you can see how many of the material on the left it takes to craft the item in the column heading. For example, it takes 125 Spirit Dust to “craft” a Sha Crystal, converting (or “upgrading”) all of your dust along the way.

The Formula

Now for the fun part. How do you know which material you should use to create your Sha Crystals? It’s easy! Simply multiply the base material by the number needed in the column to the far right (listed under “Sha Crystal”). If that number is below the market price for the crystal itself, buy it up. If it’s not, or it doesn’t present a significant profit to offset your time, then don’t do anything or sell some of your crystals.

The Spreadsheet

Nobody wants to spend their time calculating Enchanting material cost when they could be out in the world doing pet battles. If you review my post on using IQY data to retrieve market price information, you can create a spreadsheet using the conversion information and formula that will interact with current market prices. Here’s a sample of my data using the Horde auction house on Earthen Ring (US):

SPIRIT DUST MYSTERIOUS ESSENCE SMALL ETHEREAL SHARD ETHEREAL SHARD SHA CRYSTAL
378.49 367.86 259.88 334.09 447.19

As you can see, the average market price for one Sha Crystal is 447 gold. In this example, you will gain the most profit from buying 15 Small Ethereal Shards, converting them to five Ethereal Shards, and converting those to a Sha Crystal. With this information, you now have the power to drive down the price of Sha Crystals all by yourself.

Yesterday, I wrote an introduction on how to use The Undermine Journal‘s IQY data source for your gold-making strategies in World of Warcraft. Today, I’ll walk you through a scenario where I calculate the price point for Mists of Pandaria Darkmoon Faire cards.

Previously, I worked through the various calculations to determine a conservative herb milling-to-Darkmoon Faire card crafting rate. I figured that it takes about 212 MoP-level herbs to craft a single card. If you are buying herbs at or under market prices, you can then set your price for a potential, substantial profit when you list your card.

Create a Master Market Pricing File

To get started, generate an IQY file from the Undermine Journal and open it using Microsoft Excel. You will be prompted with a security notice, and it is your responsibility to understand what this means and allow/enable the security exception if you wish to proceed.

You should now have a new Excel workbook with a single worksheet that is populated with WoW auction house market price data. This will be your master market price data for all of your other workbooks. Now, save this workbook to your computer.

You will need to update this file manually in order to retrieve current market pricing, but if you link to it properly (as you’ll see below), then all of your other spreadsheet calculations will use current market prices instead of you having to search for them one at a time and enter them as static values.

How to Use the Master Market Pricing File in Your Formulas

Next, create a new Excel workbook, and type in an item name into a cell. The name must match the actual item name found in the game, or the search will fail. In another cell within the same row of the item name, type the following formula:

=VLOOKUP(<item name cell>, ‘[<pricing workbook file name>]<pricing workbook file name’s worksheet name>’!$F:$G, 2, FALSE)/10000

In my DMF Card calculator, my formula looks like this:

=VLOOKUP(A2, ‘[The Undermine Journal – H-Earthen Ring.xlsx]H-Earthen Ring’!$F:$G, 2, FALSE)/10000

For me, this returns a match for the item name in cell A2 when searching my pricing workbook, “The Undermine Journal – H-Earthen Ring.xlsx,” where my worksheet name is “H-Earthen Ring.” Columns F and G should represent the item names and market prices as returned by the IQY.

Sample Use to Determine Cost of Creating a Darkmoon Card of Mists

I multiply the market price value of each herb by 212 (135 for Fool’s cap) to determine the minimum price I should sell a card for given the current market price. Theoretically, I’m buying herbs below market price, so this value should already indicate a small profit.

Here’s an example of what my table looks like given the current market prices of herbs:

HERB M PRICE MILL VAL TOTAL COST
Green Tea Leaf 2.42 2.42 212 512.85
Rain Poppy 2.27 2.27 212 481.16
Desecrated Herb 2.48 2.48 212 525.70
Snow Lily 2.74 2.74 212 581.49
Silkweed 2.41 2.41 212 511.47
Fool’s Cap 4.25 2.85 135 573.75

The “MILL VAL” column is used to determine the milling value of Fool’s Cap when comparing the ink yield rate against the other herbs.

As you can see, it takes a decent investment just to make a single card. You also have to factor in the Scribe’s time to mill the herb, craft the inks, and diligence to make their Scroll of Wisdom each day.

You can use this method for just about every profession that makes use of raw materials to help you determine what you should sell your crafted item for at minimum. If you’re not someone that is interested in crafting, this can still be useful to you to determine whether or not an item is above or below cost.

Yesterday, I wrote about the great potential for making gold in World of Warcraft: Mists of Pandaria using the “ore shuffling” technique. Even though this was wildly successful for me in Cataclysm, I was hesitant to get into the market. After listening to others claim the viability of the process, I figured I would conduct a small experiment using actual values and compare them to the projections.

To begin the experiment, I bought 500 Ghost Iron Ore for a total of 2,770 gold from the Horde auction house on Earthen Ring (US). Using the spreadsheet provided by The Golden Crusade and values provided by The Undermine Journal, I expected a profit of 5,771 gold. Using actual values from prospecting and the auction house, I could expect to make 5,557 gold. That’s only a difference of 214 gold from the projections.

Yes, there were some variances, but nothing out of the ordinary. I would definitely recommend using the spreadsheet to give you a general idea of what kind of return on your investment you could expect by shuffling ore.

Here are some interesting points from this experiment:

So what’s next? If I craft Ornate Bands and Shadowfire Necklaces, I could make another 230 or so gold. However, for convenience’s sake, I will simply list all the raw gems and watch the Jewelcrafters that are trying to rush to 600 gobble them up.

I will report back with my sales to see if I was able to realize the 5500 gold profit. For now, this seems like a more than worthwhile market to enter, so long as the supply of ore remains high and I continue to bring my authenticator and spreadsheets with me wherever I go.