Time Series Problem

[frankiem]

I have a very large table of stock price data, which shows all the prices that occured over a year, timestamped to the nearest second. There are three type of prices -"Trade" which is the price that occured when a trade was agreed, "Ask" which is an unexecuted quote price at which a posting seller is prepared to sell, and "Bid" which is an unexecuted quote price at which a posting buyer is willing to buy. What I want to know is - how does each "Trade" price relate to the preceding "Bid" and "Ask" prices? Is it nearer the "Bid" or the "Ask"? Can anyone suggest how I could do this in SQL?

The data looks like this:
Date Time Type Price
02/03/99 09:00:32 Ask 93.51
02/03/99 09:00:37 Ask 93.53
02/03/99 09:01:14 Bid 93.47
02/03/99 09:02:02 Trade 93.48
02/03/99 09:03:44 Trade 93.51
02/03/99 09:04:03 Ask 93.54

I want to add another column which will show "Bid side" or "Ask side" for all the "Trade" events.

Frank

 

[blom0344]

Hello Frank,

Will this query ever give the right results if you do not take into account the actual "Trade" (Transaction) the prices refer to? If you can get that related data, you could open up an crosstab query with transaction as the row,type of price in the column and max(price) or something in the body and calculate the difference between the pricetypes at recordlevel.
How would you otherwise know how to compare a certain Bid with corresponding Trade (price)? T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[MichaelRed]

I'm a lot out of it today, so this may not make sense to you.

First, are you JUST wanting to add a field which is ONLY populated in the records where [Type] = "Trade" and it is populated with a representation of eoither "Ask" or "Bid"? Further, you only have a single stock in the specific file (Not need to check for a stock symbol)?

If so, the problem may be solved in a number of ways. A subquery will do just fine. Sort the tabe(s) on the date-time field(s) (ascending). In the subquery find the least previous record where [Type] <> &quot;Trade&quot; and retrieve [type] as your calculaed field.

Also, a simple loop through the recordset will provide the info. Start through the recordset, Retain the [Type] un less it is &quot;Trade&quot;. If [Type] = &quot;Trade&quot; the retained var is posted to the calculated field. If you will utilize the information often w/o altering the source, it may be convenient to add the field in the original table and just run the update once. If the source data will change, it may be more appropiate to have a static var in a small procedure (to hold the &quot;Ask&quot; / &quot;Bid&quot; indicator) and run the process as a select query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[frankiem]

Thanks for the feedback guys.

I figured out a solution along the lines that MichaelRed suggested. It proved very longwinded but it works. It uses temporary tables and then updates a new column in the original table. I tried using subqueries but this just caused my PC to stall - the table has over 4,000,000 records in it, so that may be why.

Thanks again,

Frank