Why I <3 Excel (It brings in tha' golds)

[Warwithin]

***UPDATE!***

I've now made YouTube Videos entailing the entire process!

Making Gold With Alchemy - Potions
-Associated Blog Post

Making Gold With Alchemy - Flasks
-Associated Blog Post

***Back to Post***

I've used Excel in the past to aid me in keeping track of prices, etc.; but I've never been able to make someone quite as streamlined as the most recent project I made (which is deceptively simple).

Below is the basic look of the project.

(Unfortunately, buying and selling nothing has a Net Profit of 0 )


How it works is quite simple. All I have to do is search for the current AH prices for Cinderbloom, Azshara's Veil, Whiptail, Stormvine, Heartblossom, Volatile Life (The required materials to make 5 different types of Cataclysm Potions), Mythical Mana Potions, Golemblood Potions, Potions of the Tol'vir, Volcanic Potions, and Earthen Potions (The 5 Cataclysm Potions themselves) and enter those prices into their respective "Price Per Unit" categories.

I chose these 5 potions because I've seen them as the most popular and profitable on my server. I'm looking to add more to the list in time.

At this point, the "coding" (Yes, I'm using that term loosely - sorry CS majors!) takes over, taking the base pricing information and manipulating it to produce results such as Price Per Unit of each type of Potion, Price Per 40 Units of each type of potion (I create in lots of 40 as to sell them 1x20, 1x10, 1x5, and 5x1 - just a quirk about my selling habits), Net Profit / Loss Per Unit, etc.

(On a side-note: I've got an addon that allows "Shopping Lists" to be created (Auctionator) and I've got a shopping list for all the data needed for the spreadsheet. It takes a tedious process of repeatedly searching for the items and breaks it down to 11 mouse clicks.)



As you can see, even before creating the potions, you can tell that, at current market price, there is a net profit of over 1,400 gold (That goes for you non-potion spec Alchemists out there). This gives you a quick look how much gold you can make with a few mouse clicks without having to do all the time-consuming math time and time again. But, as a Potion-Specced Alchemist, the spreadsheet was incomplete. I needed to factor in procs.

One of the last features I created in this spreadsheet was the ability to take in account any procs as I am a Potion Master. As you can see below, all it takes is a simple entering of how many procs of each potion I received to re-calculate the subsequent results.



In short, if you like making gold and have a profession where something can be produced...USE EXCEL. It can not only help to make sure that any multi-process, multi-item creation you're working on is profitable across the board, but it can do it quickly and efficiently - removing the majority of the legwork. Not only that, but this only both took a very rudimentary understanding of Excel and roughly 30 minutes to complete - and I haven't used excel in ages.

With very simple math, I am able to calculate that an investment of 1280 gold will result in an income of 3,330 gold (with the added profit via procs), yielding 2,049 gold in Net Profit. That took about 2 minutes of data entry after the spreadsheet was complete. Not only that, but, say, hypothetically, the price of Stormvine (The sole material for the Earthen Potion) was out the roof, I would be able to detect that, while I do have a positive overall Net Profit, I would be losing money by creating and selling Earthen Potions. That is the reason for breaking down the Net Profit / Loss of each potion. It gives me the ability to detect where I can make profit, how much profit I can make, and what potions I should or should not make in order to raise my Net Profit.

One of the perks of using a system like this is that, once you've done it once too your liking, you can easily translate the same principal to any profession - not just Alchemy. It's a simple process of checking the prices of required materials verses the price of the end result on a bit of a larger scale as well as having a good knowledge of what is in demand and sells well on your server.

I just wanted to share my creation and encourage many of you out there to do the same!



-=EDIT=-



After a few PMs, etc., I decided to just upload the file and let you all have your way with it

(TAKE NOTE THIS IS AN UPDATED VERSION OF THE FILE THAT JUST HAPPENS TO BE A BIT CLEANER AND BETTER - EXPLANATION BELOW)

http://www.mediafire.com/?hvz0zauuux50qy9

This is how the new interface looks:



The rule of thumb with the new interface is very simple: DO NOT TOUCH WHAT IS IN COLOR. If you're trying to enter data into a colored slot, you're doing it wrong.

And after a few numbers being inserted...



As you can see, there is a new setup in this file. The purple cells are those that were in the previous version. Nothing terribly new. In the green, however, are the amount of herbs per potion that are required; and, in the light blue are the materials to be purchased. I'll explain the difference below.

And the third image, featuring the new feature...



When comparing the two most recent images, there is a difference in "Materials to buy" as well as "Investment", "Income", and "Net Profit". That was one of the main issues I had with the older version. It would tell me how many herbs I needed to buy to make 40 of each potion, but would not take into account any adjustments due to a potion being a net loss; therefore, not being created.

In the new version, it will auto-adjust the "Investment", "Income", and "Net Profit" as well as the "Materials to buy" if any items are in the negative and should not be created. This was done in order to not have anyone (well, more importantly, me ) accidentally purchase more herbs than I needed to create potions that were a net loss to create and sell.

And just a couple side notes...

1. The "Procs" category still works - though is not displayed in the new images.
2. As said earlier, only enter data into non-colored fields. This includes the "Market Price Per Unit" row (Row 2), the "Procs" row (Row 5), and the "Price Per Unit" sub-column (G12:G18). After entering data in any of those areas, use the rest of the spreadsheet to read data and act accordingly.
3. Investment, Income, and Net Profit all function the same as in the earlier version - fairly self-explanatory.
4. If the figures look a bit off to you, it's because they are - by 5%. The AH cut of 5% is removed in the appropriate places to re-adjust prices to the 95% of gold you will receive after the AH cut. This is to avoid a figure having a marginal profit - therefore inclining you to purchase and create the potions - without taking into consideration the AH cut, resulting in a net loss.
5. Again, the download link for the file is: http://www.mediafire.com/?hvz0zauuux50qy9

Happy AHing, and if you have any questions, feel free to PM me

~Warwithin



-=EDIT #2=-


This edit was useless and confusing. Editing out edit #2.



-=EDIT #3=-



Ignore the Rapidshare link, got one on Mediafire that should work.

New Mediafire Link: http://www.mediafire.com/?hvz0zauuux50qy9

And again, any questions, feel free to PM.

This link is for the Potions Excel Spreadsheet. For the Flasks one, see below.

~Warwithin



-=EDIT #4=-



Finally uploaded my spreadsheet for Flasks. Sorry for the wait! Not gonna lie, I kinda forgot =S

http://www.mediafire.com/?7eecz361lb3b1jz

[ChildeRoland]

Thank you. Now I'll do this same thing once I get gametime

[Greandal]

I'm working on an Excel sheet myself, tho im trying to put in all the professions, aswell as a disenchant value. Anyway, nice one, keep up the good work

[Warwithin]

I'm working on an Excel sheet myself, tho im trying to put in all the professions, aswell as a disenchant value. Anyway, nice one, keep up the good work

How and why would you do that? I'm a bit confused. It's the whole "All the professions" part that I'm not quite understanding.

[ChildeRoland]

Kinda offtopic but it's about Excel. Tried to make a spreasheet, but whenever I write "Comma" something it shows as a date, and I can't change it.

4,95 becomes jan.00 and 4.95 becomes apr.95. Can someone explain this ? PM is fine =)

[Aequita]

Kinda offtopic but it's about Excel. Tried to make a spreasheet, but whenever I write "Comma" something it shows as a date, and I can't change it.

4,95 becomes jan.00 and 4.95 becomes apr.95. Can someone explain this ? PM is fine =)

