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!
