## Percentages

bknight
5StarLounger
Posts: 863
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

HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Percentages

I don't understand what the end result is supposed to be.
Regards,
Hans

bknight
5StarLounger
Posts: 863
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?

HansV
Administrator
Posts: 75279
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Percentages

What is Total? The number of symbols?
Regards,
Hans

bknight
5StarLounger
Posts: 863
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.

HansV
Administrator
Posts: 75279
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.
Regards,
Hans

bknight
5StarLounger
Posts: 863
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.

HansV
Administrator
Posts: 75279
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.

PosNeg.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

### Re: Percentages

It gives me an Unrecognized database format.

bknight
5StarLounger
Posts: 863
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.
You do not have the required permissions to view the files attached to this post.

HansV
Administrator
Posts: 75279
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;
Regards,
Hans

bknight
5StarLounger
Posts: 863
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?

HansV
Administrator
Posts: 75279
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;
Regards,
Hans

bknight
5StarLounger
Posts: 863
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?

HansV
Administrator
Posts: 75279
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.
Regards,
Hans

bknight
5StarLounger
Posts: 863
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?

HansV
Administrator
Posts: 75279
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.
Regards,
Hans

bknight
5StarLounger
Posts: 863
Joined: 08 Jul 2016, 18:53

### Re: Percentages

I attached a short version of the ss here:
bknight wrote:
22 Mar 2022, 00:39
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.
The ss holds all the fields. Besides it ran earlier.

HansV
Administrator
Posts: 75279
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:

S1237.png

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.
Regards,
Hans

bknight
5StarLounger
Posts: 863
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.