ChatGPT is incredible: NinjaTrader Brute Force

After stumbling upon Bernstein’s seasonal trade ideas, where he takes a consistent entry/exit based on nothing but the day of year, I decided to explore this across the ES futures in different ways.

Running a backtest across 2 hardcoded days and manually updating them one at a time generates thousands of combinations in the year. I quickly realized I couldn’t do this. Instead, I thought maybe I could “brute force” it. I coded a quick strategy in NinjaTrader that required a hard coded day (I used today, August 23), but not a sell date. Using GPT, I quickly put together a basic script, and added some of my old code to handle the SQL querying that would be needed for data organization.

Instead, it simply calculated how many days were left in the calendar year, and bought that many contracts on the start day. Then, it would sell one contract per subsequent day of the year. The results of each sell, along with the static buy price, were sent to my MySQL database to be stored in separate rows for each trade. From 2005 to 2023, this generated about 1200 rows of buying on August 23 of that year, and selling on each day afterward, until the end of December.

Once I successfully recorded these rows, I asked GPT to give me a query that would let me group the trades by the sell date, and it gave me this:

SELECT
    DATE_FORMAT(TradeDate, '%m-%d') AS TradeDay,
    SUM(SellPrice - BuyPrice) AS TotalProfit
FROM
    brute_force
GROUP BY
    DATE_FORMAT(TradeDate, '%m-%d')
ORDER BY
    TradeDay;

This essentially spits out the results for each day of the year, with a sum of profit/losses across every instance of that date.

That’s great, but it’s still not that useful, because the total profit hides the win rate. So I asked it to give me a separate query that will give me the groups where I can limit it by the number of rows with profits. 

SELECT
    DATE_FORMAT(TradeDate, '%m-%d') AS TradeDay,
    COUNT(*) AS PositiveProfitCount
FROM
    brute_Force
WHERE
    Profit > 0
GROUP BY
    DATE_FORMAT(TradeDate, '%m-%d')
HAVING
    COUNT(*) > 10
ORDER BY
    TradeDay;

And it did. This is a great example of just how powerful using GPT along with familiar tools (like MySQL) can be.

I still have more work left, because this only covers August 23, and I intend to brute force the entire year, as well as potentially applying this approach to forex which will be more cyclical and not on a permanent uptrend.

However, I also realized that organizing by specific sell dates is inconsistent, because of the existence of weekends. Selling on August 28 might not be possible every year. I remade the script, this time organizing it by daysHeld, and used these queries for more accurate testing.

SELECT
    daysHeld,
    COUNT(*) AS PositiveProfitCount
FROM
    brute_force
WHERE
    Profit > 0
GROUP BY
    daysHeld
HAVING
    COUNT(*) > 15
ORDER BY
    daysHeld;

This was a bit of a pain, since I was recording queries incorrectly initially. But I figured it out and now all is well. I will still have to manually change the buy date, but generating sells for the entire rest of the year is a massive time saver now.

Then, I made an even more useful query to show the win rate across some of these groups. Again, ChatGPT makes this leap instantaneous with me only having to supply a basic idea and initial query.

I asked it “

SELECT
daysHeld,
SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS profitable,
COUNT(*) AS total,
total/profitable as winrate

can we do this” and it produced the following (along with its standard explanation for why I’m not doing it right):

SELECT
    daysHeld,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS profitable,
    COUNT(*) AS total,
    -- Calculate winrate as a ratio of profitable to total trades
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force
GROUP BY
    daysHeld
HAVING
    COUNT(*) > 12
ORDER BY
    daysHeld;

And for the aggregate query, which returns all of the results we need in one easy table:

SELECT
    entryDate,
    daysHeld,
#    MAX(maxDrawdown) AS maxDrawdown,
#    AVG(maxDrawdown) AS avgDrawdown,
#    MAX(profit) AS maxProfit,
#    AVG(profit) AS avgProfit,
    -- Calculate max and avg percent drawdown from entry price
    MAX((maxDrawdown / entryPrice) * 100) AS maxPercentDrawdown,
    AVG((maxDrawdown / entryPrice) * 100) AS avgPercentDrawdown,
    -- Calculate max and avg percent profit from entry price
    MAX((profit / entryPrice) * 100) AS maxPercentProfit,
    AVG((profit / entryPrice) * 100) AS avgPercentProfit,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS profitable,
    COUNT(*) AS total,
    -- Calculate winrate as a ratio of profitable to total trades
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_gc
WHERE
    date_Format(entryDate, '%m-%d') > '00-00'
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')
HAVING
    COUNT(*) > 8
ORDER BY
    winrate desc, daysheld;
SELECT
    entryDate,
    daysHeld,
