Nathan Oldridge | Blog

Basic ETL Example: Using Google Sheets to find stocks with Inside Day and Outside Day

Summary: Motivation:

To find stock-trading plays, I used to use - it would scan the market for particular patterns that occur, including inside days and outside days. Unfortunately, it went offline at the end of October, presumably because it was expensive to run the site and donations (revenue) did not cover expenses. Can I build something to replace it? Yes, but extracting data about stocks can be challenging. Google Sheets contains a formula command called GOOGLEFINANCE which is 20-minute delayed stock data Implementation:

To accommodate the 2x2 nature of the data returned by the previous day's data, my tickers and other data are in 2-row-tall merged cells:

To designate this, I used a nested IF formula:

Lastly, I needed to collect the ticker symbols of the stocks that have been flagged 1 (or 3, separately). This can be done with a Pivot Table, which lets me:

Picture Notes: The "but not both" part of DailyStratBarType being a "2" should remind my former Toronto Prep School Computer Science students that XOR (exclusive or) does have use cases in the real world.

You can Make a Copy of this Spreadsheet, which is a work in progress, for your own purposes by visiting this link: