Analyzing the Auction House — World of Warcraft
Blizzard subtlety applied a new change to the auction house (explained here) that would slow down auction house searches. Designed to bring a little bit of fairness between players that use auction house addons (a way of optimizing sales, undercutting, etc to increase their profits). This comes as a second impactful change to the auction house, the first being earlier in the year which basically made searches more compact, easier to filter and monitor favourite items, which many addons would do. These changes meant it was less likely for players to try undercut others.
The might Brutosaur mount would is the first mount to hold an NPC that links you to the auction house, instead of travelling to the nearest major city, is being sold for 5,000,000 gold. However, this is being removed from the vendor with the pre-patch to Shadowlands, the next expansion in the warcraft series. It will be available on the Black Market auction house but this means it will be rarer to come across and the sale price could be a lot lower or higher than its current 5 Million gold. This has led to a lot of players playing the auction house in gather as much gold before its removal.
API Setup
I have collected a week's worth of auction house data from my server, Hellfire EU, to try to see which items sell the quickest, for the most money, and how players may still be able to play the auction house (though with only ~1 week before pre-patch this won’t be a method to farm your 5 million gold). I used python to gather information using the Blizzard API.
import requests # To access API
from datetime import datetime # Saving results hourly named CSV
import pandas as pd# Create a new Access Token
def create_access_token(client_id, client_secret, region = 'eu'):
data = { 'grant_type': 'client_credentials' }
response = requests.post('https://%s.battle.net/oauth/token' % region, data=data, auth=(client_id, client_secret))
return response.json()
This will create an access token, you will need to gain access and have your 2 access codes ready for the next part, see here for setup.
response = create_access_token('YOUR_CLIENT_ID', 'YOUR_CLIENT_SECRET')
token = response['access_token']
After we have created our access token we can use it to gather auction house data for one server. Hellfire-EU has the server ID of 1587. You can search for your server using the Blizzard API here. Scroll down to ‘Connect Realms Search’. Sometimes after your first ‘Try it’ will return an internal server error just run it again. I then use CTRL+F to search for my server of interest.
def get_hellfire(token):
search = "https://eu.api.blizzard.com/data/wow/connected-realm/1587/auctions?namespace=dynamic-eu&locale=en_GB&access_token=" + token
response = requests.get(search)
return response.json()["auctions"]hellfire_auctions = get_hellfire(token)
auction_df = pd.DataFrame( kazzak_auctions )
Next, we will restructure the results to be a bit more human friendly (readable). This involved renaming id to auction id, as we have a second id column for the item (being sold) id. Then we will drop the bonus list and modifiers. Most gear will come with stats to improve the performance of your character, I felt this was unnecessary. Finally, I add columns to show when each entry was collected, I planned to run this script hourly and needed to see when an auction had dropped. The API documentation explains that auctions are reported on hourly but may change without notice or explanation. Then each time this script is run it will be saved with the server name and timestamped.
# Expand the item column
auction_df = auction_df.rename(columns={"id": "auction_id",})auction_df = pd.concat([auction_df.drop(['item'], axis=1), auction_df['item'].apply(pd.Series)], axis=1)# Drop 'bonus_list' and 'modifiers'
# These are subgroups of an equipable item with the bonus stats (intellect agility, strength, etc)
auction_df['collection_year'] = datetime.now().strftime('%Y')
auction_df['collection_month'] = datetime.now().strftime('%m')
auction_df['collection_day'] = datetime.now().strftime('%d')
auction_df['collection_hour'] = datetime.now().strftime('%H')filename = datetime.now().strftime('Hellfire_EU-%Y-%m-%d-%H-%M.csv')
auction_df.to_csv(filename, index=False)
Task Scheduler and Running Hourly
If you have tested this API yourself you will have noticed that there is no field to show when or if an auction has sold, key information. To try to identify sold auctions I ran this script hourly, using task scheduler on windows. My reasoning is that an auction can be posted for either 12, 24, 48 hours so looking at auctions listed for 48 hours when they first appear in our data we will monitor it hourly and if it disappears before the 48 hours have elapsed it has either been sold or the user has removed it from the auction. Fortunately, with the new changes to the auction house, its less likely players will undercut (remove auction and repost for a lower price) but this is not a 100% way of seeing what items definitely sold!
Once you save your script you can create a task like so. Make sure it links to your python.exe in the first entry:
C:\Users\patri\AppData\Local\Programs\Python\Python38\python.exeThen provide your script name as shown, followed by the location of the script in Start In option.
C:\Users\patri\Desktop\Project_WOWUnfortunately, no matter what setting I used I could not get this script to wake up from sleep and run (removing my password, granting full access, wake from sleep, toggling power settings) so once it was logged in it would run. I used my tablet to run it at night as it took minimal power to stay on. I collected data hourly for over 7 days, needing an hour before and after the timeframe we want to analyse.
Preprocessing Data
Next is to clean up the data and extract new fields we will need for analysis. This snippet will read all CSV files in your path and combine them into 1 data frame, this returned ~14.5 Million records.
import pandas as pd
import glob # Working with a directory# Read all auction files
path = r'C:\Users\patri\Desktop\Project_WOW\'all_files = glob.glob(path + "/*.csv")# Combine all files into one df
auction_df = pd.concat((pd.read_csv(f) for f in all_files))
Unit Prices are for stackable items, and Buyout Price is for non-stackable items (you can sell a stack of 200 linen cloth but only 1 weapon at a time). Let’s combine them into one cost source.
auction_df['cost'] = (auction_df['unit_price'].fillna(0) + auction_df['buyout'].fillna(0)).astype(str)Now, items can be sold in Gold, Silver, and (available but not able to set) Copper. 100 Copper = 1 Silver, 100 Silver = 1 Gold. The Cost shows this but we need to extract the last 2 digits as copper, the previous 2 as silver, and then everything back (reading left) as gold price. Lastly, we convert any missing gold values to 0 gold (i.e it is being sold for 99 Silver or less).
auction_df['cost_gold']=auction_df['cost'].astype(str).str[:-6]
auction_df['cost_silver']=auction_df['cost'].astype(str).str[-6:-4]
auction_df['cost_copper']=auction_df['cost'].astype(str).str[-4:-2]
auction_df['cost_gold'] = auction_df['cost_gold'].fillna(0)I did perform cleaning on battle pet breeds but found there was much affecting their sell price as rare pets tended to have similar breeds. The code is available on my GitHub.
Finding Sold Auctions
This is not a guarantee that the item was sold, it more so finds items that were either sold or removed by the seller. It works by combining all the days and hours an item appeared, ordered by the timestamp into a list and appending that list as a new cell in the data frame. Therefore the first hour/day is when it first appeared, and the last entry is its sold/removal time. Taking these we can find its time to sell, and timestamp of selling hour.
hours_appearing = auction_df.groupby('auction_id', as_index=False)['collection_hour'].agg({'list':(lambda x: list(x))})
hours_appearing = hours_appearing.rename(columns={"list":'appearance_hours'})
auction_df = auction_df.merge(hours_appearing, left_on='auction_id', right_on='auction_id')
days_appearing = auction_df.groupby(['auction_id'], as_index=False)['collection_day'].agg({'list':(lambda x: list(x))})
days_appearing = days_appearing.rename(columns={"list":'appearance_days'})
days_appearing['appearance_days'] = days_appearing['appearance_days'].apply(lambda x: list(set(x)))
auction_df = auction_df.merge(days_appearing, left_on='auction_id', right_on='auction_id')
auction_df['posted_hour'] = auction_df['appearance_hours'].map(lambda x: x[0])
auction_df['posted_day'] = auction_df['appearance_days'].map(lambda x: x[0])
auction_df['finished_hour'] = auction_df['appearance_hours'].map(lambda x: x[-1])
auction_df['finished_day'] = auction_df['appearance_days'].map(lambda x: x[-1])
auction_df['posted'] = auction_df['posted_day'].astype(str) + "/" + auction_df['collection_month'].astype(str) + "/" + auction_df['collection_year'].astype(str) + " " + auction_df['posted_hour'].astype(str) + ":00"
auction_df['sold'] = auction_df['finished_day'].astype(str) + "/" + auction_df['collection_month'].astype(str) + "/" + auction_df['collection_year'].astype(str) + " " + auction_df['finished_hour'].astype(str) + ":00"
auction_df['posted'] = pd.to_datetime(auction_df['posted'], format='%d/%m/%Y %H:%M')
auction_df['sold'] = pd.to_datetime(auction_df['sold'], format='%d/%m/%Y %H:%M')Finally, we will filter items which may have sold and export to a CSV. Then we can analyze and graph.
result = auction_df[ auction_df['time_left'] == '12-48 Hrs' ]
result = result.drop_duplicates(subset ="auction_id")
result['hrs_to_sell'] = (result['sold'] - result['posted']).astype('timedelta64[h]')
result = result[ result['hrs_to_sell'] < 47]
result['cost_gold'] = result['cost_gold'].fillna(0)result.to_csv('sold_auctions.csv',index=False)
Most Sold Items (Excluding Battle Pets)
All items are ordered by how long it took to sell, quickest to longest. Based on this, it would seem that Xiwyllag ATV is the best source of income, followed by Embroidered Deep Sea Bag and Glyph of the Trusted Steed. While the ATV is taking longer to sell it is selling for far more. What is worth considering are the mats required to build the item. Currently, the 2 items needed to craft it are worth a total of 19,000 while buying the materials separately would cost ~1,850g.
Most Sold Battle Pets
Looking at battle pets it appears Festival Lantern and Lanticore Spawnling are the ways to go. However, the Festival Lanterns are only available during an event in early January, and the Lanticore Spawnling is a low drop from a rare spawn elite which is not exactly great if you are trying to sell en-masse. Time would be better spent on a better-gauranteed drop, but no one will pay big for those.
Most Profitable Items
I’ve limited the range of sell price here as The Pacifier and Chocolate Cake Slice were oddly high for what they are. While The Pacifier can be used for cosmetics, the Chocolate cake is a vendor item bought for ~8 gold. Either someone is hoping that a misclick happens and this item is bought for an obscenely high price or a player is trying to move gold between characters more discretely, who knows! (There is also the possibility as mentioned before this item was mistakenly flagged as sold instead of removed by the seller). It would be better looking into Uncanny weapons as they still appear to sell well, ~200 in 5–7 hours. I have removed the stats information earlier in the preprocessing step so it could be interesting to see what stats sell for more. Other items not to be sniffed at are stackable items with a low individual cost such as the 578 Deep Sea Satin, or Inflatable Mount Shoes.
Most Profitable Pets
While the previous battle pets section had a limit on the number of times sold, this section looks at most profitable. I separated them as the pets below are less commonly sold but for a much larger price which made the graph harder to read if combined. Below, we see the quickest seller is the Seabreeze bumblebee, this is likely due to needing them to begin a quest that lets you acquire your own giant bee to fly around on! What people may not realize is there is another pet that starts the quest but it takes 7 days to get, perhaps they don’t want to wait?
Another high grossing battle pet is the Filthy Slime. This is found in a Mythic dungeon from an almost hidden boss with a small chance to drop. It requires a group of 5 (although well-geared players may solo).
Most Sold Consumable Items
Lastly, consumable items. These are heavily consumed especially on servers with guild looking to progress, or new content has arrived. There are different flasks (hour-long primary stat increase), potions (short burst of stat increase), food (hour-long secondary stat increase), and Runes (minor hour-long increase to primary stat).
Runes are selling the fastest but also for a low price. Greater Flask of the currents (Agility) sells for the most and fairly quickly. In fact, all greater flasks are selling high and quick, with potions selling for the least and slowest.
For consumables, Greater flasks appear to be making the best gold.
Conclusion
The best items to sell are the hardest to obtain. This may explain the number of bots running around farming up items to sell en masse, or players offering to sell boosts for large sums of gold. There doesn’t seem to be a clear cut way to profit off the auction house, and with the new changes to the auction house, it makes undercutting harder/more pointless. Though if I were to choose, I’d pick battle pets. Some can be earned quicker than others and leveling them to 25 can increase the sale price, maybe even double it!
Future Work
If anyone wants to take this further, I’d recommend looking at in-game events that might impact the price of items.
- Do players buy consumables more on Wednesdays when raids reset?
- Do certain items spike before or after a new patch?
- How do stats influence the sale price of weapons/gear?
- Do pet breeds/types/levels increase their sales?
Are you able to better identify items which have sold, or boosters trying to subtly transfer gold?
I hope you enjoyed reading this and if you have any tips please feel free to comment!
