Importing market data into Excel

There is various third party software which can be used to assist players to plan their activities in EVE Online. In particular, traders may find it useful to import market data from EVE-Central to Excel, to transfer the information to a spreadsheet format and allow them to process the data.

It is important remember that the XML data of EVE Central is updated once per hour, so it can be some sometimes be out of date. Make sure to double check the data in game to ensure it is accurate. Despite this, the ability to use Excel to monitor multiple markets can help traders to look for better business opportunities.

Getting started

To get started you will need the typeID, and either the stationID or systemID or regionID (depending on what data they want to show).

Obtaining the typeID

One way to get the typeID is to use Fuzzwork's API search. To use this, direct your browser to https://www.fuzzwork.co.uk/api/typeid.php?typename= and add the name of the item. Note that any spaces in the item name should be replaced with %20.

For example

To obtain the typeID for Iron Charge S you would go to https://www.fuzzwork.co.uk/api/typeid.php?typename=Iron%20Charge%20S which gives the result: {"typeID": 215,"typeName": "Iron Charge S"}, meaning the typeID is 215 

Obtaining the other IDs

This spreadsheet contains an alphabetical list of station, system and region IDs.

Importing data

In order to import the data into Excel you will first need to generate a URL to pull the data from EVE Central, and then import that into Excel.

Obtaining the URL

To obtain the correct URL for EVE Central you need to insert the IDs you have obtained above into this URL: http://api.eve-central.com/api/marketstat?typeid=XXX&usesystem=YYY, where XXX is the type ID and YYY is the system ID (for pulling information from a particular station or a whole region you would alter the URL accordingly).

For example

We know that the typeID for Iron Charge S is 215. If we want to pull the data for Jita we would use the systemID 30000142 from the above spreadsheet. So we will use the URL http://api.eve-central.com/api/marketstat?typeid=215&usesystem=30000142 

Importing this into Excel

In Excel you will go to Data > Get External Data > From Web as shown on the left. A browser will pop-up at your spreadsheet, in which you will paste the URL which was obtained above, and click Import. A new window will pop-up asking where to put the data, and you will need to select Existing worksheet, type $A$2 (to place it in cell A2) and select Import again.

For example

If we use the URL http://api.eve-central.com/api/marketstat?typeid=215&usesystem=30000142 which we generated above in Excel it will pull the data for Iron Charge S in the system of Jita. If we also want to pull data on the same item for The Forge region we would alter the URL to end in regionlimit=10000002, meaning the link would be http://api.eve-central.com/api/marketstat?typeid=215&regionlimit=10000002. If we import that into cell A3 we will get the result below. 

Understanding imported data

Having imported some sets of data you will get a result similar to the one below:

What the data shows

The information is as follows:

Column A: The API version (can be ignored) Column B: The 'method' (can be ignored) Column C: The typeID Column D: The volume of buy orders Column E: The average buy price Column F: The maximum price out of all the buy orders Column G: The minimum price out of all the buy orders Column H: The standard deviation of buy orders Column I: The median buy prices Column J: The percentile of buy orders Column K: The volume of sell orders Column L: The average sell price

Column M: The maximum price out of all the sell orders Column N: The minimum price our of all the sell orders Column O: The standard deviation of sell orders Column P: The median sell price Column Q: The percentile of sell orders Column R: The total volume over the past 24 hours Column S: The average over the past 24 hours Column T: The maximum over the past 24 hours Column U: The minimum over the past 24 hours Column V: The standard deviation over the past 24 hours Column W: The median over the pas 24 hours Column X: The percentile over the past 24 hours

Making use of the data

Once you have this data and know what it stands for, you can start to make use of it in planning your market transactions.

Column E shows the buy average price, so if you wanted to supply all market demand for this item you would receive at the end of the transaction the ISK equivalent of column D (the volume of buy orders) multiplied by column E (the average price of buy orders).

Column I is the median buy price, so if you wanted to supply 50% of the demand while selling to the best buy orders you would receive payments between the median buy price (column I) and the maximum buy price (column F) per unit of item sold.

Column J shows the percentile of buy orders, which refers to the 95th percentile. This means that if you wanted to supply 5% of market demand you would receive payments between the percentile of buy orders (column J) and the maximum buy price (column F). You will motice that in the example data set given above, the 95th percentile is the same value as the maximum buy orders prices.

Column Q is the percentile of sell orders. In the case of sell orders this refers to the 5th percentile, so if you wanted to buy all 5% of the offers at the broker you would pay prices between the percentile of sell orders (column Q) and the minimum value (column N).

Updating data

After the data has been imported, it will not update automatically. You will need to update imported data when required by clicking at button shown to the left.