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: 407
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: 785
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, 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,550
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
 


Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 12:55 AM