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

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

Market Discussions

 
  • Topic is locked indefinitely.
12Next page
 

Extracting prices from online sources "eve-central" into an excel spreadsheet
Author
Previous Topic Next Topic
zlizilz
Brothership Of EVE
#1 - 2013-06-16 04:49:09 UTC
I have no idea if this should be asked here or science, but what is the code to extract a price from eve central into a simple excel spreadsheet?

All I need to know is the buy and sell prices for item A in Jita.

Example I want to know how much caldari fuel blocks are going for in jita, via excel spreadsheet, buy order, sell orders thats it.

Any pointers? Ive looked at eve central developer section but there isnt anything for excel users.
Rutger Janssen
Chanuur
The Initiative.
#2 - 2013-06-16 12:35:47 UTC
Tinu Moorhsum
Random Events
#3 - 2013-06-16 17:06:24 UTC
zlizilz wrote:
I have no idea if this should be asked here or science, but what is the code to extract a price from eve central into a simple excel spreadsheet?

All I need to know is the buy and sell prices for item A in Jita.

Example I want to know how much caldari fuel blocks are going for in jita, via excel spreadsheet, buy order, sell orders thats it.

Any pointers? Ive looked at eve central developer section but there isnt anything for excel users.


If you use a google spreadsheet it's much easier.
zlizilz
Brothership Of EVE
#4 - 2013-06-17 19:11:26 UTC
Rutger Janssen wrote:
Read posts here: https://forums.eveonline.com/default.aspx?g=posts&m=3003601
Read them and got it all working, sadly when I try to update the sheet I get an error.
Source: http://api.eve-central.com/api/marketstat?usesystem=0000142&typeid=1664
Error: No elements mapped
Same Source:
Error No data was imported or only some of the data was imported
Both status failed.

As you can see I am looking at Jita markets for cobalt prices.


Tinu Moorhsum wrote:
If you use a google spreadsheet it's much easier.

Google does not support more than 50 imports and I cannot for some odd reason link one book to another, hence the reason I want to do it locally.

I've used Google in the past and you are right its easier, but the amount of data I want to analyse Google will not support it.
Rutger Janssen
Chanuur
The Initiative.
#5 - 2013-06-17 22:06:55 UTC
I don't use excel, so can't really help much more.

However you need to import A LOT (5k+ items) if the 50 isn't enough. Also importing from another sheet isn't that hard:
=IMPORTRANGE("enter id here","page!cell range")
zlizilz
Brothership Of EVE
#6 - 2013-06-17 22:15:51 UTC
Rutger Janssen wrote:
I don't use excel, so can't really help much more.

However you need to import A LOT (5k+ items) if the 50 isn't enough. Also importing from another sheet isn't that hard:
=IMPORTRANGE("enter id here","page!cell range")

I need exactly 50 items, the problem is the import for buy and sell are unique so the 50 becomes 100.

What exactly goes in the "enter id here" the url of the page?
Rutger Janssen
Chanuur
The Initiative.
#7 - 2013-06-18 06:36:06 UTC
If the google docs page is https://docs.google.com/spreadsheet/ccc?key=ThisIsAFakeId19292312#&gid=0 the id is ThisIsAFakeId19292312 .

The orders are in different system/regions? If not, it can be done with 2 imports.

This is a page of mine, Sheet1:E39/E53 has prices pulled from eve-central in a single import. On Sheet 2 I'm importing that data from the same sheet:
https://docs.google.com/spreadsheet/ccc?key=0ApuvZmsRQYEfdGZQeERQdUtSZG1ZSGgyMUM0YTlEZmc#gid=0
zlizilz
Brothership Of EVE
#8 - 2013-06-18 15:14:33 UTC
Rutger Janssen wrote:
If the google docs page is https://docs.google.com/spreadsheet/ccc?key=ThisIsAFakeId19292312#&gid=0 the id is ThisIsAFakeId19292312 .

The orders are in different system/regions? If not, it can be done with 2 imports.

This is a page of mine, Sheet1:E39/E53 has prices pulled from eve-central in a single import. On Sheet 2 I'm importing that data from the same sheet:
https://docs.google.com/spreadsheet/ccc?key=0ApuvZmsRQYEfdGZQeERQdUtSZG1ZSGgyMUM0YTlEZmc#gid=0

Nice thank you, I will work on it tonight and get back if I have any errors or run ins.
Nihil Ultra
ChuangShi
Fraternity.
#9 - 2013-06-18 17:52:26 UTC
Where can you find exactly how to grab the info that you want? For example:

=importXML("http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002","//type/all/volume")

the information you are looking for specifically is //type/all/volume right? Where can someone see that XML structure so that I know where/how to get the median buy or median sell? Maybe I'm just thinking about it too much...
Rutger Janssen
Chanuur
The Initiative.
#10 - 2013-06-18 18:04:38 UTC
Nihil Ultra wrote:
Where can you find exactly how to grab the info that you want? For example:

=importXML("http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002","//type/all/volume")

the information you are looking for specifically is //type/all/volume right? Where can someone see that XML structure so that I know where/how to get the median buy or median sell? Maybe I'm just thinking about it too much...


The type is optional, //all/volume should give the same result.

You can actually enter the first part in a browser to see the xml (@ needs to be replaced by &reg). Here you can see that each type node has 3 children, buy,sell,all. Which corrospond to the first part. Each of those 3 all have 7 children, including volume, max,min, median, which corrospond to the 2nd part. For example, median buy would be //buy/median .

