Data Mining (1 Viewer)

bpk

New member
Local time
Today, 18:18
Joined
Jul 30, 2007
Messages
7
Can someone help me out with this complicated logi ( atleast for myself)

I have a set of data which has records of all the trades of a particular contract. I have so far cleaned it and i would like to study the way the pricing varies, need it for my school thesis.

For every CONTRACT MONTH, i need the price in a group of 30 minutes. I want the OPEN price, HIGH, LOW and the average of the closing prices within that 30 minutes.

My logic is to start the time 00:00-00:29, 00:30-00:59, 01:00-01:29, 01:30-01:59 etc.. and check Number of trades done in that 30 min, the OPEN, HIGH, LOW and the average of the closing prices within that time frame.

Anybody can suggest me some ideas ?

thanks
 

Attachments

  • Data_0606.txt
    58.4 KB · Views: 111

modest

Registered User.
Local time
Today, 13:18
Joined
Jan 4, 2005
Messages
1,220
Just use time functions to break the minutes and place them with their corresponding hours. I stored your data in a table called "Price"
Code:
SELECT 
   Price.Contract_Date, 
   Price.Current_Date, 
   IIf(Hour([current_time])<10,"0") & Hour([current_time]) & IIf(Minute([Current_Time])<30,"00-29","30-59") AS [Group], 
   First(Price.Open) AS FirstOfOpen, 
   Max(Price.High) AS MaxOfHigh, 
   Min(Price.Low) AS MinOfLow, 
   Avg(Price.Close) AS AvgOfClose
FROM Price
GROUP BY 
   Price.Contract_Date, 
   Price.Current_Date, 
   IIf(Hour([current_time])<10,"0") & Hour([current_time]) & IIf(Minute([Current_Time])<30,"00-29","30-59");
 
Last edited:

bpk

New member
Local time
Today, 18:18
Joined
Jul 30, 2007
Messages
7
Million thanks for the speedy reply ! Will keep you posted with the outcome. Appreciate it very much !
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:18
Joined
Feb 28, 2001
Messages
26,999
OK, there are ways to do this, but the easiest (ugliest) way is to cheat.

Here's the theory behind the cheat I'm about to give you.

Date/time fields in Access are actually just DOUBLE numbers measured from the Windows reference date, for which day 0 is Dec 31, 1899. (Day 1 is 1 Jan 1900.) You'll get a number of days and fractions of a day since midnight of the reference date. This is, in essence, a time-line that is linear across all times covered by the date range. The Windows date formatting routines are responsibile for converting the time-line number to a date and time. The nice thing about this format is that the difference between two dates in date/time formatted fields is just the number of days and fractions of a day between them - precisely because it IS a linear timeline number.

For contemporary dates, your numbers will be in the 39K to 40K range. If I did the math right, today is 39292. This means that a number will have about 5 digits to the left of the decimal. All else will be to the right. Now, for Windows, midnight is fractional time 0 and noon is fractional time 0.5, you can work out other times from there. So today at 11:59:59 PM, the exact timeline number would be something like 39292.9999884259 stated to 15 decimal digits. Which happens to be the range of DOUBLE number precision. (Actually, 55 bits, but don't worry about that.) If you back-convert that DOUBLE number, it becomes 30-JUL-2007 23:59:58.99 to the nearest hundredth of a second. (Next digit was a 2.) So you have plenty of precision in that DOUBLE to do the job you need to do.

So here's the trick. If you convert the date to a DOUBLE (via the CDBL() function) you get the time-line number that the date field contains, recast into a more traditional floating point format. With me so far?

OK, your half-hour intervals around the clock divide the day up into 48 equal pieces-parts. So MULTIPLY the floating point value by 48.00, TRUNCATE it with CLng (convert it to a LONG), then re-float it back to DOUBLE, then DIVIDE it by 48, and then finally convert the DOUBLE back to a date.

So if the time is X and your goal is to put X into one of the x-intervals that occur every 30 minutes, the formula is...

xinterval = CDate( CDbl( CLng( CDbl( x ) * 48.0 ) ) /48.0 )

Or something like that. Check my parentheses to be sure.

You can either build a query with that formula in it or you can write a public function (requires a general module as its container) to compute the date truncated to your interval given the exact time as an input.

OK, once you have the interval computed, you can do GROUP-BY operations on that interval. From there, you can do whatever study you need based on the groups.

You might be able to do this function in VBA, which would be more efficient if you compile it.

Code:
Public Sub XInterval( X as Date, N as Integer ) As Date

Dim DN as Double

DN = CDbl( N )
XInterval = CDate( CDbl( CLng( CDbl( x ) * DN ) ) / DN )

End Sub

Then you would call this function with DateInterval = XInterval( X, 48 )

The way I wrote it, you could choose 15 minute intervals (N=96), 10 minute intervals (N=144), 2-hour intervals (N=12), etc. So it is up to you to piddle with intervals as you wish.
 

bpk

New member
Local time
Today, 18:18
Joined
Jul 30, 2007
Messages
7
Just astounded, impressive style of thinking Doc !! Will keep you posted after i work it out ! Many thanks
 

Dom DXecutioner

AWF VIP
Local time
Today, 10:18
Joined
Jun 25, 2007
Messages
57
Ditto Doc, very impressive indeed... i need a tylenol just reading it, let alone thinking it :)
 

modest

Registered User.
Local time
Today, 13:18
Joined
Jan 4, 2005
Messages
1,220
Interesting Doc.

But still, keep it simple. Use a query and the time functions as I previously suggested, it's quicker to construct.

If you are in need of something more versatile, use what Doc provided. It will be able to break down your groups easier in the future.
 

bpk

New member
Local time
Today, 18:18
Joined
Jul 30, 2007
Messages
7
Date and time in one Colum

Thanks Modest, for the moment i have used your approach but for later needs i will have to use Doc's approach as i will possibly trying atleast a few more time frame.

I have one more question, how do i bring the Date and Time together in One Field ( At the moment those two are in seperate fields, as Current_Date and Current_Time)

thanks
 

bpk

New member
Local time
Today, 18:18
Joined
Jul 30, 2007
Messages
7
Modest, i hcatn figure out the IIF statement as it needs True and False values, in yours you have mentioned in case its 'True' and " & Hour(Current_Time)" in case its false. The & is not recognised. thanks for helping me out, i have already asked too much, but need to get this thesis done by end of this week. thanks
 

Users who are viewing this thread

Top Bottom