Creating a workbook from JSON manifest

Setting up the workbook layout and format

Here we are going to create a workbook from the JSON manifest. The relevant section is below, and can be found at manifest.child(“setup”). Each type of data is identifed by { “type”: “someType” } and contains options {} for how to handle the API response, some formatting information. In addition, APIs often return time in UNIX format (see this post for how to deal with those in Excel), so convertTimes{} describes which columns need to be converted from Unix times, and where to put the resultant Excel Time.

{
    "setup": {
        "types": [
            {
                "type": "ticker",
                "options": {
                    "timeFormat": "dd-mmm-yyy hh:mm:ss",
                    "fillColor": "#F79646",
                    "resultsStem": "ticker",
                    "manual": false,
                    "action": "insert",
                    "columns": [
                        "high",
                        "low",
                        "avg",
                        "vol",
                        "vol_cur",
                        "last",
                        "buy",
                        "sell",
                        "server_Time",
                        "at"
                    ],
                    "convertTimes": [
                        {
                            "from": "server_Time",
                            "to": "at"
                        }
                    ]
                }
            },
            {
                "type": "trades",
                "options": {
                    "timeFormat": "dd-mmm-yyy hh:mm:ss",
                    "fillColor": "#F79646",
                    "resultsStem": "",
                    "manual": false,
                    "action": "clear",
                    "columns": [
                        "Date",
                        "Price",
                        "Amount",
                        "tid",
                        "Price_Currency",
                        "Item",
                        "Trade_Type",
                        "at"
                    ],
                    "convertTimes": [
                        {
                            "from": "Date",
                            "to": "at"
                        }
                    ]
                }
            },
            {
                "type": "depth",
                "options": {
                    "fillColor": "#F79646",
                    "resultsStem": "asks",
                    "manual": true,
                    "action": "clear",
                    "columns": [
                        "Price",
                        "Volume"
                    ]
                }
            }
        ]
    }
}

Here’s the code

Private Function btcCreateWorkBook(Optional check As Boolean = True) As Boolean
    ' delete dashboard, plus all potential sheets of interest, and create new ones
    Dim job As cJobject, co As Collection, manifest As cJobject, workJob As cJobject, _
        joc As cJobject, venueJob As cJobject, ws As Worksheet, jor As cJobject
    Set co = New Collection
    btcCreateWorkBook = False
    Set manifest = getManifest()
    
    
    'delete all potential existing sheets
    For Each job In manifest.child("setup.types").children
        Set co = findSheetsStartingWith(job.child("type").toString & "_", co)
    Next job
    
    If co.count > 0 Then
        If check Then
            If MsgBox("need to delete " & co.count & " existing worksheets", vbYesNo) <> vbYes Then
                manifest.tearDown
                Exit Function
            End If
        End If
        deleteSheetsInCollection co
    End If


    'now create new ones
    For Each job In manifest.child("setup.types").children
    
        'find the worklist for this type
        For Each workJob In manifest.child("work").children
            If workJob.toString("type") = job.toString("type") Then
                For Each venueJob In workJob.child("venues").children
                    ' create a new sheet
                    With Sheets.add(, Sheets(Sheets.count))
                        .name = workJob.toString("type") & "_" & venueJob.toString
                        ' prettify
                        With .Cells(1, 1)
                            colorizeCell .Resize(, job.child("options.columns").children.count), _
                                                job.toString("options.fillColor")
                            
                            ' add the columns for this type
                            For Each joc In job.child("options.columns").children
                                .Offset(, joc.childIndex - 1).value = joc.value
                            Next joc
                        End With
                    End With
                Next venueJob
            End If
        Next workJob
    Next job
    
    manifest.tearDown
    Set co = Nothing

End Function

Walkthrough

Deleting existing Sheets

All the sheets to contain data returned from the bitCoin API will be called something like type_venue, so the first step is to delete any existing sheets for each type in the manifest

 'delete all potential existing sheets
    For Each job In manifest.child("setup.types").children
        Set co = findSheetsStartingWith(job.child("type").toString & "_", co)
    Next job
    
    If co.count > 0 Then
        If check Then
            If MsgBox("need to delete " & co.count & " existing worksheets", vbYesNo) <> vbYes Then
                manifest.tearDown
                Exit Function
            End If
        End If
        deleteSheetsInCollection co
    End If

Creating the new sheets

For Each venueJob In workJob.child("venues").children
                    ' create a new sheet
                    With Sheets.add(, Sheets(Sheets.count))
                        .name = workJob.toString("type") & "_" & venueJob.toString
                        ' prettify
                        With .Cells(1, 1)
                            colorizeCell .Resize(, job.child("options.columns").children.count), _
                                                job.toString("options.fillColor")
                            
                            ' add the columns for this type
                            For Each joc In job.child("options.columns").children
                                .Offset(, joc.childIndex - 1).value = joc.value
                            Next joc
                        End With
                    End With
                Next venueJob

Formatting the heading cells

We can use the functions described in Playing around with colors in VBA to apply the hex color provided in the options to the headers, as well as to colorize the font to a suitable contrasting color.

colorizeCell .Resize(, job.child("options.columns").children.count), job.toString("options.fillColor")

For more on this see. Data driven VBA apps with JSON