Percentages

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

Percentages

Post by bknight »

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

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

Re: Percentages

Post by HansV »

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

Post by bknight »

Pos (P?L)/Total Neg (P?L)/Total Those two for sure then Avg. Pos P/L Avg. Neg P/L. Helpful to understand?

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

Re: Percentages

Post by HansV »

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

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

Re: Percentages

Post by bknight »

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.

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

Re: Percentages

Post by HansV »

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

Post by bknight »

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.

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

Re: Percentages

Post by HansV »

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

Post by bknight »

It gives me an Unrecognized database format.

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

Re: Percentages

Post by bknight »

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.

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

Re: Percentages

Post by HansV »

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

Post by bknight »

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?

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

Re: Percentages

Post by HansV »

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

Post by bknight »

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?

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

Re: Percentages

Post by HansV »

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

Post by bknight »

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?

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

Re: Percentages

Post by HansV »

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

Post by bknight »

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.

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

Re: Percentages

Post by HansV »

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

Post by bknight »

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.