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