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

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

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Importing avg volume into spreadsheet / ?
Author
Previous Topic Next Topic
CJ Alland
CB Trading
#1 - 2015-06-15 17:36:18 UTC
Hello,

I'm trying to add one final thing into my spreadsheet (for now) and thats a average of items sold over a time period. say. 30 days.


I'm currently using fuzzworks scripts to import items market data but am having trouble editing it to allow me to import the 30 day avg. I'm sure it's something simple i'm missing but hey ho.

I can provide screen shots and stuff at request if that'll help,

thanks
Jarno Midumulf
Riders of Sleipnir
Backdoor Crashers
#2 - 2015-06-16 09:08:48 UTC
CJ Alland wrote:
Hello,

I'm trying to add one final thing into my spreadsheet (for now) and thats a average of items sold over a time period. say. 30 days.


I'm currently using fuzzworks scripts to import items market data but am having trouble editing it to allow me to import the 30 day avg. I'm sure it's something simple i'm missing but hey ho.

I can provide screen shots and stuff at request if that'll help,

thanks


if you post some code i can take a look.
CJ Alland
CB Trading
#3 - 2015-06-16 12:25:11 UTC
function loadPrices(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 xmlFeed = UrlFetchApp.fetch(url+cleanTypeIds.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=[rows[i].getAttribute("id").getValue(),
rows[i].getChild("buy").getChild("volume").getValue(),
rows[i].getChild("buy").getChild("avg").getValue(),
rows[i].getChild("buy").getChild("max").getValue(),
rows[i].getChild("buy").getChild("min").getValue(),
rows[i].getChild("buy").getChild("stddev").getValue(),
rows[i].getChild("buy").getChild("median").getValue(),
rows[i].getChild("buy").getChild("percentile").getValue(),
rows[i].getChild("sell").getChild("volume").getValue(),
rows[i].getChild("sell").getChild("avg").getValue(),
rows[i].getChild("sell").getChild("max").getValue(),
rows[i].getChild("sell").getChild("min").getValue(),
rows[i].getChild("sell").getChild("stddev").getValue(),
rows[i].getChild("sell").getChild("median").getValue(),
rows[i].getChild("sell").getChild("percentile").getValue()];
prices.push(price);
}
}
return prices;
}
CJ Alland
CB Trading
#4 - 2015-06-16 12:26:27 UTC
I'm going to edit what imported when I can import the 30 day averages. So I'll just be grabbing the max and mins for both sell and buy aswell as the averages.
CJ Alland
CB Trading
#5 - 2015-06-16 15:30:04 UTC
Jarno Midumulf wrote:
CJ Alland wrote:
Hello,

I'm trying to add one final thing into my spreadsheet (for now) and thats a average of items sold over a time period. say. 30 days.


I'm currently using fuzzworks scripts to import items market data but am having trouble editing it to allow me to import the 30 day avg. I'm sure it's something simple i'm missing but hey ho.

I can provide screen shots and stuff at request if that'll help,

thanks


if you post some code i can take a look.


any ideas? :D
Jarno Midumulf
Riders of Sleipnir
Backdoor Crashers
#6 - 2015-06-17 06:46:43 UTC
CJ Alland wrote:


var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+systemID+"&typeid=";



the eve central api does not provide the average items in 30 days (i think, feel free to correct me if i am wrong).

you can do this whit the crest api like this:
https://public-crest.eveonline.com/market/{SYSTEM}/types/{ITEM}/history/
and if you include the system and item id:
https://public-crest.eveonline.com/market/10000002/types/34/history/

and this will return these values in json format:
"volume_str"
"orderCount"
"lowPrice"
"highPrice"
"avgPrice"
"volume"
"orderCount_str"
"date"

as you can see this has already the low, high and avg price already.
CJ Alland
CB Trading
#7 - 2015-06-17 15:19:50 UTC
Jarno Midumulf wrote:
CJ Alland wrote:


