ReX

ReX is a Google Sheets template and an Google Apps Script that allow you to fetch data from a variety of APIs into a spreadsheet.
It can get all your personal data (Private requests) from several exchanges using Read-Only API keys.
In addition to the supported exchanges, ReX has a generic REST collector to collects from more generic sources like Coinmarketcap, CryptoCompare or others.
The sheet comes with pre-defined API request commands or you can add your own commands. Example: fetch all your historical trades.
ReX helps you to explore REST APIs and get all the data you need in a spreadsheet.  
I love your feedback;  If you don't tell me, how will I know ;-)    moosylog@gmail.com


Meet ReX
Our new loyal friend that fetches data for us from several different exchanges. ReX loves to Fetch !
Many dogs love to retrieve items for their human owners. 
Rex is one of the most popular names for dogs and/or the name can be an acronym for 'Request from eXchanges'.

Why people love ReX:  ( use cases )
* Reporting - 'get your data out' and into a sheet (because nothing is as flexible and easy to work with as a spreadsheet).
* Monitoring - configure your personalized query that is accessible with a single mouse click / url. 
* Educational - learn about these lovely APIs and see details about requests and responses  (see Howto Debug Log)



Initial setup

Follow these steps to get you going:
  • Make a personal copy 
  • Configure the Source your want to use
  • Authorize and run the script
    • Open the 'Fetch' sheet and select a command from the dropdown listbox in cell C4
    • Press the Red 'Fetch' button to authorize the script and let ReX fetch the data for you


Privacy and Security

The ReX script requires a minimum set of authorizations:
- It needs to write to the spreadsheet
- it needs to fetch data from the exchange



Read Privacy and/or Security Concerns if you are concerned.


Supported Sources (Exchanges)

Supported Exchanges and links to the REST API Documentation to explore all endpoints.

eToro - planend
Gemini  
MXC   video


More exchanges will be added on request. Please drop me an email at moosylog@gmail.com

'Other | Generic Collector'
The crypto exchanges use proprietary authentication methods that are all slidely different.
We implemented a 'Generic Collector' for sources that don't require authentications.
Under 'Other' you find several sources, like Coinmarketcap, CryptoCompare or other generic REST services.
Select 'Other' in CONFIG and create any generic REST request in the My_Commands tab.  See Demo Video

Multiple sources/exchanges
If you want to use multiple sources like Binance and BitMEX and Coinmarketcap you can do two things:
1.) Make another copy of the sheet and use multiple sheets.
2.) Add your keys to the sources tab and create a simple vlookup function so you can switch source in Config and have vlookup get the key/secret from [Sources]
Enter your API key and Secret to Column G and H in the [Sources] tab.
In CONFIG cell D6 and D7 enter the following vlookup formula to get the key/secret in the fields
=vlookup(D5,Sources!B2:H,6,0)   will go in Config D6
=vlookup(D5,Sources!B2:H,7,0)   will go in Config D7

Now, when you select another source (exchange) in CONFIG, vlookup will automagically get the key and secret for you.
You might have noticed that the tool can encrypt your keys (starting with ••••••••) so you can copy your encrypted keys to the sources tab.


Config

The table below describes the settings in the configuration tab:

 SourceSelect your exchange or select Other for generic.
Other examples: CoinMarketCap, CryptoCompare or any other (video)

 API Key and SecretPaste your read-only Exchange credentials. Cointrexer or BotMon user can paste their encrypted keys.  
Make sure you paste without formatting ctrl-shft+v

 ThirdAttrib Some exchanges require a third authentication attribute like a passphrase or userid. This is exchange specific, see exchange specific notes 

 License KeyOptional field:
With a Moosy Research license key, ReX expands his reach and can fetch even more Private data from your Exchange. 

Cointrexer/BotMon users can just paste their license key here.
New users read, howto obtain a key

 Refresh Data Recommended to leave ON
Will update/overwrite the system Commands and sources table automatically.
When turned on, Commands and Sources (exchanges) table will be refreshed periodically.  (10 min delay)

 Debug When ON, you can see what ReX is doing. You can also look at a detailed log file and see exactly what http command is send and what data is received.(howto see the log)

 VersionReX version information

 TTLCache Time To Live (default 180 seconds / 3 minutes)
