Embedded Files
2024: Unknown if ReX connectors still work. Not supported, no active development!
Cointrexer and Analyser are still used by many users.
A Toolbox to help you build your own cryptocurrency spreadsheet with live API data
A cryptocurrency data collection tool for Google Sheets with native REST integrations to a large number of exchange APIs.
Rex Builder can fetch data from multiple Exchanges (APIs) in parallel and let you define where the output is written in the sheet.
It gives complete flexibility in data you want to fetch and how and where it gets written in the sheet.
Jan 10, 2021 Connectors: Binance (blocked), BitMex, Bybit, FTX, Coinbase Pro, Bittrex, Bitfinex and more
Before you start:
You have decent Spreadsheet skills
You can read your exchange API doc and understand what data can be fetched from REST endpoints (or willing to learn)
You understand the concept behind Apps Script Triggers and are not afraid to create one manually
We have created a set of sample API commands in the sheet, getting started videos and a (hopefully) pretty complete documentation.
ReX Builder is extremely powerful !!
We love to hear what you are building and we are always willing to help, just drop us an email.
What is it?
ReX Builder is a toolbox that helps you create your own sheet with Exchange API integrations.
You can specify any API endpoints/exchanges you want to pull data from, then it's up to the end user to make the desired sheets with the dataset results of the REST API calls.
Zero code, Multi Exchange and Multi Command API Support:
zero code, create exchange api integrations without writing any code
concurrent support for multiple exchanges and multiple commands
multiple accounts can be used on the same exchange
Configurable output:
define sheet/tab and cell location for the output
conditions and simple data manipulation
views that allow what data and spreadsheet formulas are pushed to the sheet
built in feature to map/normalize exchange symbols (optional)
fully customizable LOG feature where you determine what gets logged
What, how and when to fetch what data:
commands let you define an API command and its output. This can be DYNAMIC (cool)!
bundles let you group a set of commands
run bundles from the menu, a button or scheduled from a Trigger
Create your own solutions:
use the powerful Google Sheets formula features, like QUERY, VLOOKUP and other to combine/correlate the data
option to give the data that is pushed to the sheet a logical name (named range)
text in the ReX menu can be customized as well
Requirements:
spreadsheet knowledge (the more knowledge the better)
understand what data can be fetched from your exchange APIs
Introduction to the sheet
ReX Builder has 3 system sheets that should not be deleted:
CONFIG - this is your main sheet where you configure all your API calls and assign them to Bundles.
Bundles can run from the sheet menu, from buttons (images) or from Triggers
SYSTEM(hidden) - system settings and global variables
SYMB_MAPPING(hidden) - used for symbol mapping (optional)
LOG - can be deleted and used for logging trail feature (optional). If this sheet is not available, it's created for you. See LOG section in this doc.
You choose what API endpoints/exchanges you want to pull data from, then it's up to you to make the desired sheets with the dataset results of the API calls. ReX Builder is extremely powerful, specially when you combine this with spreadsheet functionality like query and vlookup.
ReX Builder has some great features that help you building the sheet(s):
filter/change the output and push data to an exact sheet location (views and conditions)
create 'named ranges' so your sheet formulas can be simplified
inject spreadsheet formulas (views)
option to normalize symbols (symbol mapping)
ability to use spreadsheet formulas to built a dynamic endpoint queries in CONFIG (dynamic conf)
CONFIG
Description
A unique and logical description for your command.
Example: 'Balances Paul Binance'
Sheetnames
Sheets are automagically created based on the name you enter in CONFIG.
Examples:
_BALANCE - creates a sheet _BALANCE and starts writing in cell A1
_BALANCE $B - starts writing in column B of the last (filled) row
_BALANCE $B3 - creates a sheet _BALANCE and starts writing in cell B3
_BALANCE $B3#CLR - starts writing in _BALANCE cell B3. #CLR clears the complete sheet before writing.
_BALANCE $B3#namerange=Balance1 - creates a named range called Balance1 that you can use in spreadsheet formulas
_BALANCE $C3#namerange=Balance1 #NH No Header, default de header is description and a timestamp
_BALANCE$B#RFNH - Remove First Line, No Header.
Some datasets (depending on exchange) have a field descriptions on the first row.
This will just skip the first row (without looking at the content) and does not create the header.
v2.34 only
_BALANCE$B#ADDSP - Add Special (experimented - not fully tested - feedback is welcome)
This compares the first column timestamp and only adds records that are newer. Useful for several lists with a timestamp.
First Column must be the date in real date format (use fixdate)!!!. Rows are added at the top.
*) Named ranges are not removed during cleanup feature. From the Sheets Menu, [Data], [Named Ranges] to view / delete them
Image below demonstrates how we can place the output of two different API calls in the sheet. The second command is added to the last row of the first command.
Hidden sheets can be accessed from the hamburger menu in the lower left corner.
Id
The Identifier for this exchange.
A list of exchanges and ID's can be found on Github page.
Some examples: BIN = Binance, BFX = BitFinex, BMX = BitMex, BYB = Bybit
FTX = FTX, GDX = Coinbase Pro (GDAX)
OKC = OkCoin, OKX = OKEX, ZHS = ZeroHash
STD = Standard (Coinmarketcap or CryptoCompare etc.)
We are in the processes of migrating all exchange connectors to the new location. Not all connectors are available on the new location.
Let me know if you need a connector for ReX Builder that is currently not in this list and we are happy to create this.
This is used to call the external exchange library from the Github location
Command path and Payload
command: See the exchange API documentation for the REST endpoint (command) that you want to request data from.
If it does not contain 'http' it gets concatenated with Uri+version+command.
payload: A request payload is a standard term used with REST. It's the additional info that is send with the command.
Command and Payload can be found in the API documentation for your exchange.
Special tags that can be used in Command and Payload:
#### is replaced with your api key. (typical used for Coinmarketcap or CryptoCompare; see example in CONFIG)
{?} prompts for user input
['What do you want to enter'] combined with {?} will use this text for the user input
Method
Depending on exchange command is http GET or PUT. See your exchange API doc.
Xpath
This is an OPTIONAL argument that allows a direct pointer to the hierarchy in the JSON object. If you don't know the structure of the JSON object, leave blank or look at the Exchange API endpoint documenation. You can also copy the raw JSON data and paste it into a jsonviewer
example: https://api.bittrex.com/api/v1.1/public/getticker?market=BTC-LTC
Xpath: /result
Transpose
Rotates the array 90 degrees so columns will become rows. Useful for some exchanges like Kraken that have complex nested JSON objects.
Private
An exchange request is either Private or Public. Private request require authentication (api keys)
Uri and Version
HTTP path to access the API. This is also referred to as the base endpoint.
Example:
Uri: https://api.binance.com/api
Version: /V3/
Introduction to View and Conditions
Both View and Conditions are OPTIONAL, but very powerful to change/filter the received data before it gets written to the sheet.
Views - let you create a view of the columns and order that are pushed to sheet
Conditions - let you filter based on conditions
Both options require you to use hardcore notations to the array but we give you some cool examples that you can use.
The cool things is that both view and conditions can contains some simple Javascript commands as well. See below.
Views also allow you to write spreadsheet formulas to the sheet (very powerful)
View
Below is a sample how we can change the output with he view. We manually added the reference notations on row 1 to the array. (yellow)
If we only want to see Timestamp, Side, Symbol, Qty, Price (in this order), our view would be:
c[6],c[2],c[1],c[3],c[7]
View command can contain Google Script (Javascript) commands. See examples below:
Useful operations for text:
Uppercase / Lowercase c[2].toUpperCase() or c[2].toLowerCase() to transform to Upper/Lowercase (example "Sell" => "SELL")
First 3 characters string c[2].substring(0,3) to only keep the first 3 character from a string or remove last 3 characters c[2].substring(0, c[2].length-3);
Last 3 characters string c[2].substr(-3,3)
Split with separator c[2].split('-')[1] to only keep ETH from BTC-ETH, use [0] for BTC
Useful operations for dates:
date2text(c[6],"simple" ) to transform a simple date format (with T and Z) into a normal date format in text
date2text(c[6],"epoch" ) to transform an epoch (number) to a normal date. (Binance uses epoch) in text
epoch2date(c[6]) to real date field
2.33 only:
fixdate(c[4]),c[1],c[2]
This auto detects epoch and simplified extended ISO format (ISO 8601) formatted strings and converts them to date format.
epoch: 1610488353
ISO: 2021-01-12T20:38:35.061811+00:00
TIP #1: You can push other data to the sheet as well, like text or formulas.
Formulas are bit tricky because they must use indirect because you can not directly point to a row (they would all have the same row number)
"sample text", c[6], c[2], c[1],c[3],c[7], '=indirect(concat("B",ROW()))'
You can also do calculations like c[1],c[2]*c[3]
version 2.34e
TIP #2: You can use attributes from CONFIG as well in your view
c[6], c[2], c[1],c[3],c[7], config.description
Condition
Allows you to filter the array based on Google Apps Script (javascript) conditions.
You must use number notations for the columns that directly represent the array.
The array is called 'c' and the first element starts with zero.
Here are some examples:
Filter the zero balance from the array (column A = symbol and column B = balance) - c[1]>0
The Symbol in de first column (A) contains "ETH" - c[0].indexOf("ETH")>-1
Only show Sell orders (column D ) - c[3] == "Sell" or c[3].toUpperCase() == ~SELL~
Map Symbol
See "Symbol Mapping" Section in this document.
Conditions2 after Symbol mapping
Similar to Conditions1 but evaluated after symbol mapping
DynamConf
By default the CONFIG sheet is read only once into memory when you run the script.
If you want to use spreadsheet formulas in commands in CONFIG the dynamic config switch can be set to true.
if set to true, the CONFIG sheet is read again after this command. You set to true on the previous command line.
The API commands are executed one by one from top to bottom. If you would like to use/reference information in your current command fetched by commands on rows above your current command. You'll have to activate DynamConf.
Example:
This takes the list of symbols from your balance (_BINBALSPOT) as input to fetch prices on CryptoCompare:
Step 1: Get the symbols from the column B in the Balance sheet and transform them to comma separated parameters. Pretty cool, don't you think !
=join(",",UNIQUE(FILTER('_BINBALSPOT'!B6:B, LEN('_BINBALSPOT'!B6:B))))
Step 2: Now concatenate this with the command and (v)lookup your fiat currency in the system tab.
=concatenate("/data/pricemulti?fsyms=",join(",",UNIQUE(FILTER('_BINBALSPOT'!B6:B, LEN('_BINBALSPOT'!B6:B)))),"&tsyms=USD,",vlookup("fiat",SYSTEM!A1:B,2,false),",BTC&api_key=####")
Dynamic command in CONFIG (result of the formula)
output/data/pricemulti?fsyms=BTC,ETH,BNB,TRX,ETF,BTT,WIN,JST&tsyms=USD,EUR,BTC&api_key=####
In this example, there is a dependency on balances in _BINBALSPOT. Make sure balances are updated before this formula gets evaluated.
Cache in seconds
Caching is the process of saving data temporarily in the Google Cloud so ReX doesn't need to go to the API each time to get the data. It therefore speeds content downloads and helps deliver a faster, more satisfying user experience and offloads those lovely APIs.
You create your own cache policy by setting the cache time to live (ttl) value in CONFIG per command line.
The cache value can be configured per command line in CONFIG, column W.
If data is cached, data is served from the cache and ReX will not make a connection with the exchange API until the time tom live (ttl) expires.
Caching has the following advantages:
Performance (it is faster),
We don't want to abuse/overload those lovely APIs (some APIs have restrictions on number of fetches)
Google Script Quotas on number of fetches.
If caching is is 0, it is turned off. The value is seconds for the cache to live (ttl time to live)
(0 = no cache / 60 = 1 min / max = 21600 6 hour)
Recommended value for most calls is 300 (5 min) or 600 (10 min)
How Caching works:
Caching is a Google Cloud Service that allows a script to temporarily cache results that take time to fetch. The cache is private and only accessible by the current script.
Data can be cached per command in CONFIG and is privately stored.
The cacheid is created based on command description !
The raw data (JSON Object) that is received from the API is cached so all "output features", like view and conditions can be changed when data is fetched from the cache.
666 forces to clear (remove) the cache. 0 will not clear the cache but will not use the cache !!!
MENU BAR
ReX Builder adds a custom menu to the Sheets user-interface. The menu allows users to execute Bundles and do special tasks like encrypt api keys or add a license.
The text that appears in the menu can be customized from the SYSTEM tab. See image below:
To remove options from the Tools menu, set devmode to false in the SYSTEM tab. This removes the Debug and Cleanup options from the menu. Make sure you reload the sheet for changes in the menu be visible.
The cleanup feature:
You can imagine that during some testing, it can create a lot of sheets for you.
The tools menu has an option to automatically delete (cleanup) sheets that are created. This option can be turned on or off in [system] with devmode true / false. If you name your sheet starting with MY_* or with _* (underscore) the cleanup feature will not touch the sheet
SYMBOL MAPPING
Symbol Mapping allows normalization of currency symbols to a common set.
This is useful when you are using multiple exchanges or when you want to correlate with sources like Coinmarketcap or Cryptocompare.
Symbol Mapping can be turned on/off in the SYSTEM tab (mapsymbolsys) and in CONFIG. Both should be true/on for symbol mapping to work.
The SYMB_MAPPING sheet is used to configure symbol maps per exchange.
Example of symbol mapping:
change XBT to BTC
change IOT to MIOTA
RUN YOUR BUNDLES
When you created your tasks/command and assigned them to bundles there are 3 ways to 'run/execute' a bundle.
1.) From the Sheet ReX Menu
By default the tool creates a 'ReX' menu in Google Sheets that allows you to run bundles and do special tasks.
All strings in the menu can be customized from the (hidden) SYSTEM tab. watch demo video
2.) From a Trigger
Triggers can be set to execute on startup or time based. (even when the sheet is not open)
You manually need to create your Triggers on ReXRunBundle1 to 5 or CRexAll
[Tools][Script Editor] click on little clock icon and create your Triggers on ReXRunBundle1 to 5. watch demo video
3.) From an image (button) that you inserted in the sheet
[Insert] [Image] [Over Cells]
Right click the image - [Assign Script] - ReXRunBundle1 to 5 or CRexAll
TIP: existing images can be copied by right click, put image in selected cell - copy the cell
DEBUG
Run Command in Debug Mode:
The Debug option shows you a popup with the HTTP request , raw JSON object that is received and the data before it is pushed to the sheet.
You use debug for a specific command in CONFIG by setting the row value in column B to 'Debug' from the dropdown listbox.
The you run Debug from the Tool Menu. [Rex], [Tools], [Run Debug] watch demo video
Note1: You can also look at Logfile -> [Tools], [Script Editor], [View], [Logs]
Note2: Debug option can be removed from the Menu in [SYSTEM].devmode
LOG
The LOG feature allows you to record a range of values to trach changes over time.
It converts a single column to a row and adds the row to the LOG Sheet.
Logging can be turned on per command in CONFIG, col V.
The range that is logged is defined in the SYSTEM tab as logsource. The default/sample logsource is pointing to MY_SHEET!J5:J9 and is just a sample with text "Curry" and "Hamburger". It's used to have some sample data that can not be confused with the data in your sheet ;-)
The range is defined in the SYSTEM tab:
logsource = MY_SHEET!J5:J9 (can be changed but must be a vertical range)
logdestsht = LOG (can be changed as well in SYSTEM tab)
The data from source MY_SHEET, J5 .. J9 gets written to a row in the destination tab.
So, the column get transformed to a row and
You do your own magic and fill MY_SHEET!J5:J9 with data or formulas. Cell A3 would typical start with =now() to get a timestamp. This gives you all flexibility to log whatever you want !
Conditions
You determine in CONFIG when the Logline gets written by a checkbox in column V. Normally you would only have one checkbox in config, col V.
The Log function point to a vertical range (a column). If the first or second cell does not have a value is does not create a log record. In our example; If A3 or A4 is empty the logline will not get written. This gives you the flexibility to create your own conditions based on spreadsheet if then else functions.
Back to our example:
We could create a very simple condition in Cell J5 to check a value in YOURSHEET. If in yoursheet cell A12 has a value that is smaller then 1 , cell J5 is blank, else it writes a timestamp. The Log entry is only created when J5 and J6 have a value.
=if(YOURSHEET!A12 < 1,"",now())
Query the Log
You can use functions like vlookup, query, sumif etc to get your data from the log. There is one function added to the script to get the lastrow from the LOG and transform it to a column. Very useful for conditions as well.
=LastRow2Col("LOG", A1)
Get lastrow from log and transform to col. A1 is a reference that should point to a cell that changes value. This forces recalculation of the function.
SYSTEM
The SYSTEM sheet stores system settings and global variables. The sheet is not locked, the lock just gives you a warning when changing a value.
There is a short description in the system tab for every setting. Please let me know if you have any questions.
UPDATE
Update ReX Builder to a new version is a manual process.
In most cases, you can just update the script file. Please read the changelog before doing an update.
BEST PRACTICES
Separate the data and the presentation layer as much as possible
To reduce complexity it's always a good idea to separate the data from your final layout.
- Create separate tabs for data collection from exchange API sources
- Consider using named range to make formulas / cell references readable
- Dashboards just use reference and query/vlookup to the data and the formula tab
Our CtrXL is a great example of this, where we have several Dashboards that are just queries
If you prefer to create your magic in Excel you can look at publish feature.Reduce you overall complexity
- Centralize your formulas/calculations in a separate tab (e.g. sheetname _VARS)
- Create readable reference to your _VARS cells by assigning logical names to a cells by using [Data]{Named Range].
- Alternatively you can do dynamic lookups to your vars table with vlookup.
Example to system: =vlookup("fiat",SYSTEM!A1:B,2,false)
- Document your_VARS formulas. See image 2When you create your sheet, turn caching on for your command so it's faster and it does not need to fetch the data from the APIs.
Binance works best using Triggers to avoid Binance Banned Messages (and/or use caching!!)
Rex Builder sheets can become complex if you collect and combine data from multiple sources.
The CONFIG sheet can have formulas as well to dynamically request something from an exchange. Try to avoid having formulas in CONFIG and again, try to centralize and document your logic so you still understand after 6 months ;-)
Let me know if you need any help,
moosylog@gmail.com
Telegram Integration
You can configure ReX Builder to send data (e.g. cell value) to your Telegram IM account.
Howto:
Create a Telegram Bot with Botfather .
Get the API token from your Bot (this looks like NUMBER:STRING)
Start the bot by sending a message to your bot as if it was a user
In the chat window you can start the botGet the Chat ID from your bot with this url
Browse to https://api.telegram.org/bot<<api_token>>/getUpdates
Get the Chat ID from the outputNow you have the following url you can work with and test it in your browser
https://api.telegram.org/bot<<api_token>>/sendmessage?chat_id=<<chatid>>&text=text
The text "bot" must be included in the urlUse Sheets concat function to build a complete string, including the text that you can use as command in CONFIG
To avoid, it keeps sending the same message turn caching on for this command
You can make this conditional by making Col B (Execution) an if then else formula
.
Image 1.) Separate Data Sample and Presentation (query example):
=query(DATA1!$A$1:AN,"select AM,G,F,L,M,R order by H desc limit 10 ")
Image2:) Sample _VARS sheet where I centralize formulas, incl documentation
KNOWN LIMITATIONS
ReX Builder is extremely powerful, but there are a few known limitations that you need to keep in mind:
Google Sheets 'Limitations':
Named Ranges - ReX Builder can create named ranges for you. These are not automagically deleted when you delete a tab. Check [Menu], [Data], [Named Ranges]
"Sheets formula" - when you create a formula (e.g. in CONFIG) with a reference to a sheet that does not exist yet. The formula will not be reevaluated when the sheet is created. It looks like a limitation in Sheets.
If I have a pipe character "|" in a the sheetname the namedrange functionality will just not work
Other:
Updates to the script - either bug fixes or new functionality. There is not really an easy, user friendly way to update.
Only a limited amount of exchange connectors are available in ReX Builder (due to the new lib location on Github). Users need to know the Identified (3 letters) for the exchange that can be found here or in Cointrexer.
index:
spreadsheet reporting cryptocurrency api binance sha hmac private request google apps script code howto
Google Sites
Report abuse