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

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

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Need help to limit the data imported from an API.
Author
Previous Topic Next Topic
Bushdoctor Vanderspliff
The Scope
Gallente Federation
#1 - 2015-04-07 10:34:42 UTC
Good day,

I am trying to import a single price for a single item in a single cell in an Excel sheet. (NOT Google docs!)
In order to get there, I am using the URL posted at Fuzzworks.co.uk
http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002

My problem is that this URL returns heaps of data, instead of just a single price.
I have learned that the solution is to add a 'limitation' to the end of the URL that looks something like this: //sell/min
However, after many many tries I can not make this work.

I have tried using the same structure as my friend uses in his Google Docs spreadsheet:
=importxml("http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002","//sell/min")
However, Excel doesn't recognize adding the //sell/min part as it shows in the formula above.

Can somebody please post the correct way of including the 'limitation' on what data should be imported.

Thank you!
Doc.
Hel O'Ween
Men On A Mission
#2 - 2015-04-07 15:26:52 UTC  |  Edited by: Hel O'Ween
Try ...


//evec_api/marketstat/type[@id="34"]/sell/min


... for the XPath part.

[Added]
Or perhaps I'm missing the point. Not sure if Excel has an importxml equivalent.

You might also want to have a look at https://github.com/HelOWeen/eveExcel for an example on how to retrieve XML data in Excel with VBA.

[Added 2]
I've added an example on how to retrieve data from EVE Central.

EVEWalletAware - an offline wallet manager.
Sale Spotter
Royal Amarr Institute
Amarr Empire
#3 - 2015-04-07 17:39:18 UTC  |  Edited by: Sale Spotter
Hel O'Ween,

Thank you very much for the time and effort you spent on trying to explain and even make things visible for me.
I'm afraid that I don't understand much of what you're trying to show me and that's probably because my question
wasn't clear enough.

Let me try again.
I've got a list of items in my Excel sheet. Let's say that Tritanium is listed first.
Next to that item, in the next cell, I would like the price of that item to show up.
That's basically it. The price of the item has to be the highest sell order in the region The Forge.

When trying to achieve that result with the link that I posted first in my original post, it returned not just the price,
but a lot of other data that got spread out over all kinds of cells and even includes pull down menu's.
However, I only need a single price for that item.

I'm hoping you can help me. Perhaps by simply posting what I should import into that cell'.

Thank you very much.
Hel O'Ween
Men On A Mission
#4 - 2015-04-08 14:11:33 UTC
Quote:

When trying to achieve that result with the link that I posted first in my original post, it returned not just the price,
but a lot of other data that got spread out over all kinds of cells and even includes pull down menu's.


If you just pasted the link in there, I'd say "works as inteded".

Quote:

I'm hoping you can help me. Perhaps by simply posting what I should import into that cell'.


The problem here might be: I personally don't ever use Excel. I "speak" VBA, as does Excel, therefore I was able to throw a simplified version of grabbing an XML from my EWA into an Excel document. The visible sheet is only a very simple sample of how to use the provided XML call.

OK, I've refined my example in the Excel sheet.

All you need to do is to use this formula in a cell:

=ewaECGetItemData(B13;B14;B15)


This is taken from my Excel sheet.
- Cell B13 holds the (type)ID for Tritanium (34)
- Cell B14 holds the above posted XPath query, which limits the result to your desired "min" sell price
- Cell B15 holds the region's ID you want to look up. The Forge is default

All you need to do is to copy the macros from my Excel sheet into yours. Recommended so that you only copy over what's needed

Or you base your sheet off of mine. Not recommended, as you inhert all the "drek" in the sheet.

HTH,
Hel

EVEWalletAware - an offline wallet manager.
Sale Spotter
Royal Amarr Institute
Amarr Empire
#5 - 2015-04-09 11:47:27 UTC  |  Edited by: Sale Spotter
Hel O'Ween,
Again, thank you for taking the time to try and explain this to me.

I would like to recap what I think I have learned so far. Please tell me whether or not I"m correct.

