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

https://btc-e.com/api/2/eur_usd/ticker
https://btc-e.com/api/2/btc_usd/depth
https://btc-e.com/api/2/ftc_btc/trades

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": [
        [
            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



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

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
                        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

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