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!



3 thoughts on “Spreadsheet Saturday; The Basics

  1. Pingback: Spreadsheet Saturday: Basic Eve Api usage | Full Disclosure

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s