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.
 

Excel 2013 api spreadsheet question
Author
Previous Topic Next Topic
Mr Digs
Royal Amarr Institute
Amarr Empire
#1 - 2014-12-11 10:30:58 UTC
Hi All,

I have been attempting to create a capital ship construction spreadsheet. At first on Google Docs, which is great except every so often it crashes horrendously.


So I am trying to create a local copy for myself using Excel.

having read https://forums.eveonline.com/default.aspx?g=posts&m=3003601 I worked out the XML callup I needed to get the data required and I had changed it a little bit to match my needs

Just a couple of questions though

1) I can set an auto update of 60 minutes to pull the data from the eve central API. I will assume this will refresh the data every 60 minutes automatically. Is this assumption correct?

2) I only need the minimum price value of an item in a specific area. The string I am using to collect the data is as follows


http://api.eve-central.com/api/marketstat?typeid=34&usesystem=30000142


This will collect trit prices in a specific area (jita) however it gives me columns I do not want or need (see screenshot)
http://puu.sh/dq5qy/e648650385.png

How do I get it so it only shows me minimum.


Cheers
dirtydebbs
the wreking crew
#2 - 2014-12-11 16:13:57 UTC
thats what i use hope that helps


=importxml("http://api.eve-central.com/api/marketstat?&hours=24&typeid=34&usesystem=30000142","//sell/min")
Caerfinon
Caldari Provisions
Caldari State
#3 - 2014-12-11 18:03:30 UTC
Mr Digs wrote:

How do I get it so it only shows me minimum.


Put the imported XML Table on a single tab, and then reference only the cell on that tab in the place in your Excel workbook where you want that price.

Cheers C.

@Caerfinon - Twitter
Mr Digs
Royal Amarr Institute
Amarr Empire
#4 - 2014-12-11 20:11:04 UTC
Caerfinon wrote:
Mr Digs wrote:

How do I get it so it only shows me minimum.


Put the imported XML Table on a single tab, and then reference only the cell on that tab in the place in your Excel workbook where you want that price.



The obvious suggestions are always the best.

Nice one Thanks
Oriella Trikassi
Trikassi Enterprises
#5 - 2014-12-12 16:44:05 UTC  |  Edited by: Oriella Trikassi
You are almost there - in Excel the format for the price of Tritanium at Jita is

=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34),"/evec_api/marketstat/type/sell/min")

The problem I have is getting the data to refresh. Excel thinks it's 'non-volatile' and so a simple F9 ignores it. The only combination I've found is CTRL+ALT+F9 which triggers a complete recalculation taking several minutes and sometimes running out of resources! There doesn't seem to be a "just do Web calls" button.