#    MAX(maxDrawdown) AS maxDrawdown,
#    AVG(maxDrawdown) AS avgDrawdown,
#    MAX(profit) AS maxProfit,
#    AVG(profit) AS avgProfit,
    -- Calculate max and avg percent drawdown from entry price
    MAX((maxDrawdown / entryPrice) * 100) AS maxPercentDrawdown,
    AVG((maxDrawdown / entryPrice) * 100) AS avgPercentDrawdown,
    -- Calculate max and avg percent profit from entry price
    MAX((profit / entryPrice) * 100) AS maxPercentProfit,
    AVG((profit / entryPrice) * 100) AS avgPercentProfit,
    AVG((profit / entryPrice) * 100) / AVG((maxDrawdown / entryPrice) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    -- Calculate winrate as a ratio of profitable to total trades
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_cl_s
WHERE
    date_Format(entryDate, '%m-%d') > '07-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld < 10 and year(entrydate) != 2025
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')
HAVING
    COUNT(*) > 8 and AVG((profit / entryPrice) * 100) > 0.5
ORDER BY
    entrydate asc, winrate desc, daysheld;
select entryDate, entryPrice, exitPrice, daysheld, profit, '0', (profit * 100 / entryPrice) as profitpct, maxdrawdown, ((maxDrawdown / entryPrice) * 100) AS maxpctdrawdown, ((maxprofit/entryPrice) * 100) AS maxpctprofit
from
brute_force_6b where

(date_Format(entryDate, '%m-%d') = '03-23' and daysHeld = 27)
or (date_Format(entryDate, '%m-%d') = '07-08' and daysHeld = 17)
or (date_Format(entryDate, '%m-%d') = '01-06' and daysHeld = 17)
or (date_Format(entryDate, '%m-%d') = '11-21' and daysHeld = 16)




select entryDate, entryPrice, exitPrice, daysheld, profit, '0', (profit * 100 / entryPrice) as profitpct, maxdrawdown, ((maxDrawdown / entryPrice) * 100) AS maxpctdrawdown, ((maxprofit/entryPrice) * 100) AS maxpctprofit
from
brute_force_cl_s where

(date_Format(entryDate, '%m-%d') = '01-14' and daysHeld = 3)
or (date_Format(entryDate, '%m-%d') = '01-18' and daysHeld = 4)
or (date_Format(entryDate, '%m-%d') = '01-30' and daysHeld = 8)
or (date_Format(entryDate, '%m-%d') = '02-03' and daysHeld = 6)
or (date_Format(entryDate, '%m-%d') = '02-20' and daysHeld = 1)
or (date_Format(entryDate, '%m-%d') = '03-04' and daysHeld = 7)
or (date_Format(entryDate, '%m-%d') = '03-05' and daysHeld = 6)
or (date_Format(entryDate, '%m-%d') = '03-13' and daysHeld = 1)
or (date_Format(entryDate, '%m-%d') = '03-14' and daysHeld = 1)
or (date_Format(entryDate, '%m-%d') = '03-17' and daysHeld = 1)
or (date_Format(entryDate, '%m-%d') = '03-19' and daysHeld = 8)
or (date_Format(entryDate, '%m-%d') = '04-04' and daysHeld = 9)
or (date_Format(entryDate, '%m-%d') = '04-18' and daysHeld = 6)
or (date_Format(entryDate, '%m-%d') = '04-29' and daysHeld = 6)
or (date_Format(entryDate, '%m-%d') = '05-08' and daysHeld = 1)
or (date_Format(entryDate, '%m-%d') = '05-21' and daysHeld = 3)
or (date_Format(entryDate, '%m-%d') = '05-29' and daysHeld = 4)
or (date_Format(entryDate, '%m-%d') = '06-10' and daysHeld = 2)
or (date_Format(entryDate, '%m-%d') = '06-19' and daysHeld = 3)
or (date_Format(entryDate, '%m-%d') = '06-29' and daysHeld = 5)
or (date_Format(entryDate, '%m-%d') = '07-25' and daysHeld = 9)
or (date_Format(entryDate, '%m-%d') = '08-11' and daysHeld = 3)
or (date_Format(entryDate, '%m-%d') = '08-29' and daysHeld = 8)
or (date_Format(entryDate, '%m-%d') = '09-17' and daysHeld = 4)
or (date_Format(entryDate, '%m-%d') = '09-29' and daysHeld = 3)
or (date_Format(entryDate, '%m-%d') = '10-08' and daysHeld = 9)
or (date_Format(entryDate, '%m-%d') = '10-20' and daysHeld = 7)
or (date_Format(entryDate, '%m-%d') = '11-04' and daysHeld = 6)
or (date_Format(entryDate, '%m-%d') = '11-12' and daysHeld = 3)
or (date_Format(entryDate, '%m-%d') = '11-21' and daysHeld = 5)
or (date_Format(entryDate, '%m-%d') = '12-01' and daysHeld = 5)
or (date_Format(entryDate, '%m-%d') = '12-11' and daysHeld = 5)

This next query combines all of my tables to come up with the full algo for the year.

INSERT INTO brute_force_all (name, entryDate, daysHeld, maxdd, avgdd, maxp, avgp, PDRatio, wins, total, winrate)
SELECT 'ES' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_es
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')
#ORDER BY
 #   entrydate asc, winrate desc, daysheld

union all

SELECT 'ES_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_es_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all

SELECT 'NQ' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_nq
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'NQ_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_nq_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'GC' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_gc
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'GC_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_gc_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'CL' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_cl
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'CL_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_cl_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all



SELECT 'YM' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_ym
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'YM_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_ym_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'RT' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_rt
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT 'RT_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_rt_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT '6B' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_6b
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT '6B_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_6b_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT '6E' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_6e
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all


SELECT '6E_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_6e_s
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')


ORDER BY
    entrydate asc, winrate desc, daysheld;

delete from brute_force_all;

Updated the column names of Brute_force_all for easier querying.

INSERT INTO brute_force_all (name, entryDate, daysHeld, maxdd, avgdd, maxp, avgp, PDRatio, wins, total, winrate)
SELECT 'RT' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / entryPrice) * 100),
    AVG((maxDrawdown / entryPrice) * 100),
    MAX((profit / entryPrice) * 100),
    AVG((profit / entryPrice) * 100),
    AVG((profit / entryPrice) * 100) / AVG((maxDrawdown / entryPrice) * 100),
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END),
    COUNT(*),
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2)))
FROM brute_force_rt
WHERE daysHeld > 0 AND daysHeld < 60
GROUP BY daysHeld, DATE_FORMAT(entryDate, '%m-%d');


