Multi Row Formula

[cmohyi]

Hi all,

I know that the Multi Row Forumla tool will (probably) solve my problem but I don't know how to get there. 

I have a list of stocks where some securities will have multiple line items and some might just have 1. There are many fields that differentiate them beyond a Shares column but I narrowed it down for simplicity. I have a column that has the number of shares I need to sell that's constant based off the Security name and I need to continuously subtract from the 2nd column. So for GOOGL, I want to sell a total of 85 shares, so I want to sell all 40 from the first entry, all 30 from the second entry, 15 from the third entry, and none from the last two. 

It's a bit confusing so I'm happy to give further clarification if needed. Thank you greatly for any help in figuring this out!

Security	Shares	Shares to Sell	Remaining Shares to Sell	Shares Remaining
AAPL	500	300	0	200
GOOGL	40	85	45	0
GOOGL	30	85	15	0
GOOGL	100	85	0	85
GOOGL	10	85	0	10
GOOGL	5	85	0	5
AMZN	50	55	5	0
AMZN	10	55	0	5

[DavidP]

I think your problem is essentially a variation on the FIFO (first in first out) principle. When you're selling stocks, you want to sell from the oldest batches first.

There are 2 ways to solve this problem.

1. Build an iterative macro

2. Split sales and purchases into rows with quantity of 1 and then use the date for each to ensure that every sales transaction uses the oldest purchases first.

There is a previous post on the community that deals with a similar problem. I used the 2nd method. Here is the link to that post.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/FIFO-Calculation-with-multiple-product...

If the solution in that post is similar to what you're looking to achieve, I'll be happy to help you convert the solution based on your data. Let me know.

[cmohyi]

Thanks David!

That post is definitely as close as I've seen to what I'm trying to figure out but mine differs in two ways. For one, these are all holdings, not transactions, so I don't need to differentiate buys and sells. Also I'm not using FIFO but more likely LIFO since I want to sell the highest cost (and generally more recently purchased) shares first. 

I like your solution in that other post a lot, and I'm hoping to get something similar. But the more I try to get a solution, the more it looks like a macro will be necessary. 

[DavidP · Accepted solution]

Hi @cmohyi 

I've adapted the workflow for your purposes. Please see below. Let me know if this is what you're looking for and if you have any questions.

[cmohyi]

@DavidP this is perfect! Thank you so much!!