UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Do This In SQL (asks An SQL Newbie), Older SQL Server Versions    
 
   
firlandsfarm
post Mar 25 2020, 05:29 AM
Post#1



Posts: 411
Joined: 28-April 02
From: Heathfield, England


I have a table of forex 1 minute prices on SQL Server Express 2019 constructed as ...

Date_Time Pair Open High Low Close
2020-02-03 10:22:00 EURGBP 0.8481 0.8484 0.8481 0.8484
2020-02-03 10:22:00 EURUSD 1.1048 1.1049 1.1047 1.1049
2020-02-03 10:22:00 GBPUSD 1.3025 1.3025 1.3022 1.3022
2020-02-03 10:23:00 GBPUSD 1.3022 1.3022 1.3017 1.3017
2020-02-03 10:23:00 EURUSD 1.1049 1.1049 1.1047 1.1048
2020-02-03 10:23:00 EURGBP 0.8484 0.8486 0.8483 0.8486
2020-02-03 10:24:00 EURGBP 0.8486 0.8488 0.8485 0.8486
2020-02-03 10:24:00 EURUSD 1.1048 1.1048 1.1045 1.1046
2020-02-03 10:24:00 GBPUSD 1.3017 1.3018 1.3013 1.3015
2020-02-03 10:25:00 GBPUSD 1.3015 1.302 1.3015 1.3018
2020-02-03 10:25:00 EURUSD 1.1046 1.1047 1.1046 1.1046
2020-02-03 10:25:00 EURGBP 0.8486 0.8486 0.8484 0.8484
2020-02-03 10:26:00 EURGBP 0.8484 0.8485 0.8484 0.8485
2020-02-03 10:26:00 EURUSD 1.1046 1.1049 1.1046 1.1049
2020-02-03 10:26:00 GBPUSD 1.3018 1.3022 1.3018 1.3021
2020-02-03 10:27:00 GBPUSD 1.3021 1.3021 1.3017 1.3017
2020-02-03 10:27:00 EURUSD 1.1049 1.1049 1.1048 1.1048
2020-02-03 10:27:00 EURGBP 0.8485 0.8487 0.8484 0.8486

There are 18 Pairs in total and the table only runs from 01/01/2019 but already has over 7 million records so if I add earlier years as I want to it will grow enormously! I have just shown a sample of 3 Pairs for simplicity. Maybe also relevant is that not all data is complete, there are some 'minutes' randomly missing from all Pairs but there is nothing I can do about that, that is how the data comes. I can live with that.

I want to use the data to try and analyse price movement patterns but I'm failing to see how I can compare later prices with earlier prices, such as ...

The 2020-02-03 10:22:00 'minute' for the EURGBP closed at 0.8484. How would I then check the table to see if it rose by say 10 pips to 0.8494 or fell by 10 pips to 0.8474.

I can only think of finding all High values for Pair = EURGBP and High >=0.8494 and Date_Time > 2020-02-03 10:22:00 and then finding the earliest of those ...

... and likewise finding all Low values for Pair = EURGBP and Low <=0.8474 and Date_Time > 2020-02-03 10:22:00 and then finding the earliest of those ...

... then finally comparing the two found Date_Times.

But that seems to be a bit sledgehammerish bearing in mind the large number of records to hunt through and sort ... is there a more 'subtle' way of achieving it?

I'm thinking maybe by looking at the values of the High (or Low) column for EURGBP one by one in Date_Time sequence until the value condition is met which would normally be not far away from the starting Date_Time. But I don't know how to do that!

Also is this the sort of task that might be better suited to VBA with it's For/Next looping capabilities than SQL alone?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
FrankRuperto
post Mar 25 2020, 08:21 AM
Post#2



Posts: 969
Joined: 21-September 14
From: Tampa, Florida USA


