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