Share matching analysis - first in, first out - iterative macro needed ?

[unhashable]

Hi everyone,

This problem has been driving me nuts - really hoping someone can point me in the right direction! I like to think I'm pretty competent with general Alteryx workflows and batch macros, but I'm yet to really venture into iterative macros with a real life problem. I'm not sure if this problem requires one!?

The problem

I've got some data (see dummy input screenshot below - dummy data also in attached Alteryx workflow) share reporting data; what I need to do is match the purchase rows to the sale rows on a first in, first out (FIFO) basis - I'll explain how this works below.

If you look at customer ID 1 in the screenshot below, the process goes as follows:

1). 540 shares are purchased on 01/01/2017.
2). 333 shares are purchased on 24/02/2019.
3). 17 shares are sold on 17/09/2021. The 17 shares sold were 'from' the shares purchased on 01/01/2017. As these were the first shares purchased for this customer, they're also the first shares to be sold.
4). There are 523 shares remaining of the 540 that were purchased on 01/01/2017.
6). 857 shares are sold on 25/09/2023. The shares sold were the remaining 523 shares purchased on 01/01/2017, and the 333 shares purchased on 24/02/2019.
7). All shares have been sold in full – there is no shareholding remaining.

Below, I've also added a screenshot of what I'm keen to see the output looking like. I'm not super precious about the exact layout, just something like this, that allows me to have [Units Purchased], [Units Sold]. [Purchase Date] and [Sell Date] on one row, so I'm ultimately able to calculate the [Holding Period] (i.e. date diff of purchase date and sell date). [Customer ID] and [Share ID] are needed as grouping variables as the same [Customer ID] can have different [Share ID] - see customer 3.

[Units Sold] - is the actual number of units sold, per the input table.

[Actual Units Sold] - is the "capped" units sold based on the first in, first out (FIFO) rules. For example, take the second row in the output, according to the 4th row of the input, 856 shares were sold on 25/08/2023, however, 523 relate to, or are "matched back" to the initial share holding of 540 (i.e. after the first sale of 17 has been deducted). [Actual Units Sold] is the lesser of [Units Sold] AND ([Units Purchased] OR [Units Carried Forward]) depending on whether there are any units to carry forward. More on [Units Carried Forward] below.

[Units to Carry Forward] - is the number of shares remaining where the share holding hasn't been fully sold. For example, start with 540 shares, sell 17, you're left with 523 shares - the remaining share balance really. This is the new balance to 'match' future sales against.

[Units Carried Forward] - is the number of shares that were carried forward from the previous row.

I've tried a number of different approaches, but I'm stumped. I'm not sure if this is possible with just multi-row formula and running total tools, or whether this calls for an iterative macro? Either way, I'd really value your input if you think you can help! I think it's a pretty interesting problem and one that I'd like to solve.

Thanks in advance!

[CoG]

Yes, this is a good use case for an iterative macro. I have built one to solve a very similar problem in the past and it worked quite well! You will need to iterate on the entire input so that you can keep track of how many purchased shares are still outstanding while also tracking the number of share sales remaining. It's not a terribly difficult macro to build (if you know what you're doing and have clearly conceptualized your approach), that hinges on the Multi-Row Formula Tool to handle the share selling calculation.

There may be other ways to tackle the problem, and I'm happy to try to put together what I'm thinking to share here, but will leave the post first to get you thinking about your own solutions. Happy Solving! 

[unhashable]

Perfect, thanks for the pointers.

Haha yes, it's the "if you know what you're doing" part that's tripping me up - I guess it's just experience of understanding how the iteration works and getting the multi-row formula tool to work within that.

I'll look at some of the iterative macro resources available and have a go this morning. If you are able to pull together and example workflow based on what you have suggested, that would be really very helpful, I'd really appreciate that!

[unhashable]

I'm trying to conceptualize what the iteration would look like in this case - can I just check that I'm thinking about this correctly?

Technicalities aside (i.e. how to get the multi-row formula tool to calculate the running total of the remaining shares), I'm thinking that the thing that would change with each iteration would be the running total of remaining shares? As in:

Iteration 1: 520 shares remaining, less 20 shares sold = 500 shares remaining.

Iteration 2: 500 shares remaining, less 100 shares sold = 400 shares remaining.

iteration 3: 400 shares remaining, less 300 shares sold = 100 shares remaining.

iteration 4: 100 shares remaining, less 100 shares sold = 0 shares remaining.

Am I on the right tracks here?

[CoG]

Here is my approach to this problem: https://youtu.be/ytr_tRVF-8U