Amalgamated Gemstone price spreadheet

[Epertyufi 32]

Heyho o/

So, if you are crafting legendaries, you will probably need 250 of these beauties. Craft or buy? Crystalline recipe or ecto recipe? GW2API and spreadsheets to the rescue!

The spreadsheet checks the current prices on the mats for the recipes and calculates the total cost of crafting the gemstones. Crystalline recipes are never the answer. For the ecto recipes I made two calculations:

• worst case scenario - you get 10 gemstones from every forging
• best case - you get 25 gemstones for every 10 forging (see wiki)

The spreadsheet then tells you whether it's worth crafting with each material or would you just be better off buying the gemstone from TP and also, the current cheapest recipe. Prices are highest current buy offer.

Click for shiny spreadsheet!

NOTE: Spreadsheet sometimes gets messed up. Can't figure out why, but sometimes it can't load the ecto information. If this happens (you see 'loading...' in row 32 under Crystalline Dust prices) try copying the document for your drive so you can edit it, and CTRL-X - CTRL-V the cells to anywhere else in the spreadsheet. If anyone has any idea why this might be happening please do step up  Otherwise, loading the prices should take 10-60 seconds.

NOTE2: You can check the time of the last price update in row 42. If the cell does not update on refresh make sure you are logged in to your Google Drive account. If it still does not work after that, please contact me.

Edited June 25, 2016 by Epertyufi

[Sithicus 98]

Nice job man, really nice idea, thanks very much for sharing!

[Epertyufi 32]

I updated the spreadsheet so it now gets all the data directly from the GW2API instead of GW2Spidy API, because the latter often gave errors. That should be solved now (it also seems to load faster for me).

[Faye Grim 0]

Man, I just registered at this forum to say: I love you for this! AWESOME!

It would also be awesome if you could make a row with the prices of instant buy for orbs etc. Would love it!

 

Faye

[Epertyufi 32]

@Faye Grim

Wow, thank you very much  I did a quick update, now it shows recipe prices and the cheapest orb/crest/etc to buy with insta prices. However as stated in the spreadsheet this is a bit misleading ATM, as it doesn't take into consideration that you might not be able to buy all the items at the currently lowest instabuy price and might have to go above that if you need more than the current supply at that price (you might be able to instabuy a crest at 15s, but the rest at 17s as supply runs out). I'll update this to work properly maybe on the weekend or next week or maybe in the afterlife. The spreadsheet was a bit crowded as it were and now with this new addition its even more crowded so I'll rework that also. Also I was thinking about including instabuy prices on ectos too, but that wouldve made it even worse (and ectos sell very fast with buyorder anyway). To avoid more crowding the full recipe price is only shown for the normalised amalgem return (with 10% to get 25 gems from a recipe).

In the rework I'll probably include an instabuy/buyorder checkbox next to orbs/crests and ecto and update the list accordingly. Also, even though there are now less errors with the sheet using GW2API instead of GW2Spidy's, I still get at least 1 mail a day saying that something went wrong. This is due to the limited capabilities of google spreadsheets and I will probably migrate the project to a proper site which will take care of this and also should be faster to load/update and such.

Any further suggestions regarding this project or any similar are always welcome ^.^

Edited January 16, 2017 by Epertyufi

[Epertyufi 32]

Updated the spreadsheet. Now the instabuy recipes show the realistic prices according to the current state of the Trading Post. This means for example that if you instabuy 75 of a material but only 50 of those are selling for 10s, and the remaining 25 sell for 12s, it will show 50*10s + 25*12s, instead of 75*10s as it were before.

In the instabuy recipes only the orb/crest/whatevers are calculated with instabuy prices, the ecto prices are still calculated with the buy listing price as the buy orders come in pretty fast.

I also tried to tidy stuff up a bit. I've hidden some columns and rows and whether a recipe is worth crafting or not now can be seen by the colour-coded name of the ingredient.

[Pommi 3]

You are my hero of all times!!! Great job. That's what I'm searching the whole time.

[Faye Grim 0]

Hi! I am using your sheet very often. I just had an idea, the prices for the amalgamated stones often vary and sometimes it was cheaper to buy them directly.

Could you add this to the calculation? A comparison between crafting and buying the stones directly?

 

[Kotflinte 1]

wow, thank you for this. 

I really have to finish my legendary although I don't want Twilight anymore, but already have precursor and stuff

[Monica the Black 1]

You're awesome, man! Can't wait to use this