How can I state the purchase cost of units held in current portfolio?

[jamieeee]

Hi, 

I would really appreciate your help with this complex problem.

Objective

I am trying to state the purchase cost of the players that are currently in my portfolio based off a purchase order listing. I am having trouble doing this is because the units I currently hold in some players were bought across multiple purchase orders. I am not sure which processes / tools / formulae i can use to achieve this.

The first screenshot below is a subset listing of ALL purchase orders for the players currently in my portfolio. However, as you can see, the quantity purchased doesn't always tie to the units currently in my portfolio (Current Portfolio Q) for each player. This is because some units were sold and then re-purchased, for example. 

I have listed below three examples of why I have been unable to state the current purchase cost of my portfolio. 

Examples:

1) Adama (records #1 and #2) - In my current portfolio, I hold 100 units of Adama. However, I previously owned 100 units of Adama (on 01/09/2019) but then sold those 100 units. I then decided to re-purchase 100 Adama units on 27/05/2020 which now sit in my portfolio. Hence, the purchase cost for my current portfolio should be 310 and NOT 94. The 94 should be ignored.  

2) Bruno Fernandes (records #5 and #6) - I currently hold 111 units of Bruno Fernandes but this was split across 2 purchases. Hence, the total purchase cost should be 658 (440 + 218). 

3) Erling Haaland (records #10-14) -  I have previously purchased and sold multiple units of Haaland. I purchased 50, another 50 and then another 100. I sold 200 and then purchased 15 and another 115 which takes me to my current portfolio holding of 130. Hence, the total purchase price should be 1,108 (987 + 121). The previous cost of purchases should be ignored. 

For completeness, I have a separate listing for the players and units currently in my portfolio. As I am unable to post two screenshots, this listing essentially details each individual player and the current portfolio quantity. i.e. the Player column de-duplicated with the Current Portfolio Q column.

Thank you so much in advance. I will of course give anyone credit for helping me. 

[RobertOdera]

Sure thing, @jamieeee 

You're most welcome!

Also check out the Alteryx Adapt Program 🙂