For the past few months, I've been building a stock market API based on SQL. I love SQL. I learned it more than 15 years ago, and it still serves me well to this day.
To me, SQL feels such a natural way to screen stocks. With Hotstoks, I can write all sorts of queries to find stocks based on any criteria like price, performance, and other indicators. I can use this data to feed other systems, build a portfolio dashboard, generate reports, alerts, etc.
Reading Bullish (disclaimer: another one of my projects), I kept noticing that pretty much every day, there's a stock that pops 20%+ or more in a day, and sometimes I see them, again and again, every other week, and I started thinking of ways to write a query that captures that and use it to my advantage to speculate (gamble) into some quick stock trades.
The SQL I came up with is pretty straightforward. It checks if the stock performance is positive throughout the year combined with price being at least 10% below its 52 weeks high and the ten-day average volume being greater than the current volume.
SELECT
name,
symbol,
price,
price_change_percent_1d,
price_change_percent_1m,
price_change_percent_3m,
price_change_percent_6m,
price_change_percent_ytd,
moving_avg_50d_percent_diff,
moving_avg_200d_percent_diff,
all_time_high_percent_diff,
week_52_high_percent_diff
FROM
stocks
WHERE
price_change_percent_1d > 0
AND price_change_percent_1m > 0
AND price_change_percent_3m > 0
AND price_change_percent_6m > 0
AND price_change_percent_ytd > 0
AND week_52_high_percent_diff < - 10
AND volume_avg_10d_percent_diff > 0
ORDER BY
price_change_percent_1d DESC
LIMIT 20;
The next step was to find a user-friendly way to present the data. I didn't want to keep running this query by hand again and again via CURL/Postman. It's time to automate.
I spent some time thinking about all the possible ways and shinny tech I could have used to do this. But in the end, I decided to go with Google Sheets, given it's perfect for showing tabular data and can be easily embedded and shared anywhere, and it's pretty ubiquitous.
I was never a hardcore Excel or Google Sheets user, but reading all the cool things people can do with it on HN always amazed me, and now it was my chance to give it a try.
Doing some research, I've learned that Google Docs have a scripting language that is a subset of Javascript called App Script. With that, you can do neat things like calling 3rd party APIs like Hotstoks and rendering results in cells, applying formulas, etc. Their API docs are pretty extensive and well documented.
To access Script Editor in Google sheets, go in the Tools menu.
The Script editor provides a simple interface to write, run and debug your functions that act on a specific sheet or doc that you fetch via their API.
For my project, I had to learn a few things:
- How to call an external API
- How to manipulate a particular spreadsheet
- How to trigger my script to run automatically
To make requests to external API's you use UrlFetchApp. To get the current spreadsheet you call the SpreadsheetApp class like below.
SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
With an instance of SpreadsheetApp in hand, you can do all sorts of operations in your spreadsheet like appending rows, getting ranges of cells, setting values to specific ranges or individual cells, and applying formulas.
function main() {
const data = JSON.parse(fetchData());
sheet().clear({ formatOnly: false, contentsOnly: true });
sheet().clearNotes();
sheet().appendRow(
[
"Symbol",
"Price",
"1D",
"1M",
"3M",
"6M",
"YTD",
"MA 50D",
"MA 200D",
"52W High",
"All Time",
"Performance"
]
);
data.results.forEach(stock => {
sheet().appendRow(
[
stock.symbol,
`$${stock.price}`,
stock.price_change_percent_1d / 100,
stock.price_change_percent_1m / 100,
stock.price_change_percent_3m / 100,
stock.price_change_percent_6m / 100,
stock.price_change_percent_ytd / 100,
stock.moving_avg_50d_percent_diff / 100,
stock.moving_avg_200d_percent_diff / 100,
stock.week_52_high_percent_diff / 100,
stock.all_time_high_percent_diff / 100
]
);
const lastRow = sheet().getLastRow();
const lastColumn = sheet().getLastColumn();
const firstCell = sheet().getRange(lastRow, 1);
const lastCell = sheet().getRange(lastRow, lastColumn);
firstCell.setNote(stock.name);
lastCell.setValue(`=SPARKLINE(D${lastRow}:H${lastRow},{"charttype","column"})`);
});
updatedAt();
}
function fetchData() {
var sql = `
SELECT
name,
symbol,
price,
price_change_percent_1d,
price_change_percent_1m,
price_change_percent_3m,
price_change_percent_6m,
price_change_percent_ytd,
moving_avg_50d_percent_diff,
moving_avg_200d_percent_diff,
all_time_high_percent_diff,
week_52_high_percent_diff
FROM
stocks
WHERE
price_change_percent_1d > 0
AND price_change_percent_1m > 0
AND price_change_percent_3m > 0
AND price_change_percent_6m > 0
AND price_change_percent_ytd > 0
AND week_52_high_percent_diff < - 10
AND volume_avg_10d_percent_diff > 0
ORDER BY
price_change_percent_1d DESC
LIMIT 20;
`;
var options = {
'method': 'post',
'payload': sql,
'headers': {
'x-api-key': '<YOUR HOTSTOKS API KEY>',
}
};
return UrlFetchApp.fetch('https://api.hotstoks.com/query', options);
}
function sheet() {
return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
}
function updatedAt() {
const date = Utilities.formatDate(new Date(), "PST", "MM-dd-yyyy HH:mm:ss");
sheet().getRange("A23").setValue(`Last run at: ${date} PST`);
}
To run your code, you can assign triggers accessible via the left menu in Script Editor. With triggers, you give a function to run, an event source, and an event type.
Another nice bonus of using Google Sheets is the separation of concerns between code and presentation. Your script deals primarily with preparing and writing data to the spreadsheet and all the formatting you do directly on the sheet page as you would normally do.
Google App Script combined with Google Sheets turned out to be a great combo to spin some quick ideas without too much effort that I'll be exploring a lot more in the future.