英文:
Fixing a query formula in Gsheets
问题
我目前正尝试创建一个简单但有效的电子表格来跟踪股票交易，但我遇到了一些障碍。我创建了一个名为“movimentacoes”的工作表，用来输入买入和卖出（compra e venda）的交易，用葡萄牙语。通过这个工作表，我想跟踪我进行的每笔交易，包括我支付了多少钱，日期，股票代码等等。在我的一个选项卡中，我刚刚注意到来自“Renda Fixa Tab”的“Validade”列查询了错误的数值。它给我的是购买股票的日期，而不是它到期的日期。应该从“Movimentacoes Tab”的列G中获取，而不是列B。
英文:
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.
In one of my tabs, I just noticed the column 'Validade' from 'Renda Fixa Tab' is querying a wrong value. It's giving me the date I bought the stock instead of the day it expires. Column B instead of column G from 'Movimentacoes Tab'.
Spreadsheet here: https://docs.google.com/spreadsheets/d/1_qGSWwN5DmKCh8E5hwNFT6xKqpHmQEHTVkpaFvXD_J0/edit#gid=1689387102
答案1
得分: 1
=QUERY(QUERY(FILTER('Movimentações'!A:K,'Movimentações'!G:G),"Select Col3,Col4,Col5,Col7,datediff(Col7,Col2)/365,Col8,Col9*Col10,datediff(now(),Col2) where Col4 matches 'Renda Fixa' label datediff(Col7,Col2)/365 '', Col9*Col10 '', datediff(now(),Col2) '' format datediff(Col7,Col2)/365 '0'"),"Select Col1,Col2,Col3,Col4")
英文:
You can try the following query:
=QUERY(QUERY(FILTER('Movimentações'!A:K,'Movimentações'!G:G),"Select Col3,Col4,Col5,Col7,datediff(Col7,Col2)/365,Col8,Col9*Col10,datediff(now(),Col2) where Col4 matches 'Renda Fixa' label datediff(Col7,Col2)/365 '', Col9*Col10 '', datediff(now(),Col2) '' format datediff(Col7,Col2)/365 '0'"),"Select Col1,Col2,Col3,Col4")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论