Skill Points to Gold Spreadsheet Update

I recently released a dynamic spreadsheet that calculates the expected return you could expect to receive when promoting crafting materials from one tier to another and keeps its data up-to-date from GW2Spidy.com. I am happy to announce that I am ready to share the newest version of this spreadsheet with my readers. This spreadsheet is the culmination of the work I did writing blog posts about promoting common crafting materials, fine crafting materials, rare crafting materials, and piles of dust through the mystic forge. The new sheet has a number of new features, such as, a simplified view, Tier 1 - 5 Rare Crafting Materials, faster updates, and an update log. With that said here is the spreadsheet,

Egg Baron Material Promotion Spreadsheet - Last Updated February 16th, 2013.
* Feb 4th, 2013 - Posted Publically
* Feb 5th, 2013 - Added column showing profits using Mystic Binding Agent
* Feb 16th, 2013 - Added the rest of the fine crafting material promotions (tier 1-2, 2-3, 3-4, and 4-5). Also, split simple view up into three different sheets, one for rare materials, fine materials, and common materials. Access the different sheets via the tabs at the bottom of the screen. For more details see this post.

A special thanks goes out to ZoCks for helping me collect data!


Simplified View and the Risk Column


The new simplified view.
The most obvious change is the simplified view. The simplified view was designed to give readers an easy way to view the most important information about the various promotions. It shows the formula used in the Mystic Forge, the expected output, expected average profit per attempt and per skill point, and a risk assessment.

The risk assessment is based on how likely it is for you to turn a profit. "No Profit" means that even in the best scenario you will not profit. "Extreme" means that only in the very best scenario will you profit. "High" means that you will only profit in cases above average. "Average" means that you will only profit when you perform at the average or better. "Low" means you will profit in all cases expect the worst results possible. "None" means that even in the worst case scenario you will still profit. Keep in mind that none of this takes into account the fluctuations in the market while you perform transactions. That is a risk you need to access for yourself. This risk assessment only takes into account the fluctuations in the output from the Mystic Forge.

You can select a sheet to view from these tabs.
The simplified view should be the active sheet when you open the spreadsheet. If not you can access it via the sheets tabs towards the bottom of the window. You will also see three other tabs; "Detailed View", "raw data", and "log". The detailed view looks the same as the old spreadsheet showing calculations and cost/profit/revenue breakdown. For instructions on reading this sheet see my old blog post. I will talk more about the raw data sheet and log sheet below.


Other Updates


I have also added the rest of the rare crafting material promotions. Currently, none of them are profitable but recent market shifts have opened up possibilities. Now it should be easy to watch for opportunities. I hope to follow this change up by eventually adding the rest of the fine and common crafting materials but more research is required into output quantities. Also, if it turns out the new "Mystic Binding Agents" effects profitability on promoting rare materials I will attempt to factor this into the spreadsheet and make an update.

Next, I did a lot of work on how the code gets the data from GW2Spidy. Formerly, I made a series of tiny requests to GW2Spidy for each piece of data then updated each cell one at a time. You may have seen this happening as you used the old version. The new spreadsheet makes one large request for all the data. This data is then cached for use for a variable amount of time (currently, 15 minutes the same amount of time GW2Spidy keeps its data). Updating the cache with 20,729 items from GW2Spidy takes roughly 30 - 60 seconds, however, this sheet only grabs the 1,019 crafting materials as this is quicker taking roughly 5 seconds. Once the data is cached it can be accessed instantly. 

I have also moved all the raw data to its own sheet called "raw data". This sheet gives me a central place to select which items I am interested in and get all the information about them. Updating all the data in one central location is much faster then updating individual cells. Now, if the cache contains fresh data, the spreadsheet will update nearly instantly.

Finally, I have added a log. The log records each time the spreadsheet is updated, each time the cache is updated, and any errors that occur when connecting to GW2Spidy. Now it is much easier to tell if the data being displayed is fresh, or if there is some sort of error holding the update process up.


Fair Warning


Please report bugs or errors in the comments or via e-mail.
I have been running most of the new code for about a week and I believe I have eliminated all the bugs and errors. Every time my spreadsheet scripts generate an error it is logged and once an hour I am e-mailed a report. The only error I can not eliminate is a timeout error associated with Google's URLFetchApp. Sometimes when I attempt to retrieve data from GW2Spidy I am unable to complete the transfer but instead of receiving an HTTP error the fetch function times out and crashes the script. This can happen because of a slow transfer rate between GW2Spidy and Google or when the connection is interrupted, for example.  Looking into this there does not seem to currently be a work around and Google is working on a solution. There is nothing I can do as it is Google's function that causes my script to stop running. I can not even catch this and report it in the log. The only way to tell that this is happening is if you see a message in the log stating a cache update started without a reciprocal caching completed message. I tell you all this to let you know about the sheets limitations. It is not perfect. It is not fully tested. Use this sheet to look for opportunities then follow up with research in game. If you see any error or problems please do not hesitate to let me know via e-mail or through the comments below.


