EVE Technology Lab

Hello.
I am finally trying to connect my spreadsheets to eve-central API, but I have difficulties. I read probably all posts about this topic, but havent found a solution. What I need is basically to pull market prices for some items and put them into specific cells. I found a command for this (Example):
=ImportXML("http://api.eve-central.com/api/marketstat?typeid=34&usesystem=30000142&minQ=1000000","/evec_api/marketstat/type/sell/min")

and another one (Example):
=queryXML("http://api.eve-central.com/api/marketstat?regionlimit=10000002&hours=60&typeid="&C2&"","/evec_api/marketstat/type/buy/max")

Neither of those above works in my Excel 2007. I found information that this works in google docs and Excel 2003 and lower. Can anyone tell me about some function or easy way how to do the similar thing in Excel 2007? I read about solutions using macros and VB, but since I am not very familiar with those tools, it would be just probably easier for me to install older Excel or move to google docs instead. Thanks in advance for help

TL;DR: Need XML import formula for Excel 2007

Hi there,

I recently wanted the same thing. Here's how I eventually did it. I'll walk you through an example, you should be good to go from there.

Open Excel 2007, open the Developer tab. If it's not yet visible which is the default, enable it. Click the Office button in the top left, then open Excel Options. Click Popular, and then select the Show Developer tab in the Ribbon check box. Close the Options menu and switch to the Developer tab.

Click the Source button, this will show the XML maps in your spreadsheet. By default there are none. Click the XML Maps button, click on Add. A dialogue will pop up, asking you where the XML data is stored. In this case you enter the desired Eve Central URL. Just enter it as if it was a regular local file. Confirm your choice by clicking Open. Your newly added XML map has a useless name by default, I strongly suggest to change it to something memorable. Something like "jita_small_shield_extender-ii" works for me.

Take a look at the Source window. Excel lists your XML elements there. Drag and drop an element onto the desired cell. I've taken a screenshot from my own Excel 2007 for reference. Just drag and drop. To have Excel poll the actuall data, click Refresh Data. Now if you ever want to update all XML maps at once, you can for example go to the Data tab and use Refresh All.

And if you're unsure about which URL to use, feel free to read this last bit, too:

Since you're just asking for a price, I'll expain this with the marketstat API in mind. That's where Eve Central returns XML data that contains the percentile element. That's the price of an item if you wanted to buy the cheapest 5% currently available on the market (or sell to the top 5% buy orders).

As an example we'll check the price of the Small Shield Extender II in Jita. The item's typeID can be found in Chribba's typeID list, among other places. It's 380 in this case.

Let's build the URL we're going to use. In order to query Eve Central's marketstat API, we're using the URL http://api.eve-central.com/api/marketstat - we need to add parameters according to what we wanna know. In this example we're going to add ?typeid=380 and since we only want data from Jita we're also going to restrict our query by adding &usesystem=30000142. By the way, solar system IDs can be found here at eve-marketdata.com.

The complete URL looks like this: http://api.eve-central.com/api/marketstat?typeid=380&usesystem=30000142

Yup, pretty much :) There may be a way to get the best of both worlds, meaning a somewhat automated import on one hand and just the XML elements you really care about on the other hand but I don't know how to do that.