Converting Skillpoints to Gold

Today, I would like to share with you a spreadsheet that I have been working on for a while. It takes everything that I learned writing the articles on promoting common, fine, and rare materials, as well as piles of dust. This spreadsheet automatically pulls data from GW2Spidy.com and shows you the costs, revenues, profits, and risks associated with promoting crafting materials.

About the Spreadsheet

It is still a work in progress and I know there are things that I can improve. Namely, this spreadsheet is not easy to read unless you know what is being discussed. I will try to explain those things below to make it easier. Also, this spreadsheet only has conversions for tier 5 to 6 fine materials and rare materials as that is all I have researched so far. When I get more data on the rates for the lower tiers I will add those as well.

** Update ** I have updated the spreadsheet. Please go to my latest blog post for more information.

Anyway, here is the spreadsheet. It is on Google docs and anyone can access it and read it. You should not need to register for anything to view it. When it is opened it should automatically update the buy prices and sell prices for the promotion formula involved. It is also set to automatically update once an hour.

I will quickly explain the values that you will see in each of the columns.

• Description
• Lower Material
• The name of the lower tier material to be promoted.
• Higher Material
• The name of the material the "Lower Material" will be promoted into.
• Dust Required
• The name of the pile of dust required to promote "Lower Material" into "Higher Material"
• Low ID
• The GW2Spidy ID for the "Lower Material". *See getting ID below.
• High ID
• The GW2Spidy ID for the "Higher Material". *See getting ID below.
• Dust ID
• The GW2Spidy ID for the "Dust Required". *See getting ID below.
• Costs
• Lower Material Cost
• The highest buy order listed on GW2Spidy for "Lower Material".
• # of Lower Material
• The number of "Lower Material" required in the promotion formula.
• Total Material Cost
• Calculation: "Lower Material Cost" times "# of Lower Material".
• Dust Cost
• The highest buy order listed on GW2Spidy for "Dust Required".
• Number of Dust
• The number of "Dust Required" used in the promotion formula.
• Total Dust Cost
• Calculation: "Dust Cost" times "Number of Dust".
• # of Elonian Wine
• The number of Bottles of Elonian Wine needed in the promotion formula.
• Elonian Wine Cost
• The price of a Bottle of Elonian Wine from Miyani
• Total Wine Cost
• Calculation: "# of Elonian Wine" times "Elonian Wine Cost"
• Total Cost
• Calculation: "Total Material Cost" plus "Total Dust Cost" plus "Total Wine Cost"
• Revenue
• Price of High Material
• The lowest sell order list on GW2Spidy for "Higher Materail"
• Number of Higher Material Returned
• The expected number of "Higher Material" that will be produced. Divided into five categories to show you your risk.
• Low: The absolute worst case.
• Low Average: Slightly lower than the average, what you can expect in a realistic worst case when running larger numbers of promotions.
• Average: The number of materials you can expect to obtain. This is based on my trials.
• High Average: Slightly higher than the average, what you can expect in a realistic best case when running larger numbers of promotions.
• High: The absolute best case.
• Revenue
• Calculation: "Price of High Material" times "Number of Higher Material Returned".
• Trading Post Fee
• The amount taken, as a percent, by the Trading Post as a fee.
• Revenue After Fee
• Calculation: "Revenue" times (1 - "Trading Post Fee")
• Skill Points
• # of Philo Stones
• The number of Philosopher Stones required to promote "Lower Material" into "Higher Material"
• # of Crystals
• The number of Crystals required to promote "Lower Material" into "Higher Material"
• # of SP / Try
• Calculation: ("# of Philo Stones" / 10) + (3 * "# of Crystals" / 5)
• Profit
• Net Profit Per Attempt
• Calculation: "Revenue After Fee" minus "Total Costs"
• Profit Per Skill Point
• "Net Profit Per Attempt" divided by "# of SP / Try"
• Return of Costs
• "Net Profit Per Attempt" divided by "Total Costs"
• Last Update
• Low Mat
• The last time "Lower Material Cost" was updated on GW2Spidy.com
• High Mat
• The last time "Price of High Material" was updated on GW2Spidy.com
• Dust
• The last time "Dust Cost" was updated on GW2Spidy.com