Larger data (>100KB) can not and will not be cached. Example large ticker file.





Go Fetch

Make sure you have selected the correct Source (Exchange) in CONFIG so ReX is able to Fetch for you ;-)

Select a command from the dropdown listbox and press the Fetch button.

The round circle button on the left creates a filter view for you that allow you to filter and sort the data.


Troubleshoot


Something is not working for you. Below you'll find some high level troubleshoot tips:

0.) Common Google Script errors are documented in the Exchange specific doc
1.) Some exchanges required a Addition Authentication Attribute. See Exchange Specific Notes.
2.) Try another query/command so you can pinpoint what's not working.
Do you only experience the issue with this command? How about other commands for this exchange, are they working?
3.) Recreate the API keys on your exchange and copy them into the sheet with ctrxl-shft+v (without formatting)
4.) Copy a new sheet and try a new vanilla sheet
5.) Look at the Log file.(howto). Here we can see all communication details to and from the API.
6.) Contact moosylog@gmail.com

Performance

ReX performance is dependent on:
  • Google Cloud - Google Apps Script runs in the cloud and is a free and shared infrastructure that can have some hiccups 
  • Exchange - you request something from the exchange and the exchange will gather the data and respond. Some exchanges respond faster than others and some commands take longer to fetch.
  • Sheet itself - the larger a sheet gets the slower it becomes to load
  • Script / command - the more the script needs todo, the slower it becomes
You can only influence the sheet and the script a bit that (hopefully) increase performance a bit.
  • script - disable CONFIG!D11. This will prevent the command and sources tab get periodically updates
  • sheet - you can manually delete all lines from [Commands] and [My_Commands] from sources you don't use
  • sheet - you can remove sources you don't use from [Sources]
Please let me know if making these changes makes a performance difference for you.


Commands

ReX is flexible and designed so you can easily Add, Modify or Delete commands from the commands tab.
The Commands tab also allow you to change the view (fields you want to display)

Add or Modify a Command

See this [video1] or [video2] on creating your own commands in the My_Commands tab.

[Commands] is a system tab that is refreshed (overwritten) automatically.  I recommend not to edit this and add your own commands to ['My_Commands'] 
You can disable the refresh in config but this is not recommended !!! 

[My_Commands] is there for you to add you personal command queries. 

Getting started: with [My_Commands]
  • Copy a line from [Commands] to [My_Commands] to get started
  • Give your command a unique name (Label in Col A). 
    I suggest to start the text with * so you can easily find your command in the dropdown listbox in the Fetch sheet.
  • Edit the command and try it from the Fetch tab
If you only want to see the My_Command list in the Fetch tab, a query formula can be changed in the sheet. Email me and I will explain how.

Below you find a short explanation of the columns and functions 

Command
Enter the REST call you would like to make.
Special functions that can be used in both command and payload:
{?} - will prompt the user for input
#### - will take the API key from CONFIG (used for CoinMarketCap and CryptoCompare) 


output column
Will determine how the received data is parsed by the internal engine.
<<empty>> - runs the standard JSON Import function
simple - runs simple/standard fetch and parse command
box - pops-up a dialogue box with raw JSON data that ReX received from the API.
rotate - rotates a multidimensional array
flatten - this will flatten a hierarchical, nested object (specially for Kraken)

nest column
The JavaScript Object Notation (JSON) Data Structure can be a hierarchical structure, often referred to as 'nested'.
This field allows you to point to a structure in the JSON structure.
nest - can point to a hierarchy in the JSON structure

View and Conditions

If the 'view' or 'conditions' columns are empty, all data that is received is dumped into the sheet without any data transformation or filtering.
*.) The view is a 'view' on the raw data. If you are confused about the column numbers, look at the raw data !!!


view column
The 'view' feature allows you to only display the fields from the raw output that you would like to see.
Raw API output can look like this
 x[i][0] x[i][1] x[i][2] x[i][3]
 1.2    ETH true 2011-08-12T20:17:46.384Z

The view settings allow you to manipulate the output array:
 date2text(x[i][3],$$) x[i][1] x[i][0]
 12-08-2011  20:17 ETH 1.2
