[Google Sheet] Path of Exile Trading

Overview

Path of Exile Trading, a Google Sheet that updates itself with PoE market information

Features

- Google Sheet and Google Apps Script work together to collect and process the latest information from PoE Ninja API
- effortless, automatic market updates even when your computer is off
- user-friendly and visually appealing: custom formatted cells, custom menu, and custom scripted color bands
- easy installation
- shows latest currency rates
- finds skill gems to buy, corrupt, and sell
- shows daily profit from level 6 Masters
- shows 6 linking cost
- shows the profit for converting currency divination cards
- compares selected item (Mirror of Kalandra, The Queen's Sacrifice, Fated Connections) prices between Challenge and Standard servers
- compares unique item prices

Installation

1. Go to this Google Sheet (view-only).

2. File > Make a copy to get an editable copy. The script takes another minute to copy over. The custom menu "Script" will then show up to the right of Help.

3. You might have to set the timezone in File > Spreadsheet settings. For now, only the last updated time in the dashboard uses it.

Does it work?

Yes. I made 160 ex a month into Delve. I made 1,000 ex in Legacy and Incursion, but I had very little of the sheet's functions back then.

Why release this?

I think that it will save people time, make the market more efficient, and make market manipulation harder.

Manual Updates



Check out the custom menu to the right of Google Sheet's Help menu.

Update: Updates everything in every sheet
Update (dashboard/skill gems/uniques) sheet: Updates sheets separately
Update currency: Just updates the currency table in the dashboard

Automatic Updates



1. Go to Tools > Script editor to open the script editor.
2. In the script editor window, go to Edit > Current project's triggers. Click "Add a new trigger" and choose a function to run every once in a while. If you want to update everything at once, add the Updater function. To update each sheet separately, add UpdateDashboardSheet, UpdateSkillGemsSheet, or UpdateUniquesSheet. Select "Time-driven" under Events, and then the frequency.

If you're using it every day, I recommend once every 30 or 60 minutes for the dashboard and once every 8 hours for the other sheets.

Google Quota

At the time of this writing, Google allows 20,000 URL fetch calls and 60 minutes of runtime per day. I think it's account-wide. Here is a rough estimate of the time it takes to run each function and the fetches.

Updater: 20 seconds and 19 fetches
UpdateDashboardSheet: 4 seconds and 8 fetches
UpdateSkillGemsSheet: 6 seconds and 1 fetch
UpdateUniquesSheet: 12 seconds and 12 fetches
UpdateCurrency: 1 second and 2 fetches

You can go over the quota if you run Updater more than 180 times a day or once every 8 minutes, whereas that would be 3,420 fetches.

Half of the runtime is spent fetching and I use hash tables to bring down the other half. The dashboard sheet is the most important and only takes 4 seconds to update, so you can have it update more often than the others.

I'm not sure how many people are going to be using this. This script can just go on and on, running on Google's servers even if your computer is off, and even after you lose interest in it. This kind of Apps Script will be more common in the future and people may eventually go back to the triggers to lower one script's runtime to make time for another.

Dashboard Sheet

The sheet is for trading in non-hardcore Challenge, with Standard prices for comparison. I'm usually on a non-hardcore Challenge server and the majority of the playerbase is too. I might add an option in the custom menu to change the server. Until then, you can change all server names in the Sheet and Script to the one you're on.

The tables are organized into three columns. A table with PoE Ninja's currency rates is in the left column. The right part of the table shows stats for selling currency from Challenge to Standard after it's over. I think most people don't care about retaining value from Challenge leagues, but the stats are still useful while the league is going.

The center column tables are items selected for my personal trading strategy. I'm interested in Mirror of Kalandra, The Queen's Sacrifice, and the parts for them throughout a Challenge league. I make a lot from this.

The tables on the right show the profit from daily recipes from level 6 Masters, such as Vorici's 64 jeweller's -> 20 fusings. It's usually 4+ chaos worth a day from the Masters. Another table below shows if you can profit from any unlimited conversions from Yeena or Clarissa. You can occasionally profit from this at the beginning of a league, and if you can, you can change the market.

Corrupting Skill Gems Sheet

This sheet shows the expected value from buying a skill gem, corrupting it, and selling it. The script fetches from the API with basically what's on PoE Ninja's skill gem page, sifts through it, and writes some of them to this sheet.

The expected value is how much you can expect to profit on average by corrupting a gem. It factors the cost of a vaal orb and 2 * your perceived cost of a trade, because it takes two trades. I have my cost of a trade set to 3 chaos for most of a league. It makes the expected margin (next column over) more accurate for cheap skills. Expected margin is like expected value but a percentage. Lastly, the minimum sell price is the least you can sell a level 21 (or 4) for profit, holding everything else constant. I used to write this down in the margins at the time of purchase, but now I find that whatever it is at the moment is good enough.

The table on the right is a tally of gem corrupt results: corrupting with nothing happening (I'll call "plain" corrupt from now on), +1 level, -1 level, + quality, and - quality. It's definitely not even. Estimated effect isn't used for any calculations, but the coefficients in the table under it is used. It's an estimate of the value of +quality, -quality, -1 level relative to a plain corrupt. If you get -1 level, I estimate it only takes 25% off the value of a plain corrupt. Some people save money by buying 19's and leveling them back up to 20. The script doesn't care if there are no prices for -1 level or +/- quality. It only uses prices on input gems, +1 levels, and plain corrupts.

Enlighten, Empower, and Enhance are usually profitable. I think this is because everyone wants one and few people have the money to try. If you get rich early in a league, you can get richer by trying these skills.

The most popular active skills are generally unprofitable but the most popular auras are profitable. People level their main active skills and the market floods with these. Similarly, Righteous Fire is not profitable, but the auras for them, Vitality and Purity of Fire, are profitable. Vitality and Anger are consistently profitable I think because strength based builds use Kaom's Heart and Kaom's Roots and there are fewer free red sockets overall.

Uniques Sheet

This sheet just compares prices on all uniques between Challenge and Standard. Basically, I'm looking for uniques that will be valuable in six months to a year, or are cheap as with Timeworn Reliquary Keys in Delve. With more league-only uniques being added over time, chances are, an emergent interaction between them makes a new Standard-only build. Buying these guesses at new possibilities. Of course, you can always move new items from Challenge Hardcore to Standard by making a level 1 and dying with them in your inventory.

TIP: Editing the Script

This project is open source and it's easy to see the source code. For skill gems, in my experience it is more helpful to hand pick skills than to go off the top of the "everything else" table. In and around the long switch statement in UpdateSkillGems.gs, you can edit, add, and remove skills, and create new tables.

Privacy

The script does not collect any information from the user.

Final Thoughts

Path of Exile Trading is designed to take little user input, so people can for the most part look at it; at the same time, user input differentiates a spreadsheet from a website. If there are more websites, I may go more in user input, but if there are more spreadsheets, then I think it's fine. Either way, I learned a lot that I can apply elsewhere.

This is one of the things I always wanted but never bothered to code myself. I will definitely play around with it the next time I play and if it works / I like using it, I'll use it next league.