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


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

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