var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+systemID+"&typeid=";



the eve central api does not provide the average items in 30 days (i think, feel free to correct me if i am wrong).

you can do this whit the crest api like this:
https://public-crest.eveonline.com/market/{SYSTEM}/types/{ITEM}/history/
and if you include the system and item id:
https://public-crest.eveonline.com/market/10000002/types/34/history/

and this will return these values in json format:
"volume_str"
"orderCount"
"lowPrice"
"highPrice"
"avgPrice"
"volume"
"orderCount_str"
"date"

as you can see this has already the low, high and avg price already.



yeah ended up finding out that later that day. Got it working now.


I didn't realise you could pull the buy/max and sell/min from crest or can you not? if so how exactly would I go about that? What do I need to replace in the code? :D
Jarno Midumulf
Riders of Sleipnir
Backdoor Crashers
#8 - 2015-06-18 09:51:43 UTC
CJ Alland wrote:


yeah ended up finding out that later that day. Got it working now.


I didn't realise you could pull the buy/max and sell/min from crest or can you not? if so how exactly would I go about that? What do I need to replace in the code? :D


ahh my bad, you can not do that whit crest if i am correct..

i also read the api off ave central again and i saw that you can include "hours" in url, so that way you can do it..

it would look like this:
http://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=34&hours=720

this would return the the avg volume from the last 720h (30 days).
CJ Alland
CB Trading
#9 - 2015-06-20 23:49:03 UTC  |  Edited by: CJ Alland
Jarno Midumulf wrote:
CJ Alland wrote:


yeah ended up finding out that later that day. Got it working now.


I didn't realise you could pull the buy/max and sell/min from crest or can you not? if so how exactly would I go about that? What do I need to replace in the code? :D


ahh my bad, you can not do that whit crest if i am correct..

i also read the api off ave central again and i saw that you can include "hours" in url, so that way you can do it..

it would look like this:
http://api.eve-central.com/api/marketstat?regionlimit=10000002&typeid=34&hours=720

this would return the the avg volume from the last 720h (30 days).



That would be avg volume of items sold?

How would that be implemented into the code I pasted at the top?
Jarno Midumulf
Riders of Sleipnir
Backdoor Crashers
#10 - 2015-06-22 12:44:35 UTC
CJ Alland wrote:


That would be avg volume of items sold?

How would that be implemented into the code I pasted at the top?


yes.

you need to change the top part off the code to something like this:

function loadPrices(priceIDs,systemID,cachebuster,hours){
if (typeof systemID == 'undefined') {
systemID=30000142;
}

if (typeof priceIDs == 'undefined') {
throw 'need typeids';
}

if (typeof cachebuster == 'undefined') {
cachebuster=1;
}

if (hours == 'undefined') {
hours = 24;
}

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+"&hours="+hours+"&typeid=";

and then where you use the function you need to add the hours.
or you could use "function loadPrices(priceIDs,systemID,cachebuster,hours = 24)" but i am not shure if that will work in vba
CJ Alland
CB Trading
#11 - 2015-06-22 14:58:27 UTC
Jarno Midumulf wrote:
CJ Alland wrote:


That would be avg volume of items sold?

How would that be implemented into the code I pasted at the top?


yes.

you need to change the top part off the code to something like this:

function loadPrices(priceIDs,systemID,cachebuster,hours){
if (typeof systemID == 'undefined') {
systemID=30000142;
}

if (typeof priceIDs == 'undefined') {
throw 'need typeids';
}

if (typeof cachebuster == 'undefined') {
cachebuster=1;
}

if (hours == 'undefined') {
hours = 24;
}

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+"&hours="+hours+"&typeid=";

and then where you use the function you need to add the hours.
or you could use "function loadPrices(priceIDs,systemID,cachebuster,hours = 24)" but i am not shure if that will work in vba



Thanks. Going to give it a go now :)