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


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
You can get me on Google plus, Twitter or this forum.

For help and more information join our forum,follow the blog or follow me on twitter .





Comments