EVE Technology Lab

o/

I tried to use fuzzysteve's script in google sheet and it really worked well! Thanks a lot for this!

But, marketdatas imported from an entire system, like Jita or Amarr could have some "mysterious" results. The first I think about is getting sell/min order value below buy/max order. In that case it's impossible to have real production costs...

For this reason, I tried to improve Steve's script with adding a 3th script to get only specific station orders...

I have taken Jita 4 and Amarr VIII for my tests, 2 commercial hubs. Maybe I have taken bad stationId, but i don't think so : Amarr : 60008494 ; Jita : 60003760. For the systemId : Jita : 30000142 and Amarr : 30002187

I juste made some requests and i have this day again weird results :

http://imgur.com/s2z9zjF

First loop get =loadSystemPrices(Ids,JITA system)
Second loop get =loadStationPrices(Ids, JITA 4 - Jita IV - Moon 4 - Caldari Navy Assembly Plant)

If I try to access these station datas in my browser directly with the good API link, i get the same values...

As you can see, buy orders seem to be correct and generally are the same as eve-central.com. However, sell orders are totally broken...

For being sure, here's my modifier script :

/*
* Loads prices for a given set of typeIDs for a specific station using Eve-Central's data.
* @param priceIDs A range where the item typeIDs are found.
* @param systemID The system to query.
* @param {number} cachebuster Increment this variable to refresh the data.
* @return The price data in multiple columns in the following order: TypeID,Buy Volume,Buy average,Buy max,Buy min,Buy Std deviation,Buy median,Buy Percentile,Sell Volume,Sell Average,Sell Max,Sell Min,Sell std Deviation,Sell Median,sell Percentile. This is suitable for use with VLOOKUP.
* @customfunction
*/
function loadStationPrices(priceIDs,stationID,cachebuster){
  if (typeof stationID == 'undefined'){
    stationID=60003760;     /* Amarr : 60008494 ; Jita : 60003760 */
  }
  if (typeof priceIDs == 'undefined'){
    throw 'need typeids';
  }
  if (typeof cachebuster == 'undefined'){
    cachebuster=1;
  }
  var prices = new Array();
  var dirtyTypeIds = new Array();
  var cleanTypeIds = new Array();
  var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usestation="+stationID+"&typeid=";
  priceIDs.forEach (function (row) {
    row.forEach ( function (cell) {
      if (typeof(cell) === 'number' ) {
        dirtyTypeIds.push(cell);
      }
    });
  });
  cleanTypeIds = dirtyTypeIds.filter(function(v,i,a) {
    return a.indexOf(v)===i;
  });
  var parameters = {method : "get", payload : ""};
  
  var o,j,temparray,chunk = 100;
  for (o=0,j=cleanTypeIds.length; o < j; o+=chunk) {
    temparray = cleanTypeIds.slice(o,o+chunk);
    var xmlFeed = UrlFetchApp.fetch(url+temparray.join("&typeid="), parameters).getContentText();
    var xml = XmlService.parse(xmlFeed);
    if(xml) {
      var rows=xml.getRootElement().getChild("marketstat").getChildren("type");
      for(var i = 0; i < rows.length; i++) {
        var price=[parseInt(rows[i].getAttribute("id").getValue()),
                   parseInt(rows[i].getChild("buy").getChild("volume").getValue()),
                   parseFloat(rows[i].getChild("buy").getChild("avg").getValue()),
                   parseFloat(rows[i].getChild("buy").getChild("max").getValue()),
                   parseFloat(rows[i].getChild("buy").getChild("min").getValue()),
                   parseFloat(rows[i].getChild("buy").getChild("stddev").getValue()),
                   parseFloat(rows[i].getChild("buy").getChild("median").getValue()),
                   parseFloat(rows[i].getChild("buy").getChild("percentile").getValue()),
                   parseInt(rows[i].getChild("sell").getChild("volume").getValue()),
                   parseFloat(rows[i].getChild("sell").getChild("avg").getValue()),
                   parseFloat(rows[i].getChild("sell").getChild("max").getValue()),
                   parseFloat(rows[i].getChild("sell").getChild("min").getValue()),
                   parseFloat(rows[i].getChild("sell").getChild("stddev").getValue()),
                   parseFloat(rows[i].getChild("sell").getChild("median").getValue()),
                   parseFloat(rows[i].getChild("sell").getChild("percentile").getValue())];
        prices.push(price);
      }
    }
  }
  return prices;
}

What's wrong with those sell orders prices (or my script, or whatever..)?

Mh... I think my question is stupid or results are too much weird when no one can helps. *o*

Hum, of course if i try to get results with an invalid parameter....

Ty as well!