Historical Market Data Queries

Historical market data queries compare price histories for different instruments over time.

Historical market data changes infrequently, 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 RAPStore 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 RAP sample data in RAPStore. Use dbisql to run these sample queries against RAPStore. The output that appears for each query is derived from the large set of sample data provided in the RAP Samples component. See the Sybase IQ Utility Guide > Using Interactive SQL (dbisql).

• Running Historical Data Query Scripts
  To execute historical data query scripts, you must first run the $RAP/RAPStore/data/scripts/rap_setup.sh script to create temporary store tables. The hist_qry scripts (5, 6, and 7) will not run without this step.
• Historical Data Query Script Files
  The table contains descriptions of the files located in the $RAP/RAPStore/data/scripts directory on the RAPStore server.
• hist_qry1.sql
  Gets the closing price of a set of 10 stocks over a 10-year period, and groups the data into weekly, monthly, and yearly aggregates.
• hist_qry2.sql
  Adjusts all prices and volumes to reflect the split events during a specified 300-day period for a specified set of 1000 stocks, assuming that events occur before the first trade of the split date.
• 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 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 hist_qry5.sql.) Finds the points (specific days) when the 5-day moving average intersects the 21-day moving average for these stocks.
• hist_qry7.sql
  Determines 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).
• 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.