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

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

Market Discussions

 
  • Topic is locked indefinitely.
 

Questions about Spreadsheet 2013 Excel
Author
Previous Topic Next Topic
Joshua Trader
Black Hole Logistics
#1 - 2014-05-19 01:53:06 UTC
Thanks in advance for any advice.

Basically I bought the new Excel 2013 because it has the new import xml and webservice functions. Now I have been teaching myself to make spreadsheets but I have a very hard time constructing the urls to pull data. Pretty much everything I have I got from Fuzzworks and some google searches.

Right now I am using
=FILTERXML(WEBSERVICE("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&A24),"/evec_api/marketstat/type/sell/min")

to pull the sell minimum for the item.

I also know you can do /evec_api/marketstat/type/all/volume to get the volume, but I only know that from seeing others do it and I dont actually know what the number it gives me refers to. If I use it on the string above I get 33131404 for Amarr for EMP L. IS that the buy and sell order volume? Over how many days? Im so lost.
I want to know where I can learn to put the search parameters together myself.
Say I want to pull the sell volume for the last day or for the last week how do I do that? or say I want to do the avg price of the item for the last 3 days, or for the last 7 days, or for the last year is it possible?

Also how do I get this stuff to update on Excel. It seems when I hit Refresh All it does nothing. The only way I can ever seem to get it to update is to pull the function down over the cells again to force it to recalculate.


Thanks for any advice. I enjoy learning how to do spreadsheets but constructing the parameters is rough.

Thur Barbek
Republic University
Minmatar Republic
#2 - 2014-05-19 08:09:49 UTC  |  Edited by: Thur Barbek
There is probably a setting somewhere in excel preferences for how often to run external api calls. I am not 100% sure.

Below I try to explain what eve central does, skip to bottom if you want to use the easier eve-marketdata api instead of eve-central.

Eve-central's api - http://dev.eve-central.com/evec-api/start

You will see fields such as:
Marketstat call:
typeid The type ID to be queried
sethours Get orders which have been posted within the last X hours. Defaults to 36
regionlimit Restrict the view to only show from within the specified region IDs. Can be specified multiple times.
usesystem Restrict the view to the following system only.
setminQ Restrict the view to show only orders above the specified quantity

Note that there are several types of calls- quicklook, marketstat,history... each has different paramaters.

Eve-central's api is a bit odd due to how it returns data. You can paste your command into a web browser to see what it returns. Your current command returns a big list of information in XML format. you want to narrow down this info to one section. This is what the second part is for.

example - http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=34 (copy/paste this cause eve forums doesnt like the & symbol)

if you add "/evec_api/marketstat/type/sell/median" it should filter down to the median price for sell orders. If you look at the above link to see what is getting returned, you can see how it filters to type 34, then sell section, then the specific value defined as median.

OR you can not use eve central and use eve-market-data. You can achieve what you want with a command like:
-http://api.eve-marketdata.com/api/importxml_prices2.xml?char_name=demo&buysell=s&usesystem=30000142&type_ids=34

Replace "34" with a list of cells ideally. "&type_ids="&JOIN(",",$F3:$F32)" or something similar to do that. more info for this api -http://eve-marketdata.com/developers/item_prices2.php
Magnu Stormhawk
#3 - 2014-05-19 10:30:41 UTC
Joshua Trader wrote:

I also know you can do /evec_api/marketstat/type/all/volume to get the volume, but I only know that from seeing others do it and I dont actually know what the number it gives me refers to. If I use it on the string above I get 33131404 for Amarr for EMP L. IS that the buy and sell order volume? Over how many days? Im so lost.
I want to know where I can learn to put the search parameters together myself.
Say I want to pull the sell volume for the last day or for the last week how do I do that? or say I want to do the avg price of the item for the last 3 days, or for the last 7 days, or for the last year is it possible?


I think there is data for buy volume, sell volume, and all volume (ie both), but it is the total number of outstanding buy/sell/both orders and not the volume traded, which I think you are looking for. I'm not sure how to get the daily volume, i'd like to know myself.

Joshua Trader wrote:

Also how do I get this stuff to update on Excel. It seems when I hit Refresh All it does nothing. The only way I can ever seem to get it to update is to pull the function down over the cells again to force it to recalculate.



It should refresh when you use the refresh all function. I use the import from web function in the data tools, and every table that is created by that in my spreadsheet is refreshed when i us the refresh all.

Thur Barbek wrote:


Replace "34" with a list of cells ideally. "&type_ids="&JOIN(",",$F3:$F32)" or something similar to do that. more info for this api -http://eve-marketdata.com/developers/item_prices2.php



I'm not sure that works in excel, the join function works in google docs though. I'd love to be corrected though.

Disclaimer: I'm a noob at this.
Joshua Trader
Black Hole Logistics
#4 - 2014-05-19 14:00:37 UTC


Joshua Trader wrote:

Also how do I get this stuff to update on Excel. It seems when I hit Refresh All it does nothing. The only way I can ever seem to get it to update is to pull the function down over the cells again to force it to recalculate.



Magnu Stormhawk wrote:
It should refresh when you use the refresh all function. I use the import from web function in the data tools, and every table that is created by that in my spreadsheet is refreshed when i us the refresh all.



Yea I think my main issue with refresh is I didn't use the import web function. The function I listed in the first post above is actually in the cell it self for each and every cell instead of pulling data on one page and putting the data I want on another.

Oriella Trikassi
Trikassi Enterprises
#5 - 2014-05-19 21:37:54 UTC  |  Edited by: Oriella Trikassi
WEBSERVICE in Excel 2013 is what Microsoft calls "non-volatile" and doesn't Refresh normally. You really have to shout at Excel with CTRL+SHIFT+F9 which recalculates EVERYTHING. And yes, it took me a while to find that out! Grr.

In my case that means waiting for several minutes whilst Excel mutters about being overworked, so that's the point where I go and fetch my carebear milk and cookies before logging on to EVE.

The possible entries for the eve-central XML call are fairly obvious if you pull down the XML map. There are three sections "buy", "sell" and "all" and these each have subsections volume,avg,max,min,stddev,median,percentile. They are the latest figures, eve-central doesn't archive past data. EVE does of course and uses the data to draw those pretty graphs.
Joshua Trader
Black Hole Logistics
#6 - 2014-05-20 15:53:57 UTC
Oriella Trikassi wrote:
WEBSERVICE in Excel 2013 is what Microsoft calls "non-volatile" and doesn't Refresh normally. You really have to shout at Excel with CTRL+SHIFT+F9 which recalculates EVERYTHING. And yes, it took me a while to find that out! Grr.

In my case that means waiting for several minutes whilst Excel mutters about being overworked, so that's the point where I go and fetch my carebear milk and cookies before logging on to EVE.

The possible entries for the eve-central XML call are fairly obvious if you pull down the XML map. There are three sections "buy", "sell" and "all" and these each have subsections volume,avg,max,min,stddev,median,percentile. They are the latest figures, eve-central doesn't archive past data. EVE does of course and uses the data to draw those pretty graphs.





Thanks so much for your help, I was hoping I could use the same info EVE did on my spreadsheet but I see now that isn't possible.


Ctrl shift F9 will be much easier than what I have been doing so thanks!