Counting/summing/statistics by hour

[bknight]

In one of my trading accounts I have a bunch of trades and I want to run some numbers on an hourly basis, number, winners, losers.

Code: Select all

Hour([Trades].[Tradedate])

should get the hour grouping and a criteria >=9 and <=10 for a criteria should get one of the hours.
My question is it possible to run eight hours at once or separate and then transfer to excel?

[HansV]

You can create a Totals query that groups by the hour, and has

Between 8 And 15

as where-condition for the hour column.

By the way: for a single hour, a condition such as

9

is sufficient: Hour returns a whole number, so >=9 And <=10 simply means 9.

[bknight]

You mean several queries each with a different hour?

[StuartR]

I think that >=9 And <=10 will include both 9 and 10

[bknight]

I think that >=9 And <=10 will include both 9 and 10

You are correct, I was overzealous with the keyboard this morning.

[bknight]

Hmmm

Code: Select all

SELECT Hour([Trades].[Tradedate]) AS Hourly
FROM Trades
GROUP BY Hour([Trades].[Tradedate]), Trades.Symbol
HAVING (((Hour([Trades].[Tradedate]))>=9 And (Hour([Trades].[Tradedate]))<10) AND ((Trades.Symbol)="mes"));

Results in no output, same for just entering 9 in the criteria. Capitalization of mes to MES as in the table still results in no records.

[bknight]

Never mind I added a count and a sum and it is ok.

[bknight]

OK I've got all the queries built how do I combine them into one "summation" query?

[HansV]

Something like

SELECT Hour([Trades].[TradeDate]) AS Hourly, Sum([Trades].[ThisField]) AS SumOfThisField, Count([Trades].[ThatField]) AS CountOfThatField
FROM Trades
WHERE (Hour([Trades].[TradeDate]) Between 8 And 15) AND [Trades].[Symbol]="mes"
GROUP BY Hour([Trades].[TradeDate]);

[bknight]

I was thinking of a union query, and took about an hour to remember union queries are Sql.

Code: Select all

SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quEightOclock trades]
union all
SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quNineOclockTrades]
union all
SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quTenOclockTrades]
union all
SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quElevenOclockTrades]
union all
SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quTwelveOclockTrades]
union all
SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quThirteenOclokTrades]
union all
SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quFourteenOclockTrades]
union all
SELECT Hourly,  CountOfProfit, SumOfProfit
FROM [quFifthteenOclockTrades];

The result was without errors.

[HansV]

That is overkill...

[bknight]

Simplify?

[HansV]

SELECT Hour([Trades].[TradeDate]) AS Hourly, Sum([Trades].[Profit]) AS SumOfProfit, Count([Trades].[Profit]) AS CountOfProfit
FROM Trades
WHERE (Hour([Trades].[TradeDate]) Between 8 And 15) AND [Trades].[Symbol]="mes"
GROUP BY Hour([Trades].[TradeDate]);

[bknight]

Ok, simpler.

[bknight]

I thought of another statistic this morning but so far unsuccessful, I would like to find the same information in a day of week format. I don't know if there would be two queries required or one combining both hourly and DOW.

[HansV]

Do you want day of week as 1, 2, 3, ... or as Sun, Mon, Tue, ... or as Sunday, Monday, Tuesday, ...?

[bknight]

Mon, Tues etc. would be more readable.

[HansV]

For example (I added the number of the weekday to be able to sort correctly):

SELECT Format([Trades].[TradeDate],"w-ddd") As DOW, Hour([Trades].[TradeDate]) AS Hourly, Sum([Trades].[Profit]) AS SumOfProfit, Count([Trades].[Profit]) AS CountOfProfit
FROM Trades
WHERE (Hour([Trades].[TradeDate]) Between 8 And 15) AND [Trades].[Symbol]="mes"
GROUP BY Format([Trades].[TradeDate],"w-ddd"), Hour([Trades].[TradeDate]);

[bknight]

That was nothing like I had tried, but worked, now this is for all the data suppose I just want after #9/30/2023#, when I try to put that in all results disappear.

[HansV]

SELECT Format([Trades].[TradeDate],"w-ddd") As DOW, Hour([Trades].[TradeDate]) AS Hourly, Sum([Trades].[Profit]) AS SumOfProfit, Count([Trades].[Profit]) AS CountOfProfit
FROM Trades
WHERE [Trades].[TradeDate]>#9/30/2023# AND (Hour([Trades].[TradeDate]) Between 8 And 15) AND [Trades].[Symbol]="mes"
GROUP BY Format([Trades].[TradeDate],"w-ddd"), Hour([Trades].[TradeDate]);