function loadmarkethistory(priceIDs,regionID){
if (typeof regionID == 'undefined'){
regionID=10000002;
}
if (typeof priceIDs == 'undefined'){
throw 'Need a list of typeids';
}
var prices = new Array();
var dirtyTypeIds = new Array();
var cleanTypeIds = new Array();
var url="https://www.mokaam.dk/API/market/items?regionid="+regionID+"&typeid="
priceIDs.forEach (function (row) {
row.forEach ( function (cell) {
if (typeof(cell) === 'number' ) {
dirtyTypeIds.push(cell);
}
});
});
cleanTypeIds = dirtyTypeIds.filter(function(v,i,a) {
return a.indexOf(v)===i;
});
prices.push([ 'TypeID',
'Last updated',
'Vol. yesterday',
'Median vol. last week',
'Median vol. last month',
'Median vol. last quarter',
'Median vol. last year',
'Avg. price yesterday',
'Median avg. price last week',
'Median avg. price last month',
'Median avg. price last quarter',
'Median avg. price last year',
'Order count yesterday',
'Order count last week',
'Order count last month',
'Order count last quarter',
'Order count last year',
'Size yesterday',
'Median size last week',
'Median size last month',
'Median size last quarter',
'Median size last year',
'Highest price yesterday',
'Median highest price last week',
'Median highest price last month',
'Median highest price last quarter',
'Median highest price last year',
'Highest price yesterday',
'Higest price last week',
'Highest price last month',
'Highest price last quarter',
'Highest price last year',
'Lowest price yesterday',
'Median lowest price last week',
'Median lowest price last month',
'Median lowest price last quarter',
'Median lowest price last year',
'Lowest price yesterday',
'Lowest price last week',
'Lowest price last month',
'Lowest price last quarter',
'Lowest price last year',
'Spread yesterday',
'Median spread last week',
'Median spread last month',
'Median spread last quarter',
'Median spread last year',
'VWAP last week',
'VWAP last month',
'VWAP last quarter',
'VWAP last year',
'52 week low',
'52 week high',
'Std dev week',
'Std dev month',
'Std dev quarter',
'Std dev year'])
var parameters = {method : "get", payload : ""};
var o,j,temparray,chunk = 100;
for (o=0,j=cleanTypeIds.length; o < j; o+=chunk) {
temparray = cleanTypeIds.slice(o,o+chunk);
Utilities.sleep(100);
var types=temparray.join(",").replace(/,$/,'')
var jsonFeed = UrlFetchApp.fetch(url+types, parameters).getContentText();
var json = JSON.parse(jsonFeed);
if(json) {
for(i in json) {
var price=[parseInt(i),
json[i].last_data,
parseFloat(json[i].vol_yesterday),
parseFloat(json[i].vol_week),
parseFloat(json[i].vol_month),
parseFloat(json[i].vol_quarter),
parseFloat(json[i].vol_year),
parseFloat(json[i].avg_price_yesterday),
parseFloat(json[i].avg_price_week),
parseFloat(json[i].avg_price_month),
parseFloat(json[i].avg_price_quarter),
parseFloat(json[i].avg_price_year),
parseFloat(json[i].order_count_yesterday),
parseFloat(json[i].order_count_week),
parseFloat(json[i].order_count_month),
parseFloat(json[i].order_count_quarter),
parseFloat(json[i].order_count_year),
parseFloat(json[i].size_yesterday),
parseFloat(json[i].size_week),
parseFloat(json[i].size_month),
parseFloat(json[i].size_quarter),
parseFloat(json[i].size_year),
parseFloat(json[i].high_yesterday),
parseFloat(json[i].high_week),
parseFloat(json[i].high_month),
parseFloat(json[i].high_quarter),
parseFloat(json[i].high_year),
parseFloat(json[i].ab_high_yesterday),
parseFloat(json[i].ab_high_week),
parseFloat(json[i].ab_high_month),
parseFloat(json[i].ab_high_quarter),
parseFloat(json[i].ab_high_year),
parseFloat(json[i].low_yesterday),
parseFloat(json[i].low_week),
parseFloat(json[i].low_month),
parseFloat(json[i].low_quarter),
parseFloat(json[i].low_year),
parseFloat(json[i].ab_low_yesterday),
parseFloat(json[i].ab_low_week),
parseFloat(json[i].ab_low_month),
parseFloat(json[i].ab_low_quarter),
parseFloat(json[i].ab_low_year),
parseFloat(json[i].spread_yesterday),
parseFloat(json[i].spread_week),
parseFloat(json[i].spread_month),
parseFloat(json[i].spread_quarter),
parseFloat(json[i].spread_year),
parseFloat(json[i].vwap_week),
parseFloat(json[i].vwap_month),
parseFloat(json[i].vwap_quarter),
parseFloat(json[i].vwap_year),
parseFloat(json[i]._52w_low),
parseFloat(json[i]._52w_high),
parseFloat(json[i].std_dev_week),
parseFloat(json[i].std_dev_month),
parseFloat(json[i].std_dev_quarter),
parseFloat(json[i].std_dev_year)];
prices.push(price);
}
}
}
return prices;
}
Mokaam's historic market data
This site is intended as a supplement to Adam4eve,
Fuzzwork, and the ESI from CCP.
It provides an easy way to pull data based on the historic market data in EVE online without making a query
for every item. Data is based on the market history endpoint from the ESI as well as the tracker and market history end points from the Adam4eve API
The API was made with the Oz community trading sheet in mind so the implementation is heavily based on how Fuzzwork implements his API in Google Sheet.
The Oz community sheet can be found on Oz's discord under #faq.
It is a project created as a learning experience for myself and is running on my own server, so if the response times are slow or the site doesn't work,
the reason
can be the hardware, the code, or anything in between and is most likely due to n00bishness from my side.
Huge thanks to Steve from Fuzzwork, Ethan from Adam4eve, as well as Shikkoken and Bonsailinse from the Oz community for invaluable help, discussions,
and advice.
Also Big thanks to Phaelim O'Neil (Lanarion on discord) from the trading community on Oz's discord, for writing the guide on calling the API from Excel
and creating the examples.
How to format calls
MAKE SURE YOUR APPS USE THE API PROPERLY!
FORMAT REQUESTS WITH COMMA SEPARATED TYPEIDS - DONT MAKE A REQUEST FOR EACH INDIVIDUALLY!
I will block User Agents and IP addresses if they hit my server too hard. If you get the status code 403 instead of 200 contact Mokaam on Oz's discord or in game Mokaam Racor
If you're scraping the entire database for a region, use the "all" end point
There are currently 10 regions available for the ESI data:
The Forge with region id: 10000002
Domain with region id: 10000043
Lonetrek with region id: 10000016
Sinq Laison with region id: 10000032
Delve with region id: 10000060
Perrigen Falls with regionid: 10000066
Metropolis with region id: 10000042
Heimatar with region id: 10000030
Vale of the Silent with region id: 10000003
Fountain with region id: 10000058
and 3 regions available for the A4E data:
The Forge with region id: 10000002
Sinq Laison with region id: 10000032
Domain with region id: 10000043
Requests can be formated to call data for one or more items from a region (switch out '../items?..' with '../orders?..' and change the data to parse if you want the statistics based on the A4E data):
https://mokaam.dk/API/market/items?regionid=10000002&typeid=34,35
Requests can be formated to call all data from a region (only works for the ESI data). Be carefull not to use this call in Google Sheet as it will return too much data for Google Sheet to handle
https://mokaam.dk/API/market/all?regionid=10000002
You can get all the type ids I serve data for with this request (Data is only updated once per week so no need to pull every time your sheet changes):
https://mokaam.dk/API/market/type_ids
Data returned from API:
For '../market/items?..'
Based on the data from the ESI market history end point. The data the statistics are calculated on are actual
trades performed. If no price data was found for a specific type id on a specific day in a specific region, values from the day before is used. Sometimes data is missing on days from the raw data from the ESI. In those cases values are found using interpolation. Due to the
restrictions from CCP on the rate at which data can be acquired, the process of updating the 10 regions will take hours.
Update starts @ 12.05 p.m. UTC and during the update you can experience an error if you try to request data. Just wait 10 min and try again. If the problem persists or it is not within the timeframe indicated here, please let me know.
| typeid: | Typeid of the item |
|---|---|
| last_data: | Last time CCP data was updated, returns date of the newest entry, "Null" if no data from market, "Itemid not found" or "ERROR: {error_code}" if update from CCP gave an error |
| vol_{time}: | Median volume of trades on the market for {time} = yesterday, last week, month, quarter, or year respectively |
| avg_price_{time}: | Median average price for trades for {time} = yesterday, last week, month, quarter, or year respectively |
| order_count_{time}: | Median number of orders fulfilled for {time} = yesterday, last week, month, quarter, or year respectively |
| size_{time}: | Median size of market
$$Size = Price_{avg} ⋅ Volume$$ for {time} = yesterday, last week, month, quarter, or year respectively |
| high_{time}: | Median highest price traded for {time} = yesterday, last week, month, quarter, or year respectively |
| ab_high_{time}: | Highest traded price within 4 standard deviations for {time} = yesterday, last week, month, quarter, or year respectively |
| low_{time}: | Median lowest price traded for {time} = yesterday, last week, month, quarter, or year respectively |
| ab_low_{time}: | Lowest traded price within 4 standard deviations for {time} = yesterday, last week, month, quarter, or year respectively |
| spread_{time}: | Median spread of trades on the market for {time} = yesterday, last week, month, quarter, or year respectively |
| vwap_{time}: | Volume weighted average price for {time} = last week, month, quarter, or year respectively Calculated as:
$$VWAP = {∑↙{today}↖{time}Volume ⋅ Price_{avg}}/{∑↙{today}↖{time}Volume}$$ |
| _52w_{high/low}: | 52 week highest or lowest average price traded respectively |
| std_dev_{time}: | Standard deviation on average traded price for {time} = last week, month, quarter, or year respectively |
For '../market/orders?..'
This endpoint returns unreliable data
I will fix it asap, but have no eta
Based on the data from A4E market history and the A4E tracker. Please be aware that the data from the A4E market history end point the
statistics are calculated on are not actual trades but rather orders on the market across the day.
The 'Buy from sell' and 'Sell to buy'
statistics are calculated based on data collected with the "withGone" parameter in the API set to 0 (or "has_Gone" in the statics = 1), thus
being the lower bound of the estimate and also what Adam4Eve uses.
If no data was found for a typeid-regionid-date combo the data from the day before is used
for price data in regards to the A4E markethistory data, the volume is set to 0 in the A4E market history data and all data from the tracker
is set to 0 if no data is found.
Data is updated every day at 06:00 a.m. UTC and takes about 1h to complete. In that timeframe you can get errors if the database is busy.
| type_id: | Typeid of the item |
|---|---|
| region_id: | Regionid for the data |
| date: | Date of the data generated |
| buy_price_avg_{time}: | Median of the average price point across the day of the highest buy order for {time} = last week, month, quarter, or year respectively |
| sell_price_avg_{time}: | Median of the average price point across the day of the lowest sell order for {time} = last week, month, quarter, or year respectively |
| buy_vol_avg_{time}: | Median of the average total quantity in market buy orders across the day for {time} = last week, month, quarter, or year respectively |
| sell_vol_avg_{time}: | Median of the average total quantity in market sell orders across the day for {time} = last week, month, quarter, or year respectively |
| spread_{time}: | Median of the difference between the average price point across the day of the highest buy order and the average price point across the day of the lowest sell order for {time} = last week, month, quarter, or year respectively |
| buy_std_{time}: | Standard deviation for the average price point across the day of the highest buy order for {time} = last week, month, quarter, or year respectively |
| sell_std_{time}: | Standard deviation of the average price point across the day of the lowest sell order for {time} = last week, month, quarter, or year respectively |
| s2b_vol_traded_avg_{time}: | Median of the volume of the traded sell to buy order trades for {time} = last week, month, quarter, or year respectively |
| bfs_vol_traded_avg_{time}: | Median of the volume of the traded buy from sell order trades for {time} = last week, month, quarter, or year respectively |
| s2b_price_traded_avg_{time}: | Median of the average price of the traded sell to buy order trades for {time} = last week, month, quarter, or year respectively |
| bfs_price_traded_avg_{time}: | Median of the average price of the traded buy from sell order trades for {time} = last week, month, quarter, or year respectively |
| s2b_size_{time}: | Median of the size (total isk traded) of the market of the traded sell to buy order trades for {time} = last week, month, quarter, or year respectively |
| bfs_size_{time}: | Median of the size (total isk traded) of the market of the traded buy from sell order trades for {time} = last week, month, quarter, or year respectively |
| s2b_vwap_{time}: | Volume weighted average price calculated as
$$VWAP = {∑↙{today}↖{time}Volume ⋅ Price_{avg}}/{∑↙{today}↖{time}Volume}$$ of the traded sell to buy order trades for {time} = last week, month, quarter, or year respectively |
| bfs_vwap_{time}: | Volume weighted average price calculated as
$$VWAP = {∑↙{today}↖{time}Volume ⋅ Price_{avg}}/{∑↙{today}↖{time}Volume}$$ of the traded buy from sell order trades for {time} = last week, month, quarter, or year respectively |
| s2b_std_{time}: | Standard deviation of average price of the traded sell to buy order trades for {time} = last week, month, quarter, or year respectively |
| bfs_std_{time}: | Standard deviation of average price of the traded buy from sell order trades for {time} = last week, month, quarter, or year respectively |
| total_size_{time}: | Median of total size (total isk traded) of market, sell to buy and buy from sell orders traded combined for {time} = last week, month, quarter, or year respectively |
| total_vol_{time}: | Median of total vol of market, sell to buy and buy from sell orders traded combined for {time} = last week, month, quarter, or year respectively |
| total_price_{time}: | Median of total weighted average price for sell to buy and buy to sell orders traded for {time} = last week, month, quarter, or year respectively |
How to call the API in Google Sheet
The API is called in Google Sheet in the same way as Fuzzwork calls his. Below is a script function to implement in Google Sheet to pull data. I highly suggest to only parse the data you need into Google Sheet
How to call the API in Excel
The API is called from Excel by using Power Query. It requires either Excel 365 or PowerBI for the following guide to work.
I will use an example so all codes should be modified with your own cells, names and ranges.
Before we get started a few steps has to be done in Excel to ready the data for Power Query. First create a column with itemids (or use the one you have in your current sheet) and a cell with the regionid of the region to get data from.
Then in a new cell join the itemids:
=TEXTJOIN(",",TRUE,!A2:A10)
This returns a concatenated, comma seperated text string that represents all the itemids.
Now either format the cell with the regionid as text or create a new cell to convert the regionid from a number to text by using:
=TEXT(C2,0)
Define a name for the joined itemids and the regionid in the Name Manager. Go to Name Manager in the "Formula" tab. Press "New" and add the cells with
the joined text and the regionid with each their name. I named my itemids "list" and my regionid "regionID".
From the "Data" tab choose "From Web" and enter the following URL into the URL field and press "OK":
https://mokaam.dk/API/market/items?regionid=10000002&typeid=4
This should open the Power Query Editor, if it doesn't open, click "Queries & Connections" and double click the query. In the Power Query Editor you
might have a warning about privacy, Click "Continue" and check "Ignore Privacy Levels.." and press "Save".
Go to the "Home" tab of the Power Query Editor and choose "Advanced Editor".
Replace all code with the following and click "Done" and then click "Close & Load" under the "Home" tab in the Power Query Editor.
let
list=Excel.CurrentWorkbook(){[Name="list"]}[Content]{0}[Column1],
region=Excel.CurrentWorkbook(){[Name="regionID"]}[Content]{0}[Column1],
Source = Json.Document(Web.Contents("https://mokaam.dk/API/market/items?regionid="®ion&"&typeid="&list&" ")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {
"typeid",
"last_data",
"vol_yesterday",
"vol_week",
"vol_month",
"vol_quarter",
"vol_year",
"avg_price_yesterday",
"avg_price_week",
"avg_price_month",
"avg_price_quarter",
"avg_price_year",
"order_count_yesterday",
"order_count_week",
"order_count_month",
"order_count_quarter",
"order_count_year",
"size_yesterday",
"size_week",
"size_month",
"size_quarter",
"size_year",
"high_yesterday",
"high_week",
"high_month",
"high_quarter",
"high_year",
"ab_high_yesterday",
"ab_high_week",
"ab_high_month",
"ab_high_quarter",
"ab_high_year",
"low_yesterday",
"low_week",
"low_month",
"low_quarter",
"low_year",
"ab_low_yesterday",
"ab_low_week",
"ab_low_month",
"ab_low_quarter",
"ab_low_year",
"spread_yesterday",
"spread_week",
"spread_month",
"spread_quarter",
"spread_year",
"vwap_week",
"vwap_month",
"vwap_quarter",
"vwap_year",
"_52w_low",
"_52w_high",
"std_dev_week",
"std_dev_month",
"std_dev_quarter",
"std_dev_year"},
{"Value.typeid",
"Value.last_data",
"Value.vol_yesterday",
"Value.vol_week",
"Value.vol_month",
"Value.vol_quarter",
"Value.vol_year",
"Value.avg_price_yesterday",
"Value.avg_price_week",
"Value.avg_price_month",
"Value.avg_price_quarter",
"Value.avg_price_year",
"Value.order_count_yesterday",
"Value.order_count_week",
"Value.order_count_month",
"Value.order_count_quarter",
"Value.order_count_year",
"Value.size_yesterday",
"Value.size_week",
"Value.size_month",
"Value.size_quarter",
"Value.size_year",
"Value.high_yesterday",
"Value.high_week",
"Value.high_month",
"Value.high_quarter",
"Value.high_year",
"Value.ab_high_yesterday",
"Value.ab_high_week",
"Value.ab_high_month",
"Value.ab_high_quarter",
"Value.ab_high_year",
"Value.low_yesterday",
"Value.low_week",
"Value.low_month",
"Value.low_quarter",
"Value.low_year",
"Value.ab_low_yesterday",
"Value.ab_low_week",
"Value.ab_low_month",
"Value.ab_low_quarter",
"Value.ab_low_year",
"Value.spread_yesterday",
"Value.spread_week",
"Value.spread_month",
"Value.spread_quarter",
"Value.spread_year",
"Value.vwap_week",
"Value.vwap_month",
"Value.vwap_quarter",
"Value.vwap_year",
"Value._52w_low",
"Value._52w_high",
"Value.std_dev_week",
"Value.std_dev_month",
"Value.std_dev_quarter",
"Value.std_dev_year"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value",{"Value.typeid"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ( [Value.last_data] <> "ERROR: 404" and
[Value.last_data] <> "ERROR: 502" and
[Value.last_data] <> "Itemid not found" and
[Value.last_data] <> "Null"))
in
#"Filtered Rows"