Stuck on a Query for a stock trading spreadsheet

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.

enter image description here

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)

Apr 1, 2023 in Others by Kithuzzz
• 38,010 points
197 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:

enter image description here

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

answered Apr 1, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
0 answers

Write a DAX query to obtain the bottom 5 customers based on the order price

Jan 22, 2020 in Others by anonymous
• 170 points
313 views
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

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
324 views
0 votes
1 answer

Change the permission for a file created by me in linux

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

answered Mar 8, 2019 in Others by Nabarupa
758 views
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

answered Oct 11, 2022 in Others by narikkadan
• 63,420 points
422 views
0 votes
1 answer

Currency conversion:number to words excel

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

answered Oct 28, 2022 in Others by narikkadan
• 63,420 points
328 views
+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

answered Dec 24, 2022 in Others by narikkadan
• 63,420 points
627 views
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

answered Jan 9, 2023 in Others by narikkadan
• 63,420 points
189 views
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

answered Oct 31, 2022 in Others by narikkadan
• 63,420 points
1,146 views
0 votes
1 answer

A/B Testing for builds in Google Play

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

answered Feb 18, 2022 in Others by narikkadan
• 63,420 points
384 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP