For a Applied Time Series Analysis course project, we had to build forecasting model for stocks that exhibit increase in the stock price for several days in a row. More specifically, in building the forecasting models we had to use Stocks that exhibit the longest streak of increasing stock price day over day. Since I had to use the current daily data, identifying stocks that exhibit this behavior was rather challenging.
There no published list of Stocks that exhibit this behavior.
I had access to Stock data from Zepl in our class Snowflake account. But how do I identify Stocks with the longest streak of prices increases, day over day?
Initially I was thinking of using Knime to accomplish this. Then it struck me why not use MATCH_RECOGNIZE in Snowflake for this purpose.
MATCH_RECOGNIZE frequently at work to identify rows that match a certain pattern.
Problem of finding the Stocks with the longest streak of increasing closing price was not exactly what I use
MATCH_RECOGNIZE for at work. I had to come up with a
PATTERN that would match increasing stock price. I used the
LAG function for this. Here is the
SQL that I came up with
select symbol, days_of_increasing_stock_price, STREAK_START_DATE, STREAK_END_DATE from US_STOCKS_DAILY.PUBLIC.STOCK_HISTORY match_recognize( partition by symbol order by DATE measures first(DATE) as STREAK_START_DATE, last(DATE) as STREAK_END_DATE, count(*) as days_of_increasing_stock_price ONE ROW PER MATCH PATTERN (INCREASE+) DEFINE INCREASE as close > LAG(close) ) order by days_of_increasing_stock_price DESC;
Note that the
PATTERN that we are trying to match in the
INCREASE, and INCREASE is defined as the
TRUE if today’s Closing Stock price was greater than yesterday Closing price. INCREASE+ REGEX pattern indicates one or more Increases in the Stock price.
This query worked really well. If you are curious, it was OPER Stock that exhibited the longest streak of increasing closing price for at total of 48 days in a row (at the time of this writing).
For what would have taken a complex workflow in Knime to achieve, Snowflake’s powerful
MATCH_RECOGNIZE was able to perform in a single concise query. Go Snowflake!