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.
 

Importing API market data by Item and System into Google Docs

Author
Ashley Traynor
Republic Military School
Minmatar Republic
#1 - 2015-04-27 22:40:46 UTC
So I'm trying to put together a spreadsheet where I can enter an item & system and have it return max buy and min sell. I've tried fiddling with this but it keeps telling me the imported content is empty.

I'm passing in typeID as a single column and a concatenated 'TypeID, SystemID' as that site asks for:
Quote:
=ImportXML("http://api.eve-marketdata.com/api/price_type_solarsystem_buy_max.xml?char_name=Zoey Traynor&type_id="&JOIN(",",$F5:$F66)&"&v="&JOIN(",",$G5:$G66), "/emd/val")


If I just pass in a TypeID then I can grab jita prices using 'type_jita_price_buy' but I want to be able to select the hub in a dropdown...
Quote:
=ImportXML("http://api.eve-marketdata.com/api/type_jita_price_buy.xml?char_name=Zoey Traynor&v="&JOIN(",",$F5:$F66); "/emd/val") (This works)


Am I missing something here? I have vlookups to pull and concatenate the data properly, do I need to know what that "&v=" bit means or something? I made a copy of the sheet below to look at.


https://docs.google.com/spreadsheets/d/1_y-AWhvnBlod-VxOrnw9fOmMcBBRF0nCtBL48b8QViE/edit?usp=sharing
Gregor Noobius
Republic Military School
Minmatar Republic
#2 - 2015-04-28 09:23:24 UTC  |  Edited by: Gregor Noobius
Put this script in the script editor:

function loadJita(priceIDs,systemID,cachebuster){
  if (typeof systemID == 'undefined'){
    systemID=30000142;
  }
  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+"&usesystem="+systemID+"&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()),
                   parseFloat(rows[i].getChild("buy").getChild("max").getValue()),
                   parseFloat(rows[i].getChild("sell").getChild("min").getValue()),
                   parseInt(rows[i].getChild("buy").getChild("volume").getValue()),
                   parseInt(rows[i].getChild("sell").getChild("volume").getValue())];
        prices.push(price);
      }
    }
  }
  return prices;
}


Then in a cell type 'loadJita(B5:10)" where B5:B10 is referencing the cells with typeIDs of the items you want. It will spit out data in the order of typeId, buy max, sell min, buy volume, sell volume. You can modify what it spits out at the bottom of the code.

I found this easier and more reliable than importXML. Spit out all the data on one sheet, then make another that references all the raw data to manipulate it.

Using this you could make multiple functions like loadJita, loadAmarr, etc.

Credit goes to Steve Ronuken for the script, I modified it a bit for the data I want. You can change the system near the top of the code - systemID=
Ashley Traynor
Republic Military School
Minmatar Republic
#3 - 2015-04-28 12:05:56 UTC
Thanks for that script, it work and I can pull back the info from the tables it generates using a vlookup so that's nice. I'm just thinking now though, how can I change the values when i select another system. Modifying the script for other hubs is simple but obviously a vlookup only takes data from one column and gives the corresponding value from another column.

Would it be sensible to concatenate 'TypeID, SystemID' next to each script output then use that as my reference for the vlookup or is there a better way? I assume that would work fine, it just means leaving substantial space above and below loadJita, loadAmarr and so on to make sure that they loadJita doesn't try to spit data on top of loadAmarr's table if it gets too long.