Ok. I have seen a number of posts on other threads regarding gems, and whether you can make a profit.

I have created my own gem calculator ages ago, and I have used it with great success.
I doubt it has anything you can't find anywhere else, but here it is anyways.

Excell Sheet (Google doc)


*This is my first attempt to share a spreadsheet. So let me know if it doesn't work.
Erm.. also, I'm not 100% sure how to ensure that other people can't edit it.

Some notes on how to use my sheet.
There are 5 tabs at the bottom.
"Loot Check"
"Ruby"
"Amethyst"
"Emerald"
"Topaz"

The 4 named gems are obvious. The loot check is one I used to calculate the absolute minimum cost to get to some of the larger gems assuming you have looted all the required Flawless Squares and Tomes. (More on this later)





Ok. So some manual work is required when using this sheet, since we are not allowed to automatically read data from the AH.

It's quite simple to use.

Go to the Ruby tab.
Get the cost of gems and tomes from the AH and fill them in here:
*NOTE. You only need to fill in the cost of the Scroll of JC, Tome of JC and Tome of Secrets on the Ruby page. They are automatically copied to the other gem pages.

No go to the other 3 gem tabs and fill in the current buyout price of each of the gems.




On the right it shows how many of each type gem is required to get to each of the gems you want to get at.
(At the moment the number of tomes required is not shown. I'm working on getting that info there too.)



For example if you are trying to build Star gems go to row 17. (Star under the outcome table)
On the right hand side you will see that you require to make 1 Star
3456 Chipped gems
Or 432 Flawless gems
Or 27 Flawless Square gems.



On the bottom left the sheet will automatically calculate your potential profit. This is already after taking into account the 15% AH cut.
So if you see a positive number here, you can actually make money.
The type given under "Make using" tells you what gem you should start with to get the profit.





DISCLAIMER.
This sheet is provided as-is.
It may contain bugs, so don't come chasing after my blood if you actually lost some money using it.
Also keep in mind that the sheet is only valid for a very short time.
It has happened to me more than once that I see a potential profit, but by the time I have crafted the gem the prices on the AH changed, and I made a loss. If you want to get a better idea of potential profit use the average for the last day, or average for the last 10 sales as input instead of the current buyout price.

Just as an additional note. I have used this sheet to calculate the absolute minimum cost crafting a gem, assuming you manage to loot all the ingredients.



The green numbers shows what you have to sell a crafted item to basically break even.
The numbers in the second column is the minimum crafting cost.


So... the most important information:

To get to 1 radiant star gem you need:
729 Flawless Squares
1631 Secret Tomes
15 500 000 Gold



The minimum you can sell a Radiant Star that you crafted is 18 235 300 gold. (This will give you a profit of 5g, so basically break even)

So as you can see a lot of gems that are currently being sold is technically at a loss, but I guess we can thank duping for that.

I think you should include the cost of gems into the equation, especially for Emeralds. 729 FS Emeralds is like, what, 3M gold. The Tomes are like, 600k. So you have to account for the 3.5M or so from buying the lower mats. So it's really closer to 19M to craft a Radiant Star from Flawless Squares and you would need to sell it for about 22.4M to break even.

It makes sense to me. It's cheaper to craft gems for personal usage (since it takes time/tedium to craft and people don't need piles of Radiant Stars for their character). On the flip side, those who are botting or duping have stockpiles of these gems and are trying to AT LEAST break even, which means they have to sell it for 22.4M after Blizzard's cut.

The sheet normally takes the cost into account. I added that last post just to show what I would take to loot and craft a gem.

As you can see on the picture the inputs to that sheet had 0 as inputs to all gems up to and including Flawless Squares.
It also has 0 as tome cost input.

If you put the current values in there they will be included in the calculation.

@Metta Also your assesment is 100% correct. At 3000 per gem and 1000 per tome you minimum crafting cost is 19318000. So even on 22.4M you will be making a loss.

Good sheet!

so been loking for something like this that i can use without breaking a calculator..........................Pity its not really worth doing to make some gold

This is awesome. Thanks for sharing this, @Bort! :)

Posted by Undertaker at 12/18/2012 04:55 PM

.Pity its not really worth doing to make some gold

Don't know about softcore, but I made another 460k profit on a single Flawless Star gem sale on HC today.
And trust me, 460k made by running around in town is very good for HC.
That act 3 vendor has it right. "Tried the adventuring life..."

Thank you, @Bort! This will most definately come in handy. =)

@Sandalhero made some updates, and locked some fields in the Excell sheet.
I have updated the online version, and replaced the link in the first post.

*Edit. When I opened it in google docs directly it didn't look any different from previous.
But what negative numbers are supposed to show up in red so you can easily see where you will make a profit or a loss.

*Edit. When I opened it in google docs directly it didn't look any different from previous.
But what negative numbers are supposed to show up in red so you can easily see where you will make a profit or a loss.

negative still shows as red for me

I ran another set of numbers"=IF(D33>C33,(3*D33),(3*C33))" to show if it is worth the time to upgrade, as the other night I was earning 100k per radiant square ruby from flawless square rubies or 3 mil per radiant star.