Google Spreadsheet to Manage Your Stocks Using API
Spreadsheet Trading
You might think API trading is only for hard-core HFT programmers. That’s not true! If you've used a spreadsheet app with some formula, you're ready for algo trading. Today, I wanted to show you how I built portfolio management using Google Spreadsheet. Sound hard? Not really.
App Script
Like VBA in Microsoft Excel, Google Spreadsheet comes with a scripting language called App Script to extend its functionality. Unlike VBA which roots from Bill Gates’ favorite Basic language, Google chose JavaScript as a thought-leader in the web age. While not all modern JS functionality is available, it's pretty handy and well-documented.
App Script is for many Google Apps as well as used as a standalone app, but today we use it from Spreadsheet. In order to do so, you create a new spreadsheet and select [Tools] -> [Script editor] from the menu.
It opens an editor in the browser and you can start writing App Script. Write a simple function like
function doDouble(v) {
return v * 2;
}Then you can call this function in the cell formula like
= doDouble(2)Trading API
Alpaca provides a RESTful web API for free. It allows you to get information about your account, positions, and orders, and even submit new orders, through a simple HTTP interface. It’s so simple that you can easily call it from JavaScript. All you need is an HTTP request. Apps Script comes with a built-in class called UrlFetchApp that is flexible enough to send any type of HTTP request. For our sample sheet, we’ll use that to communicate with the Alpaca API.
That’s Great, Now What?
If you've ever interacted with any stock trading app, you may find something is missing. Or too much. It’s never perfect because every stock trader has unique needs. If you are managing dozens of small positions in your portfolio, it’s not even easy to see all the positions quickly. Or you just want to calculate some metrics using sum() or average(). Or you want to customize your view with your favorite fields. If existing apps don’t work for you, why not build your own?
Sample Sheet
I actually shared my sample spreadsheet with a working script (also the full code is attached below). As this screenshot shows, you can monitor your portfolio value and see it in a tabular format friendly to you. It can show your account information such as open positions and orders along with the ability to cancel any open orders. Here is the detailed step you can follow to build your own.
Here’s where you go to get started making your own copy of the sheet.
- Copy this spreadsheet as shown in the screenshot above.
- Get your API keys from the Alpaca dashboard.
- Put your API key into cell E4, the put your secret key into cell E5. (If you want to use this with a live trading account, you’ll need to uncheck the “Paper?” checkbox.)
- Click the Refresh Sheet button to run the script. (You may get a security warning which you’ll need to accept before the sheet can update.)
Other Pages
Now that your spreadsheet is hooked up to the Alpaca API, what else can you do with it? I’ve included a couple of other pages showing ways to interact with the Alpaca API.
With the Create New Orders page, you can craft a new portfolio within the spreadsheet if you want.
First, clear out any of these sample orders. Then, you can fill the cells in with the values you want. For example, if you wanted to put 10% of your money into AAPL, you would fill in the “Buy” table with a row that had AAPL as the symbol and 10 as the %. (You could leave the time as market and the TIF as day — if you’re confused about any of those other fields, though, check out the Alpaca API’s docs for ordering.)
For both percent and quantity allocation, fractional orders are enabled! To learn more about fractional orders through the Alpaca API, see the link below
Alpaca
The “View Order Fills” page uses a different API endpoint — account activities — to show you exactly how and when your orders were filled. You can specify a date to see all the orders for a particular date, or hit Refresh Sheet without a date specified to see the last few hundred order fills for your account. (They may take a while to fill in if you load that many, though!)
Unlimited Possibilities
Because it’s a spreadsheet, you can do whatever you want. One may want to calculate optimal portfolio diversification using market value weights. Another could be just to sell half of all 100 positions at once.
Not many people know but Spreadsheet comes with a built-in function called GOOGLEFINANCE() which allows you to put historical prices in a sequence of cells, and you may calculate something like moving average across many stocks easily.
One could also edit the script to include historical trades, quotes, and bars from Alpaca Market Data v2.
It’s up to you what you do with the trading API and power of Google!
Enjoy Spreadsheet Trading!
var PositionRowStart = 17;
// Submit a request to the Alpaca API
function _request(path,params,data) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Account & Portfolio")
var isPaper = sheet.getRange("E3").getValue()
var apiKey = sheet.getRange("E4").getValue()
var apiSecret = sheet.getRange("E5").getValue()
if (!apiKey || !apiSecret) {
sheet.getRange("E2").setValue("Please input an API key and secret key.")
throw "Please input an API key and secret key."
} else {
sheet.getRange("E2").setValue("")
}
var headers = {
"APCA-API-KEY-ID": apiKey,
"APCA-API-SECRET-KEY": apiSecret,
};
var paper_live = isPaper ? "https://paper-api.alpaca.markets" : "https://api.alpaca.markets"
var endpoint = (data ? "https://data.alpaca.markets" : paper_live);
var options = {
"headers": headers,
};
var url = endpoint + path;
if (params) {
if (params.qs) {
var kv = [];
for (var k in params.qs) {
kv.push(k + "=" + encodeURIComponent(params.qs[k]));
}
url += "?" + kv.join("&");
delete params.qs
}
for (var k in params) {
options[k] = params[k];
}
}
var response = UrlFetchApp.fetch(url, options);
var json = response.getContentText();
var data;
try{
data = JSON.parse(json);
}
catch(err) {
data = err;
}
console.log(data)
return data;
}
/*
* 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
});
}
// uses latest trades as price point
function getPrice(sym) {
return _request(("/v2/stocks/"+sym+"/trades/latest"), {
method: "GET",
muteHttpExceptions: true
}, true).trade.p
}
function clearPosition(sym) {
return _request(('/v2/positions/' + sym), {
method: "DELETE",
muteHttpExceptions: true
})
}
function clearPositions() {
return _request('/v2/positions', {
method: "DELETE",
muteHttpExceptions: true
})
}
function clearOrders() {
return _request('/v2/orders', {
method: "DELETE",
muteHttpExceptions: true
})
}
function listFillActivities(date) {
qs = date ? {"date": date.toISOString()} : null
return _request('/v2/account/activities/FILL', {
method: "GET",
qs: qs,
muteHttpExceptions: true
})
}
// Submit an order to the Alpaca API
function submitOrder(symbol, qty, side, type, tif, limit, stop, extendedHours) {
var payload = {
symbol: symbol,
side: side,
qty: qty,
type: type,
time_in_force: tif,
extended_hours: extendedHours,
};
if (limit) {
payload.limit_price = limit;
}
if (stop) {
payload.stop_price = stop;
}
return _request("/v2/orders", {
method: "POST",
payload: JSON.stringify(payload),
muteHttpExceptions: true,
});
}
// Format JSON responses for display
function truncateJson(json) {
var out_str = "{ "
for(var key in json) {
if(json.hasOwnProperty(key)){
out_str += (key + ": " + json[key] + "; ");
}
}
out_str += "}"
return out_str;
}
// Delete the order specified by the field value
function deleteOrderById() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Account & Portfolio");
var id = sheet.getRange("J9").getValue();
var resp = _request(("/v2/orders/" + id),{
method: "DELETE",
muteHttpExceptions: true
});
if(resp.message && resp.message == "Empty JSON string") resp = "Order Sent";
sheet.getRange("J10").setValue(resp);
updateSheet();
}
// 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("A9:A").getValues().map(function(elem){return elem.toString()}),
"sell": sheet.getRange("I9:I").getValues().map(function(elem){return elem.toString()})
}
var qtys = {
"buy": sheet.getRange("B9:B").getValues().map(function(elem){return elem.toString()}),
"sell": sheet.getRange("J9:J").getValues().map(function(elem){return elem.toString()})
}
var types = {
"buy": sheet.getRange("C9:C").getValues().map(function(elem){return elem.toString()}),
"sell": sheet.getRange("K9:K").getValues().map(function(elem){return elem.toString()})
}
var tifs = {
"buy": sheet.getRange("D9:D").getValues().map(function(elem){return elem.toString()}),
"sell": sheet.getRange("L9:L").getValues().map(function(elem){return elem.toString()})
}
var limits = {
"buy": sheet.getRange("E9:E").getValues().map(function(elem){return elem.toString()}),
"sell": sheet.getRange("M9:M").getValues().map(function(elem){return elem.toString()})
}
var stops = {
"buy": sheet.getRange("F9:F").getValues().map(function(elem){return elem.toString()}),
"sell": sheet.getRange("N9:N").getValues().map(function(elem){return elem.toString()})
}
if(rebalance) {
var positions = listPositions()
positions.forEach(function(position){
if(symbols.buy.indexOf(position.symbol) == -1) {
clearPosition(position.symbol);
}
})
}
symbolsLength = symbols.buy.length > symbols.sell.length ? symbols.buy.length : symbols.sell.length
for(var i = 0; i < symbolsLength; i++) {
if(symbols.buy[i] != ""){
sheet.getRange("G"+parseFloat(9+i)).setValue("submitting...");
var qty = parseFloat(qtys.buy[i].toString().trim());
var sym = symbols.buy[i].toString().trim()
var side = "buy"
if(percent) {
qty = portfolio_value / getPrice(sym) * qty / 100
}
if(rebalance) {
var position_qty
if(isNaN(parseFloat(getPosition(sym).qty))) position_qty = 0;
else position_qty = parseFloat(getPosition(sym).qty)
qty -= position_qty
side = (qty < 0 ? "sell" : "buy")
qty = Math.abs(qty)
}
if(qty == 0){
sheet.getRange("G"+parseFloat(9+i)).setValue("Order not sent, already have desired quantity.")
}
else {
var b_resp = submitOrder(sym,qty,side,types.buy[i].toString().trim(),tifs.buy[i].toString().trim(),limits.buy[i].toString().trim(),stops.buy[i].toString().trim())
sheet.getRange("G"+parseFloat(9+i)).setValue(truncateJson(b_resp))
}
}
if(symbols.sell[i] != "") {
sheet.getRange("O"+parseFloat(9+i)).setValue("submitting...");
var qty = qtys.sell[i].toString().trim();
var sym = symbols.sell[i].toString().trim();
var side = "sell"
if(percent) {
qty = portfolio_value / getPrice(sym) * qty / 100
}
if(rebalance) {
var position_qty
if(isNaN(parseFloat(getPosition(sym).qty))) position_qty = 0;
else position_qty = parseFloat(getPosition(sym).qty)
qty = (-1 * qty) - position_qty
side = (qty < 0 ? "sell" : "buy")
qty = Math.abs(qty)
}
if(qty == 0){
sheet.getRange("O"+parseFloat(9+i)).setValue("Order not sent, already have desired quantity.")
}
else {
var s_resp = submitOrder(sym,qty,side,types.sell[i].toString().trim(),tifs.sell[i].toString().trim(),limits.sell[i].toString().trim(),stops.sell[i].toString().trim(),extendedHours)
sheet.getRange("O"+parseFloat(9+i)).setValue(truncateJson(s_resp))
}
}
}
updateSheet()
}
// Clear existing positions from the spreadsheet so they can be updated
function wipePositions() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Account & Portfolio");
var rowIdx = PositionRowStart;
while (true) {
var symbol = sheet.getRange("A" + rowIdx).getValue();
if (!symbol) {
break;
}
rowIdx++;
}
var rows = rowIdx - PositionRowStart;
if (rows > 0) {
sheet.deleteRows(PositionRowStart, rows);
}
}
// Update the Open Positions & Orders Sheet
function updateSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Account & Portfolio");
var account = getAccount()
sheet.getRange("B9").setValue(account.id)
sheet.getRange("B10").setValue(account.buying_power)
sheet.getRange("B11").setValue(account.cash)
sheet.getRange("B12").setValue(account.portfolio_value)
sheet.getRange("B13").setValue(account.status)
sheet.getRange("B10:B12").setNumberFormat("$#,##0.00")
// Updating orders and positions can take a bit of time - avoid trying to do it twice at once.
if (sheet.getRange("B15").getValue() == "Updating") {
return
}
sheet.getRange("B15").setValue("Updating...")
wipePositions();
var positions = listPositions()
var endIdx = null
if (positions.length > 0) {
positions.sort(function(a, b) { return a.symbol < b.symbol ? -1 : 1 });
positions.forEach(function(position, i) {
var rowIdx = PositionRowStart + i;
sheet.getRange("A" + rowIdx).setValue(position.symbol);
sheet.getRange("B" + rowIdx).setValue(position.qty);
sheet.getRange("C" + rowIdx).setValue(position.market_value);
sheet.getRange("D" + rowIdx).setValue(position.cost_basis);
sheet.getRange("E" + rowIdx).setValue(position.unrealized_pl);
sheet.getRange("F" + rowIdx).setValue(position.unrealized_plpc);
sheet.getRange("G" + rowIdx).setValue(position.current_price);
});
endIdx = PositionRowStart + positions.length - 1;
sheet.getRange("B" + PositionRowStart + ":B" + endIdx).setNumberFormat("###0.00");
sheet.getRange("C" + PositionRowStart + ":C" + endIdx).setNumberFormat("$#,##0.00");
sheet.getRange("D" + PositionRowStart + ":D" + endIdx).setNumberFormat("$#,##0.00");
sheet.getRange("E" + PositionRowStart + ":E" + endIdx).setNumberFormat("$#,##0.00");
sheet.getRange("F" + PositionRowStart + ":F" + endIdx).setNumberFormat("0.00%");
sheet.getRange("G" + PositionRowStart + ":G" + endIdx).setNumberFormat("$#,##0.00");
sheet.getRange("C" + (endIdx + 1)).setValue("total")
sheet.getRange("D" + (endIdx + 1)).setValue("total")
sheet.getRange("E" + (endIdx + 1)).setValue("total")
sheet.getRange("F" + (endIdx + 1)).setValue("average")
sheet.getRange("G" + (endIdx + 1)).setValue("median")
sheet.getRange("C" + (endIdx + 2)).setFormula("=sum(C" + PositionRowStart + ":C" + endIdx + ")")
sheet.getRange("D" + (endIdx + 2)).setFormula("=sum(D" + PositionRowStart + ":D" + endIdx + ")")
sheet.getRange("E" + (endIdx + 2)).setFormula("=sum(E" + PositionRowStart + ":E" + endIdx + ")")
sheet.getRange("F" + (endIdx + 2)).setFormula("=average(F" + PositionRowStart + ":F" + endIdx + ")")
sheet.getRange("G" + (endIdx + 2)).setFormula("=median(G" + PositionRowStart + ":G" + endIdx + ")")
}
sheet.getRange("B15").setValue("")
var orders = listOrders()
if(orders.length > 0) {
sheet.getRange("J15").setValue("Updating...")
orders.sort(function(a, b) { return a.symbol < b.symbol ? -1 : 1 })
orders.forEach(function(order, i) {
var rowIdx = PositionRowStart + i;
var price = getPrice(order.symbol)
var filled_qty_str = order.filled_qty + " / " + order.qty
sheet.getRange("I" + rowIdx).setValue(order.symbol);
sheet.getRange("J" + rowIdx).setValue(filled_qty_str);
sheet.getRange("K" + rowIdx).setValue(order.filled_avg_price);
sheet.getRange("L" + rowIdx).setValue(order.type);
sheet.getRange("M" + rowIdx).setValue(order.limit_price);
sheet.getRange("N" + rowIdx).setValue(order.stop_price);
sheet.getRange("O" + rowIdx).setValue(price);
sheet.getRange("P" + rowIdx).setValue(order.time_in_force);
sheet.getRange("Q" + rowIdx).setValue(order.id);
});
sheet.getRange("J15").setValue("")
}
}
// Clear existing order fills so the table can be updated
function clearFills() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("View Order Fills")
var rowIdx = 9
while (true) {
var symbol = sheet.getRange("A" + rowIdx).getValue()
if (!symbol) {
break
}
rowIdx++
}
var rows = rowIdx - 9
if (rows > 0) {
sheet.deleteRows(9, rows)
}
}
// Update order fills table
function updateFills() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("View Order Fills")
sheet.getRange("C7").setValue("Updating...")
clearFills()
var date = sheet.getRange("E4").getValue()
var fills = listFillActivities(date)
if (fills.length > 0) {
var rowIdx = 9
fills.forEach(function(fill, i) {
sheet.getRange("A"+rowIdx).setValue(fill.symbol)
sheet.getRange("B"+rowIdx).setValue(fill.side)
sheet.getRange("C"+rowIdx).setValue(fill.price)
sheet.getRange("D"+rowIdx).setValue(fill.qty)
sheet.getRange("E"+rowIdx).setValue(fill.transaction_time)
rowIdx++
})
}
sheet.getRange("C7").setValue("")
}
This is not an offer, solicitation of an offer, or advice to buy or sell securities, or open a brokerage account in any jurisdiction where Alpaca is not registered (Alpaca is registered only in the United States).
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.
Interested in getting the latest news and content from Alpaca? Follow us on LinkedIn, Twitter, and Facebook for more.
