Fuzzwork
This is for Excel only I’m afraid. And only 2013 and up. Google Sheets just won’t load the size of file that this uses.
On https://market.fuzzwork.co.uk I generate aggregate data for all items sold on the market. This is things like average price, minimum price, maximum price, and the average price if you were to buy the cheapest 5% of the market. (handy for evening out any outliers.) This is available via an api, which is documented on the site. There’s a function for google sheets, and instructions on how to use it with power query in Excel.
However, this isn’t wonderful if you want to get everything. To that end, I’ve started dumping out a file with all the data in it. This is updated once every 30 minutes (or so), and is about 24MB uncompressed. Google sheets chokes on it. Excel handles it ok though. So that’s what I’m going to talk about here. I use Excel 2016, so this is focused towards that. I think it’ll work with 2013, but I have no way to test that.
The URL for the data is https://market.fuzzwork.co.uk/aggregatecsv.csv.gz and is a gzipped csv. With headers. The first column is the region id, the typeid, and if it’s a buy or sell order.
To load this into Excel, create a new sheet. On that sheet, create a new query (Data ribbon, get data, from other sources, blank query). In the advanced editor, you want to paste in:
let
let
Source = Binary.Decompress(Web.Contents("http://market.fuzzwork.co.uk/aggregatecsv.csv.gz"),Compression.GZip),
#"Imported CSV" = Csv.Document(Source,[Delimiter=",", Columns=10, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"what", type text}, {"weightedaverage", type number}, {"maxval", type number}, {"minval", type number}, {"stddev", type number}, {"median", type number}, {"volume", type number}, {"numorders", Int64.Type}, {"fivepercent", type number}, {"orderSet", Int64.Type}})
in
#"Changed Type"
That should then load all the aggregate data into a sheet for you. Call that sheet something appropriate, like marketdata
Then to query it:
=VLOOKUP(“10000002|34|true”,marketdata!$A:$J,9,FALSE)
Referring to the sheet name you configured. you can assemble the lookup value as you want to. Region ID, typeID, if you want buy or sell orders. true for buy, false for sell.
Something like
=VLOOKUP(“10000002|”&A1&”|false”,marketdata!$A:$J,9,FALSE)
works. Where A1 contains the typeid you’re interested in.
