Excel and the JSON CREST feeds - Fuzzwork Enterprises

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

Share this:

Related