You can also use the Dlookup function, We use it a lot for statistical analysis. With your data, how much granularity do you require for doing effective comparisons? You shouldn't have a storage space problem in Express2019, it holds up to 10GB, but you can create an aggregate fact table that summarizes the data in hourly or daily averages, instead of using the minute grains. You know you can store this data in Access and view it in Excel, right? Excel has a lot of statistical analysis functions you can use to do the kind of crunching you want to do. You can also take advantage of superior charts, rolling views with filtering, pivot tables, etc. etc .
This post has been edited by FrankRuperto: Mar 25 2020, 08:30 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
MadPiet
post Mar 25 2020, 12:13 PM
Post#3



Posts: 3,704
Joined: 27-February 09



Sounds like you need Window functions. LAG(), LEAD() let you look at previous/next records in the "window". Those have been available since at least 2012.
Go to the top of the page
 
firlandsfarm
post May 4 2020, 12:20 AM
Post#4



Posts: 411
Joined: 28-April 02
From: Heathfield, England


Hi FR/MP, sorry for my absence guys, I have got distracted into another project but this has only been shelved ... not cancelled.

FR ... Yes I can build Hour and Day tables to summarise Open, High, Low, Close but will still need the minute data to resolve 'chicken and egg' questions. smile.gif Let me explain. Let's say I want to know if price X or price Y was hit first. The problem arises if both prices fall within the High/Low range for the time period, I then have to resort to the minute data within the time period. Also I often load data into Excel for further analysis but I try to clean the data as much as possible in the database before loading because Excel can get very slow when you have 100's of thousand records.

MP, that sounds very interesting ... maybe Lead() and Lag() are just what I want.

I will read up on Lead and Lag as well as the Dlookup suggested by Frank. I must admit I have never used any of the "D" functions having never understood the difference between them and the 'not "D"' equivalent!

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
MadPiet
post May 4 2020, 05:52 AM
Post#5



Posts: 3,704
Joined: 27-February 09



SQL Server doesn't have Domain functions (DMin, DMax…) - unless I missed something. Kathi Kellenberger wrote a book about cool things you can do with Windowing Functions. This article looks really good, too. Then there's one from Joe Celko, the crotchedy old man of SQL.
Go to the top of the page
 
MadPiet
post May 19 2020, 02:06 PM
Post#6



Posts: 3,704
Joined: 27-February 09



If you're always looking the same number of "records" back, then you can use LAG(). Or you could use something like

SELECT ...
FROM Prices
WHERE Ticker = @TickerID
AND ( TimeStamp = @TimeStamp1 OR TimeStamp = @TimeStamp2 )

Then you'd get two records, and you could use LAG() to get the "previous" one (if you sort in date order inside your LAG() expression.
Go to the top of the page
 
MadPiet
post May 19 2020, 04:23 PM
Post#7



Posts: 3,704
Joined: 27-February 09



You could try something like this...

CODE
SELECT Pair
    , Date_time
    , OpenP
    , LAG(OpenP,1) OVER (PARTITION BY Pair ORDER BY Date_Time) AS PrevOpen
    , OpenDelta = OpenP - LAG(OpenP,1) OVER (PARTITION BY Pair ORDER BY Date_Time)
    , CloseP
    , HighP
    , LowP
FROM TradeData
ORDER BY Pair, Date_time;


If you need to see the change between two non-contiguous dates, you could do something like:

CODE
DECLARE @FirstDate DATETIME = '2020-02-03 10:23:00.000',
    @SecondDate DATETIME = '2020-02-03 10:26:00.000';

SELECT Date_Time
    , Pair
    , CloseP
    , lastPrice = LAG(CloseP) OVER (PARTITION BY Pair ORDER BY Date_Time)
    , delta = CloseP - LAG(CloseP) OVER (PARTITION BY Pair ORDER BY Date_Time)
FROM TradeData
WHERE Pair = 'EURGBP'
AND (Date_Time = @FirstDate
    OR Date_Time = @SecondDate)
    ORDER BY Date_Time;


You're probably going to need a clustering index on (Pair, TradeDateTime) so that all the sales for a single stock are stored together in your table... otherwise, this is going to be painfully slow.

This post has been edited by MadPiet: May 19 2020, 04:25 PM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th May 2020 - 06:51 PM