Rightclick on the selected area (maybe 1, maybe all), "Format Cells".
There u can choose between different types of formation.
Maybe your default setting is "Date".

How and why would you do that? I'm a bit confused. It's the whole "All the professions" part that I'm not quite understanding.

I thing he's gonna make different "Sheets" in 1 .xls-File for all the different professions.
1 for alchemy, 1 for enchanting, 1 for jewelcrafting, and so on.
To evaluate the most profitable things to craft/farm.
Maybe not. ^^


Sry for my bad english btw.

[Deleted]

Kinda offtopic but it's about Excel. Tried to make a spreasheet, but whenever I write "Comma" something it shows as a date, and I can't change it.

4,95 becomes jan.00 and 4.95 becomes apr.95. Can someone explain this ? PM is fine =)

select cell
press ctrl+1
most left tab
click on General or Number.

OT:
I'm working on my own sheet also for 1 month by now, it is quite big. It knows all Cata recipes, disenchant,milling,prospecting statistics (not based on wowhead, because that is inaccurate), it automaticly pulls out data from an addon, calculates many useful market values.. And much more, but it is private, and will stay like that !

[Fraza]

Hmm. I sometimes laugh at my brother's Excel files for EvE Online (aka Spreadsheets in Space). You just destroyed that and left very little to work with.

[ChildeRoland]

Rightclick on the selected area (maybe 1, maybe all), "Format Cells".
There u can choose between different types of formation.
Maybe your default setting is "Date".
Sry for my bad english btw.

Doesn't work, but thanks anyway =)

[Greandal]

I thing he's gonna make different "Sheets" in 1 .xls-File for all the different professions.
1 for alchemy, 1 for enchanting, 1 for jewelcrafting, and so on.
To evaluate the most profitable things to craft/farm.

This is correct :P

[Warwithin]

I'm working on my own sheet also for 1 month by now, it is quite big. It knows all Cata recipes, disenchant,milling,prospecting statistics (not based on wowhead, because that is inaccurate), it automaticly pulls out data from an addon, calculates many useful market values.. And much more, but it is private, and will stay like that !

