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.
Scheduling updates with task scheduler
Populating the worksheets from bitcoin api
Creating the dashboard
Creating a workbook from JSON manifest
cJobject and javascript equivalents
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,
Scheduling updates with task scheduler
Populating the worksheets from bitcoin api
Creating the dashboard
Creating a workbook from JSON manifest
cJobject and javascript equivalents
here’s the whole thing below