Pages: [1] :: one page |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
zlizilz
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.16 04:49:00 -
[1] - Quote
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
Xanadu
82
|
Posted - 2013.06.16 12:35:00 -
[2] - Quote
Read posts here: https://forums.eveonline.com/default.aspx?g=posts&m=3003601 |
Tinu Moorhsum
Random Events
222
|
Posted - 2013.06.16 17:06:00 -
[3] - Quote
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
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.17 19:11:00 -
[4] - Quote
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
Xanadu
82
|
Posted - 2013.06.17 22:06:00 -
[5] - Quote
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
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.17 22:15:00 -
[6] - Quote
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
Xanadu
82
|
Posted - 2013.06.18 06:36:00 -
[7] - Quote
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
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.18 15:14:00 -
[8] - Quote
Nice thank you, I will work on it tonight and get back if I have any errors or run ins. |
Nihil Ultra
Royal Amarr Institute Amarr Empire
0
|
Posted - 2013.06.18 17:52:00 -
[9] - Quote
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
Xanadu
82
|
Posted - 2013.06.18 18:04:00 -
[10] - Quote
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 ®). 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
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.19 04:17:00 -
[11] - Quote
zlizilz wrote: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
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.19 04:20:00 -
[12] - Quote
Nevermind got it, thank you. |
Syrk
Project Cerberus Caldari State Capturing
8
|
Posted - 2013.06.21 03:22:00 -
[13] - Quote
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
Xanadu
82
|
Posted - 2013.06.21 17:50:00 -
[14] - Quote
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
Project Cerberus Caldari State Capturing
8
|
Posted - 2013.06.21 20:01:00 -
[15] - Quote
Rutger Janssen wrote: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
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.21 20:10:00 -
[16] - Quote
Syrk wrote:Rutger Janssen wrote: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
1560
|
Posted - 2013.06.21 21:08:00 -
[17] - Quote
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. Steve Ronuken for CSM 9!-á I'm starting early :) Handy tools and an SDE conversion Twitter: @fuzzysteve on Twitter |
Rutger Janssen
Xanadu
82
|
Posted - 2013.06.21 21:09:00 -
[18] - Quote
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
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.22 07:32:00 -
[19] - Quote
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
228
|
Posted - 2013.06.22 10:15:00 -
[20] - Quote
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. |
|
Steve Ronuken
Fuzzwork Enterprises
1562
|
Posted - 2013.06.22 15:13:00 -
[21] - Quote
I've used a url like: http://api.eve-central.com/api/marketstat?typeid=34&typeid=35&typeid=36&typeid=37&typeid=38&typeid=39&typeid=40-«ionlimit=10000002 in the past. (http://preview.tinyurl.com/lfxyybf if the forums munged it)
If you want more items, just add more &typeid= to it.
hit 'from web' paste the url into the address bar. hit go wait. when the xml shows up, hit import. say ok when it tells you it doesn't refer to a schema I normally tell it to put it in a new worksheet
Then it's something like: =VLOOKUP(34,Sheet2!D:AT,38)
to find the percentile sell price
Another option is eve market data. As you can leave off the typeid limiter http://api.eve-marketdata.com/api/item_prices2.xml?char_name=demo®ion_ids=10000002&buysell=s Steve Ronuken for CSM 9!-á I'm starting early :) Handy tools and an SDE conversion Twitter: @fuzzysteve on Twitter |
Zeta Zhul
Preemptive Paranoia
39
|
Posted - 2013.06.22 15:57:00 -
[22] - Quote
This is how you do it in google spreadsheet:
Google Docs Spreadsheet example
And while the 50 importxml() limitation does limit you ... you're not actually supposed to do it that way. The function to use is Continue().
Basically for every column you do a query and then subsequent parsing of that requested xml data is done through the Continue() function.
=ImportXML("http://api.eve-marketdata.com/api/importxml_prices2.xml?buysell=s&char_name=Zhombi&station_ids=60003760&type_ids="&JOIN(",",$A4:$A101), "/emd/price")
Basically the URL used concatenates the request for a specific set of typeids that occupy column A. The station_ids is for "Oursulaert III - Federation Navy Testing Facilities" which id = 60011740 while the primary reference being used is Jita-4-4 which id = 60003760. If you want different id for different stations then ask here or download the static data dump and find it.
buysell denotes whether or not this is for buy orders or sell orders.
char_name should _NOT_ be "Zhombi" which is one of my character names but your own. Seriously don't goof on this because if you're causing a problem on the server because of a malformed request then you want to know about it to fix it.
"/emd/price" is the xpath to the queried data.
So the result of this is an updatable spreadsheet that queries eve-marketdata.com for recent buy/sell market order prices to determine the price delta between Jita-4-4 and "Oursulaert III - Federation Navy Testing Facilities". Replace as needed.
BTW the the different colors I use denote whether or not something is a good deal. I use a combination of an IF() function and Conditional Formatting (right mouse click to access).
edit: this isn't meant to be exhaustive or to provide a complete working example. It's just to show the technique. You would have to add some more functions, filtering and checking to make it worthwhile. Not a lot more, but it isn't turnkey.
As always YMMV. .... And buy my overpriced crap! Now!!! :) |
Steve Ronuken
Fuzzwork Enterprises
1562
|
Posted - 2013.06.22 16:02:00 -
[23] - Quote
If you want the station IDs: http://www.fuzzwork.co.uk/dump/odyssey-1.0.7-89602/staStations.xls.gz If you want system IDs: http://www.fuzzwork.co.uk/dump/odyssey-1.0.7-89602/mapSolarSystems.xls.gz If you want the region IDs: http://www.fuzzwork.co.uk/dump/odyssey-1.0.7-89602/mapRegions.xls.gz If you want the typeids: http://www.fuzzwork.co.uk/dump/odyssey-1.0.7-89602/invTypes.xls.gz or http://www.fuzzwork.co.uk/resources/typeids.csv Steve Ronuken for CSM 9!-á I'm starting early :) Handy tools and an SDE conversion Twitter: @fuzzysteve on Twitter |
zlizilz
Organized Chaos Inc Spectral Knights
0
|
Posted - 2013.06.22 16:59:00 -
[24] - Quote
Awesome information posted, perhaps this should be pinned. Seriously this question pops up so often. |
Steve Ronuken
Fuzzwork Enterprises
1564
|
Posted - 2013.06.22 18:18:00 -
[25] - Quote
zlizilz wrote:Awesome information posted, perhaps this should be pinned. Seriously this question pops up so often.
How does: http://www.fuzzwork.co.uk/2013/06/22/importing-price-data-into-spreadsheets/
Look for covering this?
Steve Ronuken for CSM 9!-á I'm starting early :) Handy tools and an SDE conversion Twitter: @fuzzysteve on Twitter |
Taritura
Oberon Incorporated RAZOR Alliance
5
|
Posted - 2013.06.25 06:37:00 -
[26] - Quote
Or... 1. turn on Developer tab > options > show ... 2. On developer tab insert > active x Button , check design mode .... Double click button insert the text below. Arkusz2 change to your sheet name. Forth column has the type id's of things you want to check The seventh will have the prices. This script will check 9 prices at once. Changing the Set xmlNodeList = xmlDoc.SelectNodes("//evec_api/marketstat/type/sell/min") you can set what price you want to pull out .. In MIcrosoft Visual Basic > tools > references check the "Microsoft XML 5.0"
For j = 2 To 500 < change this number to as many rows you have
Quote:Private Sub CommandButton1_Click() Dim xmlDoc As Object Dim xmlNodeList As Object Dim xmlNodeList2 As Object Dim myNode As Object
Dim adresik As String
For j = 2 To 500 DoEvents adresik = CStr("http://eve-central.com/api/marketstat?typeid=" & Sheets("Arkusz2").Cells(j, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 1, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 2, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 3, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 4, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 5, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 6, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 7, 4).Value & "&typeid=" & Sheets("Arkusz2").Cells(j + 8, 4).Value & "®ionlimit=10000002") Set xmlDoc = CreateObject("MSXML2.DOMDocument.5.0") xmlDoc.async = False xmlDoc.Load adresik Set xmlNodeList = xmlDoc.SelectNodes("//evec_api/marketstat/type/sell/min") On Error Resume Next Sheets("Arkusz2").Cells(j, 7).Value = xmlNodeList.Item(0).Text Sheets("Arkusz2").Cells(j + 1, 7).Value = xmlNodeList.Item(1).Text Sheets("Arkusz2").Cells(j + 2, 7).Value = xmlNodeList.Item(2).Text Sheets("Arkusz2").Cells(j + 3, 7).Value = xmlNodeList.Item(3).Text Sheets("Arkusz2").Cells(j + 4, 7).Value = xmlNodeList.Item(4).Text Sheets("Arkusz2").Cells(j + 5, 7).Value = xmlNodeList.Item(5).Text Sheets("Arkusz2").Cells(j + 6, 7).Value = xmlNodeList.Item(6).Text Sheets("Arkusz2").Cells(j + 7, 7).Value = xmlNodeList.Item(7).Text Sheets("Arkusz2").Cells(j + 8, 7).Value = xmlNodeList.Item(8).Text On Error Resume Next DoEvents j = j + 8 Next End Sub |
Syrk
Project Cerberus Caldari State Capturing
8
|
Posted - 2013.06.26 20:15:00 -
[27] - Quote
Well I used a bunch of different things people pointed out in this thread, and finally finished my spreadsheet. I should have realized that if you could serialize the typeIDs in the URLs for Google Docs, you could do that for Excel as well. :-P
The one problem I ran into is that the Web Query tool in Excel maxes out at 150 characters, and your browser will max out at 1000 characters or so. So I wound up dividing my typeIDs into 4 batches, each of which I put into my browser in different tabs. Each of these batches I then saved as XML files, which I then pulled up in my spreadsheet. Probably nowhere near as elegant as the post immediately above mine, but I just wanted to post what I did for whoever comes after me with similar issues. |
|
|
|
Pages: [1] :: one page |
First page | Previous page | Next page | Last page |