I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

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

https://btc-e.com/api/2/eur_usd/tickerhttps://btc-e.com/api/2/btc_usd/depthhttps://btc-e.com/api/2/ftc_btc/trades
The data that gets returned from each data type is of a different structure.

Here are 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": [
        [
            787.5,
            0.010756
        ],
        [
            790,
            0.09173359
        ],
        [
            791,
            1
        ],

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": [
                        "high",
                        "low",
                        "avg",
                        "vol",
                        "vol_cur",
                        "last",
                        "buy",
                        "sell",
                        "server_Time",
                        "at"
                    ],
                    "convertTimes": [
                        {
                            "from": "server_Time",
                            "to": "at"
                        }
                    ]
                }

and the venues to be processed are in the work section.

"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

Private Function btcProcess(manifest As cJobject, workItem As cJobject, urlStem As String) As Boolean
    ' process a piece of btc pdate work
    Dim r As Range, workType As String, job As cJobject, url As String, _
            sheetName As String, joc As cJobject, jor As cJobject, joh As cJobject, _
            jOptions As cJobject, ds As cDataSet, dr As cDataRow, jobHouse As cJobject, _
            wsConsolidate As Worksheet, dc As cCell, maxRows As Long

    
    workType = LCase(workItem.toString("type"))
    'find the options associated with this type
    For Each job In manifest.child("setup.types").children
        If job.toString("type") = workType Then
            Set jOptions = job.child("options")
            Exit For
        End If
    Next job
    
    If jOptions Is Nothing Then
        MsgBox ("cant find worktype " & workType & " in manifest setup")
        Exit Function
    End If

Walkthrough

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
    Next
    .tearDown
  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"), _
                                    Sheets(manifest.toString("manifest.name")))
                wsConsolidate.Cells.clear
                
            End If
        Next joh
    End If

A typical housekeeping package looks like this

"houseKeeping": [
                {
                    "trim": {
                        "rows": 200
                    }
                },
                {
                    "consolidate": {
                        "name": "consolidated"
                    }
                }
            ]

Actions 

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
            wholeSheet(sheetName).Resize(1).Offset(1).EntireRow.insert xlDown, xlFormatFromRightOrBelow
        End If

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
                        r.Offset(jor.childIndex, joc.childIndex - 1).value = joc.value
                    Next joc
                Next jor
            End If
            .tearDown
        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
                        dr.cell(jor.toString("to")).where.value = _
                            dateFromUnix(dr.cell(jor.toString("from")).toString)
                    Next dr
                Next jor
                .tearDown
            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
                    maxRows = joh.child("trim.rows").value
                    If (ds.rows.count > maxRows) Then
                        ds.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
                ds.headingRow.where.Copy
                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
                    r.value = job.toString
                    ' copy the data
                    For Each dc In dr.columns
                        r.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
        toEmptyBox(wholeSheet(sheetName)).EntireColumn.AutoFit
        If Not wsConsolidate Is Nothing Then
            toEmptyBox(wsConsolidate.Cells).EntireColumn.AutoFit
        End If
        ' and the dashboard will change
        Set joc = findInChildren(manifest.child("dashboards"), "type", workType)
        If Not joc Is Nothing Then
            toEmptyBox(wholeSheet(joc.toString("name"))).EntireColumn.AutoFit
        End If

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