Operating a fully autonomous managed account with Alpaca and Google Sheet

28 Nov 2020

As many of you might know, it is possible to operate a fully automated active portfolio of tradable US stocks and ETFs via Alpaca’s execution API. In this short article, I will show you a possible way of doing so.

Alpaca offers a seamless way to automate trade executions in liquid US stocks and ETFs directly from third-party applications. This allows the management of investment positions to be conducted remotely from Alpaca. Execution of required assets can then be routed directly to Alpaca with all custody, dividend collection, and cash management taken care of there.

In this article, I will use my project, ATneos Algorithmic Technologies as an example algorithm to determine trades. However, one can easily substitute all of the following steps with their own personal trading algorithm. ATneos specializes in quantitative and automated investment models trading U.S. Equities and ETFs. Additionally, it has developed an open-source approach to execute and manage these portfolios. Technologically there are many ways to achieve this, but simplicity is the key to reliability and the combination of Google Sheet and Alpaca delivers this combination perfectly.

The Stages of Production

1. Open a Google account (just set up a new email with Google)

2. Open a new Google Sheet

3. Select Script Editor

4. Collect API and Secret keys to access the Alpaca API

The first job is to collect the asset allocation output from the ATneos Investment Model or any other algorithmic model provider. A simple way is to call the data as a .csv file using the UrlFetchApp.fetch() function within a stand-alone script. The advantages of this are the ability to retrieve the most up-to-date data, either manually or automatically. The .csv file-type offers great compatibility with existing institutional fund management systems.

function importCSVFromWeb() {

# Provide the full URL of the CSV file.
var csvUrl = " full URL of the CSV file ";
var csvContent = UrlFetchApp.fetch(csvUrl).getContentText();
var csvData = Utilities.parseCsv(csvContent);

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

}

To ensure the most recent data is collected, a trigger would be set to run this function every minute or every 5 minutes. All the current asset allocation data is now in the google sheet.

The next stage is to build the Account Management sheet. ATneos has beta released a dynamic TAA algorithmic investment process using the SPDR S&P 500 ETF (SPY) and iShares US T-Bond ETF (TLT). This sheet can be built in any way the user prefers but, in its most simplistic form, the following are the key requirements.

Key Requirements:

1. Retrieve the account balance from Alpaca

2. Establish the correct allocation to SPY and TLT ETFs as delivered by the algorithm’s .csv output.

3. Calculate the number of required shares of SPY and TLT to match the algorithm’s required allocation

4. Retrieve the total number of shares of SPY and TLT held on the Alpaca Account

5. Establish the required trade in SPY and TLT shares

6. Send the trades to Alpaca for execution

This sheet then ensures that the correct number of SPY and TLT shares are always held in the Alpaca account.

The resources to provide the required Google Script code can be found in the AlpacaDOCS section at

The user determines the time at which executions or rebalances occur. With my example, the ATneos models aim to trade in the closing 10 minutes of the trading day. A Google Script trigger can be set to the desired time.

It is then a simple task to connect the Account Management Sheet to the Alpaca platform by calling the correct functions from the code script found in the link supplied. The user can build this out in whichever way they prefer. The main functions are to ensure:

positions, account balances, and price levels are reconciled
additional triggers should be set up to call the required functions.


 #Alpaca API Methods

function getAccount() {
    return _request("/v2/account",{
        method: "GET",
        muteHttpExceptions: true
    });
}
function listOrders() {
    return _request("/v2/orders",{
        method: "GET",
        muteHttpExceptions: true
    });
}
function listPositions() {
    return _request("/v2/positions",{
        method: "GET",
        muteHttpExceptions: true
    });
}
function getPosition(sym) {
    return _request("/v2/positions"+ sym),{
        method: "GET",
        muteHttpExceptions: true
    });
}
function getPrice(sym) {
    return parseFloat(_request("/v2/bars/minute",{
        method: "GET",
        qs:{
            "symbols": sym,
            "limit":1
        }
        muteHttpExceptions: true
    }, true)[sym][0].c;
}

Execution requires a simple function call of orderFromSheet() and all orders generated by the Client’s Google Sheet.

#Create a new order from the Create New Orders Sheet
function orderFromSheet(){
    clearOrders()
    var sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Create New Orders");
    var portfolio_value= parseFloat(getAccount().portfolio_value);
    var percent= sheet.getRange("F3").getValue();
    var rebalance= sheet.getRange("F4").getValue();
    var extendedHours=sheet.getRange("F5").getValue();

    var symbols={
        "buy":sheet.getRange("A10:A").getValues().map(function(elem){return elem.toString()}),
         "sell":sheet.getRange("I10:I").getValues().map(function(elem){return elem.toString()})
    }
    var qtys={
        "buy": sheet.getRange("B10:B").getValues().map(function(elem){return elem.toString()}),
         "sell": sheet.getRange("J10:J").getValues().map(function(elem){return elem.toString()})
    }
}
    var types={
        "buy": sheet.getRange("C10:C").getValues().map(function(elem){return elem.toString()}),
         "sell": sheet.getRange("K10:K").getValues().map(function(elem){return elem.toString()})
    }
}
    var tifs={
        "buy":sheet.getRange("D10:D").getValues().map(function(elem){return elem.toString()}),
         "sell":sheet.getRange("L10:L").getValues().map(function(elem){return elem.toString()})
    }
    var limits={
        "buy":sheet.getRange("E10:E").getValues().map(function(elem){return elem.toString()}),
         "sell":sheet.getRange("M10:M").getValues().map(function(elem){return elem.toString()})
    }
    var stops={
        "buy":sheet.getRange("F10:F").getValues().map(function(elem){return elem.toString()}),
         "sell":sheet.getRange("N10:N").getValues().map(function(elem){return elem.toString()})
    }
}

And that’s it!

Now, the client can post US$ funds to their dedicated Alpaca account, connect the Google Sheet they have just built, and the client algorithm allocates actively to generate the investment performance they seek. All this can be achieved for little or no direct cost. Below shows an example implementation of ATneos and the Alpaca API based on what we discussed today. One could easily substitute ATneos with any personal trading algorithm they so desire.

It is immediately clear that the pairing of Google Sheet and Alpaca offers a highly efficient means to manage accounts. All investors with a live Alpaca account can use this approach.

If you wish to learn more about ATneos Algorithmic Technologies please contact me at [email protected].

Technology and services are offered by AlpacaDB, Inc. Brokerage services are provided by Alpaca Securities LLC (alpaca.markets), member FINRA/SIPC. Alpaca Securities LLC is a wholly-owned subsidiary of AlpacaDB, Inc.

You can find us @AlpacaHQ, if you use twitter.