Stuck on a Query for a stock trading spreadsheet

Question

I'm trying to make a straightforward but useful spreadsheet to track stock trading, but I'm running across some obstacles. I made a sheet named "movimentacoes" where I could enter and exit information in Portuguese (compra e venda). I want to keep track of every transaction I make, the money I spent, the date, the ticker, and so forth. The simple thing is that. I was able to complete the majority of these inquiries with some assistance from this site (from a wonderful man named Bryan), but I am currently only partially through the last query I require.

In order to match the searched list of "sold" instances in tab "Vendas," I need to calculate the average of all "purchase" instances of a ticker (quantity X price paid) from "Movimentacoes." Being limited to this average with two conditionals, I developed the query's first and last parts. To aid whoever can assist better grasp the situation, I have attached a screenshot and a link to the spreadsheet itself below.

Spreadsheet link: https://docs.google.com/spreadsheets/d/1_qGSWwN5DmKCh8E5hwNFT6xKqpHmQEHTVkpaFvXD_J0/edit?usp=sharing

Codes used so far:

1st part query:

=QUERY('Movimentações'!A:N,"select F, C, D, E WHERE A is not null AND A = 'Venda' AND D != 'Renda Fixa'",0)

2nd part query (broken and do not know how to do the averages with conditionals)

=QUERY('Movimentações'!A:N, "SELECT (I-1*J) WHERE A = 'Compra'")

3rd part query:

=QUERY('Movimentações'!A:M,"select M WHERE A='Venda' AND D != 'Renda Fixa'",0)

Answer 1

Try this:

=QUERY(QUERY({QUERY('Movimentações'!A2:J,"Select F,C,D,E,(I*-1),(I*-1*J),(I*0),(J*0) where A matches 'Venda' label (I*-1) '', (I*-1*J) ''",0);QUERY('Movimentações'!A2:J,"Select F,C,D,E,(I*0),(J*0),I,(I*J) where A matches 'Compra'",0)},"Select Col1, Col2, Col3, Col4, sum(Col5),sum(Col6),sum(Col7),sum(Col8),sum(Col5)+sum(Col7) where Col1 is not null group by Col1, Col2, Col3, Col4",0),"Select Col1, Col2, Col3, Col4, Col8, (Col6*-1) where Col1 is not null and Col5 < 0 label (Col6*-1) ''",0)

Result:

You will just need to add the Total column that you already have next to it.