Aggregation for stock market return calculation

Hi, I’m looking for aggregation stages that can calculate return for buy/sell market stocks.

As you can see I bought 2 volumes (one 60, second 40), then I sold 50 of them. In stock market when you sell volume you sell volume that is oldest by date.

Data (from oldest to newest):

[
  {
      "_id": "660025db41facec87eb17730",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-26T13:46:08.000Z",
      "symbol": "CCC",
      "volume": 60,
      "price": 99
  },
  {
      "_id": "660025d941facec87eb1772f",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-29T09:47:21.000Z",
      "symbol": "CCC",
      "volume": 40,
      "price": 94.32
  },
  {
      "_id": "6600242741facec87eb1772b",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2022-01-20T09:09:06.000Z",
      "symbol": "CCC",
      "volume": -50,
      "price": 89.94
  }
]

I expect something like this (that I will see whats return from investing)

{
symbol: 'CCC',
// ( (89.94-99)*50 ),
return: -453 
//optional
volumesLeft: 50
//OR! 
volumesLeft: [{volume: 10, price: 99}, {volume: 40, price: 94,32}])
}

Is that possible in mongo? I completly stuck. I’ve tried $setWindowFields but stuck in middle how to preserve that 10 volume from 60 (60 volume - 50 that I sold) and then when I sell another 50 volume that how to connect that 10 left from 60 volume and 40 with different price…

It is my understanding that in some jurisdictions, the following is not necessarily the case.

In some places, when you sell you may determine from which buys you sell. This is important for taxes purposes as you may want to maximize or minimize the capital gain/loss. From the little I understand this is to determine the cost-basis.

I think I would be too with your schema.

My approach to this and to accommodate all cost-basis options a seller might have, First In First Out, Average Cost , Specific, Highest In First Out, Lowest In First Out, … My sells would be an array of sell volume/price within the buy top documents.

[
  {
      "_id": "660025db41facec87eb17730",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-26T13:46:08.000Z",
      "symbol": "CCC",
      "volume": 60,
      "price": 99 ,
      "sells" : [
         { 
              "date": "2022-01-20T09:09:06.000Z",
              "volume" : 50 ,
              "price" : 89.94 ,
         }
      ]
  },
  {
      "_id": "660025d941facec87eb1772f",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-29T09:47:21.000Z",
      "symbol": "CCC",
      "volume": 40,
      "price": 94.32
  }

The above scenario where the 50 are all sold from the first but. This would represent First In First Out and Highest In First Out. HIFO would maximize your capital loss. But if you want to you could have:

[
  {
      "_id": "660025db41facec87eb17730",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-26T13:46:08.000Z",
      "symbol": "CCC",
      "volume": 60,
      "price": 99 ,
      "sells" : [
         { 
              "date": "2022-01-20T09:09:06.000Z",
              "volume" : 10 ,
              "price" : 89.94 ,
         }
      ]
  },
  {
      "_id": "660025d941facec87eb1772f",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-29T09:47:21.000Z",
      "symbol": "CCC",
      "volume": 40,
      "price": 94.32 ,
      "sells" : [
         { 
              "date": "2022-01-20T09:09:06.000Z",
              "volume" : 40 ,
              "price" : 89.94 ,
         }
      ]
  }

The above representing a Last In First Out or Lowest In First Out cost-basis strategy.

And to represent an Average cost-basis, you would associate the sells to buys as:

[
  {
      "_id": "660025db41facec87eb17730",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-26T13:46:08.000Z",
      "symbol": "CCC",
      "volume": 60,
      "price": 99 ,
      "sells" : [
         { 
              "date": "2022-01-20T09:09:06.000Z",
              "volume" : 30 ,
              "price" : 89.94 ,
         }
      ]
  },
  {
      "_id": "660025d941facec87eb1772f",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2021-11-29T09:47:21.000Z",
      "symbol": "CCC",
      "volume": 40,
      "price": 94.32 ,
      "sells" : [
         { 
              "date": "2022-01-20T09:09:06.000Z",
              "volume" : 20 ,
              "price" : 89.94 ,
         }
      ]
  }

An alternative schema that might be worth investigating is to keep sells as top documents and simply have buy_id back to the volume you want. Such as the following for FIFO

{
      "_id": "6600242741facec87eb1772b",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2022-01-20T09:09:06.000Z",
      "symbol": "CCC",
      "volume": -50,
      "price": 89.94
      "buy_id" : "660025db41facec87eb17730"
  }

or

{
      "_id": "6600242741facec87eb1772b",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2022-01-20T09:09:06.000Z",
      "symbol": "CCC",
      "volume": -10,
      "price": 89.94
      "buy_id" : "660025db41facec87eb17730"
  } ,
{
      "_id": "6600242741facec87eb1772c",
      "walletId": "65f34ff7d2860cc54295e725",
      "date": "2022-01-20T09:09:06.000Z",
      "symbol": "CCC",
      "volume": -40,
      "price": 89.94
      "buy_id" : "660025db41facec87eb17730"
  }

With both schemas, each sell volume is associate with a specific buy volume, you can easily calculate your returns.

1 Like

Thank you for the explenation. I’ll try different way then. :wink:

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.