You may also notice that the columns and cells have different colours. The colours just represent where the data is coming from. Stuff on a white background are item names and IDs. Cells with a pink coloured background are cells that have data pulled automatically from GW2Spidy.com. Oranges cells are variables and constants associated with the promotion recipe. Cells with a grey background are cells that are calculated with a formula. Finally, green cells are cells in the profit section showing a profit, where as, red cells in the profit section show a loss.

* Getting GW2Spidy ID. To get the ID simply go to GW2Spidy.com and search for the item you wish to retrieve the ID for. For example, "Egg". Navigate to the page for the item you are looking for. The URL for this page will look something like this, "http://www.gw2spidy.com/item/12143". The five digit number, in this case 12143 for eggs, is the ID.

Using the Spreadsheet

Using the spreadsheet is easy. Once you load it up the prices will slowly update. If you scroll all the way to the right you will see the "Last Update" section and may even see these change as the prices are updated. This is live data from GW2Spidy showing you the age of the data I have pulled for the prices of the commodities. This should be no older than one hour as that is the minimum time between updates. If it is older than an hour, something is wrong with the spreadsheet or GW2Spidy might be down.

Once you are sure the data is up-to-date, scroll to the "Profits" section. If you are looking to convert skill points into gold look to the "Profit Per Skill Point" section. You will see that in this section there are five columns, Low, Low Average, Average, High Average, and High. I have added these columns to help you judge your risk. Your risk is always higher the fewer promotions you perform. This is due to something called "the law large numbers". Basically it states, the more you perform an action with random chance the more your average result will tend towards a true mean. On the wiki page linked, they use the example of rolling a six-sided die. The more you roll this die, the closer your average roll (the sum of all your rolls divided by the number of rolls made) will be 3.5 [ (1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.5]. This is because there are more ways for you to roll a large number of dice and receive an average of 3.5 than any other combination of dice rolls. For example, think of the extreme case of having an average of one over ten rolls. There is only one way to order the dice to get an average of one: every die showing a one. The next lowest average you could obtain is 1.1 with nine dice showing a one and one die showing a two. Each of the ten dice could be the two, thus there are ten different ways to achieve this average, versus the one way to obtain the average of one. You would find that if you calculated all the averages that it would be most likely to roll the dice such that the average is 3.5. And this only gets more likely the more dice you roll.

With that said, the more times you do your promotion the more likely you are to obtain the result in the "Average" column. If you only do a couple promotions or just one you could receive any result between "Low" and "High". If you do around ten promotions you will most likely be between Low Average and High Average with your result. Use this as a guide to determining your risk. Anything can happen. You could roll ten ones in a row. The Law of Large Numbers is not a fact. If you roll nine ones in a row there is nothing that says other results are more likely on the tenth roll. The tenth roll still has a one in six chance to give each of the results including another one.

I tell you all this because this method is a gamble, there is no lying about that. You need to judge the risk for yourself. You also have to keep in mind that these markets can be volatile and change within hours. Do not be afraid to hold on to produced materials until you can sell them for a profit. Also, making your own dust to lower its cost can help increase profits.

Conclusions

I hope this spreadsheet helps you. I have already shared it with some guild mates and they have told me that it is intimidating. I hope that this guide helps you understand what is going on in this spreadsheet. It is really quite simple once you take a moment to examine it. If you have any problems with it, or have a question please do not hesitate to ask it below and I will do my best to answer. Most importantly if you notice any mistakes or bugs please let me know.

Now that I have all the data in the spreadsheet I will work on making an easier to read version of this sheet that shows less data in an easier to read format, perhaps with pictures. Also, I learned a lot about scripting for spreadsheets working on this project. This means that if this sheet proves popular I will create more spreadsheets in the future, and perhaps do a tutorial on how to make your own.

Finally, let me know about your successes. As much as I am gaining from having made this spreadsheet (roughly 30-50 silver per skill point) I want you guys to be successful as well. I hope this helps you make some silver. Good luck!