That's the basic applied version. For more detailed information, google xpath.
zlizilz
Brothership Of EVE
#11 - 2013-06-19 04:17:33 UTC
zlizilz wrote:
Rutger Janssen wrote:
If the google docs page is https://docs.google.com/spreadsheet/ccc?key=ThisIsAFakeId19292312#&gid=0 the id is ThisIsAFakeId19292312 .

The orders are in different system/regions? If not, it can be done with 2 imports.

This is a page of mine, Sheet1:E39/E53 has prices pulled from eve-central in a single import. On Sheet 2 I'm importing that data from the same sheet:
https://docs.google.com/spreadsheet/ccc?key=0ApuvZmsRQYEfdGZQeERQdUtSZG1ZSGgyMUM0YTlEZmc#gid=0

Nice thank you, I will work on it tonight and get back if I have any errors or run ins.
HMMMM Tried it and I got an error stating that the id, page, or range was incorrect. I made sure the txt was right, the id was copy pasted and there is data on the cell that I am trying to retrieve.

Now if I want to retrieve single cell information do I still use range? I thought range is a c1:c10 thing.
zlizilz
Brothership Of EVE
#12 - 2013-06-19 04:20:45 UTC
Nevermind got it, thank you.
Syrk
Caldari Militia Supply Corps
#13 - 2013-06-21 03:22:04 UTC
On a related note, in Excel, how would one quickly set up a number of different type IDs to be imported? I have over 500 typeIDs, each in their own row, and I'm trying to figure out how to tell Excel to pull data from each of the URLs, rather than going through each item and individually setting up a data import.
Rutger Janssen
Chanuur
The Initiative.
#14 - 2013-06-21 17:50:16 UTC
Syrk wrote:
On a related note, in Excel, how would one quickly set up a number of different type IDs to be imported? I have over 500 typeIDs, each in their own row, and I'm trying to figure out how to tell Excel to pull data from each of the URLs, rather than going through each item and individually setting up a data import.


No idea, but maybe this thread can help you.

Having said, did you consider using google docs? it's a LOT easier :)
With IDs in D38:D53:
=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$D39:$D53),"//sell/min")
And you're done.

(Due to url length limit, you might have to split it up in multiple fetches of about 100 items each)
Syrk
Caldari Militia Supply Corps
#15 - 2013-06-21 20:01:12 UTC
Rutger Janssen wrote:
Syrk wrote:
Stuff


No idea, but maybe this thread can help you.

Having said, did you consider using google docs? it's a LOT easier :)
With IDs in D38:D53:
=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$D39:$D53),"//sell/min")
And you're done.

(Due to url length limit, you might have to split it up in multiple fetches of about 100 items each)


I would prefer to use Google Docs, but as I mentioned, I am pulling data on about 500 items, and Google Docs maxes out at 50 items.
zlizilz
Brothership Of EVE
#16 - 2013-06-21 20:10:03 UTC
Syrk wrote:
Rutger Janssen wrote:
Syrk wrote:
Stuff


No idea, but maybe this thread can help you.

Having said, did you consider using google docs? it's a LOT easier :)
With IDs in D38:D53:
=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$D39:$D53),"//sell/min")
And you're done.

(Due to url length limit, you might have to split it up in multiple fetches of about 100 items each)


I would prefer to use Google Docs, but as I mentioned, I am pulling data on about 500 items, and Google Docs maxes out at 50 items.

ya i need around 150 or so, do as I did.

Create one file for 50 another for another 50 etc etc...
Then create another file that refers to the files that are importing your information.
The only caveat is you have to have all the files open in order for it all to work and come together.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#17 - 2013-06-21 21:08:14 UTC
If you have a static list of items you're pulling, Excel makes it /very/ easy.

On the data tab, hit 'From Web'
Fill in the URL with all the ids filled in.

say ok to everything. Tada, you have a worksheet with all the information on it, which you can then use with vlookups to use the data somewhere more useful.

And updating is just a case of hitting refresh all.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Rutger Janssen
Chanuur
The Initiative.
#18 - 2013-06-21 21:09:30 UTC
I'm pulling minimum and median sell order prices of Jita in a single google docs spreadsheet of 372 items. So it is very much possible.

Convo me or drop me a mail if you want specific problems solved.
zlizilz
Brothership Of EVE
#19 - 2013-06-22 07:32:50 UTC
Steve Ronuken wrote:
If you have a static list of items you're pulling, Excel makes it /very/ easy.

On the data tab, hit 'From Web'
Fill in the URL with all the ids filled in.

say ok to everything. Tada, you have a worksheet with all the information on it, which you can then use with vlookups to use the data somewhere more useful.

And updating is just a case of hitting refresh all.

What is the code, because I tried that, and I was getting all kinds of errors.

Tinu Moorhsum
Random Events
#20 - 2013-06-22 10:15:11 UTC
zlizilz wrote:
Steve Ronuken wrote:
If you have a static list of items you're pulling, Excel makes it /very/ easy.

On the data tab, hit 'From Web'
Fill in the URL with all the ids filled in.

say ok to everything. Tada, you have a worksheet with all the information on it, which you can then use with vlookups to use the data somewhere more useful.

And updating is just a case of hitting refresh all.

What is the code, because I tried that, and I was getting all kinds of errors.



I've never been able to get Excel to work for this either.

Google docs works perfectly for this but you're right about it having a limitation to the number of imports.
12Next page