Historical Data Query Script Files

hist_qry1.sql

Gets the closing price of a set of 10 stocks for a 10-year period and groups into weekly, monthly, and yearly aggregates. For each aggregate period, determines the low, high, and average closing price value. Output is sorted by TRADING_SYMBOL and trade date.

hist_qry2.sql

Adjusts all prices and volumes (prices are multiplied by the split factor and volumes are divided by the split factor) for a set of 1000 stocks to reflect the split events during a specified 300-day period, assuming that events occur before the first trade of the split date. These are called split-adjusted prices and volumes.

hist_qry3.sql

Finds the differences between the daily high and daily low on the day of each split event during a specified period for each stock in a specified list of 1000 stocks.

hist_qry4.sql

Calculates the value of the S&P 500 and Russell 2000 index for a specified day using unadjusted prices and the index composition of the two indexes on the specified day.

hist_qry5.sql

Finds the 21-day and 5-day moving average price for a specified list of 1000 stocks during a 6-month period, using split-adjusted prices.

hist_qry6.sql

Based on query hist_qry5.sql,finds the points (specific days) when the 5-day moving average intersects the 21-day moving average for these stocks. Output is sorted by TRADING_SYMBOL and trade date.

hist_qry7.sql

Determines the value of $100,000 now if 1 year ago it was invested equally in 10 specified stocks (in other words, allocation for each stock is $10,000). The trading strategy is: When the 20-day moving average crosses over the 5-month moving average, the complete allocation for that stock is invested and when the 20-day moving average crosses below the 5-month moving average, the entire position is sold. The trades are made on the closing price of the trading day.

hist_qry8.sql

Finds the pair-wise coefficients of correlation in a set of 10 securities for a 2-year period. Sorts the securities by the coefficient of correlation, indicating the pair of securities corresponding to that row.

hist_qry9.sql

Determines the yearly dividends and annual yield (dividends/average closing price) for the past 3 years for all the stocks in the Russell 2000 index that did not split during that period. Uses unadjusted prices since there were no splits to adjust for.