Using my code


If you would like your own version of this spreadsheet you may copy it to your Google Docs for your own private use and modify it an anyway you wish. When you copy the spreadsheet you will also copy the script I wrote. If you would like to use this sheet or my script to publish your own material please leave a reference  to my blog somewhere within your body of work. I also would like to see how people are using my spreadsheet so please let me know about your modification whether private or public. You can add me as a collaborator via my e-mail address if you do not wish to make your work globally public.

With that said, yes it is now possible to modify this sheet to your hearts content. Simply add your own ID numbers to the first column of the raw data sheet in a copy of this spreadsheet on your Google Docs. The next time the spreadsheet updates the GW2Spidy data for this item will be added to the raw data sheet in the columns to the right of the ID column. I will have more details about creating your own spreadsheet using my code in the future. For now feel free to play around with it and ask questions in the comments below. I believe I have clean code that is well commented, so you should be able to make out alright yourself.


Legacy version of the Old Sheet


I will be turning off the old spreadsheet in about one weeks time. The sheet will remain shared in my Google Docs folder but the script will no longer run and the data will no longer update. I have placed warnings in the spreadsheet about this cut off dating instructing people to come here to obtain the new spreadsheet. If you would like a copy of the old spreadsheet please copy it before February 8th, 2013. At this time I will also add a warning to my blog post on the old spreadsheet indicating that it is no longer active and linking to the new spreadsheet.

~~~~

As always let me know what you think of my content. It helps me decide what to do more of and what to do less of. If you are a regular user of this spreadsheet be sure to come back to my site every now and then to check for update and to get your Guild Wars 2 news.

