Populating the worksheets from bitcoin api

Getting data from the bitcoin API

The bitcoin public api can provide different types of exchange data for a number of venues. The endpoint for each combination is different, for example these are all valid endpoints


The data that gets returned from each data type is of a different structure. Here's some samples

Ticker data

    "ticker": {
        "high": 1.28198,
        "low": 1.245,
        "avg": 1.26349,
        "vol": 2575775.15893,
        "vol_cur": 2042196.5058,
        "last": 1.26996,
        "buy": 1.26996,
        "sell": 1.26994,
        "updated": 1386269110,
        "server_time": 1386269111

Trades data

        "date": 1386269241,
        "price": 0.00066,
        "amount": 43.7763,
        "tid": 18383785,
        "price_currency": "BTC",
        "item": "FTC",
        "trade_type": "ask"
        "date": 1386269239,
        "price": 0.00066,
        "amount": 6.72247,
        "tid": 18383778,
        "price_currency": "BTC",
        "item": "FTC",
        "trade_type": "ask"
        "date": 1386269222,

Depth data

    "asks": [

These types are described in the manifest setup section

"setup": {
        "types": [
                "type": "ticker",
                "options": {
                    "timeFormat": "dd-mmm-yyy hh:mm:ss",
                    "fillColor": "#F79646",
                    "resultsStem": "ticker",
                    "manual": false,
                    "action": "insert",
                    "columns": [
                    "convertTimes": [
                            "from": "server_Time",
                            "to": "at"

and the venues to be processed are in the work section.
    "work": [
            "type": "ticker",
            "venues": [

Here's the code


Overall, we need to work through each of the work items in the manifest, and populate sheets according to the work instructions. Each work package has a series of venues to operate on, and potentially some housekeeping instructions.
Public Sub doBTCUpdates()
Dim job As cJobject
' update all data from rest API
With getManifest
For Each job In .child("work").children
If Not btcProcess(.self, job, .child("url").toString) Then Exit For
End With
End Sub

HouseKeeping - consolidating sheets

One of the housekeeping tasks that may be needed is to consolidate all the sheets of a particular work package into a single sheet. If that is going to be needed then we have to clear out a place for a consolidated sheet before getting going. 
    'will any any housekeeping be required?
Set jobHouse = workItem.childExists("housekeeping")
If Not jobHouse Is Nothing Then
For Each joh In jobHouse.children
If Not joh.childExists("consolidate") Is Nothing Then
' need to clear out this consolidated view
Set wsConsolidate = getSheetOrCreate(workType & "_" & joh.toString("consolidate.name"), _
End If
Next joh
End If

A typical housekeeping package looks like this

            "houseKeeping": [
                    "trim": {
                        "rows": 200
                    "consolidate": {
                        "name": "consolidated"


Each package can also have an action. This describes how to handle the data. If we are planning to insert it, we need to make some room at the top of the existing data
        ' check if we are inserting
If LCase(jOptions.toString("action")) = "insert" Then
(sheetName).Resize(1).Offset(1).EntireRow.insert xlDown, xlFormatFromRightOrBelow
End If

Getting DATA

This small piece of code is the heart of what this app is about - getting data from the bitcoin API. Luckily we have the cRest class,  which knows how to get data from an API and populating a sheet by extracting JSON fields that match the column Headings. In Creating a workbook from JSON manifest we already automatically created receiving sheets for the data that this API will return. Most of the code below is dealing with the option where we cannot allow cRest to populate the data - in this case because some types of data (with {manual:true} in their work package) - don't actually have field names in the data returned from the REST API - instead relying on data position. This is kind of odd, but in any case, we need to simply access the data returned from the crest query and deal with it manually. In addition to holding the data, crest also helpfully supplies where it needs to go
        ' now its a common query
With restQuery(sheetName, , , , url, jOptions.child("resultsStem").toString, , _
Not jOptions.child("manual").value, LCase(jOptions.child("action").toString = "clear"), , True)
' this is a manual populate for 'depth' which has no object keys
If jOptions.child("manual").value Then
Set r = .dset.headingRow.where.Resize(1, 1)
For Each jor In .datajObject.children
For Each joc In jor.children
.Offset(jor.childIndex, joc.childIndex - 1).value = joc.value
Next joc
Next jor
End If
End With

A primer on cRest

Here's a few slides in case you need a quick overview of how cRest works

Unix Times

We might get some unix times that need converting, so we need to a quick scan of the data and do the conversion.
        ' any dates needs calculated?
If Not jOptions.childExists("convertTimes") Is Nothing Then
Set ds = New cDataSet
With ds.populateData(wholeSheet(sheetName), , , , , , True)
For Each jor In jOptions.child("convertTimes").children
.column(jor.toString("to")).where.NumberFormat = jOptions.toString("timeFormat")
For Each dr In .rows
.cell(jor.toString("to")).where.value = _
Next dr
Next jor
End With
End If

HouseKeeping clear up

Some house keeping items might be to limit the number of items allowed on a sheet - useful if we are using {"action":"insert"} and only want to keep the latest 'n' rows.
          maxRows = ds.rows.count
For Each joh In jobHouse.children
' need to keep the rows at some maximum number
If Not joh.childExists("trim") Is Nothing Then
= joh.child("trim.rows").value
If (ds.rows.count > maxRows) Then
.where.Resize(ds.rows.count - maxRows).Delete
End If
End If
Next joh
Another housekeeping task is to consolidate worksheets of the same type. 
' any consolidation need to happen ?
If Not wsConsolidate Is Nothing Then
' copy headings
With wsConsolidate.Cells(1, 1)
.Resize(1, ds.headingRow.where.columns.count).Offset(, 1).PasteSpecial xlPasteAll
' make a new column for the venue stamp
.value = "Venue"
.Offset(, 1).Copy
.PasteSpecial xlPasteFormats
End With
' and append the data
Set r = wsConsolidate.Cells(1, 1) _
.Offset(wsConsolidate.Cells.find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row - 1)
For Each dr In ds.rows
' may have been trimmed....
If dr.row > maxRows Then Exit For
Set r = r.Offset(1)
'stamp the venue
.value = job.toString
' copy the data
For Each dc In dr.columns
.Offset(, dc.column).value = dc.value
Next dc
Next dr
End If

Then we simply wrap up by refitting the column widths, including the dashboard, so that the new data is visible

       'finally refit for the data
If Not wsConsolidate Is Nothing Then
End If
' and the dashboard will change
Set joc = findInChildren(manifest.child("dashboards"), "type", workType)
If Not joc Is Nothing Then
End If

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 .