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:

A	version
B	method
C	id
D	volume
E	avg
F	max
G	min
H	stddev
I	median
J	percentile
K	volume2
L	avg3
M	max4
N	min5
O	stddev6
P	median7
Q	percentile8
R	volume9
S	avg10
T	max11
U	min12
V	stddev13
W	median14
X	percentile15

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!