Need help in Django calculated field/queryset

[Jay Prajapati]

Hello Everyone,

I need a help from you. I'm learning Django and working on my own project but got stuck on a point. Your inputs will be highly appreciated.

Little background: I'm creating a web app like investment portfolio. I have created a model based form to input the stock transaction i.e. buy and sell.

class eq_txn(models.Model):
user = models.ForeignKey(User, on_delete=models.CASCADE,)
investor_name = models.CharField(max_length=50)
deal_type = models.CharField(max_length=50)
deal_date = models.DateField()
scrip_name = models.CharField(max_length=50, blank=True)
quantity = models.FloatField(default=0)
price = models.FloatField(default=0)
isin = models.CharField(max_length=50, blank=True)
folio = models.CharField(max_length=50)

def deal_value(self):
return self.quantity * self.price

I have created a calculated field to derived deal value. Now I'm trying to create Cost of Investment and Weighted Average Price (WAP) but no luck yet. Please help with this or any alternate workaround for same either by model or view. Below is the logic that I want to plot (also i have created the same in excel sheet):

Cost of Investment: This will be a cumulative running sum value where deal_type is equal to Buy needs to be add in accumulated running total value with similar calculation of deal_value and where deal_type is equal to Sell needs to be calulated as (WAP)*(quantity) then deduct from cumulative running total value.

WAP: This will be also cumulative running sum value as calulated when deal_type is equal to Buy then it should be Cost of Investment devided by Cumulative Quantity (May be additional field which can be derived by adding Buy quantity and sell quantity should be deducted in running total), When deal_value is Sell then it should use previous transaction's calculated WAP.

Here are the codes i have tried through queryset to derived by not worked exactly I wanted.

all_txn =eq_txn.objects.annotate(cumqty=Window(Sum(Case(When(deal_type='Buy', then='quantity'),
When(deal_type='Sell', then=F('quantity')*-1))), partition_by=[F('scheme_name'),], order_by=F('deal_date').asc()),
cumamt=Window(Sum(Case(When(deal_type='Buy', then=F('price')*F('quantity')))), partition_by=[F('scheme_name'),], order_by=F('deal_date').asc()),
)\
.all().order_by('deal_date').filter(user=request.user)
test = all_txn.annotate(wap=Case(When(deal_type='Buy', then=F('cumamt')/F('cumqty'))),
cumamtsale=Case(When(deal_type='Sell', then=(F('wap')*F('quantity')*-1)), default=Value('0')),
amt=F('cumamt')+F('cumamtsale')).values().filter(user=request.user)

Thank you !

[Jay Prajapati]

Hello everyone,

Still stucked on this point....Has anyone tried it?