FIFO Close Out

Question

I am trying to make use of Alteryx to calculate the Profit and Loss based on FIFO close out methodology.

below are my input files and expected output.

Input 1

This file contains new transactions.

- no. of contracts - -ve means sell, +ve means buy

Input 2

This file contains existing positions.

- no. of contracts - -ve means sell, +ve means buy

Output

- FIFO

First In First Out, by trade date (oldest buys vs oldest sells) and by trade price (lowest price buys vs lowest price sells)

Therefore, only if the new transactions results in a close out of positions of the existing positions based on FIFO, will it then appear in the output file

- i should only see the ones that are relevant as my output file is only to tell me what positions have been closed out

Input 2.xlsx

Input 1.xlsx

Output.xlsx

Raj · Answer

@yeojy  please find attached mark done if solved.

New Workflow5.yxmd

yeojy · Answer

have provided more info in my original post.

essentially input 2 is the existing positions I have. While Input 1 is the new transactions that I did.

i can be doing different contracts and into different accounts, but ultimately whatever new transactions i did, i am trying to see whether that results in a closed out of any of my existing positions.

if it does, then it should appear in my output file.

I did not perform any new transactions in contract B therefore that doesnt close out my existing position of contract B so shouldnt appear in my output file

i performed 2 new transactions on 1 Aug (as per input 1) however my existing position only has 6 contracts that can be closed out. The choosing criteria to determine which of the 2 new transactions in input 1 is based on FIFO, and if there is a same trade date then the lower price will be taken.

because of FIFO, the new transactions of 6 contract will close out the existing positions of 6 contracts also. As such within the existing positions (input 2), FIFO also applies. Therefore the first row of 1 contract + the 2nd row of 4 contracts + only 1 contract from the 3rd row is used to fulfil the close out of the new transactions of 6 contracts

hope this explains!

Raj · Answer

please find attached
hope this solves your requirement.

Mark done if solved.

New Workflow7.yxmd