I'd like to somehow streamline mine a bit more to incorporate more automated functions, so it's got room to grow.

On a personal note, however. I've always gotten more satisfaction from sharing my works than hoarding them for personal benefit...so long as you're not on my server

[Suuicide]

Have fun using that method for Inscription. :P

[Ibis]

Kinda offtopic but it's about Excel. Tried to make a spreasheet, but whenever I write "Comma" something it shows as a date, and I can't change it.

4,95 becomes jan.00 and 4.95 becomes apr.95. Can someone explain this ? PM is fine =)

Well, first of all you really need to use the correct decimal separator. Excel decides based on your country whether you should use comma or period as your decimal separator. So if you have "European" settings it automatically changes something like 4.3 to a date because we use comma as the decimal separator. To change this, you actually need to change the regional language settings in Windows itself: (for Win7) Control Panel -> Region and Language -> From the bottom of the first (formatting) tab: Advanced settings, decimal separator. When you use the correct decimal separator your numbers shouldn't get formatted as date as long as they really are true numbers.

I haven't found a way to disable the automatic formatting which sometimes is really annoying. But if you have a large number of ... numbers with commas that need to get switched to decimal periods, you can save them to a text file and either use search & replace or open the text file with excel and go through the steps of the importing window where you can change the decimal separator and it imports them correctly.

If you're not planning on using the number with the "incorrect" separator in any formulas, you can (before typing) set the cell type to "text" and it will be treated as just text, not numbers. But obviously since excel only recognizes one type of a separator, it can't perform any mathematical procedures with the numbers with the incorrect one. Another way is to put a ' in front of the number ('4.3) and it prevents any attempts to format the numbers, but still you can't do any mathematical stuff with it.

Note that you need to change the cell type to text/number/whatever BEFORE you type the content. If it gets to treat the input as a date you don't get the original input back by changing the cell formatting type afterwards.

But I think the most important point was in the first paragraph: you need to change the decimal separator setting from your Windows settings to match your preferred decimal separator.

(Just for giggles: decimal separator usage worldwide (Green=Comma, blue=period, grey=unknown and red=Non-West-Arabic Numerals))

[ChildeRoland]

Well, first of all you really need to use the correct decimal separator. Excel decides based on your country whether you should use comma or period as your decimal separator. So if you have "European" settings it automatically changes something like 4.3 to a date because we use comma as the decimal separator. To change this, you actually need to change the regional language settings in Windows itself: (for Win7) Control Panel -> Region and Language -> From the bottom of the first (formatting) tab: Advanced settings, decimal separator. When you use the correct decimal separator your numbers shouldn't get formatted as date as long as they really are true numbers.

I haven't found a way to disable the automatic formatting which sometimes is really annoying. But if you have a large number of ... numbers with commas that need to get switched to decimal periods, you can save them to a text file and either use search & replace or open the text file with excel and go through the steps of the importing window where you can change the decimal separator and it imports them correctly.

If you're not planning on using the number with the "incorrect" separator in any formulas, you can (before typing) set the cell type to "text" and it will be treated as just text, not numbers. But obviously since excel only recognizes one type of a separator, it can't perform any mathematical procedures with the numbers with the incorrect one. Another way is to put a ' in front of the number ('4.3) and it prevents any attempts to format the numbers, but still you can't do any mathematical stuff with it.

Note that you need to change the cell type to text/number/whatever BEFORE you type the content. If it gets to treat the input as a date you don't get the original input back by changing the cell formatting type afterwards.

But I think the most important point was in the first paragraph: you need to change the decimal separator setting from your Windows settings to match your preferred decimal separator.

(Just for giggles: decimal separator usage worldwide (Green=Comma, blue=period, grey=unknown and red=Non-West-Arabic Numerals))

JEez... I think I'll rather just use another computer =p

[Ibis]

JEez... I think I'll rather just use another computer =p

It really does only take a couple of seconds to change the setting:P

I'm not sure what happens if you open the excel file with decimal commas in a computer with decimal points.

[theK1ll577]

Excel is quite handy, my current market watch file has been scanning twice a day for 3 years and has about 400 gigs of data in it :#

[Deleted]

only reason why i hate excel spreadsheets are that i have to enter shitload of prices.. why ppl dont make simple ingame addon what pulls price database from other addons?

[Deleted]

Is there an addon that can export auction history to an excel readable format?

I wonder how much work it would be to set up an SAP system to handle my jewel crafting business.

[Mageshadow]

You can export mysales data to excel, it's the best I can think of. How to export Mysales Data to Excel

Also if you're looking for gold tips/gold making strategies, check out Just My Two Copper

[Elk]

Any chance you'd be willing to upload / e-mail me that actual excel file?