Summing categories of financial records per month in a query

Question

My program is working properly but I'm unconfortable with code repetition.

class Movimentacao(models.Model):
    data = models.DateField() # complete datetime field from 2019->2022
    movimentacao = models.CharField(max_length=200) # text field
    valor_da_operacao = models.DecimalField(max_digits=19, decimal_places=2) # decimal field

# get all objects filtered by `movimentacao` field
proventos = Movimentacao.objects.filter(movimentacao__in=('Dividendo', 'Juros Sobre Capital Próprio', 'Rendimento', 'Reembolso')).order_by('data', 'id')

currentDate = proventos[0].data # get date from the first record
lastRecord = proventos.last().id # get ID from the last record

assets = []
stockSum = 0
fiiSum = 0

for m in proventos:    
    if(m.data.month != currentDate.month or lastRecord == m.id):
        dataFormatada = f'{currentDate.strftime("%y")}-{currentDate.strftime("%b")}'
        
        if(lastRecord == m.id):

            # first if-else block (necessary but I'd like to 'remove')
            if(m.movimentacao == 'Dividendo'):
                stockSum += m.valor_da_operacao
        
            if(m.movimentacao == 'Rendimento'):
                fiiSum += m.valor_da_operacao
        
        assets.append([dataFormatada, 'FIIs', fiiSum])
        assets.append([dataFormatada, 'Stocks', stockSum])
        
        stockSum = 0
        fiiSum = 0
        
        currentDate = m.data
    
    # second if-else block (this one I can't remove)
    if(m.movimentacao == 'Dividendo'):
        stockSum += m.valor_da_operacao 
        
    if(m.movimentacao == 'Rendimento'):
        fiiSum += m.valor_da_operacao

As you can see, the code below is being repeteaded:

if(m.movimentacao == 'Dividendo'):
    stockSum += m.valor_da_operacao

if(m.movimentacao == 'Rendimento'):
    fiiSum += m.valor_da_operacao

The goal of this code is to build the assets list formatted accordingly below:

[
  ["19-Nov", "FII", 411.97], ["19-Nov", "Stocks", 0], 
  ["19-Dec", "FII", 368.21], ["19-Dec", "Stocks", 1542.08], 
  ["20-Jan", "FII", 0], ["20-Jan", "Stocks", 401.06], 
]

In this way, I'd like to see if it's possible to "remove" the first if-else statement, keeping the code working properly (this would be my first choice).

The issue is: if I remove the first if-else statement, the variable m.movimentacao from the last object is not added inside stockSum or fiiSum variables - that was the reason I need to insert the first if-else statement. The last object can't reach the last if-else statement to be checked either m.movimentacao is a 'Dividendo' or 'Rendimento'.

If not, what is the best way to avoid code repetition in this case (function use)?

Answer 1

Mixing Portuguese and English makes this code messy. Please pick one or the other. I recommend coding consistently in English, because Python, its library, and its code ecosystem are in English, so Portuguese will always feel out of place.

Why do you call .filter(movimentacao__in=('Dividendo', 'Juros Sobre Capital Próprio', 'Rendimento', 'Reembolso'), when only "Dividendo" and "Rendimento" matter for building assets? Also, wouldn't you want to do another two assets.append(…) statements at the end of this code?

This date formatting code is clumsy:

dataFormatada = f'{currentDate.strftime("%y")}-{currentDate.strftime("%b")}'

… and it could be better expressed as

dataFormatada = currentDate.strftime("%y-%b")

But really, none of this loop should exist: all of the filtering and summation should be done by the database instead! Transferring so much data from the database to Python for analysis defeats the purpose of the database. It would be more efficient, scalable, and readable to do this in SQL rather than Python.

Here's a query (and a fiddle) for postgresql that gets you the same results:

SELECT DISTINCT
        date_trunc('month', data) AS mes,
        CASE
            WHEN movimentacao = 'Dividendo' THEN 'Stocks'
            ELSE 'FIIs'
        END AS categoria,
        SUM(valor_da_operacao) OVER monthly AS total
    FROM Movimentacao
    WHERE movimentacao IN ('Dividendo', 'Rendimento')
    WINDOW monthly AS (PARTITION BY date_trunc('month', data), movimentacao)
    ORDER BY mes, categoria;

You can use a similar query for sqlite, except that the datetime functions are different:

SELECT DISTINCT
        date(data, 'start of month') AS mes,
        CASE
            WHEN movimentacao = 'Dividendo' THEN 'Stocks'
            ELSE 'FIIs'
        END AS categoria,
        SUM(valor_da_operacao) OVER monthly AS total
    FROM Movimentacao
    WHERE movimentacao IN ('Dividendo', 'Rendimento')
    WINDOW monthly AS (PARTITION BY date(data, 'start of month'), movimentacao)
    ORDER BY mes, categoria;

Well, almost the same results. With your Python code, if there is any month in which there is a Dividendo but no Rendimento, or vice versa, both "FIIs" and "Stocks" get appended to assets anyway — one of them having a 0 value. With some creativity and perhaps some Common Table Expressions, the SQL query can be tweaked to produce such 0 values as well:

WITH MovimentacaoRelevante AS (
    SELECT date(data, 'start of month') AS mes,
           movimentacao,
           valor_da_operacao
        FROM Movimentacao
        WHERE movimentacao IN ('Dividendo', 'Rendimento')
), MovimentacaoRelevanteComZeros AS (
    SELECT * FROM MovimentacaoRelevante
    UNION ALL
    SELECT DISTINCT mes, 'Dividendo', 0 AS total FROM MovimentacaoRelevante
    UNION ALL
    SELECT DISTINCT mes, 'Rendimento', 0 AS total FROM MovimentacaoRelevante
) SELECT DISTINCT                                    
        mes,
        CASE
            WHEN movimentacao = 'Dividendo' THEN 'Stocks'
            ELSE 'FIIs'
        END AS categoria,
        SUM(valor_da_operacao) OVER monthly AS total
    FROM MovimentacaoRelevanteComZeros
    WINDOW monthly AS (PARTITION BY mes, movimentacao)
    ORDER BY mes, categoria;