At first, I assumed that I could use the same method that my friend is using to 'grab' a price for his Google Spreadsheet.
He imports a single command (or macro, or whatever it's called) into a single cell, and that produces a price.

However, I'm starting to realize that this can't be done in Excel. From what I can tell from your example, I first need
a section where certain data is available (like the Type ID, Xpath and Region ID) and, in order to get a price
in a certain cell, I have to refer to that data with the formula that you showed me.
Is that correct?

If the above assumptions are correct, then I've got a problem with how to fit all that nicely into my Excel sheet.
Basically it looks like this, where the brackets represent a [cell]:
[Tritanium] [price]
[Pyerite] [price]
[Mexalon] [price]
[Isogen] [price]
etc.

Do I want to build a list for all those items on a separate sheet, and then use the formula that you provided
in each of the pricing cells, where I adjust the location for the Type ID in each pricing cell?

Thank you for your patience.
Doc.

-Edit-
I have tried to replicate what you showed me, but somehow it's not working. Here is my sheet:
https://www.dropbox.com/s/fq0b9yfknkdax2q/test2.xls?dl=0
Hel O'Ween
Men On A Mission
#6 - 2015-04-09 15:15:11 UTC  |  Edited by: Hel O'Ween
Sale Spotter wrote:

However, I'm starting to realize that this can't be done in Excel. From what I can tell from your example, I first need
a section where certain data is available (like the Type ID, Xpath and Region ID) and, in order to get a price
in a certain cell, I have to refer to that data with the formula that you showed me.
Is that correct?


More or less. I've designed the provided methods ("macros") so that cell values can be passed as parameters, as this seems to be the way "Excel folks" do it: the "master sheet" which holds all the (calculated) information and various "lookup sheets", which hold the parameters (type ID, region ID etc.).

But it's VBA and you can ofc rewrite the macros and hard-code the paramters, if you prefer it that way.

The main work I've provided is a general purpose "download this XML for me, please" macro, called ewaGetXML. It accepts the base URL (i.e. http://api.eve-central.com/api/marketstat) and the URL parameter string, if applicable (i.e. typeid=34&regionlimit=10000002). It does just that: downloa the XML so that you may work with it locally.

That's not much use, so there are two other macros, which will actually retrieve a value from an XML:
- GetXMLNodeValue
- GetXMLAttributeValue

To select which value you'd like to return from an XML, an XPath query (that this //evec_api/ ... string) is used

They're also general-purpose macros which accept any XML, and return a value from it.

The next thing I've done is that I put those macros to ggod use: load an XML from and retrieve some values from it.

Wrapping the complexer macro stuff together, there's now ewaECGetItemData, encapsulating both ewaGetXML and GetXMLNodeValue to return the desired min sell value. By making it accept parameters for the item (typeID), the desired value (buy/sell, min/max) via the necessary XPath expression and the region (regionID), its not restriced to Trit or min sell, but you could alter the parameters (cell values) to gather all the information needed.

Quote:

If the above assumptions are correct, then I've got a problem with how to fit all that nicely into my Excel sheet.
Basically it looks like this, where the brackets represent a [cell]:
[Tritanium] [price]
[Pyerite] [price]
[Mexalon] [price]
[Isogen] [price]
etc.

Do I want to build a list for all those items on a separate sheet, and then use the formula that you provided
in each of the pricing cells, where I adjust the location for the Type ID in each pricing cell?


As I said: this seems the way the "Excel masters" seems to do it. But you can keep it all in one sheet, if you prefer that.

Quote:

I have tried to replicate what you showed me, but somehow it's not working. Here is my sheet:
https://www.dropbox.com/s/fq0b9yfknkdax2q/test2.xls?dl=0


Yepp, you haven't imported my macros into your sheet, therefore your sheet doesn't know about that ewaECGetItemData-thingy ... perhaps you should try - although I did recommend otherwise - to download my Excel sheet and build your stuff in there.

I admit this is a bit more complex than Ecel's built-in macros/functions (=SUM()), but its basically the same. However - knowing how to deal with (user) macros in Excel is an implicit prerequisite for what I provided.

EVEWalletAware - an offline wallet manager.