INSERT INTO brute_force_etf (name, entryDate, daysHeld, maxdd, avgdd, maxp, avgp, PDRatio, wins, total, winrate)
SELECT 'SPY' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_spy
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'QQQ' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_qqq
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')
union all

SELECT 'DIA' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_dia
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'USO' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_uso
WHERE
    date_Format(entryDate, '%m-%d') > '00-00' and date_format(entryDate, '%m-%d') < '12-31'
    and daysHeld > 0 and daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')
;

After creating a new brute_force_etf table that combines my ETFs (QQQ, SPY, DIA, USO), the following query gives us the best direct long trades across the 4.

Select name, entrydate, daysheld, maxdd, avgdd, avgp, wins, total, winrate from brute_force_etf where (winrate > 0.75 and avgp > 1)
and date_format(entrydate, '%m-%d') > '09-00' and total > 15 and daysheld < 30 order by date_Format(entrydate, '%m-%d');

Master query for brute_force_etf now includes the entire above table plus:
FXE
FXE_S
FXB
FXB_S
SPY_S
QQQ_S
DIA_S
USO_S
GLD
GLD_S
IWM
IWM_S

INSERT INTO brute_force_etf (name, entryDate, daysHeld, maxdd, avgdd, maxp, avgp, PDRatio, wins, total, winrate)
SELECT 'FXB' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_fxb
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'FXE' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_fxe
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'FXB_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_fxb_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'FXE_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_fxe_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')


union all
SELECT 'SPY_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_spy_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'QQQ_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_qqq_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'DIA_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_dia_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'USO_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_uso_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'GLD' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_gld
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'GLD_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_gld_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'IWM' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_iwm
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

union all
SELECT 'IWM_S' AS name,
    entryDate,
    daysHeld,
    MAX((maxDrawdown / Abs(entryPrice)) * 100) AS maxdd,
    AVG((maxDrawdown / Abs(entryPrice)) * 100) AS avgdd,
    MAX((profit / Abs(entryPrice)) * 100) AS maxp,
    AVG((profit / Abs(entryPrice)) * 100) AS avgp,
    AVG((profit / Abs(entryPrice)) * 100) / AVG((maxDrawdown / Abs(entryPrice)) * 100) AS PDRatio,
    SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) AS wins,
    COUNT(*) AS total,
    (SUM(CASE WHEN Profit > 0 THEN 1 ELSE 0 END) / CAST(COUNT(*) AS DECIMAL(10, 2))) AS winrate
FROM
    brute_force_iwm_s
WHERE
    daysHeld <= 60
GROUP BY
    daysHeld, date_Format(entryDate, '%m-%d')

Findings

I’m learning a lot of new things by doing this.

First, I realized just now that Crude Oil futures, using a stop loss of 3% for example, are extremely volatile for an account balance if it’s a static stop. At $100 per barrel, 3% is $3 or $300 of a drawdown, but at $20 a barrel, 3% is just $60. As a result, I need to tailor my stops more dynamically so that we are not at extraordinary risk, especially when a high value CL market has a 4-5% drawdown that can wipe the gains made when it was at a cheaper price.

To do this, I am going to try a tier system using a formula like 9 – CL / 20. This way, if it’s $60, we get a stop of 6%, but at 100, we get a stop of just 3%. I will floor it at 3% and cap it at 8% as well.

When comparing the raw trade table with the aggregation queries, also keep in mind that there is a difference between the way max profit is being calculated and displayed.

In the individual trades, max profit is a data point that represents the max potential profit on that trade. We will almost never achieve this because we are holding positions until EOD, rather than trying to maximize profit taking. The max profit percentage is also relying on this data point.

On the other hand, the aggregation query that is used to locate the best sets of trades in a single table is doing something else. It is calculating the max of the final profit against the entry price, from the set of data. This max profit represents the most points we would actually get across that period of years on that trade. The percentage of this is also displayed.

Leave a Reply

Your email address will not be published. Required fields are marked *