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,

* 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

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.

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

if it helps yous ave some time, here is my spreadsheet for common materialshttps://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

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.

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

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.

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.

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.