To say the least Google Sheets are both convenient and functional. Combining these features with an algo trading setup must surely yield good results - so we thought here at ProfitView.

And with a little configuration we see great possibilities in this approach.

In this article we’ll show how to do this and provide all the code on the Github.

Sheets are a great tool, but to really make them work you can use Apps Script via the Extensions menu. Apps Script is a quite large subset of Javascript and gives you access to a number of Sheets (and Docs) components. In particular it lets you interact with the cells and ranges of your spreadsheets.

We’ll give a simple example of using Sheets with Apps Script to interact with ProfitView’s Trading Bots.

All it will do is to keep a list of the last X trades, filtered by source (exchange), coin and side. You can configure the number of trades to show. Not exactly mind-blowing functionality, but it illustrates the flexibility of the approach. Readers should be able to extrapolate to useful applications for their own algos.

The trades are sourced from the exchanges via ProfitView’s Trading Bots. You can write unrestricted Python 3 in the Bots. We’ll show an example of this in the mechanism used to keep track of the trades. The Bots make use of the internal API. We’ll walk through that process.

If you want to follow along you’ll need to sign-up to ProfitView and get at least the Hobbyist plan - click the Get Started button to do so.

Setting up the Bot

ProfitView makes it simple to get the raw trading data. You just sign up and add an exchange (we’ll use BitMEX):

To use the bots you need at least the Hobbyist plan. That makes the Trading Bots tab available.

Click on the icon with the + in the sidebar to create a new Strategy. You’ll probably want to rename it too: just click on its name in the editor’s top bar to do so. We named ours BotSheet. Now you’ll see the default code for the Trading class. The first 4 methods are the entry points driven by the 4 events that allow very general trading bots to be written. See the docs for details - we’ll in particular look at trade_update() for current purposes.

    def order_update(self, src, sym, data):
        """Event: receive order updates from connected exchanges"""

    def fill_update(self, src, sym, data):
        """Event: receive trade fill updates from connected exchanges"""

    def quote_update(self, src, sym, data):
        """Event: receive top of book quotes from subscribed symbols"""

    def trade_update(self, src, sym, data):
        """Event: receive market trades from subscribed symbols"""

trade_update() is called immediately for every trade for the coins you select. Latency is as low as it can reasonably be given the positioning of exchanges (in most cases, AWS or another cloud).

For the purpose of this demonstration we will simply store all trades. In practical trading bots you would likely do something different: process trades in relation to the top-of-book data from order_update() and your inventory perhaps. You would possibly store the results of this processing - or do other work on it.

In this example we use SQLite for storage. It is available to us since it is part of Python 3. There are many other methods that could be used.

To set Sqlite up we initialise a database in the __init__() constructor, creating a single table trades.

self.cur = self.con.cursor()
self.cur.execute(
  "CREATE TABLE IF NOT EXISTS trades(src, sym, price, side, time)")        

This is very simply populated when the trade_update() method is called:

self.cur.execute("""
  INSERT INTO trades VALUES
  (?, ?, ?, ?, ?)
  """, (src, sym, data['price'], data['side'], data['time']))

The Google Sheet

We populate the sheet using another feature of ProfitView’s event-driven framework: webhooks. In the default bot code you see two methods get_example() and post_example() both decorated by @http.route. These become GET and POST webhooks with signatures based on the back half of the method name - by default example but you can change it to anything you like and have lots of them (we’ll create a couple in this exercise). If you want to try it out, first run a default bot (this is safe since none of the methods have implementations). Now then click on the lightning icon again and then on “Webhooks” you will see

If you click on either link you will have a copy of the appropriate URL in your buffer. Paste it into your browser address line and it will call the get_example() method.

There’s a variety of ways to make all this work in Google Sheets. We’ve chosen to use an Apps Script but you can probably do it just using the =IMPORTDATA() Sheets function and others. You can create functions in Apps Script and use them via =FUNCTION_NAME(), but a convenient alternative is to make “buttons” by creating a Drawing from the Insert menu. You can then assign scripts to them:

which is what we do.

Using Apps Script

The scripts called in Drawings can’t take parameters - instead we get the script to read directly from the sheet:

  this.params = this.ssheet.getRange('I6:L7').getValues();
...
  var payload = 'latest_trades';
  for (var i = 0; i < this.params[0].length; i = i + 1) {
    payload = payload + (i ? '&':'?') + this.params[0][i] + '=' + this.params[1][i];
  }

That last part constructs the back-part of the URL for the bot’s webhook method get_latest_trades(). The interaction with ProfitView is like this:

get_profitview(action) {
  var payload = this.token + '/' + action;
  var url = BOT_URL + payload;
  var fetched = UrlFetchApp.fetch(url);
    ...
}

The bot code decomposes the parameters, makes a SELECT statement from them and returns the results:

@http.route
def get_latest_trades(self, data):
    where_clause = "where"
    for i, (k, v) in enumerate(list(data.items())[:-1]):
        if v: where_clause += (" and " if i else " ") + f"{k} = '{v}'"
    order_limit = f"order by time desc limit {data['num'] or 10}"
    statement = f"SELECT * from trades {where_clause} {order_limit}"
    trades_cur.execute(statement)
    return trades_cur.fetchall()

Because the Python Sqlite module’s fetchall() creates an Python list and that this is automatically serialized as JSON array it is easily handled in Apps Script and rendered without processing in the Sheet:

var response = JSON.parse(fetched);      
return response.data;

How it All Works Together

Getting this running can be as a stepping stone to incorporating similar features in the reader’s own bot. Just follow the instructions in the GitHub.

You can clone the repo with:

git clone git@github.com:profitviews/botsheet.git
cd botsheet

Feel free to comment with corrections and suggestions.