open All Channels
seplocked EVE Technology Lab
blankseplocked Example Google Doc spreadsheet on how to grab data from eve-central
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

Zeta Zhul
Caldari
Preemptive Paranoia
Posted - 2011.07.29 04:17:00 - [1]
 

Ok a couple things:

Google Docs Spreadsheet - Jita Mineral Prices

1. This keeps on coming up so I figured I'd put up an *example* spreadsheet. By example I mean that it is available for people to *copy*. Please don't just bookmark the spreadsheet and then just re-use it. The reason why is that if eve-central.com gets hammered by 50,000 people all trying to refresh the same spreadsheet then that will just lock the whole thing up.

So please copy the spreadsheet and then go to town and do your own thing.

2. The primary function used is called "ImportXML()".

ImportXML() has two main requirements. One is the url for the XML data which in this case is "http://api.eve-central.com/api/marketstat" and the other is a way for Google to decode the XML data. The complete URL that I use is:
Quote:
http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=39&typeid=40&typeid=11399


to see the raw XML data: Linkage

Let's separate everything out into their components:

Quote:
http://api.eve-central.com/api/marketstat

This is the base URL

Quote:
?

This tells the website that everything that follows afterwards is a parameter or information that needs to be sent to the website.

Quote:
usesystem=30000142

This is CCP's unique ID number that identifies Jita solar system.

Quote:
&

This is a separator which tells the web server that there is a new parameter that needs to be separated out from the previous one.

Quote:
typeid=34

This is CCP's unique ID number that references Tritanium.

...

Ok now that is covered let's take a good look at the spreadsheet itself:

Column A: TypeId - this is the column that lists CCP's unique ID numbers for minerals
Column B: TypeName - This is the column that lists CCP's unique mineral names
Column C: Jita Median Price (buy & sell) - this is pretty self explanatory.
Column D: Jita Median Buy Price - same
Column E: Jita Median Sell Price - same

Now the workhorse is this:
Quote:
=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$A2:$A9), "/evec_api/marketstat/type/all/median")


The difficult part is this:
Quote:
"http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$A2:$A9)


The more difficult to understand part is probably this:
Quote:
&typeid="&JOIN("&typeid=",$A2:$A9)


"JOIN()" is a function in Google Docs spreadsheet that basically concatenates, or adds together, the content of a range of cells. In this case it is combining the TypeID numbers in the range "$A2:$A9" or Column A, cell 2 to cell 9. The "$" is a code that tells the spreadsheet to not change the Column to anything else if this gets copied.

The "&typeid=" part of the JOIN() function is what is called the delimiter. If you were to change it from "&typeid=" to say "&t=" then instead of seeing
Quote:
http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=39&typeid=40&typeid=11399

You would get instead
Quote:
http://api.eve-central.com/api/marketstat?usesystem=30000142&t=34&t=35&t=36&t=37&t=38&t=39&t=40&t=11399

The problem though is that Eve-Central.com doesn't like "&t=" and wants to see "&typeid=". Now instead of using the JOIN() function you could just manually write it all out. But it's easier to just use the JOIN() especially since it has been put in there already.


Zeta Zhul
Caldari
Preemptive Paranoia
Posted - 2011.07.29 04:23:00 - [2]
 

Continued ...

The last part is this:
Quote:
"/evec_api/marketstat/type/all/median"


Please bring up this link in another browser window.

This is the XPath, hang on, which is a special code used to decode specific parts of XML data. If you look at the above webpage you will see that there is a definite structure to it. This structure can be decoded by the spreadsheet by using XPath.

The structure is:
Quote:

<evec_api version="2.0" method="marketstat_xml">
<marketstat>
<type id="34">
<all>
<median>3.48</median>


The XPath code we use in the ImportXML() function is this:
Quote:
"/evec_api/marketstat/type/all/median"


I'm pretty sure you can see the link between them.

As an example to get the Sell Volume instead of the Sell Median Price you would need
Quote:
"/evec_api/marketstat/type/sell/volume"


To get the Max Buy Price you would use
Quote:
"/evec_api/marketstat/type/buy/max"


Hope this works out for you. Have a happy. Auf wiedersehen. Now go away. Very Happy

Devai Starchild
Caldari
Posted - 2011.07.29 05:20:00 - [3]
 

Edited by: Devai Starchild on 29/07/2011 06:43:04
Since google docs only updates the data from xml every 2 hours you can add

minQ="&1+round(second(now())/10,0)

Such as:
=importXml("http://api.eve-central.com/api/marketstat?&typeid=0000&usesystem=30000142minQ="&1+round(second(now())/10,0),"//buy/max")

Eve-central doesn't appear to accept any random variable so what this does it makes it so that it ignores orders under 1-7 quantity and changes this number every so often. This makes the url change and forces google docs to import new data.

I don't believe eve-central updates live. It might only update every 10 minutes or so but it is better than the 2 hours that google docs updates.

------

Volume appears to give the quantity for buy or sale up on the market. Is there any way to get the quantity bought or sold over the past day?

Devai Starchild
Caldari
Posted - 2011.07.29 11:14:00 - [4]
 

Also to get an itemid, the easiest way I have found is to link it in chat then copy and paste it to an out of game notepad.

EdwardNardella
Capital Construction Research
Posted - 2011.07.29 17:35:00 - [5]
 

Sweet, I think I will be able to make good use of this information. Thanks!
/Bookmarked for later use

Piemang
Posted - 2011.07.29 23:54:00 - [6]
 

Just out of curiosity, what would your formula look like if you wanted to just pull the median buy price of an item?

Devai Starchild
Caldari
Posted - 2011.07.30 00:26:00 - [7]
 

"/evec_api/marketstat/type/buy/median"

?

Piemang
Posted - 2011.07.30 01:02:00 - [8]
 

Edited by: Piemang on 30/07/2011 01:09:04
Edit: Found my problem, I accidentally put *0 at the end Embarassed

kasai zenpachi
Posted - 2011.08.08 19:52:00 - [9]
 

thx for the info I will try to make good use of it.


 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only