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

Summary:

• You can use Google Sheets' built-in method called GOOGLEFINANCE to extract data about the current day's high and low, and the previous day's high and low.
• You can use IF formulas to flag whether the current day is inside of the previous day's price action or outside (or neither). Setting this flag is a form of data transformation
• You can use a Pivot Table to find all of the stock tickers that had inside days (or outside days) and you can even order them by intraday volume. This is a method of loading the data into a more easily-digestible form.

Motivation:

• To find stock-trading plays, I used to use http://www.runstrat.com - 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 get the current day's high for a stock like GOOG, use =GOOGLEFINANCE("GOOG","high")
• To get the current day's low for a stock like GOOG, use =GOOGLEFINANCE("GOOG","low")
• To get the previous day's high, you need to have a 2x2 set of blank cells, and use =GOOGLEFINANCE("GOOG","high",TODAY()-1,1)
• To get the previous day's low, you need to have a 2x2 set of blank cells, and use =GOOGLEFINANCE("GOOG","low",TODAY()-1,1)
• ​​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:

​DailyStratBarType is 1 if the current day's low and high are within the previous day's
DailyStratBarType is 3 if the current day's low is lower than yesterday AND the current day's high is higher as well
DailyStratBarType is 2 if either the current day's low is lower than yesterday's OR the current day's high is higher than yesterday's (but not both)

To designate this, I used a nested IF formula: =IF(AND(J4>G3,L4<H3),1,IF(AND(J4<G3,L4>H3),3,2))

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:

• display only the ticker symbols that have been flagged as 1 (or 3, separately)
• sorted from highest to lowest volume

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: docs.google.com/spreadsheets/d/1v9ONxggTIFebN6Tf09hRNnLE21VWhXQhRgQDI1myaRE/edit?usp=sharing