S&P Technical PostgreSQL Analysis using Window Functions

Back in the good old days, pandas-datareader used to be the cool-kids way to pull finacial data. Alas, all good things must come to an end. Now, due to Yahoo Finance and Google not-cooperating, this API has now become defunct. However, if you're like me, you're still craving financial data.

Instead of relying on another person's API, I've launched a personal postgreSQL database on AWS RDS to store data for this site and my personal projects. Over the past three days, I've loaded in all historical S&P data and launched a backend server to update the data daily (blog post here if you're interested in reading more in-depth on that process).

Using PostgreSQL, the database driver chosen for my RDS, I ran some technical analysis on the S&P. For results of the below queries, click here to download an excel with visualizations.

First up, let's take a look at the structure of the data.

SELECT *

FROM visualizations.stock_prices

LIMIT 1;

To finish our warm-up, let's get the average stock price per year.

SELECT
  EXTRACT('year' from s.date) as trading_year,
  AVG(close) AS average_closing_price


FROM visualizations.stock_prices s

GROUP BY 1

ORDER BY 1;

First Value

Now, for our first window function, we'll use first_value to determine the date with the highest close price for any day of that year.

/* Max Cloing Price with Date Per Year */
WITH prices_with_max_close_date AS (
  SELECT
    EXTRACT('year' from date) AS trading_year,
    date,
    close,
    first_value(date) over (PARTITION BY EXTRACT('year' from date) ORDER BY EXTRACT('year' from date), close DESC) AS max_close_date
  FROM visualizations.stock_prices
)
SELECT
  trading_year,
  date,
  close as max_closing_value

FROM prices_with_max_close_date

WHERE date = max_close_date

ORDER BY 1;

Lag

Another good pathway of analysis is to look at the deviation between days. We'll use lag to determine the days with the 25 largest changes in closing value from the prior day.

/* 25 Largest Changes in Closing Value */
WITH prices_with_daily_delta AS (
  SELECT
    date,
    LAG(close) OVER (ORDER BY date) AS previous_day_stock_price,
    close,
    close / LAG(close) OVER (ORDER BY date) - 1 AS daily_price_delta

  FROM visualizations.stock_prices
)
SELECT *

FROM prices_with_daily_delta

WHERE date >= '1957-03-04' /* Start of the S&P, there were too many dates in the 1930s without this filter! */

ORDER BY ABS(daily_price_delta) DESC

LIMIT 25;

Now, as lag is a very important function for analysis, we'll use it again to determine the average daily deviation by decade.

/* Average Deviation by Decade */
WITH prices_with_daily_delta AS (
  SELECT
    date,
    LAG(close) OVER (ORDER BY date) AS previous_day_stock_price,
    close,
    close / LAG(close) OVER (ORDER BY date) - 1 AS daily_price_delta

  FROM visualizations.stock_prices
)
SELECT
  EXTRACT('decade' FROM date) AS trading_year,
  AVG(daily_price_delta) * 100 AS average_daily_deviation

FROM prices_with_daily_delta

GROUP BY 1

ORDER BY 1;

Break Time

Let's take a quick break from window functions to look at the top intra-day trading range percentage.

/* Max Intra-Day Trading Range */
SELECT
  date,
  open,
  low,
  high,
  (high - low) / open  as intraday_trading_range_over_opening

FROM visualizations.stock_prices

ORDER BY 5 DESC

LIMIT 25;

Avg

For our grand finale, let's look at the 200-day moving average using the avg window function.

The 200-Day Moving average is a technical indicator used to gauge long-term price trends. If the price is consistently trading above the average, this can be seen as an upward trending market. Conversely, markets trading below the 200-day moving average are viewed to be in a downtrend. When the current price crosses below the 200-day moving average, this is seen as a nexus point indicating a change in price-trends.

/* 200-day close moving-average crossings */
WITH prices_with_ma AS (
  SELECT
    date,
    close,
    AVG(close) OVER (ORDER BY date ROWS BETWEEN 200 - 1 PRECEDING AND CURRENT ROW) AS close_200d_ma

  FROM visualizations.stock_prices

  WHERE date >= '1957-03-04' /* Start of the S&P, there were too many dates in the 1930s without this filter! */
),
prices_with_ma_lagged AS (
SELECT
  *,
  close - close_200d_ma AS close_minus_200d_ma,
  LAG(close - close_200d_ma) OVER (ORDER BY date) AS close_minus_200d_ma_lagged


FROM prices_with_ma
)
SELECT *

FROM prices_with_ma_lagged

WHERE (close_minus_200d_ma > 0 and close_minus_200d_ma_lagged < 0)
   OR (close_minus_200d_ma < 0 and close_minus_200d_ma_lagged > 0)
   OR close_minus_200d_ma = 0;

Window functions are an advanced but rewarding tool needed in any data scientist's arsenal.

Thanks for reading!