These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

Science & Industry

 
  • Topic is locked indefinitely.
 

Auto Updating Market Spreadsheets
Author
Previous Topic Next Topic
Nate Armstrong
Ven0m Ltd.
#1 - 2013-05-10 04:36:23 UTC
I am looking to make a nice spreadsheet setup for my manufacturing process but I suck at being a excel guru. It doesn't look to hard but Im not entirely sure how the API from websites like Eve-Central or eve-marketData to refresh values like prices in my excel spreadsheet. I would love to make a spreadsheet like this https://forums.eveonline.com/default.aspx?g=posts&t=190211. I would try to use this once but it seems to be broken and the owner has sold the account so I cant contact them or information on how it was compiled. Does anyone have a similar spreadsheet setup that they could show me as a example or explain/point me in the right direction on where I can learn to compile organized spreadsheets like this?

Thanks for any comments! I appreciate you going out of your way to help!

o7 - Nate
Rutger Janssen
Chanuur
The Initiative.
#2 - 2013-05-10 06:59:22 UTC
=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",{34,35}),"//sell/min") for tritanium and pyerite, the {34,35} can also be a cell range like

=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN("&typeid=",$D39:$D49),"//sell/min")

If it only shows 1 entry, select cell and press ctrl+shift+e, google also caches the result so it's not completly real time.
Soldarius
Dreddit
Test Alliance Please Ignore
#3 - 2013-05-10 15:11:55 UTC  |  Edited by: Soldarius
If you are using OpenOffice Calc, they do not have a standard XML import function. You will have to construct a custom XML transform and register it with OOC as a file type, and then program a custom macro to pull all the XML data from eve-central. Because of this, i feel that Excel is far superior than OOC for XML imports.

You will also have to consider what parameters you wish to pass to eve-central. Do you want universal data or just regional? System only? You will also need a list of all the itemtypes from the Eve static data dump, so that eve-central will give you the right data.

Looking at the two example formulas Rutgers gave, and considering that you have stated that you are not a spreadsheet guru, I would like to take a moment to explain the details of what those do.

The first example tells eve-central to give you the sell/min data for items 34 and 35 from system number 30000142.

The second example tells eve-central to give you sell/min data from the same system, but for whatever item types your spreadsheet has in the current sheet from cells SD39 to SD49.

As a small favor, here are the itemtypes for minerals:

  • Tritanium: 34
  • Pyerite: 35
  • Mexallon: 36
  • Isogen: 37
  • Nocxium: 38
  • Zydrine: 39
  • Megacyte: 40
  • Morphite: 11399


Good luck.

http://youtu.be/YVkUvmDQ3HY
Novia Ephrael
You'll Thank Me Later
#4 - 2013-05-11 04:56:05 UTC  |  Edited by: Novia Ephrael
I use Google Docs for this purpose, mostly because it makes it easy to share my spreadsheets with corp members.

Here is the link to one of my spreadsheets that updates itself from EVE-Central, to determine where is best to by minerals / ore, and whether it is better to refine or to just buy the minerals.


Please contact me in-game or on the forums if you have trouble understanding any part of it, or require help with a spreadsheet in the future.


https://docs.google.com/spreadsheet/ccc?key=0AoQ1vEBV3nnRdDJFb2xuUWV0WHdwd0hvZ05fSkUzZVE&usp=sharing


Here is a [not up to date, but includes most typeIDs] list of the item IDs in game - http://eve-files.com/chribba/typeid.txt

Novia Ephrael,
Nate Armstrong
Ven0m Ltd.
#5 - 2013-05-12 23:25:20 UTC
Thanks everyone for commenting. Sorry I wasnt active on getting back, had a crazy weekend. I am using Microsoft Excel 2013 btw.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#6 - 2013-05-13 01:14:04 UTC  |  Edited by: Steve Ronuken
For using excel 2013 to load data from eve market data: Construct a url like:

http://api.eve-central.com/api/marketstat?typeid=34&typeid=35&regionlimit=10000002

Where you list each of the typeids that you want to load the price data for. (see http://www.fuzzwork.co.uk/resources/typeids.csv for a way to match typeids and type names)

Then go to the data ribbon. hit 'from web'. fill in the url and select import.
Hit ok when it complains about the schema.
select 'new worksheet' hit ok.


You'll end up with a sheet that has a bunch of columns. you can grab the bits you need out of it with vlookups against column d, to get the other ones, for use in your sheet.


There's a refresh all button you can use to get updated data.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Skipimp
Caldari Provisions
Caldari State
#7 - 2013-07-25 19:00:43 UTC
Steve Ronuken wrote:
For using excel 2013 to load data from eve market data: Construct a url like:

http://api.eve-central.com/api/marketstat?typeid=34&typeid=35&regionlimit=10000002

Where you list each of the typeids that you want to load the price data for. (see http://www.fuzzwork.co.uk/resources/typeids.csv for a way to match typeids and type names)

Then go to the data ribbon. hit 'from web'. fill in the url and select import.
Hit ok when it complains about the schema.
select 'new worksheet' hit ok.


You'll end up with a sheet that has a bunch of columns. you can grab the bits you need out of it with vlookups against column d, to get the other ones, for use in your sheet.


There's a refresh all button you can use to get updated data.


Do you have to do this for each unique url? I.e. each typeid you want to look up. Is there a way to automate the process given a list of typeIds?
Elena Thiesant
The Scope
Gallente Federation
#8 - 2013-07-25 19:09:01 UTC
You can specify multiple typeIDs in a single URL, there is a character limit though.
eg
http://api.eve-central.com/api/marketstat?typeid=1230&typeid=17470&typeid=17471&usesystem=30002659

I've got a copy of the static data extract that I query to get typeIDs, Steve's site is great for looking up one or two.
Skipimp
Caldari Provisions
Caldari State
#9 - 2013-07-25 19:12:11 UTC
Elena Thiesant wrote:
You can specify multiple typeIDs in a single URL, there is a character limit though.
eg
http://api.eve-central.com/api/marketstat?typeid=1230&typeid=17470&typeid=17471&usesystem=30002659

I've got a copy of the static data extract that I query to get typeIDs, Steve's site is great for looking up one or two.

Yeah I have the .csv of the typeIds so thats not an issue. I didnt know you could lookup multiple at a time. that will save me some time at least. Thank you!