More and more things are being put into EVE’s new public CREST endpoints.
For people who do industry, the most important 2 which are now there, are Cost Indexes, and adjusted prices.
Now, I’m not much of one for using spreadsheets myself, preferring to code a custom solution, but I know there’s a large group of people who won’t live without them.
Lockefox has put together a solution for googledocs. I’ve not used it, but it probably works well. If you need it, go take a look.
That leaves Excel. It used to be that working with things in Excel was easier, as it had better handling for loading entire XML sheets, rather than single cells. Unfortunately, it has no native JSON support. Even if it did, it’s nested data, which makes it somewhat harder to pull out.
Looking at the cost indexes, first you have an array of solar systems, and within each of those, you have an array of cost indexes. So you have to manually flatten that 3d data structure, into a 2d one.
I spent some time working on this, and came up with: costIndexes
It’s a macro enabled Excel workbook built in excel 2010, which loads in the cost indexes. I leave price data as an exercise for the reader. It takes a while to work but that’s because it’s processing a relatively large file, and it probably isn’t the most efficient way of doing it.
Now, to explain how it works, it’ll be easier if you open up Visual basic, from the development ribbon (file->options->customize ribbon, tick the development tab)
It’s including 3 files from http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html. The JSON modules, the cJSONScript and cStringBuilder class modules
Then you have the following subroutine in the source for the workbook. Finally, you have a button which calls the Test() subroutine. All in all, it’s actually pretty simple (because the json parser makes it easy)
Long term, there are probably better ways to do it, using .net or similar. But it works.
Sub Test() Dim oHttp As Object Dim jsonText As String Dim jsonObj As Dictionary Dim jsonRows As Collection Dim jsonRow As Dictionary Dim costInex As Collection Dim ws As Worksheet Dim currentRow As Long Dim startColumn As Long Dim i As Long Set oHttp = CreateObject("MSXML2.XMLHTTP") If Err.Number <> 0 Then Set oHttp = CreateObject("MSXML.XMLHTTPRequest") MsgBox "Error 0 has occured while creating a MSXML.XMLHTTPRequest object" End If On Error GoTo 0 If oHttp Is Nothing Then MsgBox "For some reason I wasn't able to make a MSXML2.XMLHTTP object" Exit Sub End If oHttp.Open "GET", "https://public-crest.eveonline.com/industry/systems/", False oHttp.Send 'Create a real JSON object jsonText = oHttp.responseText Set ws = Worksheets("CostIndexes") 'Parse it Set jsonObj = JSON.parse(jsonText) 'Get the rows collection Set jsonRows = jsonObj("items") 'Set the starting row where to put the values currentRow = 1 'First column where to put the values startColumn = 1 'A 'Loop through all the values received For Each jsonRow In jsonRows currentRow = currentRow + 1 ws.Cells(currentRow, startColumn).Value = jsonRow("solarSystem")("id") For Each costIndex In jsonRow("systemCostIndices") ws.Cells(currentRow, costIndex("activityID") + 1).Value = costIndex("costIndex") Next costIndex Next jsonRow End Sub
Excel and the JSON CREST feeds by Fuzzwork Enterprises is licensed under a Creative Commons Attribution 4.0 International License.