26 comments:

  1. Sweet mother of Profit!
    After blowing my mind with the last spreadsheet for turning skillpoints in gold you just blew my mind again! thx man keep it up, this is a awesome site :)

    ReplyDelete
    Replies
    1. Thanks! Glad you are enjoying it. Now that I have the tricky bit of coding done I can make all kinds of fancy spreadsheets. Expect more in the future. :D

      Delete
    2. Help! I would really like to see the best way to turn skill points into gold, but the spreadsheet is no longer live. Is there a way to turn it back on?

      Delete
  2. Great sheet! I actually miss the bit more detailed old one, but this one has a better overview.

    As I have stated before, I am highly interested on the other Tiers of crafting materials since my results with T1 to T2 are much more profitable! I am making around 1 Gold per Skill Point promoting t1 crafting materials to T2 !!

    ReplyDelete
    Replies
    1. I ment t1 to t2 common crafting materials.

      ZoCks

      Delete
    2. Wow I will have to add that in. Also, you can still access the more details view via the tabs at the bottom of the screen. You should see a "simplified view" "detailed view" "raw data" and "logs".

      Thanks for taking the time to comment! I will look into adding the rest of the common materials.

      Delete
    3. Thank you for taking the time answering ;)

      You are absolutly right, with the excitment of this new version I read to fast and overlooked the detailed version, thats great! excellent work! Thank you very much!

      Looking foward to it =)

      Zocks

      Delete
    4. Wow T1 to T2 common crafting mats make that much?!
      Time to hit the math on those, that really sounds promissing :)

      Delete
    5. I did some preliminary tests that look promising. Still need to test more to determine the answers to these questions.

      1) How many materials does promoting T1-T2 give?
      2) Is this the same as for T2-T3? T3-T4? T4-T5? (I know T5-T6 is different is this the only one?)

      My first tests I just did 1000 Copper Ore to Iron Ore. For the four tries it took I got 60, 60, 61, and 63 Iron Ore for 250 Copper.

      So, just from that, as a general rule of thumb, look for T2 materials that you can sell for at least 4 times what you buy the T1 material for. Whether this carries on for the other tiers IDK.

      I will try testing T4-T5 next, if that holds I think it is same to assume it is the same of the other tiers. I will do that tomorrow.

      Delete
    6. FYI Copper -> Iron is not a profitable example.

      Delete
    7. I am very sorry, I ment the FINE crafting materials, (venom sac, blood, scales..etc) Sorry for that, I got all the names mess up. :/

      Try tiny venom sac into small venom sac promotion.

      ZoKCs

      Delete
    8. if it helps yous ave some time, here is my spreadsheet for common materials
      https://docs.google.com/spreadsheet/ccc?key=0AmM8m4-QrjIDdHFueDRBUXNOMy1uLVpGamtMZktqb3c&usp=sharing

      i never used common material promotion though, since i used t1->t2 fine promotion.
      Here are my other two sheets i made:
      https://docs.google.com/spreadsheet/ccc?key=0AmM8m4-QrjIDdF9mVVdzYVRickVheHo0UmRlemRVTkE&usp=sharing

      https://docs.google.com/spreadsheet/ccc?key=0AmM8m4-QrjIDdE9ZNVJqNmJ1ZklEZDRJWjl1YUk4dnc&usp=sharing


      though your sheets are way better and avoid the errors that are bound to happen on long item lists, because of google spreadsheets limitations.

      I might look into your method, for my other spreadsheets...right now i am looking into other areas.

      I posted my spreadsheets above on reddit some weeks ago and got downvoted pretty fast (funny though since there have constantly been users on my sheets since then...especially the fine promotion one

      Delete
    9. Heh, funny with Reddit isn't it? I posted my sheet to reddit too and got quite a few down votes. Now I see someone has reposted my sheet again, and it got down voted right off the main page. But still, there are like 20+ people constantly on the sheet viewing it.

      I like your sheets. They remind me of how I originally handled data from GW2Spidy, but like you say you run into limitations. Still you have some good data there. I also like you calculate the number of higher materials needed to profit. That is a smart way to look at the profitability of these promotions that I did not think of.

      My script can handle all the items from GW2Spidy and dump the data into a spreadsheet although it will occasionally timeout. I hope to have a tutorial up in the next week or two describing how to use my script to build a spreadsheet with live GW2Spidy data.

      Delete
    10. yeah i like your caching solution and decided to make my scripts around it. Though now i hit the daily urlfetch quota. I don't know if it is the data size quota per day is reached or the amount of querries.
      Anyway i will reduce my traffic by making a central spreadsheet that fetches the data of all my items with your script.
      Other spreadsheets will import the raw data with Importrange().
      This way i can make as many tradepost spreadsheets as i want and they will all share the same data.

      Will just have to find out if the central spreadsheet hits the quota, if it uses .../all-items/all

      Delete
    11. I am not sure what the limit on the URLFetch is, either way that is on Google's side. I used to run my spreadsheet with "...all-item/all". I never ran into the URLFetch problem, but occasionally I would get a time out which could then put bad data into the cache. Thus, my guess it is a number of uses thing. I know I read somewhere that Google was trying to increase the time and the number of uses allowed with URLFetch.

      I was thinking of expanding the caching script to go through each item type one by one to populate the cache. It wouldn't be difficult to do, then it could grab all the items without timeouts.

      Delete
    12. I've heard that the downvoting on Reddit happens to keep things more secretive, especially with market stuff -> the fewer people that know about it the longer the profits stay viable.

      Great work on the new sheet, too. I really like being able to see both the simplified and complex breakdowns of the transmutations.

      Delete
    13. One comment on the spreadsheet details - it shows Trading Post fees at 15% - but my understanding is that they are 10% - 5% listing fee and a further 5% when sold.

      Delete
  3. Try tiny venom sac ;)

    ReplyDelete
  4. When I try to open the spread sheet I get error ID not in cache can anyone help me out?

    ReplyDelete
    Replies
    1. That occasionally happens if GW2Spidy fails to respond to my scripts request with the correct data. The cache then gets filled with blanks which are updated to the spreadsheet. This is normal fixed in the next update which will occur 10 minutes later.

      Delete
    2. Hey Phil, thanks alot for the awesome script. :)

      I decided to try my hand at making a sheet of my own, based on yours. But I can't seem to get the script to fetch data from other IDs than the ones already there.
      Whenever I add another ID (example: 44969) I get the Error: ID not in cache. I've been trying for well over an hour, and updating doesnt seem to fix it.

      I'm no good with code, but if you could help me out I'd be grateful!

      Delete
    3. It has been a while since I have looked at that code, but my guess is that you need to make a modification to line Line 87 and Line 81.

      I only pull in the crafting materials data in my code.

      You may need to pull in more data by modifying the category here.

      See the documentation for the Spidy API here: https://github.com/rubensayshi/gw2spidy/wiki/API-v0.9#wiki-full-item-list

      Oh and here are the different "top level types". Only the bolded words count, like Armor, Bag, Consumable. http://www.gw2spidy.com/types

      I hope that helps.

      Delete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. I think your script is fetching the data for the highest offer not the lowest sales price. Can you check?

    ReplyDelete
  7. I'd love to see this updated again, thanks for the initial hard work though ~

    ReplyDelete
  8. Still a great sheet.. Thanks again!!!

    ReplyDelete