Monday, January 3, 2011

Excel at enchanting


Welcome once again,
To the AH pile my friend!
I'm so glad you could attend.
Come and buy, come and buy!

Yes the endless AH pile here,
My many bankers ready and near.
Our single purpose, golden clear.
I-I!
I have a goblin army!
They-they!
They make me rich each day!

Aloha all and welcome to the official return of the AH pile. Here is where you'll find the best tips on the entire internets on how to make yourself swim in WoW gold. It's been a long break for me, but vacation is over and work is sorted so now it's time to start working the AH. I am now officially back and will be writing everyday Monday - Friday and with special side projects I might have on the weekends. I hope all of my old readers still enjoy and learn more and for all you new readers, don't be intimidated by the content. Go back and read as much as you can as you will learn a lot.

It feels great to be back and I'm looking forward to more AH silliness and crazy trade antics. One last note. I'm still actively working on updating The List as I work more and more in certain markets. Look forward to seeing it in a week or so.

And now on with the post!
-----------------------------------------------------------------------------------

This is something I mentioned a few times in the past and am going to go in depth with it today. For enchanting and anything else with semi complicated material costs (I'm looking at you engineering!) I use a Microsoft Excel document to easily keep track of how much I should be selling my wares for. First here's a link to megaupload to get the basic enchanting version I've put together to include cata enchants. Note that in this file, I'm using a one day snapshot of the prices on my server just so you can see how it works. Just change things around as needed for your personal prices. If you don't have Microsoft Excel you can also use OpenOffice which can be gotten for free from their website here

My spreadsheet

The point of using an Excel doc is to help you keep track of thresholds in a market where prices change often, which is everything at the start of a new expansion by the way. It also lets you find out a specific threshold amount and refer back to it later in case something with your addOns gets FUBAR. And of course if you stop working a certain market and come back to it later on prices will likely have changed by then so you can just type in the current prices of everything and find out how much you should be selling it for.

Ok now that you have it, here's a quick once over on how it all works.


In column A you have a listing of all the materials you could be using for enchanting. Simple enough.

In Column C you see the average price that you pay for these materials in stacks of 1.

In Columns E and G are the lists of all the things you do with enchanting.

Columns F and I are the minimum prices that you should sell these scrolls for to make a 15% profit on the mats. This is where it gets interesting.


Excel has an automatic calculator function that you can work with. Each of the little boxes you see are called "cells" like next to the material list you see their price in each cell. Using Enchant Shield - Blocking as an example, here's the simple version of what's going on here.

I'm telling Excel to multiply the price of hypnotic dust by 12, add the cost of a vellum, and then add 15% to all of that and let me know the answer. The actual math question behind that is:


((Dust x 12) + VellumPrice) x 1.15 = Sale Price


Not too complicated right? The only catch is that Excel has no clue WTF dust means so you have to refer to it by the cell number. In this case, the cell for the price of dust is C10 and the cell of vellum is C45. Note that you have to use the "*" symbol to multiply and not the letter "X". This translates into Excel-speak as:


=((C10*12)+C45)*1.15



If you don't get it try to remember back to your freshman year algebra class. If you don't get it still, go FIND your algebra teacher and apologize to them for saying their class was stupid and how you'll never ever use it in real life evar. It's ok, I can wait.

Alright back? Good, hope it went well. Not really, but moving on.


Here's how to set it up yourself for your own market values and buying thresholds and work it into other professions if you want.

1. Set up the columns and name them as you see fit. Be sure to leave plenty of room. If you have to move something down you need to make sure that you drag the sale price column the same amount as the item name or you'll have some strange looking prices.

2. Type in your buying thresholds of all the mats in the price column. Make sure you put in the value as singles and not full stacks.

3. Select a cell in the sale price column next to an enchant.

4. Up top you'll see a long bar with a red X, green check mark, and FX next to it. Leave the far left alone where it says "SUM." This is where you type in the pricing equation. Here's how you form the equations for each one:

=((materialCell*amount)+(materialCell*amount))*1.15

The 1.15 at the end is where you set how much profit you want to be making. I have mine at 15% because I'm a dick like that. If want to be making at least 30% profit change the 1.15 at the end to 1.30 and you're set. If you just want to know the total cost of all the materials then just remove the whole *1.15 at the end. After you have the equation typed in hit the green check mark and Excel will double check your math and let yo uknow if you done goofed up. If you did it will ask if you want it to try and fix it for you which works sometimes and sometimes not.

Either way once you have that set it will spit out a number in the price cell and that's how much that scroll should be sold for to make a 15% profit. Now this is almost always going to give you a decimal answer (the numbers after the decimal should be translated as silver). If you want to get rid of that there's an icon at the top right with an arrow and a bunch of 0's on it. When you mouse over the icon it will say "remove decimal." Hit this and Excel will round the number up, so a price of 8.2 becomes 9. That part is just personal preference, but I like it that way. Of course if you want to know the EXACT amount you can just ignore that part and be on your merry way to the AH.

Lastly here's a short recap of what you can use this for. Markets where material prices change rapidly. A secondary listing of your sale thresholds. A reminder if you leave and reenter a market. an easy way to figure out how much something should be sold for. If you should bother crafting or just sell the mats if you're not trying to push somebody out.


Ok that should do it, I hope you learned something and can find a use for this. Feel free to share this with anybody that you want. All I ask is that you make sure to direct them to where you found it at.


Thanks for stopping by!

13 comments:

  1. Great post, Stok. I am glad to see that someone has put together a spreadsheet like this. I have one from pre-Cata that has every profession that I have. I pirated the idea from someone, and modified to fit my specifics. Anyway, I use it all the time to re-calculate the cost of crafted items for AH sell. I have not taken the time to update for Cata items, as I am still working on leveling toons & professions.

    Thanks for the enchanting spreadsheet.

    Keep up the good work, and welcome back!

    Azz

    ReplyDelete
  2. This is a very nicely done spreadsheet. If I were to expand on the spreadsheet, I'd suggest using absolute references ('$') so that cells can be moved around without worry about chancing some wonky prices.

    ReplyDelete
  3. I'm so glad that you're back to writing your blog :) really enjoyed it through WotLK.

    ReplyDelete
  4. Welcome back indeed :) I'm pretty new to all this ah stuff but I use excel everyday at work so I also built a spreadsheet for some of my regular crafted items. I haven't been as thorough as you though so thanks a million for taking the time to do this & to share it so freely. I shall look forward to reading as you post.

    ReplyDelete
  5. Nice guide for basically any profession, especially for people that haven't used Excel much before.

    I've had a spreadsheet like this for enchanting during WotlK but I didn't use a profit margin to calculate the price. Instead of multiplying the price by 1.15 I divided the price by 0.95 so it accounts for the AH cut of 5%. This value I then put into QA3/Auctions profit master as the treshold for scrolls.

    I kept your blog in my reader while you were gone and I was excited to see you started writing AH-related posts again, keep it up! :D

    ReplyDelete
  6. well, continuing with my inscription only marketing... the ilvl 346 relics continue to sell very well, Darkmoon Decks, the market is good but pretty saturated atm and it hard to get insane prices, but still doing what i can, have made over 20 complete decks and have about 15 still around.. about 20K or more each and only cost about 14K to make, however there is the ton of cards i still have to make more decks... so 200-250% profits there, but huge risk... Running high volumes of herbs and mats from the AH as i dropped herbalism for alchemy... not looking good so far, went elixirs, but the market is still slow on flasks, prolly will make a bunch then shift to xmute. any time i run out of cash for the darkmoon business, i make a couple hundred fortune cards for 60-100% profits... sold 700 in one day... time sink to make em though cause the only profits are in milling... yeah about 50,000 herbs later that i have milled... After your previous posts i have really explored into the markets a lot and playing with much bigger number in the AH, i spent about 180,000 in on day on herbs and volitiles for my business... the most i have ever had at one time... 70 somthin K... working on not going to zero anymore... actually get a gold stockpile but i keep putting it into cards and decks... i figure, all said and done i have around about 300K in decks waiting for buyers.
    the fair is in town so it should continue to pick up... now to play the game to sell mine over the next guy... if not, i will have 3 weeks before the next fair to sell em as the BOE trinkets that i will make out of them. Still good to see you back....

    Other notes, velums 10silver, yeah unlimted supply on the enchant vendor...

    spreadsheets, yeah got mine started in open office. too many addons are not at full speed to do what i need, like milling results so i have milled thousands of herbs and recorded to find out what my thresholds are... welcome back, happy new year and good to see you back at it...

    ReplyDelete
  7. I cannot stress enough how awesome it is working with a spreadsheet for AH selling. It makes everything so much easier once you have it set up correctly. I use separate "sheets" within the same document for each profession.

    In addition to my "threshold" column, I also have a column to input what the current going price is on my server. Then I have a third column, where I subtract the price of crafting from the current market value and multiply the result by 0.95 (taking into account the 5% AH cut) which gives me the current profit margin for that item. Finally, I have a profit percentage column, in which I divide the profit margin by the crafting cost, and then I can see at a glance what has the best ROI. By doing this, I notice for my server that doing Transmute: Living Elements (taking Volatile Life into Volatile Air) has a higher ROI (106%, going on the low-end of getting 14 Air, and not taking procs into account) than using the cooldown for a Truegold transmute (36%). Eventually I want to make a Vial of the Sands for myself, but here is how I plan on making it as cheaply as possible: Transmute enough Volatile Life into Air, Fire, and Water for 12 Truegold (120 of each Volatile). Once I reach the amount I need, I will make my Truegold (of which I'll certainly get some nice procs). Any leftover Volatiles I'll post on the AH or put to use in other professions.

    I suppose I could go even further and add a 15% markup to the threshold column. I have even color-code certain sections by epic/rare/uncommon/common quality. I never thought spreadsheets would be this fun!

    ReplyDelete
  8. hey good to see you blogging again! just a tip for the spreadsheet maybe google docs is a nice place to put it on so everybody can easily reach it and get the last version.

    ReplyDelete
  9. Omg thank you for this idea.
    Back in wrath I kept track of my enchanting scrolls with a pen and paper. All my other stuff I sell can easily be kept track of through APM (except the engineering stuff).

    ReplyDelete
  10. @ all
    Wow I didn't think so many people would be so greatful for this. Glad I could help! There's pleanty of resources out there, it's just a matter of knowing that they exist and then you just think "oh wow why didn't I think of this yet?" So glad you all enjoy this, it should serve you all well.

    ReplyDelete
  11. hey Stok i'm going to create a spreadsheet containing all cataclysm craftables for professions wanna help me a hand?

    ReplyDelete
  12. Give a chance to TradeSkillMaster add-on. I think it does all of this...

    ReplyDelete
  13. As Megaupload has been brought down, is there any other place to download that spreadsheet?

    ReplyDelete