Importing market data into Excel

This article should be cleaned up or improved.
The reason is: Grammar and sentence structure needs some revision, and altering from first person

Introduction

In EVE Online we have lot of third part software that helps players to plan their activities (always reseach a lot before installing third part software), but what to do if you can't find information you want? Here I will teach unistas how to import market data from eve-central to Excel 2007, in this way you can use creativity to process data and create the information you want in a spreadsheet.

What you will need?

To start you will need to have some data. These data and how to get them are listed below:

1. You will need to know the typeid of the item you are looking for. To get the typeid you can use FuzzworkAPI. In this example we will import data from "Iron Charge S" (to use space in this method type "%20", so Iron Charge S = Iron%20Charge%20S), so type the following URL at your browser: https://www.fuzzwork.co.uk/api/typeid.php?typename=Iron%20Charge%20S . We will get the following answer {"typeID": 215,"typeName": "Iron Charge S"}, so Iron Charge S typeid is 215;
2. You will need to know the folowing data: stationID or systemID or regionID, you can find this alphabetical ordered at this Spreadsheet ;
3. You will need Excel 2007 [:-P]

So, let's start work!

Importing data

As said before, we will import the price of "Iron Charge S", this itemID is 215. We will do this to a system (Jita, systemID = 30000142) and to a region (The Forge, regionID = 10000002).

To do this to Jita system we will use the URL http://api.eve-central.com/api/marketstat?typeid=215&usesystem=30000142 , if you want to do this to a diferent item or a diferent system you can change the number at "typeid=215" with a different typeid or change the number at "usesystem=30000142" with a diferent systemid.

We need to open Excel 2007, go to "Data > Get External Data > From Web", see "Image 1" below:

Image 1.

After that, a browser will pop-up at your spreadsheet, type our URL at the address and click at import. A new window will pop-up asking you where you want to put the data, choose "Existing worksheet" and type "$A$2" and click import again.

To do this to The Forge region we will repeat the process with the link http://api.eve-central.com/api/marketstat?typeid=215&regionlimit=10000002 , so go to "Data > Get External Data > From Web", type the URL, click at import, choose "Existing worksheet" and, now type "$A$3" to import data to cell "A3". The result will be as the image below:

Image 2.

Understanding imported data

We have at column A of the spreadsheet the "API Version" and at the column B we have the "Method", both columns can be ignored. At column C we have the "typeid", at D "buy volume", that represents the total volume of buy orders at the broker, at column E "buy average price", that means that if you want to supply all market demand for the typeid you will receive at the end of the transactions ISK equivalent to column D multiplied by column E, column F is the "maximum price offer of the buy orders", column G is the "minimun price offer of the buy orders", column H is the "standard deviation of buy orders", that is a measure of dispertion of the data around the buy average price, column I is the "median buy prices", that means if you want to supply 50% of the demand selling for the best buy orders you will receive payments between the median and maximun price offer of the buy orders per unit of item sold. At column J we have "percentile of buy orders", this percentile reffers to 95th percentile, that means that if you want to supply 5% of market demand you will receive payments between percentile of buy orders and maximum buy orders prices, in our example we can see at Image 2, that the 95th percentile is the same value of maximum buy orders prices. To understande percentile better I suggest to read Percentile

Columns K to Q are information about sell orders, respectively: Volume, average, maximum, minimum, standard deviation, median, percentile. In the case of sell orders we are talking about 5th percentile, so that means if you want to buy all 5% of the offers at the broker you will pay prices between percentile value and minimum value.

Columns R to X are information about the last 24 hours opperations in market, so it is historical data, they are important to preview future negociations. They are respectivelly: Volume, average, maximum, minimum, standard deviation, median, percentile.

Importing multiple data

To import multiple data you only need to add "&typeid='number'" to URL, so we will add to our data the typeid number 223, that is the number of Iron Charge M. In this way we will have the following URL http://api.eve-central.com/api/marketstat?typeid=215&typeid=223&usesystem=30000142 . We can see at Image 3 the final result of importing multiple data.

Image 3.

Final considerations

It is important to say that you can not trust only in imported data, you need to check the in game market to make sure you are doing the right choice. This happens because XML data of EVE Central is updated each hour, so it can be some deprecated data on it.

On the other hand, you can import multiple data at your Excel to monitor multiple markets, it can help traders to look for better business opportunities.

Another thing that you must know is that after imported, data will not update automatically, so you will need to update imported data everytime you want clicking at button showed at Image 4.

Image 4.