Search code examples
google-sheetsgoogle-sheets-formulastock

Trying a formula for a new stock trading tracking spreadsheet

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.

The hard part now is to collect this information from this sheet into another to create some sort of portfolio, combining multiple buys and sells from the same ticker, also populating some other information such as which broker I used, which field of work is the stock from, etc. And the most important, how many shares I have and how much it is worth. Anyway, I am trying to use a combination of UNIQUE, FILTER and SUMIFS but for some reason I get a parse error.

Could someone assist on where the error is? Or better, am I using the right formula and thinking? Maybe this is not the way?

Please see the screenshot. enter image description here

Thanks!

I tried this formula:

=UNIQUE(filter('Movimentações'!F2:F5),sumifs('Movimentações'!H2:H5,['Movimentações'!F2:F5],['Movimentações'!F2:F5]>0))

It doesn't work.

Solution

  • Using this formula in any cell of a different sheet will get you the data filtered the way you need:

    =UNIQUE(QUERY('Movimentações'!A:L; "Select C,E,F,SUM(H),AVG(I),(SUM(H)*AVG(I)) group by C,E,F OFFSET 1 LABEL SUM(H)'Quantidade',AVG(I)'Preco Medio Pago',(SUM(H)*AVG(I))'Investimento Total'"))

    Like this based on the sample data:

    Example

    This is assuming that:

    • Quantidade: is the sum of all the values corresponding to the specific group in column H.
    • Preco Medio Pago: is the average of all the Preco values for that group in column I.
    • Investimento Total: is the product of Quantidade and Preco Medio Pago.

    Feel free to provide more clarity if my assumption is incorrect, a sample of the desired output (even if manually entered) would be helpful to better understand the end goal, I'll gladly update my answer if needed.

    Update:

    I created new formulas to match your format and desired output. These have been added to the file but I created a second solution where you can save one Query by changing the format:

    =QUERY(QUERY({QUERY('Movimentações'!A2:J;"Select C,D,E,F,(I*-1),(I*-1*J) where A matches 'Venda'");QUERY('Movimentações'!A2:J;"Select C,D,E,F,I,(I*J) where A matches 'Compra'")};"Select Col1,Col2,Col3,Col4,sum(Col5),sum(Col6)/sum(Col5) where Col1 is not null group by Col1,Col2,Col3,Col4 label sum(Col5) '',sum(Col6)/sum(Col5) ''";0);"Select Col1,Col2,Col3,Col4,Col5,Col6,Col5*Col6 where Col5 >0 and Col2 != 'Renda Fixa' label Col1'Corretora', Col2 'Tipo', Col3'Setor',Col4'Ticker', Col5'Quantidade', Col6 'Preço Médio Pago', Col5*Col6 'Investimento Total'")

    new solution

    =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 Col3 'Corretora', Col4 'Tipo', Col5 'Setor', Col2 'Validade', datediff(Col7,Col2)/365 'Anos', Col8 'Taxa a.a', Col9*Col10 'Investimento Total', datediff(now(),Col2) 'Dias em Carteira' format datediff(Col7,Col2)/365 '0'")

    Solution

    Note that:

    The Spreadsheet's locale is set to "Brazil" which is requiring the formulas to use ; instead of , as parameter separators in the formulas.

    For example:

    =IF(Condition;TRUE;FALSE)

    Instead of:

    =IF(Condition,TRUE,FALSE)