The view settings for this example are: date2text(x[i][3],$$), x[i][1],x[i][0]
This example will skip the second element from the array and transform the date field to the format that is configured in CONFIG
Keep in mind that the array starts with zero, this is  x[i][0].

Commonly used commands in view:
date2text(x[i][38],"simple","MMM d,yyyy,hh:mm aaa")
Convert the simple date format that's received to a more friendly format
The Simple date format looks like 2011-08-12T20:17:46.384Z

date2text(x[i][38],"simple",$$)
Convert the date format as configured in CONFIG

date2text(x[i][1],"epoch")
Converts the received field with an epoch/linux time format (a number) to the default (EU) format

xbt2btc(x[i][16])
Transforms the text 'XBT' on col 15 to 'BTC'

[i][0].toUpperCase()
We can also use some simple Javascript in the view string, like convert text to Uppercase.


conditions column
Conditions can be simple operators to filter the data.
Display when certain field is  'false'  x[i][10]==false
Display when certain field contain 'Sell' x[i][2]  == ~Sell~
Skip all that have a value of zero x[i][3] > 0
Important: Quotes can not be used and should be replaced with ~
a little help creating views and conditions
To help building views or conditions, you can place the following formula in all cell on row6:
=concatenate("x[i][",COLUMN(D6)-4,"],")

Faq


Q1: Can I create a BUY or SELL order?
Rex allows you to fire off any API command so we can also create buy or sell commands.
!! We recommend not doing this unless you know exactly what you are doing and understand the API commands.
The Binance and BitMEX source have an example command to place an order (start with 'ZZ')

Q2: What is the license key? Is ReX licensed?
ReX can be used for free but has a limitation in private request.
Most exchanges have public and private requests.  Private requests typically require authentication via API keys and give access to your private data.
Without an license key, ReX will display a maximum of 15 lines. You will see a message in the lower right corner if there is more info that is not displayed.

Q3: How can I get a license key and are there costs associated?
A: With a license key, you can use all Moosy Research sheet. See our sheets, incl. future updates.

Q4: Can I access the source code?
A: The core (exchange collectors) are opensource and are published on GitHub.

Q5: What is JSON?
JavaScript Object Notation (JSON)

Q6: What is REST?
Representational state transfer (REST) is a software architectural style that defines a set of constraints to be used for creating Web services. Web services that conform to the REST architectural style, called RESTful Web services, provide interoperability between computer systems on the Internet.



Multiple Exchanges:

To quickly Switch

Adv. Tip:
You can add your keys/secret to the [Sources] tab and pop-in the formulas in CONFIG api key/secret
API key: =vlookup(D5,Sources!B2:H,6,0)
Secret: =vlookup(D5,Sources!B2:H,7,0)



ChangeLog



March 17, 2020 - coinAll, BitFlyer and eTorox added
  • This is automagically pushed to the clients (no update required)
February 23, 2020 - version 1.4 Fixes
  • Improved update mechanism for commands (fallback)
  • 15 lines when no lic
February 11, 2020 - version 1.3 Maintenance release
  • Main change is the way refresh is handled  |   work around for a bug in latest Google update V8
  • Coinbase and Gemini should work
  • Other small changes
February 8, 2020 - Recommendation to turn Refresh temporary to OFF in CONFIG
  • Issue in the Google Cloud
January 16, 2020 - version 1.2c - Small fix 
  • Improved error handling - example tools.gs not found
  • Added option to call custom function - allows cool new stuff ;-)
January 10, 2020 - version 1.2b - Currency_com Collector added
  • Requested by Andrey
  • 1.2b tiny change in template to extend the range to config!d5
December 20, 2019 - version 1.2a - Bug fix 
  • Encrypted API key did not work for Coinmarketcap and Cryptocompare 
December 19, 2019 - Bybit 
  • Added Bybit that was requested by one of our users
December 16, 2019 - ReX version 1.2
  • Other source - see this video on the generic fetcher
  • More lines displayed in the evaluation version (think 15)
  • Query Tab renamed to Fetch
  • Bug fixes
December 9, 2019 - ReX version 1.15
  • My_Commands *new
  • Bug fix - Filter button in Query
  • Remove Refresh Now button in CONFIG
  • Changed the Refresh value to 10 min

December 8, 2019 - ReX version 1.1

December 4, 2019 - ReX version 1.0 was born






Comments