These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

Science & Industry

 
  • Topic is locked indefinitely.
 

Excel/Eve Central Question
Author
Previous Topic Next Topic
Jas Dor
Republic University
Minmatar Republic
#1 - 2012-03-08 06:51:57 UTC
How do I automatically pull the sale price of an item (in Jita) off eve central and import it into excel?
Sesindir
The Scope
Gallente Federation
#2 - 2012-03-08 07:13:18 UTC  |  Edited by: Sesindir
There are a couple of ways, depending on what/how much info you want.

1) You can use a web query by navigating to the item's page on eve central and using that url as the target for the query. (please make sure it is set to a reasonable update time to avoid spamming their servers. This is a lot of work and requires a bit of fiddling around to get the results you are after.

2) Use the import XML function provided by eve central. This is fairly straightforward if you know the region id of jita *cough 3000014 cough* then you just need to feed it the typeid of the item you want to look up. I saw that someone had used this in their spreadsheet (sorry, cant remember who) and it works really well, while taking much less effort that the webquery.

Eve central guide to the xml query is here: http://eve-central.com/home/develop.html#xml

Region id's are found in the Technology Lab: HERE

TypeIDs are also in the Technology Lab: HERE



Hope this helps.


edit 1: forgot to add in links.

edit2: credit to iNFoRMaLiTY11 for the great work on the xml query published for free. Link to the thread with the link HERE
Snarf Aldes
University of Caille
Gallente Federation
#3 - 2012-03-08 09:54:55 UTC
Just create an url with all the items you want a price for and create a 'web query' in excel that points to that url.

Tritanium + Pyerite example:
http://eve.addicts.nl/api/prices.php?typeID=34,35

The regionID for Jita (The Forge) is 10000002 btw...

Creator of Eve Addicts
John DaiSho
Immortalis Inc.
Shadow Cartel
#4 - 2012-03-08 15:17:37 UTC
I use this command in my spreadsheet.

=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="34, "/evec_api/marketstat/type/buy/max")

If you put this into a field of your spreadsheet it will import the highest buyorder
(because of buy/max. /sell/min, sell/max, and so on. The are command for the median and so on too, i just dont remember them atm, so if anyone could add them please?)
of the item with the typeID 34 (Tritanium) in the system 30000142 (Jita).

To know which typeID an item has search for it at eve central and look at the link the site has then.
For example if i search for a Megathron i get this link
http://eve-central.com/home/quicklook.html?typeid=641

there you can see that a Megathron has the TypeID 641.

o/ John
Sesindir
The Scope
Gallente Federation
#5 - 2012-03-08 22:57:26 UTC
I stand corrected.

Region ID of JIta (The Forge) is 10000002 as Snarf said, but the actual system ID for Jita is 30000142.

That's what happens when you post at the end of a nightshift Roll
Jas Dor
Republic University
Minmatar Republic
#6 - 2012-03-09 05:49:53 UTC
John DaiSho wrote:
I use this command in my spreadsheet.

=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="34, "/evec_api/marketstat/type/buy/max")

If you put this into a field of your spreadsheet it will import the highest buyorder
(because of buy/max. /sell/min, sell/max, and so on. The are command for the median and so on too, i just dont remember them atm, so if anyone could add them please?)
of the item with the typeID 34 (Tritanium) in the system 30000142 (Jita).

To know which typeID an item has search for it at eve central and look at the link the site has then.
For example if i search for a Megathron i get this link
http://eve-central.com/home/quicklook.html?typeid=641

there you can see that a Megathron has the TypeID 641.

o/ John


I cut and pasted this into excel and got an error message. Is there some way I need to format the cell to use this command?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#7 - 2012-03-09 10:53:38 UTC
Jas Dor wrote:
John DaiSho wrote:
I use this command in my spreadsheet.

=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="34, "/evec_api/marketstat/type/buy/max")

If you put this into a field of your spreadsheet it will import the highest buyorder
(because of buy/max. /sell/min, sell/max, and so on. The are command for the median and so on too, i just dont remember them atm, so if anyone could add them please?)
of the item with the typeID 34 (Tritanium) in the system 30000142 (Jita).

