So ive been trying to figure out how to import market data, specifically from the amarr trade hub, to a spreadsheet for industry purposes, but i cant for the life of me figure it out.
I was able to get SOME numbers using the eve marketer api, but the numbers are all wrong. I’m pretty sure its because its querying all the data from quite a while back. Can anyone give me an idiot proof step by step guide to using the eve marketer api to import current highest buy orders and lowest sell orders?
Well, I can’t give you instructions for google sheets specifically, but I’ve done it in excel. Also, I don’t particularly feel like writing up complete instructions. But, since you have gotten to the point where it is pulling market data, I don’t think it’s necessary anyway… well, hopefully it isn’t necessary.
Anyway, I can think of two potential problems off of the top of my head -you either set up your web query address wrong, or you’re misinterpreting the data.
First, your web query address should look something like this:
http://api.evemarketer.com/ec/marketstat?typeid=44992®ionlimit=10000002
Replace 44992 with the typeid of the item you want data for. And replace 10000002 with your preferred region number. For domain, that would be 10000043
Note that the typeid list is constantly expanding as new items are added to the game. Adam for Eve has been on the ball with keeping his list up to date. However, if you ever find it isn’t, do a google search for “eve typeid list”.
https://www.adam4eve.eu/info_types.php
https://www.adam4eve.eu/info_locations.php
Now, once you’ve set everything up and imported your data, you’ll get a bunch of data that looks like this (note that I added the labels manually).
Now, you might assume that the price you want to look at is the average price, however, these labels can be misleading. That’s because (afaik) that average is not the average price of “serious” buy and sell orders, or those that have actually been filled, but of ALL buy and sell orders on the market -including the “scam” orders.
So, this begs the question then, which columns should you be looking at. Well, maybe someone else can offer their input, but I think that the percentile column is the best one to look at. I don’t know how it’s calculated, but it just seems like it’s the most accurate representation of actual prices. But what about, max column for buy order prices, and the min column for sell order prices? Well, I think that they can work, but what happens when someone isn’t paying attention, and accidentally creates a standing sell order when selling to standing buy order (i.e. they had duration set to three months instead of immediate, and they had more units than the buy order wanted)? Now, all of a sudden, the reported standing sell order price is going to be more indicative of buy order prices, than it is of sell order prices (hopefully that made sense). And, I think the median prices fall victim to the same problem that the average prices do, because it’s calculation includes all the scam orders. So, afaik, I think that the percentile columns are the most useful columns to look at if you want an accurate representation of what buy and sell orders are actually being filled.
Alright, hopefully that helps.
The average price should come from the API.
It takes average prices over … 3 month I think ? It’s not explained by CCP. Anyhow the history is path is ■■■■, the data are modified by CCP algorithm so don’t trust/use it.
@op :
You need to use the URL given by shipwreck but you should concatenate all the IDs with coma
eg http://api.evemarketer.com/ec/marketstat?typeid=1,2,3,4®ionlimit=1000003
in my case I have a page for the prices only, in which a URL cell that creates the URL from the ids I want the prices (they are in F2:F)
J2 : =CONCATENATE("https://api.evemarketer.com/ec/marketstat?typeid=",JOIN(",",filter(F2:F201, isnumber(F2:F201))),"®ionlimit=10000002&"&$J$1)
I limit to 200 because that’s evemarketer limit (also URL has a limit in chars)
Then I have one column for the ids
H2 : =importxml($J2,"//@id")
And another column for the BO prices
I2 : =importxml($J2,"//type/buy/max")
This is on line 2, I repeat this on line 202 if I have more than 200 items.
then I can find the BO price of items with id in B2:B with
C2 : =ARRAYFORMULA(if(ISNUMBER(B2:B),vLOOKUP(B2:B, H2:I, 2, false),))
This is what it looks like
the N/A is because it’s an item I did not register the id yet. I have another program that makes the central columns (hardcoded name to id) but you can use eg adam4eve or everef or even marketer to write it by hand.
The column A is the aggregation of the different items I use. col B is the searched id in the hardcoded cols. col C is the price .
Feel free to use this code, credits go to @CJ_Kilman for writing it up. This is built for Fuzz works API but you can easily switch the Url and some of the variables out to work with the API you want.
You only need this in one cell since it works with arrays, and it works with any length of array.
Those are 2 different functions, one is for a single hub, the other is for all the hubs.
First, what spreadsheet system are you using. Excel, Calc, Google Spreadsheets?
You will have to use JSON request anyway, but implementation is different in all systems as they use different scripting languages.
nono.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
