Applying Statistical Analysis to Intraday Forex Trading Using SQL

If you were interested in financial markets, and in particular Forex, and if it was intraday trading, then I think this material will be interesting for you. You have probably noticed that the market price dynamics sometimes behave in a similar way at different time points during the day.

If we exclude the factors that set the global trend, as well as events that cause great volatility, then we can see a certain pattern of price behavior during the day. This is influenced by the activity of the players when some organizations start and finish work, and a number of other factors.

Assuming that during the day there is a time when the price reaches a peak, as well as a time when it is at a minimum, then you can try to make the corresponding trades in order to make a profit.

To reduce the impact of strong fluctuations, it is advisable to choose a currency pair with moderate volatility. At the same time, I would like to reduce the cost of various commissions, in particular the spread (the difference between the purchase and sale prices set by the broker).

Since we plan to close orders during the day, we will not pay a swap (commission for transferring a position to the next day). Although in some cases it may be more profitable to close the transaction the next day, if the maximum falls on the next day from the moment the position was opened, provided that we use "long" positions.

Let's try to analyze the GBPUSD currency pair and test our theory in a slightly unusual way, using SQL to collect statistics.I took GBPUSD minute bars for 2021, about 373k records in total (1440 minute bars per day, about 260 trading days).

For those who may not know, a bar is a combination of OHLC numbers - the values of the Open, High, Low, and Close rates. In addition, of course, there is the time of the bar, as well as the trading volume. If you focus on the range from Low to High, you can get a more profitable difference in rates during the day, but in this case, we will have to track these highs and lows, set floating stop losses, or use any other ways to track the moment of closing a position.

We want to get the exact time values when we can open and close a position, so we will focus on the closing price.

I took the data of one of the brokers and exported the data on the rates from the Metatrader platform.

Record example:

2021.11.19	17:01:00	1.34520	1.34530	1.34460	1.34463	178	0	3

Next, I created a table in the database and loaded data into it (here I am using MySQL):

CREATE TABLE test.gbpusd (
  d date,
  t time,
  o decimal(8, 5),
  h decimal(8, 5),
  l decimal(8, 5),
  c decimal(8, 5),
  v int
);

We can add a couple of indexes:

ALTER TABLE test.gbpusd ADD INDEX IDX_gbpusd_d (d);
ALTER TABLE test.gbpusdADD INDEX IDX_gbpusd_t (t);

Let's see how much data we have:

SELECT COUNT(DISTINCT DAYOFYEAR(d)) FROM gbpusd;

We have 260 trading days. Dynamics throughout the year:

SELECT d, AVG(c) AS avgc
FROM gbpusd
group BY d
ORDER BY d;

With a spread in min and max close:

SELECT d, AVG(c) AS avgc, MIN(c) AS minc, MAX(c) AS maxc
FROM gbpusd
group BY d
ORDER BY d;

It is interesting to try to overlay the days on top of each other to try to find the overall price action during the day. We can get the average values by the query:

SELECT t, AVG(c) AS avgc
FROM gbpusd
GROUP BY t
ORDER BY t;

Here you can highlight the period when the price is at a minimum (red), suitable for opening a long position (buying), and high (green) where you can close this long position. You can also see that there is a moment when the price is even lower, and where it seems that there will be more profit, but visually this period looks like a period of high volatility, and you need to check how difficult it will be to catch the price at this point.

In addition, it is not entirely correct to impose absolute price values, and normalization must be performed.

With the help of a SQL, we will take the minimum and maximum for each day and calculate the percentage value of the close price of each bar:

SELECT t, AVG((c - minc) / (maxc - minc)) AS avgc
FROM (
	SELECT d, t, c,
      MAX(c) OVER(PARTITION BY d) AS maxc,
      MIN(c) OVER(PARTITION BY d) AS minc
    FROM gbpusd
) a
GROUP BY t
ORDER BY t
;

We got some average "picture of the day". It is easy to visually find the minimums and maximums. It seems that you can open positions at 00:30 and close at 10:35. Let's analyze what profit could be received if we made transactions at the same time:

