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 are some samples

Ticker data

Trades data

Depth data

These types are described in the manifest setup section

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

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.

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. 

A typical housekeeping package looks like this


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

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

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.

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.

Another housekeeping task is to consolidate worksheets of the same type.

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

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