Spreadsheet

I have created a Spreadsheet that automatically calculates profits for the major crafts available in Legion professions. The Spreadsheet contains all the Legion crafting professions and their recipes. You can download the Microsoft Excel version of the spreadsheet here: Lazy-Goldmakers-JCENCH-Spreadsheet-TSM-API. This version sadly only works on Windows-based systems.

The Google Sheets version of the spreadsheet can be found here: Lazy Goldmakers Spreadsheet Google Sheets Version.

The Google Sheets version works on all systems and is available anywhere as it can be stored in the cloud. Make sure you make a personal copy by clicking file and then Make a copy as the original is set to view only so it does not get changed.

This page contains the instructions to set it up as well as some information about how it works.

How does it work

The spreadsheet uses the Tradeskillmaster.com API to download pricing data. The documentation for the function used can be found here if you are interested.

To download the data into the spreadsheet I have written a VBA macro that takes your API key and a region and server name and downloads the data automatically. A separate macro refreshes the data.

Keep in mind that you can only query the TSM API 10 times per day for this data. If you try refreshing more than that it will fail.

The source code for the Macros for downloading and refreshing the data can also be found in my pastebin here. I welcome anyone with a better knowledge of VBA than me to look it over. Make sure that the code in the worksheet matches the paste.

After downloading the pricing data to the “Data” sheet the value of all the items are calculated.

I set the AH price for the items as a weighted average between the current minimum buyout and the market value. The weighting is set as 1/3 minimum buyout and 2/3 market value. This will hopefully give a good tradeoff between the volatile minimum buyout and the more stable market value pricing. For a look into how the market value in TSM is calculated take a look here.

I have protected most of the spreadsheet to make sure my formulas aren’t screwed up by accident.

How to set it up

When you open it you should go to the How to and assumptions  worksheet. It will look like the picture below.

spreadsheet2

To set up the worksheet you first need to fill out the cells in the yellow box. The top cell should hold your tradeskillmaster API key. To get this you need to have a tradeskillmaster.com account. I strongly suggest getting this so you can use the TSM Desktop app for in-game pricing data.

If you have an account go to www.tradeskillmaster.com/user to find your API-key. You can find it at the bottom of the page. Copy and paste it into the spreadsheet.

The region should be the two-letter abbreviation for your region, US or EU.

The realm name should be written in all lower caps. spaces should be replaced by dashes (-) and special characters should be ignored. Examples:

Emerald Dream = emerald-dream

Mal’Ganis = malganis

After filling in the realm data and the API key press the top button marked with red in the picture to download the pricing data. If you want to make a version for a separate server I suggest downloading a new copy of the spreadsheet. The same button can be used to refresh the pricing data.

In the green box you see the pages for professions and one sheet that contains the pricing data.

I have hidden the sheets that do the intermediate calculations for Jewelcrafting, but they are the same as the ones that have been used in my previously released spreadsheets.

Contact me if you have any questions or need help. I suggest joining me on discord where I can answer your questions and talk to you in a more informal and live manner. You can find it here:

https://discord.gg/cYKRGxw

Ledger Nano S - The secure hardware wallet