Path of Exile: Guild Gem Spreadsheet

We have a big guild bank for our Path of Exile guild thanks to the donations of a wide variety of people. Greenspielers has 24 guild tabs and 3 of those have been dedicated to storing skill gems according to a system I worked out last November. The basic idea is each colour gets a tab, the gems are divided into auras, skills, and support gems, and then they assigned slots in the bank alphabetically. This way if you need a gem you can go check the bank alphabetically for it to see if we have any of them lying around. This system takes up about half of each tab so the other half gets used for 'incoming' gems from people uninterested in checking to see if we need those gems and for overflow since a lot of gems are pretty rare and we'd like to keep extras on hand for when more people need them.

It's worked reasonably well but I was the only one who really knew how many of each gem we 'need' so I was the only one doing the sorting. Which is fine, I actually like doing repetitive tasks like this. While I'm playing the game, anyway... Eventually I get off to playing something else and then the tabs decay. Also, while keeping things clean is pretty easy actually setting it all up in the first place is more effort.

I was slow in doing so this time around so Snuggles asked for the layout in a Google spreadsheet so he could clean things up. They'd added a bunch of new gems that I needed to add to my layout but once I did that it didn't take too much extra time to copy some of the data from my local Excel sheet into a Google one. Snuggles went off and sorted things out and then I bought a bunch of identify scrolls to make the outline again and things are great.

Lino then asked what gems we currently need. Looking at the guild bank you can figure that out with some effort but when you're cashing a quest or when a gem drops from a monster you're not at the guild bank and you don't want to put that effort in. So I figured I could add this information to the spreadsheet, and I did just that today. Now there's a tab for the layout, a tab for the current gems on hand (I wonder if there's an API to get at the guild bank like there is for the personal bank so I could automate this further instead of filling the sheet out manually), and a pivot table showing which gems are 'understock'.

But now the question is how do we define a gem as being understocked? For now I've categorized all the gems by where you can first obtain them via a quest. If you can get it in act 1 normal then the bank only wants one copy. If you can get it in the rest of normal the bank wants two copies. If you can get it from a quest in cruel the bank wants three copies. If you can only get it from the merciless library quest or from a monster drop then the bank wants five copies. And then because auras and support gems are more likely to be used when someone switches characters I add an extra one for each of those.

This seems like a reasonable start, but I'm not sure I like it all that much. Do we need 6 copies of cast on death? Apparently it keeps getting abused so maybe having lots of copies on hand makes sense? But it also keeps getting nerfed... The bank has 4 copies of iron will right now. The formula says we want 4 copies just in case, but I've never heard of anyone ever using that gem. We're probably fine with just 1 of them... And if we only had 3 of them there's no way I'd want someone to take a 4th as their quest reward!

Probably the best thing would be to have someone who knew a lot about the economy and/or character builds to tell us which gems we care about and which we don't. I don't know enough about it for sure. Last season I would have been all in on throwing all copies of multiple traps and elemental proliferation because those seemed bad... Until we had the trap belt drop and then suddenly I wanted as many copies of both as I could get my hands on!

What we have it certainly a start though. Now there's a sheet you can look at to see if we have any of a given gem at a glance. (If the gem is listed on the 0 owned pivot then we don't have any of them right now!) And then you can use the other pivot to see if we need extra copies of stuff or not, too, if that's something you care about.