Importing market data into Excel: Difference between revisions

Line 1:

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 [https://eve-central.com/ EVE-Central] to Excel, to transfer the information to a spreadsheet format and allow them to process the data. ~~This guide assumes~~ the use of Excel ~~2007~~.

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 [https://eve-central.com/ 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 ==

Line 35:

Line 37:

{|

|-

| [[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 />

| [[File:Import_data_to_excel_1.png|350px]] || In Excel 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.

Line 42:

Line 44:

{{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: }}

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 result below. }}

~~[[File:Import_data_to_excel_2.png]]~~

== Understanding imported data ==

~~== Understanding~~ imported data ==

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

[[File:Import_data_to_excel_2.png|center]]

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]

=== What the data shows ===

~~Columns K to Q are~~ information ~~about sell orders, respectively~~: ~~Volume, average,~~

The information is as follows:

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.

|-

|

* {{co|lightgreen|Column A:}} The API version (can be ignored)

* {{co|lightgreen|Column B:}} The 'method' (can be ignored)

* {{co|lightgreen|Column C:}} The typeID

* {{co|lightgreen|Column D:}} The volume of buy orders

* {{co|lightgreen|Column E:}} The average buy price

* {{co|lightgreen|Column F:}} The maximum price out of all the buy orders

* {{co|lightgreen|Column G:}} The minimum price out of all the buy orders

* {{co|lightgreen|Column H:}} The standard deviation of buy orders

* {{co|lightgreen|Column I:}} The median buy prices

* {{co|lightgreen|Column J:}} The percentile of buy orders

* {{co|lightgreen|Column K:}} The volume of sell orders

* {{co|lightgreen|Column L:}} The average sell price

||

* {{co|lightgreen|Column M:}} The maximum price out of all the sell orders

* {{co|lightgreen|Column N:}} The minimum price our of all the sell orders

* {{co|lightgreen|Column O:}} The standard deviation of sell orders

* {{co|lightgreen|Column P:}} The median sell price

* {{co|lightgreen|Column Q:}} The percentile of sell orders

* {{co|lightgreen|Column R:}} The total volume over the past 24 hours

* {{co|lightgreen|Column S:}} The average over the past 24 hours

* {{co|lightgreen|Column T:}} The maximum over the past 24 hours

* {{co|lightgreen|Column U:}} The minimum over the past 24 hours

* {{co|lightgreen|Column V:}} The standard deviation over the past 24 hours

* {{co|lightgreen|Column W:}} The median over the pas 24 hours

* {{co|lightgreen|Column X:}} The percentile over the past 24 hours

|}

=== ~~Importing multiple~~ data ===

=== Making use of the 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~~.

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

~~== Final considerations ==~~

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.

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

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.

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

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

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

== Updating data ==

{|

|-

| [[File:Import_data_to_excel_4.png]] || 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.

|}

[[Category:Guides]]

[[Category:Trade]]

@@ Line 1: / Line 1: @@
 {{Work in progress}}
-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 [https://eve-central.com/ EVE-Central] to Excel, to transfer the information to a spreadsheet format and allow them to process the data. This guide assumes the use of Excel 2007.
+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 [https://eve-central.com/ 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 ==
@@ Line 35: / Line 37: @@
 {|
 |-
-| [[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 />
+| [[File:Import_data_to_excel_1.png|350px]] || In Excel 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.
@@ Line 42: / Line 44: @@
 {{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: }}
+  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 result below. }}
-[[File:Import_data_to_excel_2.png]]
+== Understanding imported data ==
-== Understanding imported data ==
+Having imported some sets of data you will get a result similar to the one below:
+[[File:Import_data_to_excel_2.png|center]]
-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]
+=== What the data shows ===
-Columns K to Q are information about sell orders, respectively: Volume, average,
+The information is as follows:
-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.
+|-
+|
+* {{co|lightgreen|Column A:}} The API version (can be ignored)
+* {{co|lightgreen|Column B:}} The 'method' (can be ignored)
+* {{co|lightgreen|Column C:}} The typeID
+* {{co|lightgreen|Column D:}} The volume of buy orders
+* {{co|lightgreen|Column E:}} The average buy price
+* {{co|lightgreen|Column F:}} The maximum price out of all the buy orders
+* {{co|lightgreen|Column G:}} The minimum price out of all the buy orders
+* {{co|lightgreen|Column H:}} The standard deviation of buy orders
+* {{co|lightgreen|Column I:}} The median buy prices
+* {{co|lightgreen|Column J:}} The percentile of buy orders
+* {{co|lightgreen|Column K:}} The volume of sell orders
+* {{co|lightgreen|Column L:}} The average sell price
+||
+* {{co|lightgreen|Column M:}} The maximum price out of all the sell orders
+* {{co|lightgreen|Column N:}} The minimum price our of all the sell orders
+* {{co|lightgreen|Column O:}} The standard deviation of sell orders
+* {{co|lightgreen|Column P:}} The median sell price
+* {{co|lightgreen|Column Q:}} The percentile of sell orders
+* {{co|lightgreen|Column R:}} The total volume over the past 24 hours
+* {{co|lightgreen|Column S:}} The average over the past 24 hours
+* {{co|lightgreen|Column T:}} The maximum over the past 24 hours
+* {{co|lightgreen|Column U:}} The minimum over the past 24 hours
+* {{co|lightgreen|Column V:}} The standard deviation over the past 24 hours
+* {{co|lightgreen|Column W:}} The median over the pas 24 hours
+* {{co|lightgreen|Column X:}} The percentile over the past 24 hours
+|}
-=== Importing multiple data ===
+=== Making use of the 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.
+Once you have this data and know what it stands for, you can start to make use of it in planning your market transactions.
-[[File:Import_data_to_excel_3.png|thumb|center|400px|alt=Image 3|Image 3.]]
+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).
-== Final considerations ==
+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.
-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.
+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.
-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.
+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).
-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.
+== Updating data ==
-[[File:Import_data_to_excel_4.png|thumb|center|100px|alt=Image 4|Image 4.]]
+{|
+|-
+| [[File:Import_data_to_excel_4.png]] || 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.
+|}
 [[Category:Guides]]
 [[Category:Trade]]