I have stock data in 1 min intervals and would like to convert it into other timeframes (e.g., 10 min, daily, monthly).
Here's is some sample data and my final goal:
[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.87 3.87 10
10-Feb-05 12:11:00 3.87 3.87 3.87 3.87 2
10-Feb-05 12:12:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:13:00 3.85 3.87 3.84 3.85 23
10-Feb-05 12:14:00 3.85 3.85 3.85 3.85 6
10-Feb-05 12:15:00 3.86 3.86 3.86 3.86 1
10-Feb-05 12:16:00 3.85 3.85 3.85 3.85 1
10-Feb-05 12:18:00 3.85 3.85 3.85 3.85 3
10-Feb-05 12:19:00 3.85 3.85 3.85 3.85 3
[DateTime] [Open] [High] [Low] [Close] [Volume]
10-Feb-05 12:10:00 3.88 3.88 3.84 3.85 50 *
*sum
changing timeframes with stock data
Jusong
Correction, the group by should read
GROUP BY m.Interval10Mins,CONVERT(CHAR(8),t.[DateTime],112)
DjMels
Assuming your intervals will never span midnight,
you can do this by joining to an intervals table. This
has all minutes in one day with any intervals you want
and can be set up like this
CREATE TABLE MinuteIntervals(Mins INT NOT NULL PRIMARY KEY,
HMS CHAR(8) NOT NULL,
Interval10Mins INT NOT NULL,
Interval1Hour INT NOT NULL)
DECLARE @i INT
SET @i=0
WHILE @i < 24*60
BEGIN
INSERT INTO MinuteIntervals(Mins,HMS,Interval10Mins,Interval1Hour)
SELECT @i, CONVERT(CHAR(8),DATEADD(minute,@i,'19000101'),108),
@i/10, @i/60
SET @i=@i+1
END
Now you need to join to this to your stock table
grouping by whatever interval you want.
SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
INNER JOIN MinuteIntervals m ON m.HMS=CONVERT(CHAR(8),t.[DateTime],108)
GROUP BY m.Interval10Mins
Spermij
Of course this will only work for intervals less that one day.
Should have read the question fully!
For daily or longer intervals, don't join
to the MinuteIntervals table, simply group by the relevant amount
e.g. for daily
SELECT MIN(t.[DateTime]) AS [DateTime],
(SELECT t2.[Open]
FROM stocktable t2
WHERE t2.[DateTime]=MIN(t.[DateTime])) AS [Open],
MAX(t.[High]) AS [High],
MIN(t.[Low]) AS [Low],
(SELECT t2.[Close]
FROM stocktable t2
WHERE t2.[DateTime]=MAX(t.[DateTime])) AS [Close],
SUM(t.[Volume]) AS [Volume]
FROM stocktable t
GROUP BY CONVERT(CHAR(8),t.[DateTime],112)
For monthly
GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),6)
For yearly
GROUP BY LEFT(CONVERT(CHAR(8),t.[DateTime],112),4)
whaveman
Syed Zishan Kalam
I very much appreciate your help. If I understand the roles of tables t and t2, it appears the code only works if two different tables are used.
Does that mean I have to first create a copy of the original table in order to run the script correctly
SeamusJunior44
Yes, you are so right
. That's what I get for trying to cut back on my caffeine.
It works like a charm
. Thanks to all who have helped. I really do appreciate it.