SELECT
	SUM(IF((b.c - a.c - @spread) > 0, 1, 0)) profits,
	SUM(IF((b.c - a.c - @spread) > 0, 0, 1)) losses,
	ROUND(SUM(IF((b.c - a.c - @spread) > 0, 1, 0)) / COUNT(1) * 100, 2) AS profits_pct,
	ROUND(SUM((b.c - a.c - @spread) * @lot * @leverage), 2) total_profit
FROM
(SELECT @equity := 1000.00000, @spread := 0.0001, @lot := 100, @leverage := 100) AS init,
(SELECT d, c FROM gbpusd WHERE t = '00:30:00') a
JOIN
(SELECT d, c FROM gbpusd WHERE t = '10:35:00') b ON a.d = b.d
;

Not very much, but nevertheless, there is a positive trend.

But it is better to find the maximum difference. To do this, it will be more convenient to create a separate table:

CREATE TABLE gbpusd_avgnorm AS
SELECT t, AVG((c - minc) / (maxc - minc)) AS avgc
FROM (
	SELECT d, t, c, MAX(c) OVER(PARTITION BY d) AS maxc, MIN(c) OVER(PARTITION BY d) AS minc FROM gbpusd
) a
GROUP BY t
ORDER BY t;

A little over a million combinations in total:

SELECT COUNT(*)
FROM gbpusd_avgnorm a
JOIN gbpusd_avgnorm b ON b.t > a.t
;

We get data sorted in descending order of the price difference:

SELECT b.avgc - a.avgc, a.t, b.t
FROM gbpusd_avgnorm a
JOIN gbpusd_avgnorm b ON b.t > a.t
order BY 1 DESC
;

And we see that the combination 00:08 - 15:44 should be the most profitable:

SELECT
	SUM(IF((b.c - a.c - @spread) > 0, 1, 0)) profits,
	SUM(IF((b.c - a.c - @spread) > 0, 0, 1)) losses,
	ROUND(SUM(IF((b.c - a.c - @spread) > 0, 1, 0)) / COUNT(1) * 100, 2) AS profits_pct,
	ROUND(SUM((b.c - a.c - @spread) * @lot * @leverage), 2) total_profit
FROM
(SELECT @equity := 1000.00000, @spread := 0.0001, @lot := 100, @leverage := 100) AS init,
(SELECT d, c FROM gbpusd WHERE t = '00:08:00') a
JOIN
(SELECT d, c FROM gbpusd WHERE t = '15:44:00') b ON a.d = b.d
;

And finally, a query that simulates trading:

SELECT
	a.d,
	b.c - a.c AS delta, (b.c - a.c) * 10000 AS profit,
	CAST((b.c - a.c - @spread) * 10000 AS decimal(10,2)) AS profit_with_spread,
	CAST(@equity := @equity + (b.c - a.c - @spread) * 10000 AS decimal(10,2)) AS equity
FROM
(SELECT @equity := 1000.00000, @spread := 0.0001) AS init,
(SELECT d, c FROM gbpusd WHERE t = '00:08:00') a
JOIN 
(SELECT d, c FROM gbpusd WHERE t = '15:44:00') b ON a.d = b.d
ORDER BY a.d;

Conclusions

In general, it can be noted that there are some patterns of price from time to time during the day, although it is quite difficult to use them for correct trading. As always, a lot of factors have to be taken into account.

What are the pitfalls:

1. Floating spread. I have taken the minimum spread equal to 1 pip. But as it increases, the profit drops sharply, and at 5 pips it goes to 0.
2. The longer we hold positions, the higher the probability of occurrence of events that will lead to a strong price change, and therefore, triggering stop loss or margin call.
3. You need to have enough balance to withstand losses, especially when trading with large leverage. As the leverage increases, the risks increase exponentially.

It is interesting to consider these statistics for various currency pairs, as well as to explore the patterns of the day of the week, perhaps there are stronger periodic fluctuations.

It all depends on the specific conditions, but if you try to sort through various currency pairs, taking into account the conditions of a particular broker, you can try to implement the strategy.