Translate this page

Some beautiful music to read the blog with

Sunday 24 November 2013

Excel reading prices from Eve-Central - step by step guide

I have had lots of questions referring to my post on automatically updating Jita prices in my excel spreadsheets where I pointed readers in the direction of the Fuzzwork blog written by Fuzzysteve where he talks about this exact same subject and gives examples.

So, for completeness of my blog below is my step-by-step guide on how to import prices from Eve-Central onto your spreadsheet - and hopefully this will work for those who don't like to use Excel beyond its basic functions!


The Aim:

To build a spreadsheet on Excel 2007 to download the prices of 1 item in Jita from the Eve-Central website.


The Set-up:

The Workbook (in the lingo - a workbook contains a number of worksheets, we would call "tabs") will have 3 worksheets

a worksheet with all the item IDs (known as typeid) - call it "Type Ids"

a worksheet that downloads the prices from Jita - call it "downloaded data"

a worksheet that selects the pricing from the data that is downloaded - call it "workings"


Step 1: create the worksheet with all the typeid's

this step need only ever be done once - or when new items are added to the game.

the Eve-Central database, and indeed every Eve Online data base works off typeid's, not their name.

Therefore, we need first to download the Items Names and IDs so that we can determine the typeid if we are given the Item Name.

You could just look the item number up everytime online - but that is very time consuming given this step is quick to do and needs only be done once.

Fuzzysteve has provided an excel spreadsheet that can be downloaded - right click the link and "save as" to your desktop, and open it.

The downloaded spreadsheet will look in part like this:


copy and paste the entire spreadsheet onto your worksheet called "Type Ids".

as you can see on the spreadsheet - the typeid is in column A and the item name is in column C.


Step 2: create the worksheet that downloads the prices from Jita

This is the where the action happens - and call this worksheet "downloaded data"

For this example we will download the price of Tritanium.

there are 2 parts to this step - the first part is deriving the URL to download the data, the second part is downloading the data.


Deriving the URL:

What we are aiming for is the URL http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002

where "typeid=34" is the typeid for Tritanium
and "regionlimit=1000002" is The Forge Region

. . . . . in other words, we will be downloading the price of Tritanium from The Forge region.

Given we will always be looking at prices in The Forge (which contains Jita) then the only piece of the URL that will change is the typeid.

How you insert the correct typeid is up to you but I use the excel MATCH and INDEX functions.

on my worksheet "downloaded data", in cell F137 is the name of the item I am looking for, in this case Tritanium.

and then in cell E137 is this formula:

=INDEX('Type Ids'!A:A,MATCH(F137,'Type Ids'!C:C,0))

. . . . .what this does is look down column C in the worksheet "Type Ids" for the name in cell F137 (in this case Tritanium) and then returns the value that is in the same row that is in column A on that worksheet.  I.e. MATCH looks up the row number and INDEX then reads the value in the other column which is on that row.

Then in cell D133 I have the formula
 
"http://api.eve-central.com/api/marketstat?typeid="&E137&"&regionlimit=10000002"

. . . . in other words, it returns the URL
http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002

the final step in this part is to then copy that URL and paste it in the line below as a "value" - in other words, copy the value it gives to another cell, not the formula.


downloading the data:

now copy the URL that was derived, i.e:

http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002


on the same excel worksheet called "downloaded data":

1. select the work tab "Data" from the top row of menu items
2. make sure the cell selected is where you want to download the data to (so for the first one in cell A1)
3. press "From Web" which should be the second item along below the menu items at the top
4. This will open up your home webpage on the spreadsheet (for me google)
5. replace the URL with the URL you have saved - as shown in the above picture
6. Press "Go" or Enter / Return

This will then change the webpage to:


Now press "Import" to download the data for the first time, and you will be presented with this box:


the cell $A$1 is the position of the current selected cell, so change that if you want to, then press "OK"

