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