0 votes

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.

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)

Apr 1, 2023 in Others 261 views

## 1 answer to this question.

0 votes

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.

answered Apr 1, 2023 by
• 63,720 points

0 votes
0 answers

0 votes
1 answer

## I want workshop recordings because missed it due to some work. Registered for "Edureka CIoud Workshop - Build a Smarter ChatBot with AWS" conducted on 20 and 21st June

Hey, @Tanush  If you have already registered for ...READ MORE

0 votes
1 answer

## How to query a matrix for multiple values and receive value

Try: =IFERROR(INDEX(SORT(SORT(FILTER(MATRIX, (LENGTHS>length)*(WEIGHTS>weight)),1,1),2,1),1,3),1) , where MATRIX, LENGTHS, and WEIGHTS ...READ MORE

0 votes
1 answer

## Change the permission for a file created by me in linux

If you are the system administrator or ...READ MORE

0 votes
1 answer

## How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

0 votes
1 answer

## Currency conversion:number to words excel

Try looking for javascript solutions to use ...READ MORE

+1 vote
1 answer

## Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

0 votes
1 answer

## Filter/Extract a text from excel sheet having similar values

ISNUMBER(SEARCH("Maria,",SUBSTITUTE(A1:A4,"]",",")))  shows TRUE if Maria is found and false if it would ...READ MORE

0 votes
1 answer

## Formula for inserting a thumbnail picture into excel cell, based on another cell's value

Here is a really excellent tutorial on ...READ MORE

0 votes
1 answer

## A/B Testing for builds in Google Play

there is a staged roll out feature ...READ MORE