that will then download the data which will fill the row from column A to column X.

what each column represents, assuming you started in column A is:


Aversion
Bmethod
Cid
Dvolume
Eavg
Fmax
Gmin
Hstddev
Imedian
Jpercentile
Kvolume2
Lavg3
Mmax4
Nmin5
Ostddev6
Pmedian7
Qpercentile8
Rvolume9
Savg10
Tmax11
Umin12
Vstddev13
Wmedian14
Xpercentile15

The sales price I look for is in column Q "percentile8".


Step 3: create the worksheet that selects the pricing from the downloaded data

and this is the final part


on the third worksheet in your workbook called "workings" you make a formula to select the cell in column Q,

again, I use the INDEX and MATCH excel functions to find what I am looking for.

=INDEX('Downloaded Data!Q:Q,MATCH(B153,'Downloaded Data!C:C,0))

where the cell B153 on my "Workings" worksheet has the typeid for Tritanium (which is 34) and the MATCH function would then find it in column C of the "Downloaded Data" worksheet in row 1 and the INDEX function would then read the value in row 1 in column Q of the "Downloaded Data" worksheet.

. . . . .and there we are.


From here on in, to update this data press the Refresh button at the top - that will then update all your downloaded data points:




To add more items to download merely go through Steps 2 and 3 again!

13 comments:

  1. Hi - Love your blog

    Have you got a source of the file that Steve put up with the type id's that isn't a bz2 file

    http://www.fuzzwork.co.uk/dump/odyssey-1.0-89097/invTypes.xls.bz2

    having issues trying to extract the excel spreadsheet

    ReplyDelete
    Replies
    1. You need to unzip the file first. I prefer 7-zip. Google it...it's free. Once it's unzipped it will be a standard excel file.

      Delete
  2. Thanks for all this, it was a great help!
    I ended up using (with Excel 2013) the formula

    =VLOOKUP(F7,typeid!B:C,2,FALSE)

    where cell F7 has the name of the item.
    and since I only need one bit of data, say the selling price in Jita, my eve-central formula is
    =FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&G7),"/evec_api/marketstat/type/sell/min")

    ReplyDelete
    Replies
    1. =NUMBERVALUE(FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A32);"/evec_api/marketstat/type/sell/min");".")

      For people ho need the data in number, not text.

      Delete
    2. This comment has been removed by the author.

      Delete
  3. You may also just make an example public...

    Here's a fully completed spreadsheet, with automatic update from Jita prices: https://docs.google.com/spreadsheet/ccc?key=0Am1DnUpYOEztdE9BaU01cFNEaDNURGtZV2JWRnZkRVE#gid=0
    Feel free to copy it and edit it with your settings.
    Enjoy

    ReplyDelete
    Replies
    1. Just for your information: You DON'T need to request a share!

      All you need to do, is to click on "Data" and then copy it to your Google Drive account. Now you got your own copy and may edit it as you wish. :-)

      Delete
    2. Hey Daniel, would you be so kind and teach me how to retrieve prices from other systems / regions excepting Jita? I would be so greatfull

      Delete
    3. This comment has been removed by the author.

      Delete
  4. Hi Croda thanks for the help. Question on the "downloaded data" page and where you're placing everything. Because if I start doing 10-20 items maybe more, that page just gets longer and longer, is that correct?

    ReplyDelete
  5. i wish google would fix the issue that keeps erroring when you re-open the spreadsheets.

    ReplyDelete
  6. https://www.youtube.com/watch?v=bLm0FQD_cN4 (part 1)
    https://www.youtube.com/watch?v=6DuavODssDE (part 2)
    These videos aren't spam fyi, it's a video about making a google docs worksheet that pulls the data from eve-central. I wan't to do something similar via excel but cannot figure it out. I'm not very knowledge with excel but if anyone can provide me with a help link or something that gives directions how to do this with excel I'd greatly appreciate it.

    ReplyDelete