Need help in Django calculated field/queryset
Hello everyone, Still stucked on this pointHas anyone tried it? -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f1f56856-a7cd-469a-896b-2427bd53b2ff%40googlegroups.com.
Need help in Django calculated field/queryset
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 !
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/ca50ba51-ff41-4150-b7a3-b6edbeea41b2%40googlegroups.com.
equity cases.xlsx
Description: MS-Excel 2007 spreadsheet
Need help in Django calculated field/queryset
Hello All,
Hope you are doing well!
I need a help. 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:
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 !
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/e32e03b3-e477-4377-8171-351c6ed59fa7%40googlegroups.com.
