Data driven VBA apps with JSON

 

Separating coding from defining

 

 I think of 3 different roles in workbook app definition

  • Consume – Use the apps and consume or maintain the data in it, and provide process, informational and operational needs
  • Define – Translate these needs into data structure and process flows.
  • Code – Create the code needed to execute the defined structure

Because Excel is so accessible, more often than not the define and code role get mixed up, and indeed all 3 are often done by the same person at the same time. Like this, the code very quickly becomes specific to the initial requirement, and is hard to adapt to change. You’ll find the finished app in downloads section – bitCoinAuto.xlsm, or as you’ll see in Scheduling updates with task scheduler a real time version on Google Drive. 

Declarative definition

 

Making the business of definition more declarative is pretty tricky with Excel.

It’s normally fairly difficult to create declarative apps with VBA compared to other languages. Partly this is because it’s hard to communicate options and parameters to a VBA app, especially when they are complex.

One way is through forms, but persistence of defaults is quite difficult (although here’s a way to do it Persistence of data for userforms), and additionally the forms themselves can become really complex when there are many options. Another way is through parameter sheets, as I’ve implemented in various places such as How to use the Excel Roadmapper and Integrating Excel with Maps and Earth, but in this case it’s difficult to deal with multi-level hierarchies. Most other languages use either JSON or XML to create more of a parameter driven, declarative approach. I’m going to use the same approach to put together a fairly complex VBA app, describing the function of the app using JSON. As you know, VBA does not naturally support JSON, so I’ll be using the cJobject custom class – see How to use cJobject for background. I could have used XML, but 

  • It’s too long winded and I just don’t like it
  • I want to use the same manifest in JavaScript and Google Apps Script apps where JSON is much more suited
  • It more naturally fits into an app structure than xml

besides, I guess I can do what I want on my own site…

 

Objective

 

We’ll create a workbook that has these capabilities, and specify the details and parameters through a single jSON manifest. 

  • Get bitCoin exchange data from multiple venues (such as USD_BTC) and different types (such as ticker and trades), using the API from https://btc-e.com/
  • For ticker type data, keep historical data
  • Schedule automated running at regular periods to build up unattended ticker data
  • Create a separate sheet for each type and venue combination
  • Create a summary dashboard
  • Generate an entire workbook including formats and formulas based on the instructions in the manifest
  • Carry out various housekeeping tasks like consolidating sheets of the same type, or removing old data.

 

Example completed dashboard

 

 

Example Ticker sheet

  

Example depth sheet

 

 

Example trades sheet

 

You can see and example JSON manifest at the end of this page. We’ll be dissecting that and showing the code for the app in the some more detail below.

 

JSON manifest

 

First of all, where to store it. There are many options from Hiding data in Excel Objects, getting it from an online rest query (my preference, since like that t can be shared among many workbooks and different languages), or sticking it a cell in the workbook and getting it from there. For simplicity in this example and to allow you to tailor it easily, I’m going to do that. Therefore the getManifest Function looks like this. If you want to store it somewhere else, just change this function as appropriate. 

JSON manifest

First of all, where to store it. There are many options from Hiding data in Excel Objects, getting it from an online rest query (my preference, since like that t can be shared among many workbooks and different languages), or sticking it a cell in the workbook and getting it from there. For simplicity in this example and to allow you to tailor it easily, I’m going to do that. Therefore the getManifest Function looks like this. If you want to store it somewhere else, just change this function as appropriate.

Public Function getManifest() As cJobject
  ' this is the work manifest
  Dim job As cJobject
  Dim workRange As Range
  Set workRange = Range("manifest!a1")
  With JSONParse(workRange.value)
    With .add("manifest")
        .add "range", SAd(workRange)
        .add "name", workRange.Worksheet.name
    End With
    Set getManifest = .self
  End With
  
End Function

This is the manifest that describes how to do all this.  The same manifest controls how to create, layout and format an empty worksheet and dashboard, as well as what to run, and how to treat the data returned from the API.

{
    "url": "https://btc-e.com/api/2/",
    "dashboards": [
        {
            "type": "ticker",
            "name": "tickerDashboard",
            "timeFormat": "dd-mmm-yyy hh:mm:ss"
        }
    ],
    "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"
            ],
            "houseKeeping": [
                {
                    "trim": {
                        "rows": 200
                    }
                },
                {
                    "consolidate": {
                        "name": "consolidated"
                    }
                }
            ]
        },
        {
            "type": "trades",
            "venues": [
                "btc_usd",
                "ftc_btc"
            ]
        },
        {
            "type": "depth",
            "venues": [
                "btc_eur",
                "btc_rur",
                "btc_usd"
            ]
        }
    ],
    "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"
                    ]
                }
            }
        ]
    }
}

The Code

We’ve walked through most of  the code in these detail pages, 

here’s the whole thing below