英文:
Stuck on a Query for a stock trading spreadsheet
问题
我正在尝试创建一个简单但有效的电子表格来跟踪股票交易，但我遇到了一些障碍。我创建了一个名为'movimentacoes'的表格，基本上是用来输入买入和卖出（compra e venda）的数据，使用葡萄牙语。通过这个表格，我想追踪我做过的每笔交易，包括我支付了多少钱，日期，股票代码等等。这部分很容易。在这里得到了一些帮助（来自一个叫Bryan的了不起的人），我已经能够完成大部分查询，但现在我在需要的最后一个查询上卡住了。
我需要计算'Movimentacoes'表格中所有与'Vendas'表格中查询的'卖出'匹配的'买入'（数量乘以购买价格）的平均值。我已经创建了查询的开头和结尾部分，但在这个带有两个条件的平均值上卡住了。我附上了一个截图以及电子表格的链接，以便帮助理解案例的人更好地理解。
电子表格链接：https://docs.google.com/spreadsheets/d/1_qGSWwN5DmKCh8E5hwNFT6xKqpHmQEHTVkpaFvXD_J0/edit?usp=sharing
到目前为止使用的代码：
第一部分查询：
=QUERY('Movimentações'!A:N, "select F, C, D, E WHERE A is not null AND A = 'Venda' AND D != 'Renda Fixa'", 0)
第二部分查询（中断了，不知道如何使用条件进行平均值计算）：
=QUERY('Movimentações'!A:N, "SELECT (I-1*J) WHERE A = 'Compra'", 0)
第三部分查询：
=QUERY('Movimentações'!A:M, "select M WHERE A='Venda' AND D != 'Renda Fixa'", 0)
提前感谢大家的帮助！
英文:
I am currently trying to create a simple but effective spreadsheet to track stock trading, but I am coming across some barriers. I created a sheet called 'movimentacoes' to basically input in and out (compra e venda), in Portuguese. With that, I want to track every transaction I made, how much I paid, date, ticker, etc etc. That is the easy part. Via some help here already (from an amazing guy named Bryan), I was able to do most of these queries, but now I am partially stuck on the last query I need.
I need to get an average of all 'buy' instances of a ticker (quantity X price paid) from ‘Movimentacoes’ that matches the queried list of 'sold' ones in tab ‘Vendas’. I created the beginning of the query and the last part, being stuck only on this average with 2 conditionals. I am attaching an screenshot below as well as a link to the spreadsheet itself to make it easier for whoever is able to help understand better the case.
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 dont 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)
Thank you all in advance!
答案1
得分: 1
我相信你的目标是这样的：
=QUERY(QUERY({QUERY('Movimentações'!A2:J,"选择 F,C,D,E,(I*-1),(I*-1*J),(I*0),(J*0) where A 匹配 '销售' label (I*-1) ''', (I*-1*J) ''",0);QUERY('Movimentações'!A2:J,"选择 F,C,D,E,(I*0),(J*0),I,(I*J) where A 匹配 '购买'",0)}, "选择 Col1, Col2, Col3, Col4, sum(Col5),sum(Col6),sum(Col7),sum(Col8),sum(Col5)+sum(Col7) where Col1 不是空的 按 Col1, Col2, Col3, Col4 分组",0), "选择 Col1, Col2, Col3, Col4, Col8, (Col6*-1) where Col1 不是空的 and Col5 < 0 label (Col6*-1) ''",0)
结果:
你只需要在旁边添加已经拥有的 Total 列即可。
英文:
I believe your goal goes like 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论