Manage Your Stocks from Google Spreadsheet Using API
This is the Dec 4th entry for Trading API Advent Calendar. Yesterday was a post with Yoshi’s futuristic view on automation. We still have slots available for this Advent Calendar so please reach out to me (@umitanuki) or @AlpacaHQ if you are down to write something (pretty much anything is ok)!
Spreadsheet Trading
You might think API trading is only for hard-core HFT programmers. That’s not true! If you have used a spreadsheet app with some formula, you are ready for algo trading. Today, I wanted to show you how I built portfolio management using Google Spreadsheet. Sounds hard? Not really.
App Script
Like VBA in Microsoft Excel, Google Spreadsheet comes with a script 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 is pretty handy and well-documented.
App Script is for many Google Apps as well as used as 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 a editor in 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 RESTful web API for free. It allows you to get account, positions, and orders information as well as submit orders through the simple HTTP interface. It’s so simple that you can imagine you can call it from JavaScript. All you need is a HTTP request. App Script actually comes with a built-in class called UrlFetchApp that is very flexible to send any type of HTTP request.
That’s Great, Now What?
If you have ever interacted with any stock trading app, you may find something is missing. Or too much. It’s never perfect because every single stock trader has her own 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 don’t you build your own?
Sample Sheet
I actually share my sample spreadsheet with working script (also the full code is attached below). As this screenshot shows, you can update your portfolio values in one click and see it in a tabular format friendly to you. It can show your account information such as portfolio value and list of positions you hold. Here is the detail step you can follow to build your own.
- Copy this spreadsheet
- [Tools] -> [Script editor]
- Add your API key ID and secret in the script. If you are doing it in paper trading, change the endpoint to https://paper-api.alpaca.markets
- Click on the UPDATE button (you may get security warning but you can trust me :D), then that’s all!
The SUBMIT button submits an order using the “Order” section information. If it’s in the market hours, you will get order acceptance response in the “Result” cell.
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 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. It’s up to you what you do with the trading API and power of Google!
Enjoy Spreadsheet Trading!
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.
