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

Spreadsheet Saturday; The Basics

While the tools that I’ve shared are great, the real power and customizability in market number crunching comes from the humble spreadsheet.

There are two things you need to form a solid foundation: an API and a typeID list.

The api is how you hook into a market data gathering site, like eve-marketdata or eve-central, and pull data to use to populate your spreadsheet. I’m using eve-marketdatas, which you can find here.

The typeID list, I’m getting from Chribbas list. It is a simple TSV list that you can import to your spreadsheet. TypeIDs are the numbers that CCP attaches to every item in Eve, and you need to be able to reference them in order to manipulate data easily. All the market trackers index items largely via typeID.

An extract from the typeID list

To get started, go to Google Documents and start a new spreadsheet. Call it whatever you want, pick something memorable so you can find it if you end up making a few. Go to the file menu, then click ‘Import’. You’ll see this pop up:

Select ‘Insert New Sheet’, click choose file and put ‘http://eve-files.com/chribba/typeid.txt‘ into the box and hit ok. This will put a new tab on the bottom of your screen, which you can click to go to your new sheet. On it, you’ll see the typeid.txt list, neatly imported into your spreadsheet. Right click the tab, and rename it to something sensible such as ‘typeid’.

Now you have a typeid list, you can start building a lookup page. A lookup page is a simple self built tool so you can look up a typeid for a specific item, or the name of the item that a typeid represents, or the price of an item… and so on. It is the foundation of the unwieldy monstrosity that your spreadsheet will one day become.

I’ve structured my lookup page like so:

The left section, typeID and price lookup works by putting a name in one of the cells in column A. The formula in the spreadsheet then look up the typeID, and price for it via eve-marketdata. These work like so:

TypeID formula: =filter(typeid!A:A, typeid!B:B = A3)

What this formula does is look in the range of the first section, ‘typeid!A:A’(all of column A on sheet ‘typeid’), for data to copy. It copies the data in the same row as the row(s) where the second section,  ‘typeid!B:B = A3′ is true. Actually it’ll return an array of cells, each where the second section has hit a match. The outcome of this is it puts the typeid that matches the item you’ve entered in cell B.

In cell C, you get the price data. The formula for this is:

=ImportXML(“http://api.eve-marketdata.com/api/importxml_prices2.xml?char_name=Unknown &buysell=b&type_ids=”&JOIN(“,”,$B3:$B26); “/emd/price”)

I don’t fully understand the importXML function, but what this does is find items that match the typeid in column B next to where the formula is, and returns the price. I’ll dissect this further in a future Spreadsheet Saturday.

The name lookup field uses something similar to the first formula:

=filter(typeid!B:B, typeid!A:A = E3)

The order of the ranges is switched, because this time we’re looking in the opposite field. If it matches E3, which contains the typeID, it returns the name.

You can use these tools to pull data from freshly updated sources, and combine them to check out all sorts of things. You could calculate:

  • The difference between sell orders and buy orders, and the profit margin you’d get station trading
  • The profit you’d make by manufacturing an item, by comparing its materials to the sell price
  • Comparing a range of Jita sell prices to Rens, or another markets sell prices, so you can see the margin without manually looking it up.

And so on.

Experiment with what I’ve covered today, and see what you can turn up!

V