Science & Industry

 
 

Eve market prices - Excel

Author
Goonswarm Federation
#1 - 2017-02-17 04:55:27 UTC
Gallente Federation
#2 - 2017-02-17 11:31:51 UTC
Who needs Excel? Who needs Spreadsheets? Twisted

I'm my own NPC alt.

#3 - 2017-02-17 12:21:25 UTC
Not sure if I get your problem right, but this should give you URLs for the individual item types quite fast:

Put the URL without the type ID in one cell, put the type IDs into other cells. Use CONCATENATE to put those together, format the result as URLs.

Moo! Uddersucker, moo!

Goonswarm Federation
#4 - 2017-02-17 14:45:12 UTC
CowQueen MMXII wrote:
Not sure if I get your problem right, but this should give you URLs for the individual item types quite fast:

Put the URL without the type ID in one cell, put the type IDs into other cells. Use CONCATENATE to put those together, format the result as URLs.



Can you use CONCATENATE without using xml? The functions I have seen so far are all =importxml functions
#5 - 2017-02-18 18:15:16 UTC  |  Edited by: Oriella Trikassi
Quote:
Is there really no function to pull the item # out of column a and look up and import the price into column c?

Like this? Price of something at Jita.

=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min")

where cell A7 has the typeID or more likely a VLOOKUP to a Name >> ID table.

FILTERXML is the Excel single cell function for downloading one thing from an XML table. WEBSERVICE gives the URL and passes on parameters, in this case the system and the itemID. The expression at the end defines which bit of the XML table we want, in this case the minimum sell price. Other possibilities should be obvious, "/type/buy/max" is the top buy price for example.

VLOOKUP is the simple but limited Excel way of pulling data from a table. The more complex but more powerful one is INDEX MATCH which uses Boolian logic and arrays, best of luck with that! I can write the things but can't recall how I did it afterwards...
Goonswarm Federation
#6 - 2017-02-19 00:54:25 UTC  |  Edited by: Jaseac
Oriella Trikassi wrote:
Quote:
Is there really no function to pull the item # out of column a and look up and import the price into column c?

Like this? Price of something at Jita.

=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min")

where cell A7 has the typeID or more likely a VLOOKUP to a Name >> ID table.

FILTERXML is the Excel single cell function for downloading one thing from an XML table. WEBSERVICE gives the URL and passes on parameters, in this case the system and the itemID. The expression at the end defines which bit of the XML table we want, in this case the minimum sell price. Other possibilities should be obvious, "/type/buy/max" is the top buy price for example.

VLOOKUP is the simple but limited Excel way of pulling data from a table. The more complex but more powerful one is INDEX MATCH which uses Boolian logic and arrays, best of luck with that! I can write the things but can't recall how I did it afterwards...



Tried inputing that into excel 2010. It doesn't like something about it, giving a error "The formula contains unrecognized text".

Do I need a newer version of excel or is something missing or spelled wrong?
#7 - 2017-02-19 14:20:09 UTC
Jaseac wrote:
Tried inputing that into excel 2010. It doesn't like something about it, giving a error "The formula contains unrecognized text".

Do I need a newer version of excel or is something missing or spelled wrong?

You are correct - you need at least Excel 2013 for FILTERXML which is what I have - or switch to Google Docs which has a similar function IMPORTXML.
Goonswarm Federation
#8 - 2017-02-20 01:29:33 UTC

=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min")


Ok, so I got it working, but for some reason eve central doesnt show prices for over half the items. Even tried looking them up manually. They are priced on eve market, but not on eve central
Goonswarm Federation
#9 - 2017-02-20 02:18:01 UTC
my biggest issue is that eve-central (for some stupid reason) doesn't believe in pricing bpo's. So that website is worthless
The Bastion
#10 - 2017-02-20 09:32:48 UTC
Jaseac wrote:
my biggest issue is that eve-central (for some stupid reason) doesn't believe in pricing bpo's. So that website is worthless


The biggest issue is that your spreadsheet (for some stupid reason) isn't capable of filtering incorrect pricing. So it's worthless.

On a more serious note though, Excel is nice and all for doing what it does, but using it to fetch and analyse the price of 40k manufactured items with multiple webservice queries is a bit beyond its capabilities. You might want to look for a better tool for the job, or reduce your expectations.

The above message presents my opinions on the topic at hand. If there is a conflict between my views and reality, consider reality to be correct until proven otherwise.

Goonswarm Federation
#11 - 2017-02-20 14:37:08 UTC
YeuxVerts Belle wrote:
Jaseac wrote:
my biggest issue is that eve-central (for some stupid reason) doesn't believe in pricing bpo's. So that website is worthless


The biggest issue is that your spreadsheet (for some stupid reason) isn't capable of filtering incorrect pricing. So it's worthless.

On a more serious note though, Excel is nice and all for doing what it does, but using it to fetch and analyse the price of 40k manufactured items with multiple webservice queries is a bit beyond its capabilities. You might want to look for a better tool for the job, or reduce your expectations.



I thought about creating my own program/app but I would guess that is alot harder than even Excel. Maybe one day.
The Bastion
#12 - 2017-02-20 15:30:00 UTC
Jaseac wrote:
I thought about creating my own program/app but I would guess that is alot harder than even Excel.


It is. But it'd be a custom tool, so much more adapted to the task. Better in every way.

The above message presents my opinions on the topic at hand. If there is a conflict between my views and reality, consider reality to be correct until proven otherwise.

#13 - 2017-02-22 03:02:29 UTC
Jaseac wrote:

=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A7),"/evec_api/marketstat/type/sell/min")

Ok, so I got it working, but for some reason eve central doesnt show prices for over half the items. Even tried looking them up manually. They are priced on eve market, but not on eve central

The Third Party apps and databases rely on players sending in data, as CCP does not publish Regional Market data out of game. Eve Market has gaps too! The two seem to have different criteria for what stuff they delete when out of date.

For high volume commodity stuff that sells at Trade Hubs Eve Central rarely fails. If you are searching for big profit unobvious niche items then the databases aren't so forthcoming.
Forum Jump