In our first article in this series, we covered all of our prerequesites for modeling market behavior in the MMORPG Old School Runescape (OSRS). Equipped with transaction data for the in-game Grand Exchange, we can now get started analyzing the in-game economy.
In this article, we will:
- Demonstrate basic scripting strategies for using the API to collect and stage market data
- Discuss a basic market inefficiency present on the Grand Exchange: the “High-Low spread”
- Break-down fundamental market concepts and reporting techniques in-steps, with the goal of sorting data by potential profitability
- Introduce more nuanced filtering and honing of results by taking into account the differences between market datapoints over time.
Disclaimer: This article is strictly academic in-nature and does not constitute financial advice. I have deleted my OSRS accounts and have no stake in any position within the game.
Getting our Data Ready
Before we can begin any analysis, we first need to fetch and stage data from the OSRS Wiki API. There are many different methods and tools that can be used to convert the raw JSON provided by the API into useful reports. Formulas in Excel or Google Sheets provide an accessible means of generating dynamic reports, while SQL, R, and/or other languages and tools permit automation and more detailed data analysis. I personally enjoy using Pandas and SQLite via Python to manipulate data; while I’ll be using these in my examples, feel free to pick the tools that best suit your use-case.
Let’s start by grabbing the latest data from the /24h endpoint. The following Python script will download and convert the JSON data provided by the endpoint into a .csv file which will be saved to the same filesystem location where the script resides:
## Import modules
import csv
import requests
import json
import os
## Change working directory to script location
pathtorunfile = os.path.dirname(__file__)
os.chdir(pathtorunfile)
## Define url and headers to use for API call
url = 'https://prices.runescape.wiki/api/v1/osrs/24h'
headers = {
#'User-Agent': 'You must uncomment this line and modify this string before using the API. See: https://oldschool.runescape.wiki/w/RuneScape:Real-time_Prices'
}
## Fetch data
jsonresponse = requests.get(url, headers=headers)
jsondata = jsonresponse.json()
currenttimestamp = jsondata["timestamp"]
responsedata = jsondata["data"]
keylist = list(responsedata.keys())
valuelist = list(responsedata.values())
totalvalues = []
totalkeysandvalues = []
for value in valuelist:
subvalues = list(value.values())
totalvalues.append(subvalues)
for key1, value1 in zip(keylist, totalvalues):
totalkeysandvalues.append([key1] + value1)
with open(f'24h.csv', 'w', newline='') as f:
writer = csv.writer(f, quoting=csv.QUOTE_MINIMAL, quotechar="'")
writer.writerow(['id','avgHighPrice','highPriceVolume','avgLowPrice','lowPriceVolume'])
writer.writerows(totalkeysandvalues)The resulting tabular data, “24h.csv” will look something like this:
id,avgHighPrice,highPriceVolume,avgLowPrice,lowPriceVolume
2,173,22774662,169,6727341
6,208238,107,195674,283
8,188316,86,184342,259
10,182737,125,178931,296
12,201003,109,194923,264
28,925,74,506,74
30,954,1254,889,27
...- each id represents a unique item sold on the Grand Exchange. This can be cross-referenced with the /mapping endpoint or the OSRS Wiki Prices website to obtain the more human-friendly named item.
- avgHighPrice and avgLowPrice reflect the respective average High (insta-buy) and Low (insta-sell) prices for a given item. Average calculations provided by the /24h endpoint are weighed against volume exchanged over the span of the past 24 hours.
- highPriceVolume and lowPriceVolume likewise provide the total quantity of items exchanged at High and Low prices respectively for each corresponding id over the span of the past 24 hours.
Great! Now that we have some data, let’s create a template for generating reports. The following snippet dumps the contents of the .csv file into a temporary SQLite database, prints the results of our “FinalOutput” table to the terminal, and exports these results to .csv.
## Import modules
import pandas as pd
import sqlite3
import os
## Change working directory to script location
pathtorunfile = os.path.dirname(__file__)
os.chdir(pathtorunfile)
## Import csv file as Pandas Dataframe
DailyCSV = pd.read_csv("24h.csv", sep=",")
## Initialize temp sqlite db and import Pandas Dataframe
tempdb = sqlite3.connect(":memory:")
## Import Pandas Dataframe into temp sqlite db
DailyCSV.to_sql('DailyCSV', tempdb, if_exists='append', index=False)
cur = tempdb.cursor()
## run your queries here
cur.execute('''CREATE TABLE FinalOutput AS SELECT * FROM DailyCSV;''')
## print results to terminal
FinalOutputReport = pd.read_sql('''SELECT * FROM FinalOutput;''', tempdb)
print(FinalOutputReport)
## export results to .csv
FinalOutputReport.to_csv('HighLowSpreadReport.csv', index=False)What we now have is a rudimentary template that we can use to analyze a single market datapoint. By modifying the portion outlined in bold by adding intermediate SQLite queries, we can treat “FinalOutput” as a destination table for our completed results which will be displayed in our terminal and saved to our filesystem as a spreadsheet.
High-Low Spread Overview
Before we start generating reports, we first need to establish the general sense of direction and focus for our analysis. One of the simplest and most obvious market inefficiencies present in markets is the bid-ask spread, or, using the nomenclature of the OSRS Wiki API, the High-Low spread.
In a theoretically perfect market, there are no “high” or “low” prices; the equilibrium price of a good is always a single value.
…Theoretically perfect markets don’t exist, and the market of OSRS is no exception. There are always inefficiencies, frictions, costs, and other factors involving trade which result in the market equilibrium price never being a single value. In the case of the Grand Exchange in OSRS, for each commodity there is a high value where an item can be instantly purchased, and a low value where an item can be instantly sold. The difference between these values is the spread. Hence, we have the High-Low spread.
This makes intuitive sense; if an individual needs to instantaneously cash-out or cash-in on an item, they pay a premium for the convenience of instantaneous liquidity or supply. Likewise, more patient individuals can either post low offers or high prices with the intention of profiting the difference. These conditions provide a window for entrepreneurial individuals to effectively “buy low and sell high” to return a profit. The resulting spread is just the natural result of players managing their opportunity costs within a free market.
So, the goal and direction for our analysis and reporting will involve identifying opportunities where we can maximize profit against the High-Low spread.
I’ve selected the High-Low spread as the market inefficiency to cover first because it is one of the simplest features of any market. This simplicity will help ease us into fundamental market concepts like price, volume, taxes, buy limits and ROI. With some baseline knowledge of these market concepts and reporting strategies presented in this article, future articles covering more complicated market inefficiencies should be easier to digest and follow along with.
That said, this simplicity comes at the cost of efficacy; many price tracking websites including the OSRS Wiki Prices site feature varying degrees of spread information. This ease of visibility creates a low barrier to entry, resulting in high competition, low margins, and low profit potential. This extends not only to the High-Low spread of OSRS, but also to the equivalent Bid-Ask spreads for other games and real-life exchanges. As a result, while trading on the High-Low spread is very accessible and easy, generating sizable yields with this strategy can be very difficult, and like all trading strategies, carries risk.
High-Low Spread Analysis In-Steps
Step 1: Calculating the Spread
So, where should we start? All else being equal, we want to trade items where the spread is the greatest, because these items should yield the greatest profit per-transaction. Let’s use the template we created earlier to form a list of IDs sorted by their spread, which is just the average High price subtracted by the average Low price:
...
## run your queries here
cur.execute('''CREATE TABLE FinalOutput AS SELECT id, (avgHighPrice - avgLowPrice) AS Spread FROM DailyCSV ORDER BY Spread DESC;''')
...This returns the following result:
id Spread
0 23342 23832032.0
1 12426 14161579.0
2 12437 12976998.0
3 12424 12424717.0
4 12819 10307000.0
... ... ...This is where the OSRS Wiki Prices page comes in-handy; let’s spot-check the IDs with the highest spreads…
… And the top five results are all “3rd age” items. For the uninitiated, these are some of the most expensive items in the game. Without delving too far into the nuance concerning these particular items, one fundamental problem with assessing their trading potential based off of their High-Low spread alone has to do with their lack of Volume.
Step 2: The Importance of Volume: Calculating Potential Gross Profit
...
## run your queries here
cur.execute('''CREATE TABLE FinalOutput AS SELECT id, lowPriceVolume, highPriceVolume, (avgHighPrice - avgLowPrice) AS Spread FROM DailyCSV ORDER BY Spread DESC;''')
...
id lowPriceVolume highPriceVolume Spread
0 23342 1 1 23832032.0
1 12426 13 9 14161579.0
2 12437 4 1 12976998.0
3 12424 3 2 12424717.0
4 12819 2 1 10307000.0
... ... ... ... ...In any market, price means exactly nothing unless there are successful trades and corresponding movement of volume. If a commodity is too obscure, or if buyers and sellers are too stubborn to ever mutually agree on price, the prices that are set serve no functional meaning or purpose, other than to highlight dysfunction.
In the case of these 3rd age items, we can see that while there is some movement of volume, it is extremely low, with single-digit movement being typical on any given day. While prices in this case are not completely meaningless, the relative infrequency of trade volume for 3rd age items limits their potential for trading on the High-Low spread.
So, let’s incorporate volume into the mix by taking the product of our spread and half of our daily Volume, which will be our “Potential Daily Gross Profit”:
...
## run your queries here
cur.execute('''CREATE TABLE FinalOutput AS SELECT id, ((avgHighPrice - avgLowPrice) * ((lowPriceVolume + highPriceVolume) / 2)) AS PotentialDailyGrossProfit FROM DailyCSV ORDER BY PotentialDailyGrossProfit DESC;''')
...We divide daily volume by half because profit is contingent on a trading player performing two transactions, including a purchase and a sale.
This returns the following result:
id lowPriceVolume highPriceVolume PotentialDailyGrossProfit
0 27277 290 202 1.534928e+09
1 20997 292 209 1.051944e+09
2 22486 238 200 9.475407e+08
3 26384 181 221 7.470668e+08
4 13652 1026 917 7.263031e+08
... ... ... ... ...The top five results in this case are all still extremely expensive items, however they all have around two orders of magnitude greater trade volume on the Grand Exchange than the items returned from our previous price-centered query. This higher trade volume makes short-term trading based off of the High-Low spread more feasible given that there are a substantially greater number of opportunities available to conduct successful trade… However, we are neglecting one of the Grand Exchange’s most important features: Taxes.
Step 3: The Importance of Taxes: Calculating Potential Net Profit
Here are the tax mechanics for the Grand Exchange as-described in the first article in this series:
Sell orders are subject to a 0-1% tax. More specifically, for items sold below 100gp per-unit, there is no tax. For every 100gp in per-unit price, a 1gp tax is charged. There is an upper tax limit of 5,000,000gp per-unit which applies to items sold for 500,000,000gp or more.
Let’s stick the High price and the spread back into our query and see how these five items hold-up against the immutable strength of taxation. We use the High price to determine taxes because it indicates our target sale price, with taxes being levied against the seller of a commodity:
...
## run your queries here
cur.execute('''CREATE TABLE FinalOutput AS SELECT id, avgHighPrice, (avgHighPrice - avgLowPrice) AS Spread, ((avgHighPrice - avgLowPrice) * ((lowPriceVolume + highPriceVolume)/2)) AS PotentialDailyGrossProfit FROM DailyCSV ORDER BY PotentialDailyGrossProfit DESC;''')
...
id avgHighPrice Spread PotentialDailyGrossProfit
0 27277 1.520622e+09 6239546.0 1.534928e+09
1 20997 1.705741e+09 4207778.0 1.051944e+09
2 22486 9.991165e+08 4326670.0 9.475407e+08
3 26384 4.523861e+08 3716750.0 7.470668e+08
4 13652 9.650768e+07 747995.0 7.263031e+08
... ... ... ... ...The tax levy for all but one of these items is greater than their corresponding spread, which effectively nullifies any potential profit trading these items in the short-term against the High-Low spread.
So, it is clear that in-order to improve our results, we definitely need to incorporate taxes into our reporting. We can accomplish this with some additional queries:
...
## run your queries here
cur.execute('''ALTER TABLE DailyCSV ADD COLUMN Tax;''')
cur.execute('''CREATE TABLE MaxTax AS SELECT * FROM DailyCSV WHERE avgHighPrice > 500000000;''')
cur.execute('''CREATE TABLE MinTax AS SELECT * FROM DailyCSV WHERE avgHighPrice <= 500000000;''')
cur.execute('''UPDATE MaxTax SET Tax = 5000000;''')
cur.execute('''UPDATE MinTax SET Tax = round((avgHighPrice * 0.01) - 0.5);''')
cur.execute('''CREATE TABLE DailyCSVwithTax AS SELECT * FROM MaxTax UNION SELECT * FROM MinTax;''')
cur.execute('''CREATE TABLE FinalOutput AS SELECT id, ((avgHighPrice - avgLowPrice - Tax) * ((lowPriceVolume + highPriceVolume)/2)) AS PotentialDailyNetProfit FROM DailyCSVwithTax ORDER BY PotentialDailyNetProfit DESC;''')
...We start by adding a new column called “Tax”. We then split our table into two, with “MaxTax” containing items with an average high price greater than 500M GP, and “MinTax” containing the inverse.
Next, we apply different tax formulas to each subset of data. “MaxTax” is really simple; we set the Tax column to 5M. Calculating MinTax is a bit trickier, but can be accomplished with a single formula. Since taxes start at 0gp and increase by 1gp for every 100gp of price, we can accurately calculate tax for each ID by first multiplying an item’s price by 0.01. We then subtract the result by 0.5 before rounding, which effectively forces rounding-down tax for any price that isn’t a multiple of 100, providing us with an accurate representation of the taxes seen in-game.
We then recombine our split tables with a UNION command and use our now fully-populated Tax column to calculate “Potential Daily Net Profit”:
id PotentialDailyNetProfit
0 27277 304928316.0
1 12426 100777369.0
2 11252 95814194.0
3 22477 68250221.0
4 22975 66826250.0
... ... ...This time, our top 5 results contain a more interesting and varied group of items. I’ve included screenshots from the OSRS Wiki Prices page illustrating the 1 and 7-day price history for some items, with the orange and green lines representing High and Low price histories respectively:
- ID 27277 “Tumeken’s shadow (uncharged)” and ID 12426 “3rd age longsword” are items we have seen before in our earlier reports. The High-Low spread and traded volume for these super-expensive items offsets their associated taxes enough to top this list. We will take note of these and return to them as we continue to improve our reporting methodology.
- ID 11252,”Magpie Impling Jar” appears to have temporarily held a large High-Low spread, minus taxes. When checking historical prices, this item recently experienced a sharp upward spike in price toward the end of February 18th 2024, followed by a correction toward the end of the next day, after which the spread almost completely evaporated. In this circumstance, the large spread we detected only manifested as a result of supply/demand shock. While sudden volatility can potentially widen the High-Low spread, there is substantial risk associated with purchasing a commodity in the wake of a sudden price spike due to the risk of a subsequent correction destroying our position.
- ID 22477, “Avernic defender hilt”, appears to have consistently maintained a spread ranging from 1.0-1.5M GP, despite being taxed at around 650K GP:
- ID 22975, “Brimstone Ring” has likewise maintained a spread ranging from 40-70K GP, despite being taxed at around 37K GP:
So, are any of these items worth trading on the High-Low spread? While the Avernic Defender Hilt and the Brimstone Ring both appear to be decent options, there is, once again, another market factor that we are neglecting to consider: Buy Limits.
Step 4: The Importance of Buy Limits: Adjusting Potential Net Profit
Here are the buy limit mechanics for the Grand Exchange, as-discussed in the first article:
Purchasing parties are subject to buying limits per-commodity, which expire in 4-hour intervals. These buying limits can range from as few as 4 units, to as many as tens or hundreds of thousands of units.
Up-until this point, we have been calculating our Daily Potential Net Profit using the volume of all activity in the market as a whole. While this usefully conveys how quickly a player’s trades might turn-over, it does not accurately represent the stated metric in most circumstances. This is because each in-game account is bound to buy limits which restrict the quantity of any unique commodity that a player can purchase on the Grand Exchange within a 4-hour window of time. While some players utilize multiple accounts, or leverage direct player-to-player trading to bypass these limits, these exceptions represent edge cases with limited scaling potential.
In the case of our above examples, the Avernic Defender Hilt and Brimstone Ring both have a buy limit of only eight units per 4-hour window. This limits a player to 48 purchases per 24-hour window, or 96 total transactions per ID if we include the converse sell orders a player would need to place if conducting short-term trades on the High-Low spread.
In the case of the Avernic Defender Hilt, total trade volume within our reported window was 383 units. As a result, a player using a single account could potentially control a quarter of all trade activity for this item. This is in stark contrast with the Brimstone Ring, which traded at a daily volume of 3861 units, effectively restricting a single player to ~3% of the total market share of trade for this item.
With this information in-mind and given equal buy limits between these two item IDs, Avernic Defender Hilts are the superior choice for trading on the High-Low spread due to their higher per-unit potential profit margin.
So, lets go back to the drawing board and incorporate buy limits into our script. Since buy limits are not included in the /24h endpoint, we will need to start by fetching the /mapping endpoint data which contains the “limit” variable that we will need to proceed. We can fetch the latest /mapping data and convert it to .csv with the following script:
## Import modules
import requests
import json
import os
import pandas as pd
## Change working directory to script location
pathtorunfile = os.path.dirname(__file__)
os.chdir(pathtorunfile)
## Define url and headers to use for API call
url = 'https://prices.runescape.wiki/api/v1/osrs/mapping'
headers = {
#'User-Agent': 'You must uncomment this line and modify this string before using the API. See: https://oldschool.runescape.wiki/w/RuneScape:Real-time_Prices'
}
# define our request and query the API
jsonresponse = requests.get(url, headers=headers)
jsondata = jsonresponse.json()
df = pd.DataFrame(jsondata)
#this pop command drops the "examine" column, containing superfluous data
df.pop(df.columns[0])
print(df)
df.to_csv('mapping.csv', index=False)
...Next, we’ll want to modify our report generation template to incorporate this data into our DailyCSV table so that it can be used with our new, buy limit-aware reporting. Changes are outlined in bold:
## Import modules
import pandas as pd
import sqlite3
import os
from functools import reduce
## Change working directory to script location
pathtorunfile = os.path.dirname(__file__)
os.chdir(pathtorunfile)
## Import csv and mapping files as Pandas Dataframes and add to a list "dataframes"
DailyCSVpremap = pd.read_csv("24h.csv", sep=",")
mapping = pd.read_csv("mapping.csv", sep=",")
dataframes = [DailyCSVpremap, mapping]
# Combine DailyCSV and mapping data, using id as key
DailyCSV = reduce(lambda left,right: pd.merge(left,right,on=['id'],how='outer'), dataframes)
## Initialize temp sqlite db and import Pandas Dataframe
tempdb = sqlite3.connect(":memory:")
## Import Pandas Dataframe into temp sqlite db
DailyCSV.to_sql('DailyCSV', tempdb, if_exists='append', index=False)
cur = tempdb.cursor()
## run your queries here
cur.execute('''CREATE TABLE FinalOutput AS SELECT id, name, "limit" FROM DailyCSV;''')
## print results to terminal
FinalOutputReport = pd.read_sql('''SELECT * FROM FinalOutput;''', tempdb)
print(FinalOutputReport)
FinalOutputReport.to_csv('testlimit.csv', index=False)
...These changes use the reduce function to combine the /24h and mapping data into a single table, using “id” as a key. Since the column name “limit” is also the name of a SQLite function, we have to explicitly reference it in quotations. Running this query reveals our desired buy limit data, validating that it can now be used in subsequent queries. The /mapping endpoint also provides us with full item names which are easier to read than id numbers:
id name limit
0 2 Cannonball 11000.0
1 6 Cannon base 70.0
2 8 Cannon stand 70.0
3 10 Cannon barrels 70.0
4 12 Cannon furnace 70.0
...Great! So, how do we improve our reporting with this additional data? Our goal will be to adjust our current “Potential Daily Net Profit” value so that, rather than only looking at the product of spread and volume, we also consider the product of spread and limit, with our final result being the lower of the two values. It is important to note that there are items in the game that do not have a documented buy limit; this is typically the result of nobody in the greater OSRS community reaching and logging said limit, which simply means that the limit is arbitrarily high, and currently irrelevant. We’ll want to keep these nuances in-mind and make sure that our reporting accurately reflects them.
We can achieve this through some modifications to our most recent set of queries, with changes outlined in bold:
## run your queries here
cur.execute('''ALTER TABLE DailyCSV ADD COLUMN Tax;''')
cur.execute('''CREATE TABLE MaxTax AS SELECT * FROM DailyCSV WHERE avgHighPrice > 500000000;''')
cur.execute('''CREATE TABLE MinTax AS SELECT * FROM DailyCSV WHERE avgHighPrice <= 500000000;''')
cur.execute('''UPDATE MaxTax SET Tax = 5000000;''')
cur.execute('''UPDATE MinTax SET Tax = round((avgHighPrice * 0.01) - 0.5);''')
cur.execute('''CREATE TABLE DailyCSVwithTax AS SELECT * FROM MaxTax UNION SELECT * FROM MinTax;''')
cur.execute('''CREATE TABLE NoBuyLimit AS SELECT *, ((avgHighPrice - avgLowPrice - Tax) * ((lowPriceVolume + highPriceVolume)/2)) AS NoBuyLimitProfit FROM DailyCSVwithTax;''')
cur.execute('''CREATE TABLE WithBuyLimit AS SELECT id, ((avgHighPrice - avgLowPrice - Tax) * "limit") AS WithBuyLimitProfit FROM DailyCSVwithTax''')
cur.execute('''CREATE TABLE FinalOutput AS SELECT *, MIN(NoBuyLimit.NoBuyLimitProfit, COALESCE(WithBuyLimit.WithBuyLimitProfit, 'NONE')) AS AdjustedPotentialDailyProfit FROM NoBuyLimit, WithBuyLimit WHERE NoBuyLimit.id = WithBuyLimit.id ORDER BY AdjustedPotentialDailyProfit DESC;''')
## print results to terminal
FinalOutputReport = pd.read_sql('''SELECT id, name, AdjustedPotentialDailyProfit FROM FinalOutput;''', tempdb)
## export results to .csv
FinalOutputReport.to_csv('HighLowSpreadReport.csv', index=False)
...The “NoBuyLimit” table and resulting “NoBuyLimitProfit” column are the products of our last set of queries, reflecting what our potential profit could be without buy limits. We then introduce the “WithBuyLimit” table, returning “WithBuyLimitProfit”, reflecting our potential profit restricted by the “limit” value for any given ID. Our next query then takes the minimum between these two values, piping the result into FinalOutput as our “AdjustedPotentialDailyProfit”. The COALESCE argument returns ‘NONE’ if an item does not have a known buy limit, allowing the MIN function to default to potential profit without buy limits.
Let’s start by reviewing the Avernic Defender Hilt and Brimstone Ring following these reporting changes. The former is ranked #100 on the list, with an Adjusted Potential Daily Profit of ~2.8M GP, while the latter has returned #566 and 277K GP respectively. This expected result confirms that our incorporation of buy limits into our reporting is having the intended effect of tempering profit expectations in circumstances where buy limits impose a real restriction to trade.
Next, our top three rankings have not changed from our previous report, which can be easily explained:
- The extremely expensive ID 27277 “Tumeken’s shadow (uncharged)” does not have an established buy limit, resulting in our reported potential profit value being unchanged.
- The similarly expensive ID 12426 “3rd age longsword” has a buy limit, however daily traded volume is so low that it would be almost impossible to ever reach this limit, resulting in our reported potential profit being unchanged.
- Likewise, ID 11252 “Magpie Impling Jar” has a buy limit which also typically exceeds daily trade volume, resulting in our reported potential profit being unchanged.
The ranking of other items on our list follow a similar pattern; highly-ranked items possess non-restrictive buy limits relative to volume traded.
Step 5: The Importance of Opportunity Cost: Calculating ROI
Many of you still reading up until this point who are familiar with OSRS, or markets in-general might have noticed a potentially annoying pattern: Most of the reported items are extremely expensive. Most OSRS players do not have hundreds of millions, or billions of GP at their disposal to trade many of the items we see appearing at the top of our report results. Unlike real markets, players cannot buy fractional shares of items; purchasing rare items is an all-or-nothing affair.
3rd age equipment and other super-expensive items have been topping our lists up-until this point because these items provide the greatest potential for absolute returns regardless of investment cost. Our reporting has failed to take into account the need to allocate limited capital as efficiently as possible in-order to maximize returns.
Since no traders have unlimited GP, traders must manage the Opportunity cost of capital allocation by using their available GP to engage in trade which maximizes returns relative to investment cost. We identify efficient trades by calculating their Return on Investment (ROI). Taking ROI into account is not only critical for maximizing returns given limited capital, it also hedges against risk, as trading within the thinner margins (relative to investment cost) present in low-ROI items such as 3rd age equipment and other rare drops exposes a trader to a greater margin of error and potential loss in the event of unfavorable volatility.
So, how do we calculate ROI and incorporate this into our reporting? By dividing per-unit profit (High – Low – Tax) by per-unit investment (Low), we obtain our ROI, which we can multiply by 100 to view as a percentage.
So, lets incorporate this into our reporting, with changes outlined in bold:
(I’ve also made a slight correction to how NoBuyLimitProfit is calculated which is also outlined in bold; since trading on the High-Low spread requires both purchases and sales of an item, we need to take the minimum between High and Low volume (rather than product divided by 2) to calculate potential profit, since any asymmetry between the two creates a bottleneck.)
## run your queries here
cur.execute('''ALTER TABLE DailyCSV ADD COLUMN Tax;''')
cur.execute('''CREATE TABLE MaxTax AS SELECT * FROM DailyCSV WHERE avgHighPrice > 500000000;''')
cur.execute('''CREATE TABLE MinTax AS SELECT * FROM DailyCSV WHERE avgHighPrice <= 500000000;''')
cur.execute('''UPDATE MaxTax SET Tax = 5000000;''')
cur.execute('''UPDATE MinTax SET Tax = round((avgHighPrice * 0.01) - 0.5);''')
cur.execute('''CREATE TABLE DailyCSVwithTax AS SELECT * FROM MaxTax UNION SELECT * FROM MinTax;''')
cur.execute('''CREATE TABLE NoBuyLimit AS SELECT *, ((avgHighPrice - avgLowPrice - Tax) * (MIN(lowPriceVolume + highPriceVolume))) AS NoBuyLimitProfit FROM DailyCSVwithTax;''')
cur.execute('''CREATE TABLE WithBuyLimit AS SELECT id, ((avgHighPrice - avgLowPrice - Tax) * "limit") AS WithBuyLimitProfit FROM DailyCSVwithTax''')
cur.execute('''CREATE TABLE DailyCSVwithProfit AS SELECT *, MIN(NoBuyLimit.NoBuyLimitProfit, COALESCE(WithBuyLimit.WithBuyLimitProfit, 'NONE')) AS AdjustedPotentialDailyProfit FROM NoBuyLimit, WithBuyLimit WHERE NoBuyLimit.id = WithBuyLimit.id;''')
cur.execute('''CREATE TABLE FinalOutput AS SELECT *, ((avgHighPrice - avgLowPrice - Tax) / avgLowPrice) * 100 AS ROI FROM DailyCSVwithProfit ORDER BY AdjustedPotentialDailyProfit DESC;''')
## print results to terminal
FinalOutputReport = pd.read_sql('''SELECT id, name, lowPriceVolume, highPriceVolume, avgLowPrice, avgHighPrice, AdjustedPotentialDailyProfit, "limit", ROI FROM FinalOutput;''', tempdb)
...The value ROI provides to our reporting is most evident when reviewing our previously highest-ranked item, the “Tumeken’s shadow (uncharged)”. This super-rare and super-expensive item provides us with an ROI of 0.08% when trading on the High-Low spread, which is extremely bad. As we continue reviewing the report, most 3rd age items including the “3rd age longsword” sit in the 0.8-2.0% ROI range, with ID 23185 “Ring of 3rd age”, the least expensive 3rd age item, providing the greatest ROI of about 8%.
It is important to note that an item listing a high ROI does not automatically make it suitable for trade. While ROI takes into account the opportunity cost of capital (GP), there are countless items reporting extremely high ROI which aren’t worth trading due to poor opportunity cost of time and a character’s eight available trade slots on the GE. Because of this, we are still sorting our list by Adjusted Potential Daily Profit; having ROI readily visible allows us to temper profit projection against the required initial investment.
Getting More Data Ready
Up-until this point, we have been conducting our analysis and reporting against a single datapoint. With a granularity of 1, we are lacking any concept or understanding of time. While this has been adequate for our analysis so far, we have reached the limit of useful inferences we can make while maintaining this restriction. In-order to make further inferences, we will need to introduce the concept of time into our analysis by importing and cross-referencing more data.
Fortunately in the case of the OSRS Wiki API, in-addition to the /24h and mapping endpoints, we also have access to /5m and /1h endpoints which present volume and price data captured within the bounds of 5-minute and 1-hour windows of time respectively, allowing us to look at and compare transaction data within and between smaller windows of time, with older data being available to download by specifying a timestamp. Real-time price information can also be captured using the /latest endpoint.
The scripts I shared earlier in this article are fairly simple; they only capture the most-recent endpoint and mapping data. In-order to better leverage historical data, we need better tools. On the Projects page, I’ve shared links to source code for data fetching and report generation scripts written using Python, Pandas and SQLite. While anybody is free to peruse these scripts and review the comments, the important takeaway is that these scripts allow us to effectively leverage the granularity and history of market data rather than observing the market at a single point in-time.
With a larger dataset now at our disposal, let’s take the reporting queries we’ve developed so far and fit them into the more advanced, time-aware schema:
...
cur.execute('''CREATE TABLE MasterTableTax AS SELECT * FROM MasterTable;''')
cur.execute('''ALTER TABLE MasterTableTax ADD COLUMN Tax;''')
cur.execute('''CREATE TABLE MaxTax AS SELECT * FROM MasterTableTax WHERE round(GranularDailyMeanHigh) > 500000000;''')
cur.execute('''CREATE TABLE MinTax AS SELECT * FROM MasterTableTax WHERE round(GranularDailyMeanHigh) <= 500000000;''')
cur.execute('''UPDATE MaxTax SET Tax = 5000000;''')
cur.execute('''UPDATE MinTax SET Tax = round((GranularDailyMeanHigh * 0.01) - 0.5);''')
cur.execute('''CREATE TABLE DailyCSVwithTax AS SELECT * FROM MaxTax UNION SELECT * FROM MinTax;''')
cur.execute('''CREATE TABLE NoBuyLimit AS SELECT *, (GranularDailyMeanHigh - GranularDailyMeanLow - Tax) * 24 * MIN(GranularDailyMeanVolumeLow, GranularDailyMeanVolumeHigh) AS NoBuyLimitProfit FROM DailyCSVwithTax;''')
cur.execute('''CREATE TABLE WithBuyLimit AS SELECT id, ((GranularDailyMeanHigh - GranularDailyMeanLow - Tax) * mappinglimit) AS WithBuyLimitProfit FROM DailyCSVwithTax''')
cur.execute('''CREATE TABLE DailyCSVwithProfit AS SELECT *, MIN(NoBuyLimit.NoBuyLimitProfit, COALESCE(WithBuyLimit.WithBuyLimitProfit, 'NONE')) AS AdjustedPotentialDailyProfit FROM NoBuyLimit, WithBuyLimit WHERE NoBuyLimit.id = WithBuyLimit.id;''')
cur.execute('''CREATE TABLE FinalOutput AS SELECT *, ((GranularDailyMeanHigh - GranularDailyMeanLow - Tax) / GranularDailyMeanLow) * 100 AS ROI FROM DailyCSVwithProfit ORDER BY AdjustedPotentialDailyProfit DESC;''')
FinalOutputReport = pd.read_sql('''SELECT id, itemlistname AS name, round(GranularDailyMeanVolumeLow * 24) AS lowPriceVolume, round(GranularDailyMeanVolumeHigh * 24) AS highPriceVolume, round(GranularDailyMeanLow) AS avgLowPrice, round(GranularDailyMeanHigh) AS avgHighPrice, AdjustedPotentialDailyProfit, mappinglimit AS "limit", ROI FROM FinalOutput;''', tempdb)
...Besides some differences in the naming of some tables and columns, this script is functionally equivalent to our earlier example. The key difference here is that we are now leveraging the /1h endpoint (designated by the “Granular” prefix used throughout) and multiplying the resulting volume data by 24 to convert hourly data into daily figures to present in our report. The advantage this provides over the previous iteration of our script is better recency; because the 1h timestamp updates on an hourly basis, we can generate fresh insights hour-by-hour rather than day-by-day.
High-Low Spread Analysis Continued: Accounting for Market Changes Over Time
Besides improving recency, how can we incorporate market changes over time to further improve our reporting?
So far, our reporting does a great job at organizing data by potential profitability; items further down the list, or items with very unfavorable ROI are, all else being equal, objectively worse to trade on the High-Low spread than items ranked higher-up the list.
The presumption of ceteris paribus falls apart the second we stop analyzing items within a time vacuum. As we’ll come to find, many items appearing on our reports provide inaccurate or deceptive profit expectations. Our goal in this section will be to leverage market history in-order to identify examples of items possessing poor or otherwise undesirable market history and use this information to filter our results down to a much smaller and succinct list of desirable items to trade on the High-Low spread.
Step 6: The Importance of Real-World Trading: Identifying and Filtering Gold Farming Activity
When we first started calculating ROI, one highly-ranked item on that report stood out from the rest. ID 11463 “Agility mix(1)” was ranked #6 in-terms of Adjusted Potential Daily Profit. Unlike the other highly-ranked items, this item reported an extremely high ROI of 200%.
Not only is Agility mix(1) not suitable for trading on the High-Low spread, it is by all accounts an objectively less than worthless item within the game. For some context, this item is virtually equivalent to a half-finished bottle of Orbitz soft drink. Not only does this item provide limited/no utility on its own, manufacturing this item represents a catastrophic failure of opportunity cost, as its intermediate ingredients could otherwise be used to manufacture more-useful products. Its scarcity is only the result of it failing to serve as a useful substitute good; just like Orbitz soft drinks, Agility Mix potions, whether full or half-finished, are just a novelty product.
So, why has it appeared in our reporting? We can understand this a bit better by checking the OSRS Wiki Prices page for historical prices and volume:
The Low price for this item (outlined in green) has often reported being greater than its High price. Inversions of the High and Low prices like this are extremely unusual; in the case of Agility Mix(1) this appears to have happened at least three times with an extremely large spread of 500K GP.
Given the established less-than-worthless nature of this item, this price is completely farcical. This is increasingly evident considering that there is virtually no consistent day-to-day or hour-to-hour movement of volume for this item at all. We just happened to capture data on a day where an anomalous burst of exchange occurred.
So, why are there occasional bursts of trade activity for this less-than-worthless item? If we look at the data for January 26th 2024, we see that 967 units were exchanged at a High price of 421GP each, while 961 units were exchanged at a Low price of 500K GP each. These transactions represent almost 500 million GP changing hands over a completely worthless item, which makes absolutely no sense… until we consider the prevalence of real-world trading in OSRS.
Like many other online games (and in some less-than-stellar real-world economies), there is an unsanctioned black market for exchanging the in-game currency, GP, with real-world currencies. What we have likely witnessed with this particular commodity is an example of real-world traders obfuscating their trades through the Grand Exchange, using a less-than-worthless commodity with virtually zero organic trade activity as a medium of exchange.
I would speculate further that the very limited day-to-day transactions that we do see are most likely the result of real-world traders probing the market to ensure that there are no open buy or sell orders opened by other players before committing to a real-world trade. This type of behavior is further supported by limitations the Grand Exchange places on the quantity of open trade offers allowed per-player, which effectively disincentivizes leaving offers open for many extremely low-volume, low-value items. This fosters the prevalence of “dead” or “zombie” items being listed on the Grand Exchange which possess no open offers, providing the necessary conditions for laundering and selling GP in the real world.
As much as economic models like to approach markets as though they exist in vacuums, none actually do; real-world trading is a major component of OSRS that must be taken into account in-order to make any sense of the game’s market behavior as a whole.
So, how do we account for real-world trading in our reporting? While we won’t be able to account for every example, we can start by recognizing that, in the context of trading on the High-Low spread, this particular item is just about as useless as any item gets.
While it would be easy to just pick this item out of our report by setting a hard filter, there are many other items just like this one which will continue to appear in our reports. There is also a risk that the game developers might re-balance and modify this item, rendering it useful and thus potentially desirable to trade in the future. So, instead of setting a hard filter, we should identify the qualities that make this item, and others like it, unsuitable for our desired trading strategy:
- First, we have an unnatural High-Low price inversion which appeared a few weeks before our reporting window. While it is possible for slight inversions like this to present themselves under normal conditions, especially on days where price is especially volatile or moving in a single direction, in no circumstance will these inversions naturally occur at the magnitude or duration seen with Agility Mix(1). So, we should exclude items that have traded in the recent past at a significantly inverted spread.
- Next, we have the sporadic movement of volume. Volume traded for this item is concentrated within a few select hours, on a few select days in the past. The item’s trade history is otherwise extremely sparse on an hour-to-hour or day-to-day basis. While this isn’t as unnatural of a phenomenon as the inverted High-Low price history, this trade behavior isn’t suitable for trading on the High-Low spread, as we want trades to be executed frequently and consistently enough to appreciate consistent rates of return. So, we should exclude items experiencing little/no trade volume on an hour-to-hour or day-to-day basis.
If we execute our new report generation script against a more recent set of market data (February 28th 2024), it seems like “Agility mix(1)” has disappeared from our report. This is to be expected, as this item was never suitable for trade in the first place. By applying what we’ve learned about this item into our queries, we can cross-reference results and see if the items being removed share the poor attributes we identified.
So, let’s see if we can translate these poor trade qualities into SQLite WHERE clauses, starting with the High-Low inversion:
...
FinalOutputReport = pd.read_sql('''SELECT id, itemlistname AS name, round(GranularDailyMeanVolumeLow * 24) AS lowPriceVolume, round(GranularDailyMeanVolumeHigh * 24) AS highPriceVolume, round(GranularDailyMeanLow) AS avgLowPrice, round(GranularDailyMeanHigh) AS avgHighPrice, AdjustedPotentialDailyProfit, mappinglimit AS "limit", ROI FROM FinalOutput WHERE MonthlyMaxHigh > MonthlyMaxLow;''', tempdb)
...MonthlyMaxHigh is calculated by taking the average High prices for each day over the past month and selecting the maximum value. MonthlyMaxLow is calculated the same way against Low price.
The inclusion of this WHERE clause effectively filters-out any item where any maximum Low price encountered on a given day in the last month exceeds any other maximum High price. We are deliberately using datapoints provided by the less-granular /24h endpoint here, because this helps prevent the elimination of items which have experienced temporary or otherwise insignificant High-Low inversions. The items which are filtered by this clause are those which have experienced inversions so significant or long-lasting that they have dominated an entire day’s worth of price history and have also persisted against the backdrop of a month’s worth of volatility.
So, let’s compare our results before and after adding this clause to our script. Going down our list, the first item to get filtered was ranked at #29, ID: 2237, “Premade w’m crun'”.
If we spot-check this item’s price and volume history via the OSRS Wiki Prices page, we’ll see some very familiar-looking historical price and volume graphs:
… This item almost exactly matches the behavior of Agility Mix(1). For some context, “Premade w’m crun” is an almost useless item within the game which can be purchased from NPC traders at a fraction of the prices seen on the Grand Exchange. This item is clearly being used to conduct real-world trading; each sudden spike in volume represents an exchange of approximately 180 million GP.
So, our inclusion of this clause is working as-intended! Let’s move onto our next WHERE clause:
...AND MonthlyMedianVolumeHigh > 0 AND MonthlyMedianVolumeLow > 0 AND GranularDailyMedianVolumeHigh > 0 AND GranularDailyMedianVolumeLow > 0;''', tempdb)
...MonthlyMedianVolumeHigh is calculated by taking the average High volume for each day over the past month and selecting the median value. MonthlyMedianVolumeLow is calculated the same way against Low volume. The “GranularDaily” counterparts perform the same calculations, but are instead calculated against all hours within the past day.
These clauses eliminate any items which experience zero movement of High or Low volume, either within more than half of the past 24 hours, or within more than half of the past 30 days. We use median rather than mean in this circumstance, because median values provide a better representation of the volume a trader is most likely to encounter within a given frame of time. This effectively fulfills our second filtering condition, as it encompasses all items which trade infrequently, even if the trades that do occur involve relatively large quantities of volume.
So, how do these results compare with our last results?
Working down our list, the very first item to disappear is ranked #5, ID: 26353 “Ancient mix(1)”. Just like our real-world trading examples covered earlier, this item is also similarly-worthless and possesses similarly suspect trade history. The key difference with Ancient mix(1) is the absence of an inverted Buy-Sell spread.
Further down our list, other notable examples of filtered items include many pieces of 3rd age equipment. This is to be expected, because their median trade volume on an hour-by-hour basis is very likely to be zero for the rarest and most expensive ids. While these are not trademark examples of real-world trading zombie items, they do possess some qualities (low frequency of trades and poor ROI) which otherwise make them undesirable for trading on the High-Low spread.
Step 7: The Importance of Preferences: Fine-Tuning Reports to Fulfill our Trading Strategy:
With some objectively undesirable items now excluded from our report, what else should we filter? Currently, almost half of the items available in-game appear on our report. We should hone this down further so that our results reflect items which best suit our desired trading strategy, as well as our personal preferences and limitations.
Some examples include:
- Filtering IDs reporting potential profit, volume, or ROI below set thresholds, which can be useful in circumstances where we desire either higher absolute returns, faster transactions, better relative returns, or a combination of each.
- Filtering IDs reporting Low price above a set threshold, which can be useful in circumstances where we may not have enough liquidity or risk tolerance to purchase a particularly expensive item.
- Filtering IDs reporting large price/volume differences between the present and past, which can be useful in circumstances where we may not wish to trade items with a volatile trade history, including large spikes or dips in price.
- Filtering IDs where real-time data indicates no present profit potential, which can be useful to signify whether or not an item can be traded in this exact moment within a profitable spread
These small tweaks can be baked into SQLite queries, allowing us to better-target desirable items to trade. This is especially poignant when considering time-sensitive market opportunities where we cannot afford the time needed to manually sift through hundreds or thousands of items. As our preferences change, so too can our queries.
Let’s incorporate some preferential filtering into our WHERE clause:
... WHERE ROI > 4 AND AdjustedPotentialDailyProfit > 500000 AND MIN(GranularDailyMeanVolumeHigh, GranularDailyMeanVolumeLow) > 4 AND (MonthlyMeanVolumeLow + MonthlyMeanVolumeHigh) > 12 * (GranularDailyMeanVolumeLow + GranularDailyMeanVolumeHigh) AND (high - low - tax) > 0 AND MonthlyMaxHigh > MonthlyMaxLow AND MonthlyMedianVolumeHigh > 0 AND MonthlyMedianVolumeLow > 0 AND GranularDailyMedianVolumeHigh > 0 AND GranularDailyMedianVolumeLow > 0;''', tempdb)
...- The first clause eliminates any results where ROI is less than 5. Traders starting with limited capital will want to prioritize items with greater ROI, while wealthy traders may find lower-ROI trades more lucrative. Setting this to 5 provides us with a baseline that we can adjust if desired in the future.
- The next clause eliminates any items where Adjusted Potential Daily Profit is less than 500k GP. This weeds-out items which may have good ROI, but otherwise possess poor return on a trader’s time or available trade slots. This, again, could be adjusted to better suit an individual’s own tolerances and wealth.
- The next clause eliminates any items where the volume traded within the past 24 hours is more than double the daily average of the past month. This tempers our reporting against recent supply-side shocks, filtering-out results which may be experiencing higher than normal volatility.
- Finally, our last new clause eliminates any items where the real-time spread provided by the /latest endpoint indicates zero profit. This eliminates items demonstrating no potential profitability in this moment using the latest price data available.
Prior to adding these WHERE clauses our report contained ~1700 items. With the combined filtering capacity of these additional clauses, we have narrowed this down to just 47.
After performing a cursory review of the the top ~20 results… I am very satisfied with the results. Pretty much all of the listed items demonstrate fairly consistent High-Low spreads which maintain profitability in light of taxation. They all trade and exchange volume fairly consistently. While some items experience volatility, there are no extreme supply shocks, and none appear to be RWT zombie items. All-in-all, a great baseline report!
Step 8: The Importance of Convenience: Report Automation
Up-until now, I have been sandboxing the scripting for this article within an IDE, using Libreoffice Calc to view the resulting reports. While this is great for testing and prototyping, it is a clunky way to actually use the resulting data.
With the help of some more Python and PHP, I’ve fully-automated the generation of this report, which has been published on a page on my Projects tab. This report is automatically refreshed every minute, providing a convenient and easy way to view the latest report results from anywhere on the web.
If you’re interested in generating their own reports using data provided by the OSRS Wiki, please be sure to respect their API guidelines. There is always room for improvement and refinement of reporting strategies, and in the case of the High-Low spread, I am sure there are OSRS players who might have slightly different trading preferences or objectives.
I hope this article has been insightful; I will be covering additional market inefficiencies in subsequent articles, so please stay tuned for more analysis and scripting examples!
Thank you for reading!
*Update: “Virtual Markets, Part Three: Price Floors (and more!)” is now live!