Fuzzwork market data


(Miss Negotiatrix) #1

So I noticed fuzzwork offers an API for current prices including a weighted average which im guessing is the best indicator of current prices if you want to figure for large sales or purchases…

anyway its been… forever really sicne I tried to do any programming and I mostly use excel although I may expand at some point to something else

Can I use this to JUST get the weighted average of a sell or a buy order? say item 34 to make it easy…

if I want it narrowed down to 1 cell I would assume I can use the filterxml(webservice( stuff… for instance:

=FILTERXML(WEBSERVICE(CONCATENATE(“https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=","34”)),???)

I am unsure the xpath Id need yet to narrow it down to 1 cell however

the concatentate is in there so I can change the 34 to a cell ID later


(Miss Negotiatrix) #2

hmmm may be better to just figure out how to edit a URL in a powerquery, atm my powerquery loads this:

https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=2268,2305,2267,2288,2287,2307,2272,2309,2073,2310,2270,2306,2286,2311,2308,3645,2397,2398,2396,2395,9828,2400,2390,2393,3683,2399,2401,3779,2392,2389,2329,3828,9836,9832,44,3693,15317,3725,3689,2327,9842,2463,2317,2321,3695,9830,3697,9838,2312,3691,2319,9840,3775,2328,2358,2345,2344,2367,17392,2348,9834,2366,2361,17898,2360,2354,2352,9846,9848,2351,2349,2346,12836,17136,28974,2867,2868,2869,2870,2871,2872,2875,2876

Which is all teh PI stuff… may be better to just be able to add or remove items from that URL if thats possible, then it all would be in 1 pull vs many pulls which would eat up resources for no real reason

and I probably wont ever need to edit it much, mainly Im thinking for forward compatibility if CCP adds or changes crap lol


(Blacksmoke16) #3

If you use Google Sheet you could use this function:

/**
* Query's Fuzz market API for the given types
* @param {range} A vertical range of type_ids.
* @return maxBuy and minSell for each type_id
* @customfunction
*/
function fuzzApiPriceData(type_ids) {
  if (!type_ids) throw 'type_ids is required';
  var type_ids = Array.isArray(type_ids) ? type_ids.map(function(id) { return id[0]; }) : [type_ids];
  var fuzz_price_data = JSON.parse(UrlFetchApp.fetch("https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=" + type_ids.join(',')));
  return [['minSell', 'maxBuy']].concat(type_ids.map(function(type_id) { return [parseFloat(fuzz_price_data[type_id]['sell']['min']), parseFloat(fuzz_price_data[type_id]['buy']['max'])]; }));
}

Then can give it a range of type_ids like =fuzzApiPriceData(A1:A100)


(Steve Ronuken) #4

what you want to do, I’d suggest, is have it pull the details from a series of cells, and assemble the url.

If you look at the advanced version of the script (which is a script, rather than just the steps) you can see the url. And you can hit sheet cells from there, assembling the list.


(Miss Negotiatrix) #5

TY! Thats exactly the sort of thing I was looking for :slight_smile:

so 1 other section I am doing makes it so it will automatically load up how many planets are available… to do this I obviously need to bust out the SDE…

I am using mapsolarsystems to do a search for solarsystemid basically a indexfunction with a match for the J id but it will work for any solarsystem id really…

I havent done this part yet, as it it got late and I needed sleep :slight_smile:

But Im planning on using mapdnormalize to match how many items in that category have the correct solar system as well as groupID 7?? I think anyway it counts matches between planets and solarsystemID

I’ll use the same general idea to list said planets and get their types so I can have an accurate option of planets in the worksheet…

I’ll probably get into vbscript and program it using SQL code since I dont think there is a good way to do this in excel without vbscript…

Anyway, is there a better way to achieve this in your opinion?


(Steve Ronuken) #6

I’d suggest using an odbc connection with sqlite.


(Miss Negotiatrix) #7

I thought of that but the only catch is if I want to give the excel worksheet to others… unless I just host the files on a webserver I guess.

but at that point I may as well teach myself a real programming language and do the thing correctly :confused:


(Miss Negotiatrix) #8

not that vbscript isint real I suppose but not very practical outside of excel :slight_smile:


(Miss Negotiatrix) #9

So if I decide to give in and do it properly… what language should I teach myself?

I’ll be honest here, I mostly teach myself everything… back in highschool and some college was the last time I really programmed I think…

I knew HTML pretty well along with CSS of course… I wasnt the best with jscript, ok I sucked at it lol… and I had a workable knowledge in php… mainly so I could edit wordpress sites.

I was pretty good in excel vbscript back in the day, Ive forgotten a lot since its been years but it is coming back to me as I work… some slower than I like but it is coming back to me.

So with that in mind if I want to make my PI helper properly, what language would you reccomend I do it in, since I’ll probably be teaching myself along the way I’m open to suggestions.

Basically I’ll need the program to interact with SDE, possibly ESI ( I see no reason yet but may as well be a language that I can)

If Im writing it from scratch I may as well make it so it can be accessed in a website but Im open to anything here really.

So what language should I start learning to do this properly


(system) closed #10

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.