First In First Out remaining quantity calculation?

My data looks like this

Date	Transaction	Quantity	Security
01/01/2018	Buy	100000	Apple
01/02/2018	Buy	200000	Apple
01/05/2018	Sell	50000	Apple
05/02/2018	Buy	200000	Banana
05/03/2018	Buy	200000	Banana
13/03/2018	Sell	180000	Banana
17/03/2018	Sell	50000	Banana

and after applying the first in first out method, I would like to figure out the remaining quantities, and thus the data should look something like this

Date	Transaction	Quantity	Security	Remaining
01/01/2018	Buy	100000	Apple	50000
01/02/2018	Buy	200000	Apple	200000
01/05/2018	Sell	50000	Apple
05/02/2018	Buy	200000	Banana	0
05/03/2018	Buy	200000	Banana	170000
13/03/2018	Sell	180000	Banana
17/03/2018	Sell	50000	Banana

Appreciate your assistance in advance.

Workflow

Accepted answers

DavidP

Ouch! @AkisM - that's a lot of rows!

There are 2 things I can think of:

1: Remove all unnecessary fields both before and after to Generate Rows and also in the join tool. You can see in the updated version attached where I've removed unnecessary fields. Also, I changed the logic in the filter tool to use Record ID instead of Date, so that Date can be removed as well. For the purpose of calculating the value for [Count] in the Summarize tool, everything that is not shown in this flow should be removed. All this might only moderately reduce the size of the dataset, but it should have some improvement.

2. The biggest impact is due to the number of rows generated. The only way to reduce this is to set a minimum Buy and Sell quantity. Currently the Generate Rows reduces each Buy and Sell event to rows with Quantity of 1. By having a minimum sell quantity of 10 for instance, you'll get a factor of 10 improvement in each Generate rows tool which should give you at least a 20% improvement. The changes required to implement this is to change the Loop expression in each Generate Rows tool and in the final formula tool where you have to multiply [Count] with the minimum buy/sell quantity as shown in example 2 attached.

Fifo for remaining quantity - buy sell minimum 10.yxmd

Fifo for remaining quantity trimmed down.yxmd

All comments

DavidP

Hi @AkisM

This is a very similar problem to the below post. Please look at the 2 solutions on page 3

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/FIFO-Calculation-with-multiple-products/td-p/528612/page/3

Let me know if you have any questions.

AkisM

Hi @DavidP , thanks for your post. I went through all pages of the thread you mentioned before posting. In fact it was your solution that I downloaded and wanted to try first since it didn't use an iterative macro (which is a bit outside my knowledge at the moment).

The issue is that that thread calculates something else (price according to FIFO). I'm not looking for price, I'm looking for remaining quantity. I downloaded your proposed solution that wasn't using an iterative macro from that post, but unless I'm missing something it wasn't giving me what I needed (remaining quantities after applying FIFO), it was giving price.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703