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
    • 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!

19 comments:

  1. For those new to the EggBaron, could you help clarify what the spreadsheet essentially should be used for?

    Is this a crafting spreadsheet to aide in leveling for profit? Sorry for the silly question, I'm sure there are others who might ask the same.

    I see you went through the trouble and a lot of hard work to make it, so I'd personally like to be able to use it to its full potential.

    ReplyDelete
  2. It compares the different profits that can be made from promoting one crafting material into another via the mystic forge. For example, you can take 50 Intricate Totems and turn then into a random number of Elaborate Totems. There can be profit in this depending on the number of Elaborate Totems you get and their market prices.

    Well this spreadsheet takes all the formula for promoting materials and determines the profit to be made in doign the promotion across a number of cases. If you get the worst case of materials, a low average, the average, a high average, and the best case. You can use this to determine where best to invest your limited skill points for profit.

    ReplyDelete
  3. Hey The Egg Baron,

    We love you and your content and found your site through this post from another friendly site. Because your site and content is awesome, we've syndicated your content over in our community news section on Gold Wars 2 http://www.goldwars2.com/news/

    By default we post the full content of your articles, but we can truncate it down to excerpts only for you if you'd like (most are fine with the full content).

    Thanks!

    ReplyDelete
    Replies
    1. Glad you are liking my stuff! The way you have my news setup to feed to your site is fine. :D Thanks for sharing my articles with a larger audience.

      Delete
  4. Awesome Spreadsheet, thank you very much! I would also love to see the profits with lower tier mats, since you also need less skillpoints for it and maybe the profit per skillpoint is higher. I copied the spreadsheet and tryed to adapt it to lower mats changing the id numbers, etc, but it didnt really adapt the prices after reloading it.. Am I missing something?

    Best Regards

    ZoCks

    ReplyDelete
    Replies
    1. Thanks for commenting!

      I too would love to get into figuring out the lower tier mats. The main thing I need to work on is the conversion rates. I have read and experienced them being higher on the lower tiers which would effect profits. I will try to experiment with that in the future.

      As to your other concern, this spreadsheet would be difficult to modify in the way you have suggested. This first version of the spreadsheet was coded in a non-dynamic way that will prevent you from really modifying it.

      I am working on another version of the spreadsheet that is much more dynamic. When I release this you will be able to play around with it to your hearts content. Shortly after I release this "better" spreadsheet I hope to release the code I have written so that anyone can use it and modify it to create their own spreadsheets. The new stuff works way better and I am excited to share it with the community.

      Delete
  5. Hello Mr. Baron

    I'm new to Spreadsheets and crafting mat. promotion but comparing your sheet and GW2wiki I noticed that GW2wiki notes that for Common craft promotion you also need 1 unit of the target craft tier,
    example: from Silk Scrap to Gossamer Scrap you need to put 1 Gossamer Scrap in the forge as well)But I don't see this requirement in your Sheet. Did I miss this?
    Anyway thx for the sheet and sorry for the maybe stupid question.

    ReplyDelete
    Replies
    1. No this is a good question, I take that into account in the average number of the new material produced. I have subtracted one from the average produced for each accounting for the requirement for needing one of the materials in recipe.

      Delete
    2. Oh ok, thank you that is smart :P
      But doesnt that change the total cost?
      Seeing as you need to buy 1 next tier craft.mat.?

      Delete
    3. It would the first time you do it. But, then so long as you always just keep that one "seed" you can do it as many times as you want and never have to buy another one again.

      Delete
    4. AH, ofcoure! *facepalm* sorry:P
      PS you can ignore the Anon post of my "math" down here

      Delete
    5. No worries, it is nice to know people are double checking my math :D

      Delete
  6. Looking at the sheet the total cost is calculated by:
    Lower tier *250 + dust *5= total cost.
    but doesn't the cost of the 1 next tier craft mat needs to be added?
    Because you subtracted 1 of the output but doesn't need the total cost of the procedure needs to be increased?
    Big chance I just totally missed your point, sorry

    ReplyDelete
    Replies
    1. Lets put it this way and it is good you make me check this :D

      Costs = T6 * 1 + T5 * 250 + Dust * 5
      Revenue = T6 * 18

      Profit = Revenue - Costs = T6 * 18 - (T6 * 1 + T5 * 250 + Dust * 5)

      if you simplify the above equation you can see how you can get away with ignoring the one T6 in your costs if you ignore one in your revenue. Technically the sheet is not reporting the correct costs, you are right, but it is recording the correct profits.

      Delete
  7. Good day sir,
    I have noticed that the uploaded sheet in this topic is not updated by GW2spidy since 13:00 UTC 31-01-13. but GW2spidy is online, is this an error?

    Thank you,
    Anon

    ReplyDelete
    Replies
    1. Thanks for pointing that out, seems something broke the triggers that keep the sheet up-to-date. I have rebuilt the triggers, and updated the sheet.

      Thanks again!

      Delete
  8. Hold on there is still an issue. I'll look into it. :)

    The new version is almost done. I might just work on that and release it later today. We shall see how long this takes me to fix.

    ReplyDelete
  9. Alright there we go seems to be working again. I think it was getting bad data from GW2Spidy and crashing.

    ReplyDelete
  10. I have updated the spreadsheet. Please go to my latest blog post for more information (http://eggbaron.blogspot.ca/2013/02/converting-skill-points-to-gold-update.html).

    ReplyDelete