Spreadsheet Saturday: Basic Eve Api usage

Ok, to start with I’m aware its not Saturday anywhere right now.

I sat down and tried to figure out a new name for a spreadsheet topic, but I came up with nothing. So, lets just pretend its Saturday in Rens.

In my last post I didn’t cover what XML is very well. XML is short for EXtensible Markup Language, and it was designed to be a reasonably simple language that can be read without much difficulty by humans and computers.  For an example, check out my character sheet in XML format.

For more, click the link to see it. Go on, I’ll wait.

Ok, so CCP fails pretty hard at the human readable part of it. In particular, your skills and certificates. Still, given our list of typeIDs referenced in the last Spreadsheet Saturday post, we can figure things out without too much difficulty.

Now, the XML is all well and good to read with eyeballs, but we want to read it with spreadsheets. So, we use the importxml() function of Google Spreadsheets.

Basically, when you call the importxml() function from your spreadsheet, it runs off and downloads the file and then scans it for whatever you tell it to scan. It uses the following syntax:

=importxml(xml url, xpath query)

XPath is a query language, which you use to tell the spreadsheet how to navigate the XML document. The syntax is pretty straightforward, though getting your head around it to start with can be tricky. You can find a full tutorial on how to use it here.

Now, building your own functions from scratch with no experience can quickly turn into a clusterfuck, so we’re going to break it down a bit by putting the individual elements into a table and then concatenating the together in the final function.

First, lets take what we know and put them in a spreadsheet:

Replace my API key details with yours of course.

I’ve cut the url up into two sections, “http://api.eveonline.com” and “/account/Characters.xml.aspx?” for simplicity and for indirection’s sake. Now, we’ll build functions to put these bits all together. The function in question we use to combine strings is concatenate(). It takes as many strings as you want to give it, and sticks the together. The base syntax of concatenate is as follows:

=concatenate(string1, string2, string3, ...) 
e.g. =concatenate("In Rust", " ", "We Trust")
Would result in the string "In Rust We Trust"

We can use this with the elements above to build our XML url:

=concatenate("http://api.eveonline.com", B7, "keyID=", A3,"&vCode=",B3)

Results in:

http://api.eveonline.com/account/Characters.xml.aspx?keyID=711880&vCode=Pm5yBWlW5bYPwZwSW8gDmxbHIoCyLCmaYn5R2qNeB8621nCYm1HRHoqLrPLttGdq

We can then use this URL to go fishing for data with an XPath, using the importxml() function, like so:

=importXML(B11,C7)

And we get something lovely that looks like this:

So, we’ve put together a bunch of simple bits of data and pulled some stuff out of the Eve API. Lets take the character ID we retrieved, and use it to get stuff from my character sheet.

Extrapolating that out, you can derive skill levels as well using the following formula:

=importxml("https://api.eveonline.com/char/CharacterSheet.xml.aspx?keyID=711880&vCode=Pm5yBWlW5bYPwZwSW8gDmxbHIoCyLCmaYn5R2qNeB8621nCYm1HRHoqLrPLttGdq&characterID=91758676, "//row[@typeid='16622']/attribute::level")

The 16622 in the formula above is the typeid of the Accounting skill. This will spit out 0-5, representing your skill level. These techniques can be used to further extrapolate other pertinent trade information, like the following:

With these skills, and the market price lookups covered in the last Spreadsheet Saturday post you can do the vast majority of tasks that are needed from a marketeers spreadsheet, such as tracking the profitability of station trading an item, manufacturing a specific good, or comparing prices between trade hubs. There is a reference on the Eve Development Network wiki upon which you can find almost anything you need. Go forth and do amazing things with it.

V

Advertisements

7 thoughts on “Spreadsheet Saturday: Basic Eve Api usage

  1. API is not meant to be human readable, it is meant to be computer readable. Hence the acronym standing for Application Programming Interface.

      • Or rather a better way to put it would be that it’s not readable in the sense that a human can understand it, but it’s readable in the sense that it has a consistent structure that can easily be referenced. That doesn’t mean that the content of the structure needs to be meaningful to people.

        A person can look at the row in the skill section and understand that skill 3429 has 8000 skillpoints and is level 3. Just because they don’t know what 3429 is doesn’t mean it is any less readable.

        it’s not a book.

  2. Really interesting article,

    I have a problem though, while using the importxml, I can’t format the value I get such as the Wallet Balance or any dates at all, is it possible to do it ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s