API-enabled Jewelcrafting spreadsheet

Edit: I found a mistake in the first publicized version of the spreadsheet. The link has been updated so I strongly suggest redownloading it!

You can now save all the tedium of manually inputing prices to figure out whether jewelcrafting is good on your server. I just finished my API-enabled jewelcrafting spreadsheet.

This is essentially the exact same spreadsheet I have posted earlier in my Jewelcrafting guides (here and here). With the addition of macros that allow you to automatically import the prices of all relevant items from your server at the click of a button.

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

Download the spreadsheet here and then follow along my installation guide to get your servers prices. This is simple should not take more than a minute or two.

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

spreadsheet

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. This should only be done once, I have not bug-tested this macro extensively and I have suspicion things could get screwy if it’s run several times. If you want to make a version for a separate server I suggest downloading a new copy of the spreadsheet

Refresh the data using the button marked in blue.

And that’s it

Feel free to pop any questions you have to me. I have had some help in testing the spreadsheet from twitter.com/scoobydew79 I would like to give him my sincere thanks.

Report any bugs or problems in the comments or tweet at me at twitter.com/lazygoldmaker or on discord at https://discord.gg/cYKRGxw.

4 thoughts on “API-enabled Jewelcrafting spreadsheet

  1. Thanks for making this! Sadly the VBA data pull doesn’t work on Excel for Mac 2016. I’ve spent a few hours failing to get it working, but I’m not a VBA expert by any means. The URL is being correctly generated to pull the data from the TSM API, so I’m just manually downloading the csv and copy pasting it for now. I’ll let you know if I figure out how to fix the data pull.

    1. I’m not an expert on the differences between mac and windows when it comes to Excel. I want to make a google sheet that automatically gets pricing data to remove any sort of platform problem, but I need to learn a bit more about how the google sheets works first.

Have a question or a thought? Leave it here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.