Toggle search
Toggle menu
4.5K
7.6K
15.6K
232K
EVE University Wiki
Toggle preferences menu
Toggle personal menu
Not logged in
Your IP address will be publicly visible if you make any edits.

Importing market data into Excel: Difference between revisions

From EVE University Wiki
More actions
← Older editNewer edit →
VisualWikitext
Starting cleanup
No edit summary
Line 5: Line 5:
== Getting started ==
== Getting started ==


To get started a player will need the typeID, and either the stationID or systemID or regionID (depending on what data they want to show).
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 ===
=== Obtaining the typeID ===
Line 14: Line 14:
  To obtain the typeID for {{co|wheat|Iron Charge S}} you would go to {{co|#7acdef|<nowiki>https://www.fuzzwork.co.uk/api/typeid.php?typename=Iron%20Charge%20S</nowiki>}} which gives the result: {{co|lightgreen|<nowiki>{"typeID": 215,"typeName": "Iron Charge S"}</nowiki>}}, meaning the typeID is {{co|wheat|215}} }}
  To obtain the typeID for {{co|wheat|Iron Charge S}} you would go to {{co|#7acdef|<nowiki>https://www.fuzzwork.co.uk/api/typeid.php?typename=Iron%20Charge%20S</nowiki>}} which gives the result: {{co|lightgreen|<nowiki>{"typeID": 215,"typeName": "Iron Charge S"}</nowiki>}}, meaning the typeID is {{co|wheat|215}} }}


# You will need to know the folowing data: stationID or systemID or regionID, you can find this alphabetical ordered at this [https://docs.google.com/spreadsheets/d/1HkBZ_KvCYQeFf3awm91fmoPdHTBWugvVKpcwZUv7gXM/edit?usp=sharing Spreadsheet] ;
# You will need Excel 2007 [:-P]


== So, let's start work! ==
=== Obtaining the other IDs ===


=== Importing data ===
This [https://docs.google.com/spreadsheets/d/1HkBZ_KvCYQeFf3awm91fmoPdHTBWugvVKpcwZUv7gXM/edit?usp=sharing spreadsheet] contains an alphabetical list of station, system and region IDs.


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).
== Importing data ==


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.
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.


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


[[File:Import_data_to_excel_1.png|thumb|center|400px|alt=Image 1|Image 1.]]
To obtain the correct URL for EVE Central you need to insert the IDs you have obtained above into this URL: {{co|#7acdef|<nowiki>http://api.eve-central.com/api/marketstat?typeid=XXX&usesystem=YYY</nowiki>}}, 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).


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.
{{example|'''For example'''<br />
We know that the typeID for {{co|wheat|Iron Charge S}} is {{co|wheat|215}}. If we want to pull the data for Jita we would use the systemID {{co|wheat|30000142}} from the above spreadsheet. So we will use the URL {{co|#7acdef|<nowiki>http://api.eve-central.com/api/marketstat?typeid=215&usesystem=30000142</nowiki>}} }}
 
 
=== Importing this into Excel ===
 
{|
|-
| [[File:Import_data_to_excel_1.png|350px]] || If you are using Excel 2007 you will go to Data > Get External Data > From Web as shown on the left.<br />
A browser will pop-up at your spreadsheet, in which you will paste the URL which was obtained above, and click {{co|wheat|Import}}. <br />
A new window will pop-up asking where to put the data, and you will need to select {{co|wheat|Existing worksheet}}, type {{co|wheat|$A$2}} (to place it in cell A2) and select {{co|wheat|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:


[[File:Import_data_to_excel_2.png|thumb|center|400px|alt=Image 2|Image 2.]]
{{example|'''For example'''<br />
If we use the URL {{co|#7acdef||<nowiki>http://api.eve-central.com/api/marketstat?typeid=215&usesystem=30000142</nowiki>}} which we generated above in Excel it will pull the data for {{co|wheat|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 {{co|#7acdef|<nowiki>regionlimit=10000002</nowiki>}}, meaning the link would be {{co|#7acdef|<nowiki>http://api.eve-central.com/api/marketstat?typeid=215&regionlimit=10000002</nowiki>}}. If we import that into cell A3 we will get the following result: }}
 
[[File:Import_data_to_excel_2.png]]


=== Understanding imported data ===
== 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 [https://en.wikipedia.org/wiki/Percentile Percentile]
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 [https://en.wikipedia.org/wiki/Percentile Percentile]
Retrieved from "https://wiki.eveuniversity.org/Importing_market_data_into_Excel"