Creating the dashboard

Making a ticker dashboard for all venues

Next we’ll create a dashboard – most recent ticker data for each venue on one page that looks like this.

The dashboard is described in this piece of the manifest

 "dashboards": [
        {
            "type": "ticker",
            "name": "tickerDashboard",
            "timeFormat": "dd-mmm-yyy hh:mm:ss"
        }
    ],

and the venues to be included

"work": [
        {
            "type": "ticker",
            "venues": [
                "btc_usd",
                "btc_eur",
                "btc_rur",
                "ltc_btc",
                "ltc_usd",
                "ltc_rur",
                "nmc_btc",
                "usd_rur",
                "eur_usd",
                "nvc_btc",
                "trc_btc",
                "ppc_btc",
                "ftc_btc"
            ]

Here’s the code

Public Function btcMakeDashboard(Optional dashType As String = "ticker") As Boolean

    ' this creates an empty dashboard based on the latest ticker values
    ' should setup the workbook sheets first
    Dim boardName As String, co As Collection, jobDash As cJobject, jobSetup As cJobject, jobWork As cJobject, joc As cJobject, _
        prefix As String, ws As Worksheet, rhead As Range, r As Range, rData As Range, manifest As cJobject, jor As cJobject

    Set manifest = getManifest()
    
    ' find out all about this kind of dashboard
    Set jobDash = findInChildren(manifest.child("dashboards"), "type", dashType)
    If jobDash Is Nothing Then
        MsgBox "cant find type " & dashType & " in dashboard description"
        Exit Function
    End If
    
    ' delete the existing dashboard
    Set ws = sheetExists(jobDash.child("name").toString, False)
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
    
    ' make a new one
    Set ws = Sheets.add(, Sheets(manifest.toString("manifest.name")))
    ws.name = jobDash.child("name").toString
    Set rData = ws.Cells(1, 1)
    
    ' get whats in this type
    Set jobSetup = findInChildren(manifest.child("setup.types"), "type", dashType)
    If jobSetup Is Nothing Then
        MsgBox "cant find type " & dashType & " in setup description"
        Exit Function
    End If

    ' get what work needs done in this type
    Set jobWork = findInChildren(manifest.child("work"), "type", dashType)
    If jobWork Is Nothing Then
        MsgBox "cant find type " & dashType & " in work description"
        Exit Function
    End If

    ' add the columns
    colorizeCell ws.Cells(1, 1).Resize(, jobSetup.child("options.columns").children.count + 1), _
                                                jobSetup.toString("options.fillColor")
    rData.value = dashType
    For Each joc In jobSetup.child("options.columns").children
        With rData.Offset(, joc.childIndex)
            .value = joc.value
            ' if this is a date convert column, set the appropriate format
            If Not jobSetup.child("options").childExists("convertTimes") Is Nothing Then
                For Each jor In jobSetup.child("options.convertTimes").children
                    If LCase(jor.child("to").value) = LCase(.value) Then
                        .EntireColumn.NumberFormat = jobDash.toString("timeFormat")
                    End If
                Next jor
            End If
        End With
    Next joc
    
   ' now add the rows
    For Each jor In jobWork.child("venues").children
        rData.Offset(jor.childIndex).value = jor.value
        ' add the data as formulas
        For Each joc In jobSetup.child("options.columns").children
            rData.Offset(jor.childIndex, joc.childIndex).Formula = _
                Replace("=INDIRECT('" & dashType & "_'&$a" & 1 + jor.childIndex & "&'!'&CHAR(CODE('A')+COLUMN()-2)&2)", "'", q)
        Next
    Next jor

 'finally refit for the data
    toEmptyBox(wholeSheet(ws.name)).EntireColumn.AutoFit
    manifest.tearDown

End Function

Walkthrough

Converting times

As covered in Creating a workbook from JSON manifest, it’s possible that there are some times that need converted from Unix timestamps. In order to display correctly, we can apply a given time format to the receiving columns,

For Each joc In jobSetup.child("options.columns").children
        With rData.Offset(, joc.childIndex)
            .value = joc.value
            ' if this is a date convert column, set the appropriate format
            If Not jobSetup.child("options").childExists("convertTimes") Is Nothing Then
                For Each jor In jobSetup.child("options.convertTimes").children
                    If LCase(jor.child("to").value) = LCase(.value) Then
                        .EntireColumn.NumberFormat = jobDash.toString("timeFormat")
                    End If
                Next jor
            End If
        End With
    Next joc

Generating formulas

Instead of copying the data over from the worksheets that make up the dashboard, we instead want to create a formula that references them. That way, if the worksheets get updated in some way other than this automated process, they will still display the current values of the contributing sheets. Essentially we want a formula like this example to appear in each cell of the dashboard.

=INDIRECT(“ticker_”&$A2&”!”&CHAR(CODE(“A”)+COLUMN()-2)&2)

here’s how

' now add the rows
    For Each jor In jobWork.child("venues").children
        rData.Offset(jor.childIndex).value = jor.value
        ' add the data as formulas
        For Each joc In jobSetup.child("options.columns").children
            rData.Offset(jor.childIndex, joc.childIndex).Formula = _
                Replace("=INDIRECT('" & dashType & "_'&$a" & 1 + jor.childIndex & "&'!'&CHAR(CODE('A')+COLUMN()-2)&2)", "'", q)
        Next
    Next jor

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