Historical market data queries

Historical market data queries compare price histories for different instruments over time. Historical market data does not change frequently, and updates typically occur at the end of the trading day.

The end-of-day data is consolidated into the historical TAQ time series in VLDBServer as well. Stock History, Stock Quotes, and Stock Trade tables contain entries for end-of-day data records.

The Historical market data queries are optimized to run with the Risk Analytics Platform sample data in VLDBServer.

The Historical market data queries for VLDBServer all begin with a commit statement. This commit statement causes the data to refresh, so the query accesses the most recent data. When you write your own queries for VLDBServer, be sure to precede the query with a commit statement.

Script name	Description
hist_qry1.sql	Get the closing price of a set of 10 stocks for a 10-year period and group into weekly, monthly, and yearly aggregates. For each aggregate period, determine the low, high, and average closing price value. Output is sorted by TRADING_SYMBOL and trade date.
hist_qry2.sql	Adjust 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	For each stock in a specified list of 1000 stocks, find the differences between the daily high and daily low on the day of each split event during a specified period.
hist_qry4.sql	Calculate 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	Find the 21-day and 5-day moving average price for a specified list of 1000 stocks during a 6-month period. (Use split-adjusted prices).
hist_qry6.sql	(Based on the previous query) Find 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	Determine the value of $100,000 now if 1 year ago it was invested equally in 10 specified stocks (That is, 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	Find the pair-wise coefficients of correlation in a set of 10 securities for a two year period. Sort the securities by the coefficient of correlation, indicating the pair of securities corresponding to that row.
hist_qry9.sql	Determine 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. Use unadjusted prices since there were no splits to adjust for.

Script name

Description

hist_qry1.sql

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

hist_qry2.sql

Adjust 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

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

hist_qry4.sql

Calculate 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

Find the 21-day and 5-day moving average price for a specified list of 1000 stocks during a 6-month period. (Use split-adjusted prices).

hist_qry6.sql

(Based on the previous query) Find 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

Determine the value of $100,000 now if 1 year ago it was invested equally in 10 specified stocks (That is, 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

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

hist_qry9.sql

Determine 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. Use unadjusted prices since there were no splits to adjust for.