Percentages
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Percentages
I have a linked ss on some of my trades, both the ss and Db are to large to attach, but I can provide the initial queries. What I am looking for is the % pos and neg with the avg. pos and neg how would this be accomplished from what I have started a union or something else?
Pos
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS CountOfSymbol, Sum(QuTrades.[-3958#96]) AS [SumOf-3958#96]
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.[-3958#96]))>0));
Neg
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS CountOfSymbol, Sum(QuTrades.[-3958#96]) AS [SumOf-3958#96]
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.[-3958#96]))<0));
Thanks
ETA: Substitute P/L for -3958@96
Pos
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS CountOfSymbol, Sum(QuTrades.[-3958#96]) AS [SumOf-3958#96]
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.[-3958#96]))>0));
Neg
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS CountOfSymbol, Sum(QuTrades.[-3958#96]) AS [SumOf-3958#96]
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.[-3958#96]))<0));
Thanks
ETA: Substitute P/L for -3958@96
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
Pos (P?L)/Total Neg (P?L)/Total Those two for sure then Avg. Pos P/L Avg. Neg P/L. Helpful to understand?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
The summation of all the Pos and Neg. would be the total.
Symbol CountOfSymbol SumOfP/L
ZBM21 37 $51.00
ZBU21 170 $81.20
So the avg. ZBM21 would be 51/37 Etc. All the trades are in QuTrades a count of the symbol The Neg is similar.
Symbol CountOfSymbol SumOfP/L
ZBM21 37 $51.00
ZBU21 170 $81.20
So the avg. ZBM21 would be 51/37 Etc. All the trades are in QuTrades a count of the symbol The Neg is similar.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
I never understand this financial stuff, perhaps someone else can help.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
It really has nothing to do with financials, just Pos. and Neg. Num of Pos./Total trades = Percent Pos. Sum of symbol Profit/ Num. Pos trades = Avg. Pos.
Same for Neg.
Same for Neg.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
See if the attached helps. I realize that QuTrades is a query in your database, but in this sample database it is a table since I don't have the underlying data.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
It gives me an Unrecognized database format.
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
SELECT TradeHistory_20080920110258.Symbol, TradeHistory_20080920110258.Quantity, TradeHistory_20080920110258.Price, TradeHistory_20080920110258.ActionNameUS, TradeHistory_20080920110258.TradeDate, TradeHistory_20080920110258.SettledDate, TradeHistory_20080920110258.Amount, TradeHistory_20080920110258.Commission, TradeHistory_20080920110258.Fees, TradeHistory_20080920110258.CUSIP, TradeHistory_20080920110258.Description, TradeHistory_20080920110258.ActionId, TradeHistory_20080920110258.TradeNumber, TradeHistory_20080920110258.RecordType, TradeHistory_20080920110258.Profit
FROM TradeHistory_20080920110258
WHERE (((TradeHistory_20080920110258.Symbol) Is Not Null) AND ((TradeHistory_20080920110258.Profit) Is Not Null));
This is QuTrades
SELECT QuTrades.Symbol, Sum(QuTrades.Profit) AS [Sum], Count(QuTrades.Symbol) AS Num
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.Profit))>0));
This is QuPos
SELECT QuTrades.Symbol, Sum(QuTrades.Profit) AS [Sum], Count(QuTrades.Symbol) AS Num
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.Profit))<0));
This QuNeg
Attached is the main sheet with only a few rows, to make it small enough.
FROM TradeHistory_20080920110258
WHERE (((TradeHistory_20080920110258.Symbol) Is Not Null) AND ((TradeHistory_20080920110258.Profit) Is Not Null));
This is QuTrades
SELECT QuTrades.Symbol, Sum(QuTrades.Profit) AS [Sum], Count(QuTrades.Symbol) AS Num
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.Profit))>0));
This is QuPos
SELECT QuTrades.Symbol, Sum(QuTrades.Profit) AS [Sum], Count(QuTrades.Symbol) AS Num
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.Profit))<0));
This QuNeg
Attached is the main sheet with only a few rows, to make it small enough.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
These are the queries I added in the database that you cannot open:
QuGroup:
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS CountOfSymbol, Sum(QuTrades.[-3958#96]) AS [SumOf-3958#96], Avg(QuTrades.[-3958#96]) AS [AvgOf-3958#96]
FROM QuTrades
GROUP BY QuTrades.Symbol;
and
QuCount:
SELECT -Sum([SumOf-3958#96]>0) AS CountOfPos, -Sum([SumOf-3958#96]<0) AS CountOfNeg, Count([Symbol]) AS Total, [CountOfPos]/[Total] AS PercOfPos, [CountOfNeg]/[Total] AS PercOfNeg
FROM QuGroup;
QuGroup:
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS CountOfSymbol, Sum(QuTrades.[-3958#96]) AS [SumOf-3958#96], Avg(QuTrades.[-3958#96]) AS [AvgOf-3958#96]
FROM QuTrades
GROUP BY QuTrades.Symbol;
and
QuCount:
SELECT -Sum([SumOf-3958#96]>0) AS CountOfPos, -Sum([SumOf-3958#96]<0) AS CountOfNeg, Count([Symbol]) AS Total, [CountOfPos]/[Total] AS PercOfPos, [CountOfNeg]/[Total] AS PercOfNeg
FROM QuGroup;
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
That all works with one exception, the % are overall, a good metric, but is there a way to code it to a symbol. I tried making a group and got an error : Symbol is not part of an aggregate function.
Also would this require two queries or just one?
Also would this require two queries or just one?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
Try this; you won't need the other queries that I posted:
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS TotalCount, Sum(QuTrades.[-3958#96]) AS TotalSum, -Sum([-3958#96]>0) AS PosCount, -Sum([-3958#96]<0) AS NegCount, [PosCount]/[TotalCount] AS PosPerc, [NegCount]/[TotalCount] AS NegPerc, Avg(QuTrades.[-3958#96]) AS TotalAvg, -Sum([-3958#96]*([-3958#96]>0)) AS PosSum, IIf([PosCount]=0,Null,[PosSum]/[PosCount]) AS PosAvg, -Sum([-3958#96]*([-3958#96]<0)) AS NegSum, IIf([NegCount]=0,Null,[NegSum]/[NegCount]) AS NegAvg
FROM QuTrades
GROUP BY QuTrades.Symbol;
SELECT QuTrades.Symbol, Count(QuTrades.Symbol) AS TotalCount, Sum(QuTrades.[-3958#96]) AS TotalSum, -Sum([-3958#96]>0) AS PosCount, -Sum([-3958#96]<0) AS NegCount, [PosCount]/[TotalCount] AS PosPerc, [NegCount]/[TotalCount] AS NegPerc, Avg(QuTrades.[-3958#96]) AS TotalAvg, -Sum([-3958#96]*([-3958#96]>0)) AS PosSum, IIf([PosCount]=0,Null,[PosSum]/[PosCount]) AS PosAvg, -Sum([-3958#96]*([-3958#96]<0)) AS NegSum, IIf([NegCount]=0,Null,[NegSum]/[NegCount]) AS NegAvg
FROM QuTrades
GROUP BY QuTrades.Symbol;
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
That works great, thanks Hans
One question on the
SELECT -Sum([SumOf-3958#96]>0) AS CountOfPos, -Sum([SumOf-3958#96]<0) AS CountOfNeg, Count([Symbol]) AS Total, [CountOfPos]/[Total] AS PercOfPos, [CountOfNeg]/[Total] AS PercOfNeg
The results are 2 Pos 3 Neg 5 total, however there have been a total of 315 not 5. Can you explain?
One question on the
SELECT -Sum([SumOf-3958#96]>0) AS CountOfPos, -Sum([SumOf-3958#96]<0) AS CountOfNeg, Count([Symbol]) AS Total, [CountOfPos]/[Total] AS PercOfPos, [CountOfNeg]/[Total] AS PercOfNeg
The results are 2 Pos 3 Neg 5 total, however there have been a total of 315 not 5. Can you explain?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
That was one of the previous queries. It should not be needed anymore.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
I understand that it isn't needed, but could you explain the difference.
I do have a problem, I shut down the Db and restarted and now the last query doesn't run giving me a data type mismatch in criteria expression. Any thoughts to resurrect the query?
I do have a problem, I shut down the Db and restarted and now the last query doesn't run giving me a data type mismatch in criteria expression. Any thoughts to resurrect the query?
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
The query that you mention counts symbols, not records.
Without additional information/seeing the database, it is impossible for me to know why the new query doesn't work.
Without additional information/seeing the database, it is impossible for me to know why the new query doesn't work.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
I attached a short version of the ss here:
The ss holds all the fields. Besides it ran earlier.bknight wrote: ↑22 Mar 2022, 00:39SELECT TradeHistory_20080920110258.Symbol, TradeHistory_20080920110258.Quantity, TradeHistory_20080920110258.Price, TradeHistory_20080920110258.ActionNameUS, TradeHistory_20080920110258.TradeDate, TradeHistory_20080920110258.SettledDate, TradeHistory_20080920110258.Amount, TradeHistory_20080920110258.Commission, TradeHistory_20080920110258.Fees, TradeHistory_20080920110258.CUSIP, TradeHistory_20080920110258.Description, TradeHistory_20080920110258.ActionId, TradeHistory_20080920110258.TradeNumber, TradeHistory_20080920110258.RecordType, TradeHistory_20080920110258.Profit
FROM TradeHistory_20080920110258
WHERE (((TradeHistory_20080920110258.Symbol) Is Not Null) AND ((TradeHistory_20080920110258.Profit) Is Not Null));
This is QuTrades
SELECT QuTrades.Symbol, Sum(QuTrades.Profit) AS [Sum], Count(QuTrades.Symbol) AS Num
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.Profit))>0));
This is QuPos
SELECT QuTrades.Symbol, Sum(QuTrades.Profit) AS [Sum], Count(QuTrades.Symbol) AS Num
FROM QuTrades
GROUP BY QuTrades.Symbol
HAVING (((Sum(QuTrades.Profit))<0));
This QuNeg
Attached is the main sheet with only a few rows, to make it small enough.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Percentages
OK, I linked your spreadsheet into my sample database and changed -3958#96 to Profit in the query.
QuTrades returns 1 symbol with 3 records.
This is the result of the new query:
It works after closing and reopening the database. I'm still none the wiser as to why it fails for you.
QuTrades returns 1 symbol with 3 records.
This is the result of the new query:
It works after closing and reopening the database. I'm still none the wiser as to why it fails for you.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Percentages
That would be correct for the small ss I linked. What types of mismatches give that error message? I can play around with either the query or SQL.