To know which typeID an item has search for it at eve central and look at the link the site has then.
For example if i search for a Megathron i get this link
http://eve-central.com/home/quicklook.html?typeid=641

there you can see that a Megathron has the TypeID 641.

o/ John


I cut and pasted this into excel and got an error message. Is there some way I need to format the cell to use this command?


That's a google calc formula, unfortunately.

On the other hand, you're using excel. This makes life easy (at least if it's 2010. I don't have the lower ones to work with)

Just do to the data ribbon, select 'from web', and paste in the url http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34
Hit go. Wait for a short time then hit import. Hit ok when it complains about the schema.
select where you want the data to go. A new worksheet makes sense.

You should now have the data imported. And it can be refreshed too.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jaqa
Royale Strategic Imperative
#8 - 2012-03-10 17:14:11 UTC

Quote:

Just do to the data ribbon, select 'from web', and paste in the url http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34
Hit go. Wait for a short time then hit import. Hit ok when it complains about the schema.
select where you want the data to go. A new worksheet makes sense.

You should now have the data imported. And it can be refreshed too.


Anyway to fix the schema issue?

The Original Jaqalope Pilot of the Transportosaurus Rex 100% Free Range
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#9 - 2012-03-10 17:51:55 UTC
Jaqa wrote:

Quote:

Just do to the data ribbon, select 'from web', and paste in the url http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34
Hit go. Wait for a short time then hit import. Hit ok when it complains about the schema.
select where you want the data to go. A new worksheet makes sense.

You should now have the data imported. And it can be refreshed too.


Anyway to fix the schema issue?


I suspect it's because there isn't a DTD or similar in the xml. But that's just an educated guess. it's not a big deal. Doesn't stop it working.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jaqa
Royale Strategic Imperative
#10 - 2012-03-11 07:58:31 UTC
True, but then the issue becomes one of convenient/consistent formatting.

The Original Jaqalope Pilot of the Transportosaurus Rex 100% Free Range
Tinu Moorhsum
Random Events
#11 - 2012-03-11 11:59:35 UTC
Anybody have an idea how to get these calls into OpenOffice Calc?

I can get it to work if I first put it in Excel or Googledocs and then save it in OpenOffice format but I can't seem to find a way to enter it directly into OpenOffice.

T-
Jas Dor
Republic University
Minmatar Republic
#12 - 2012-03-12 19:20:33 UTC
Man PI items IDs are all lover the place.
Scrapyard Bob
EVE University
Ivy League
#13 - 2012-03-13 05:15:01 UTC
Jas Dor wrote:
Man PI items IDs are all lover the place.


That's because a lot of PI items used to be NPC sold goods, used in the old NPC-driven courier economy. NPC stations would say "I want to buy N units of XYZ at price A", other NPC stations would sell XYZ at price B. Players would haul goods from one to the other as long as A stayed above B. As this happened, prices would adjust over the day until A dropped below B and players would no longer haul the items.

(It was botted to hell, which is one reason why CCP removed all of it. But those goods were also used for PI fuels and manufacturing, so they couldn't just take them out.)

So, some PI goods have existed in the database since the dawn of time (which is why they have 2-digit IDs), others were added later (the 3-4 digit IDs), and some were just added back in June 2010 when PI was introduced (those with 5-digit IDs).
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#14 - 2012-03-13 10:58:10 UTC
Tinu Moorhsum wrote:
Anybody have an idea how to get these calls into OpenOffice Calc?

I can get it to work if I first put it in Excel or Googledocs and then save it in OpenOffice format but I can't seem to find a way to enter it directly into OpenOffice.

T-



I've not been able to do it with the xml feed from anywhere, but I have with the semicolon delimited feed you can get from eve marketeer.


https://forums.eveonline.com/default.aspx?g=posts&m=925487#post925487

covers how to do it in excel.

To do it in open office, there's an option on the insert menu, 'sheet from file' (or something like that).

use that, and give it the url with the details you want. That should pretty much pull the details you want (and it'll ask if you want to update it, when you open the sheet).

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter