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.
 

Converting IMPORTXML from GDocs to FILTERXML for Excel?

First post
Author
Previous Topic Next Topic
Alyssa Secheh
Royal Amarr Institute
Amarr Empire
#1 - 2017-01-01 02:45:12 UTC  |  Edited by: Alyssa Secheh
Hi,

I have zero experience with Excel but decided I could really use a spreadsheet to calculate the manufacturing and selling cost of some T1 rigs I've been making.

After a lot of searching around and trying to get my head around how the IMPORTXML function works in Goole Docs, I finally managed to pull data from eve-central, and found it was completely accurate, which was perfect!

However, after a while when returning to the document, I'd see #ERROR on most of the imported data. Turns out this is a common problem and the only way to get G docs to refresh it is to delete a character from the formula, hit enter, then re-enter that character... which is quite long winded for every cell I need to do this for.

Now please bare with me as I have no spreadsheet knowledge, but this is the formula I got working for grabbing the max buy order price of Contaminated Nanite Compound from Amarr:

=IMPORTXML("http://api.eve-central.com/api/marketstat?usesystem=30002187&typeid=25590","/evec_api/marketstat/type/buy/max")

This worked great for what I needed, however I would like to transfer this over to Excel to try to avoid the issue with Google Docs, but I see IMPORTXML doesn't work in Excel (2013), and instead you're supposed to use FILTERXML. Now I've tried to transfer this over and get it working in Excel, but I just can't get my head around it. I've never worked with APIs or anything like this and I'm honestly out of my depth. I've seen several guides for pulling data from various sources but I can't get anything to work.

If someone could help me convert the formula I posted to be Excel friendly I'm sure I'll be able to work the rest of the changes I need to make myself.

Thanks,
Oriella Trikassi
Trikassi Enterprises
#2 - 2017-01-01 14:42:13 UTC
For the price of tritanium in Jita

=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&34),"/evec_api/marketstat/type/sell/min")
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2017-01-01 17:58:12 UTC
I'd suggest taking a look at powerquery.

Get all the data you want into a single sheet, then use vlookup from there.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Alyssa Secheh
Royal Amarr Institute
Amarr Empire
#4 - 2017-01-01 18:45:28 UTC
Oriella Trikassi wrote:
For the price of tritanium in Jita

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



Thank you! This is exactly what I needed, I'm very grateful!
Alyssa Secheh
Royal Amarr Institute
Amarr Empire
#5 - 2017-01-01 18:50:54 UTC
Steve Ronuken wrote:
I'd suggest taking a look at powerquery.

Get all the data you want into a single sheet, then use vlookup from there.


I'm looking into Power Query now Steve thanks, although how successful I'll be remains to be seen!
Oriella Trikassi
Trikassi Enterprises
#6 - 2017-01-02 15:12:23 UTC
Steve's PowerQuery method worked for me, just follow his instructions very carefully. If I were starting the Spreadsheet from scratch I would consider using it, as adding more items is easy.
https://market.fuzzwork.co.uk/api/

However, as it is I have FILTERXMLs all over the place and converting would be a pain so I most likely won't.

A tip: XML tables refresh normally with Ctrl-Alt-F5. However, WEBSERVICE ignores this (thanks Microsoft) and requires Ctrl-Alt-F9 for a global update. To update a single WEBSERVICE cell it has to be Edited, pressing RETURN won't do it.

The fun with Excel comes with INDEX MATCH. Have your headache meds of choice to hand!