Importing price data into spreadsheets

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.

 

 

Importing price data into spreadsheets by Fuzzwork Enterprises is licensed under a Creative Commons Attribution 4.0 International License.

  • Dean

    Thanks for doing a tutorial on this-it’s great stuff. I’m in the middle of redoing my industry spreadsheet from scratch because of this and the end result should be much less time intensive.

    Is there a way to pull the average price for the region/system via api/automatically?

  • Atfal alNudjum

    Just a quick note also to anyone wanting to use the typeid sheet as it is once you copy/import it into your spreadsheet. You can also use the INDEX and MATCH functions to obtain data that is not found in the first column of the sheet.

    =INDEX(TypeId,MATCH(A2,TypeName,0),1)

    In this example TypeId is a defined array for the whole TypeId table (I usually make this array larger than the data, row wise, to cover additional items to the game). A2 is the cell with the item name in it. TypeName is a defined array containnig just the typeid names eg Column C in this case. This will look up the matching item name, return its row number and then the index function will return the TypeId. This also means that should you add or remove columns that the formula shouldn’t break.

    As for pulling the data automatically, the latest version of excel has WEBSERVICE() which can be used as follows to fill a single cell with data.

    =FILTERXML(WEBSERVICE(C2),”//all/volume”)

    Cell C2 would contain the URL for the eve-central query, In my case I use CONCATENATE function in cell C2 to construct the url from the base Eve-Central API URL, TypeId, SystemId (or RegionId). The FILTERXML will let you get the data that you are after specifically. For example, the one above retrieves the ALL VOLUME for a selected item in the region or system that you chose.

    The thing to note is that this data does require you to do a refresh. It also means that each cell is sending a query to eve-central. The use of the web query means you can send one query, getting say 10 items data at a time, and put it into a pivot table (This is how I used to do it using a macro, and most likely will again once I finish playing around with my updated sheet).

    Attached is a pic of what the data sheet ends up looking like. The item name is copied there by a macro from another page in the workbook. The TypeId is obtained using the INDEX/MATCH functions. The URL is made up using the CONCATENATE function, which pulls the TypeId from cell C2 and also the system or region id from a setting page. Cell data is then filled using the FILTERXML/WEBSERVICE functions. The sheet currently gets all the data for an item, as it is eventually intended for CORP use, to allow a user to decide how they wish to evaluate items for sale (eg Use Max Buy or Min Sell) to see if it is profitable.

    Hope this has been a little helpful.
    Cheers
    Atfal

    • DoucheCannon

      Any way to get past the 10000 row limit for eve-marketdata? the excel part looks great except theres more than 10k items in the DB so some things populate as N/A when I look them up 🙁

    • Statik

      How would we pull every item from lets say Jita….http://api.eve-central.com/api/quicklook?typeid=&usesystem=30000142

      like this?

  • Deluca

    Hello Fuzzysteve.

    First, Thanks for your inspiration, even if I needed 9 hours to figure out the most important stuff of your work. Your thread is not written for amateurs like me, I presume. Would be a good idea to write your text a little bit more beginner-friendly.

    Never the less, I got the most important stuff and wrote it like this.

    In Short:

    Excel 2013

    =NUMBERVALUE(FILTERXML(WEBSERVICE(“http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=”&B4);”/evec_api/marketstat/type/sell/min”);”.”)

    FILTERXML and WEBSERVICE to extract my datas from Eve-Central.

    VLOOKUP for your typIDs works fine.

    NUMBERVALUE because of a problem with currency format. (Probably because of my German Windows. Set my excel to English)(In Germany we use numbers like “40.241,24” instead of the English on “40,241.24”

    Everything seems to work normal, except the Tritanium (36,281)and Pyerite(45,261) Prices are displayed wrong.

    Any idea and I would be very thankful.

    • OldSwimmer1650

      Where would I get a list of the current:
      Item ID’s
      System ID’s

      • OldSwimmer1650

        Found them

  • Bushdoctor

    Thank you for explaining this.
    I would like to point out that the first formula mentioned has an error: Instead of the word Regionlimit, it reads ®ionlimit.

    When applying the formula in Excel, it returns a lot of data. However, I just need the price of the top sell order. I have seen others work with google spreadsheets and they use a command to limit the amount of data to what they need. For them it look something like //sell/min (at the end of the formula).

    My problem is that I haven’t got a clue on how to work that into the formula that is supplied here. Any help would be greatly appreciated.

    Thank you.

  • hept

    Awesome article and really helpful but a question… is there a way to lookup the volume at sell/min price? I can get the total volume easily enough but need a little more granularity if possible 🙂

  • Ryan Easte

    x0x0

  • Douche Cannon

    Any way to get past the 10000 row limit for eve-marketdata? the excel
    part works great except there’s more than 10k items in the DB so some
    things populate as N/A when I look them up 🙁

  • nicolas fraenkel

    Thanks for the tutorial, all works, kind of. When I hit refresh it all kind of goes tits up. I get a no elements mapped and NO data was imported or only some of the data was imported error.
    the current time on the table updates, however the sheet that pulls the raw data gets jumbled up and looses columns, until i crtl-z.

    • Robert

      To refresh the FilterXML WEBSERVICE command you must Ctrl-Alt-F9

  • Morthos Boirelle

    Anyone have a list of all the ships with packaged volumes? The ships in this list are unpackaged.

  • OldSwimmer1650

    This is what I’m using to pull Eve prices into Excel:

    =IF($B17″”,FILTERXML(WEBSERVICE(CONCATENATE(
    EveCentral!$B$3,VLOOKUP($B17,TypeID,2,FALSE),
    EveCentral!$D$3,VLOOKUP(C$4,StationIDTable,2,FALSE))),
    IF(C$3=”Buy”, “//sell/min”,”//buy/max”)),””)

    $B17 = Item being looked up

    EveCentral!$B$3 = http://api.eve-central.com/api/marketstat?typeid=

    EveCentral!$D$3 = &usesystem=

    C$4 = Station to lookup (Jita, Amarr, Hek, etc…). This is a drop down that pulls from StationIDTable to find the SolarsystemID

    TypeID = is a table used for VLOOKUP to return the TypeID of item in $B17 (this table from Fuzzwork)

    StationIDTable = is a table used for VLOOKUP and return the SolarsystemID of C$4 (Jita, Amarr, Hek, etc…) (this table from Fuzzworks also)

    IF(C$3=”Buy”, “//sell/min”,”//buy/max”) = is used in a drop down to select if you’re buying for selling. Cell C$3 is drop down location

    • Eve Guy

      Anyway to chat with you in game sometime to help figure this stuff out? I’m new to excel and these things are difficult concepts for me to understand to begin with.

      • OldSwimmer1650

        Contact in game: Sauron Zanjoahir

        • Eve Guy

          Will do, outta town atm but when I get back expect to hear from. Thanks!

  • Sindrero

    Do you have an updated TypeID list? cant seam to find one with all the new items

  • Eve Newbie Trader

    Under for Both when I click region/station id’s etc… nothing comes up, except a 404 Not Found error.

  • E Fyre

    I’ve been having a problem with this for the last few days, and doing nothing about it has only made it worse. Using this method, I tried building a url with all the items I’m looking into (about a hundred or so), but excel won’t take any greater than about 250 characters or so. So I split it up into about 10 separate ones. ex: (http://api.eve-central.com/api/marketstat?typeid=2073&typeid=2288&typeid=2286&typeid=2306&typeid=2309&typeid=2305&typeid=2311&typeid=2310&typeid=2308&typeid=2270&typeid=2287&typeid=2267&typeid=2307&typeid=2272&typeid=2268&typeid=2393&usesystem=30000142)
    These sets worked flawless the first night. The next morning, when refreshing all, about 4 of them failed as the source could no longer be found. When copy/pasted into a browser, those url’s still displayed the EVE Central data.
    Now, a few days later, with no intervention, they all fail to refresh. Additionally, copy pasting them into a browser returns a 502 Gateway Error.

    What have I done wrong? I’m not an expert in XML or API’s so this is unfamiliar territory for me. I’m most bewildered by the fact that they worked perfectly for a few hours… and gradually failed over time without so much as opening the file.

  • Zeryphim

    Whats de difference between the first and second fil for typeids?

  • Danich

    I copied your example URL of http://api.eve-central.com/api/marketstat?typeid=34&typeid=35®ionlimit=10000002 and used Excel (2010) “data from web”. When I hit “go” it says “the webpage cannot be found”
    Stuck.

  • Robert

    Does anyone know how to download your pilots inventory into an excel spreadsheet using cell functions and not a macro?