Importing price data into spreadsheets

24 Replies

Importing up to date price data into your spreadsheets is a large part of being a successful manufacturer or trader in Eve online. Some bypass it by using tools, but the tolls will still have to do it.

I’ve posted about how to load the data from eve central into a web page, but I thought it was time to talk about how to do it, into a couple of different spreadsheet  packages. Namely Excel and Google’s spreadsheet. Open Office is quite a bit harder to do XML imports with, so I’m just mentioning one option right down at the bottom, which is a lot less flexible.

Your first choice is the source of your data. There are two main sources, Eve-central, and eve-marketdata. They’re pulling data from pretty much the same source, so there’s not a lot in it.

Eve Central’s API

This is fairly well documented at http://dev.eve-central.com/evec-api/start. The short version is:

  • Create a URL to pull the data you want.

This will look something like: http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002

If you want more types, just add them to the url with &typeid=36 etc. The numbers are the typeIDs from invTypes.   Just grab an up-to-date copy from my data dump conversions when you can’t find something. It’s not a bad idea to include this, or a cut down copy consisting of typeid,typename,typeid in your spreadsheet on a separate worksheet, for use in vlookups.

Eve Market Data’s API

I’d recommend this one if you’re going to be pulling a lot of data, as it’s possible to pull the entire market in one go, for a particular station or region. Documentation can be found on their site

The url will look like this: http://api.eve-marketdata.com/api/item_prices2.xml?char_name=demo&type_ids=34,12068&region_ids=10000002&buysell=s

Just add the typeids to the comma separated list. Or remove &typeid=[stuff] from it, if you want everything. Change the character name to your own.

For Both:

If you want the station IDs: https://www.fuzzwork.co.uk/dump/latest/staStations.xls.bz2
If you want system IDs: https://www.fuzzwork.co.uk/dump/latest/mapSolarSystems.xls.bz2
If you want the region IDs: https://www.fuzzwork.co.uk/dump/latest/mapRegions.xls.bz2
If you want the typeids: https://www.fuzzwork.co.uk/dump/latest/invTypes.xls.bz2 or http://www.fuzzwork.co.uk/resources/typeids.csv

 

Excel

Be glad of heart, for Microsoft made this really simple. All of this applies to Excel 2007 and above. My screenshots are from Excel 2013 (Office 365 home premium is pretty good.)

On the Data tab

data

Pick ‘From Web’

import

Fill in the url you have, into the Address bar. Hit go. When it’s loaded, it’ll look like this. Hit import.

query

Just hit OK to this. It’s not important.

where

I normally put it into a new worksheet, to keep it out the way.

Once it’s loaded in, you can update the data by hitting the ‘refresh all’ option on the data tab. It /should/ refresh when it’s opened.

You now have market data in your workbook, that you can get up with a vlookup. See later for an example. You can download the example workbook here.

Google Documents

I’m not going to go into screenshots. You can find an example of how it’s done here. I thought this was probably the base way to do it.

Short version is:

Get the url, and use importXML() with it.

With Eve market data I suggest, instead, using importdata(), and the text format. It makes it a /lot/ easier to get everything you want out of it. The url is almost the same, except it’s txt, rather than url. you then split it on tabs ( char(9) )

Be aware, google isn’t great when you’re working with a lot of data. A desktop spreadsheet package works a lot more smoothly. Google will run into processing limits pretty quickly.

Vlookups

 

Learn to love this function. It’s very very useful for what you’ll be doing.

=vlookup(what you want to search for, where you want to find it, which column to return,if it’s not ordered data put true here)

so you could use =VLOOKUP(34,Sheet2!D:AT,34) to get, from the excel sheet above, the percentile price for Tritanium (typeid 34)

And you could use use =vlookup(‘Tritanium’,typeids!B:C,2,true) to get the typeid for tritanium from a worksheet called typeids, with typeid,typename,typeid as columns.

 

Open Office/Libre Office

Doing an XMl load into open office is a complete PITA, involving a lot more work. There are no screenshots, as I don’t have it installed any more. What I’d suggest doing is using the text option from eve market data.

Use ‘Insert’->’Sheet from file’

When it asks for a file, give it the full url (like http://api.eve-marketdata.com/api/item_prices2.txt?char_name=demo®ion_ids=10000002&buysell=s )

Hit open and wait. It’ll take a while to work the first time. Eventually, it’ll pop up the text import screen. Make sure tab is selected. Hit ok. It’ll take a while before it’ll become responsive, but eventually ‘sheet1’ should show up in the from file box. Make sure the link checkbox is ticked. Hit ok. You now have a sheet that should reload whenever the workbook is opened. It can be vlookuped in the same way as everything else.