High Cost, LIFO, FIFO

Question

Hello,

I am having a hard time creating a flow that has a running total of sorts.  I essentially need a LIFO/FIFO flow, but instead of the first shares or last shares being closed first, I need to have the highest cost price closed first.  I have sample data attached.

I have reviewed the other community posts on FIFO and LIFO, but I am still having trouble fitting those flows into my data.

Example:
724 & 91 shares are traded at the same price they can be group if its easier to calc
the 122 shares are at a new price --> the -14 shares need to be closed out at the 122 share price, leaving a balance of 108.

the -154 shares first must take the higher price of 21640.09 of the remaining 108 shares, and then the delta of -46 shares needs to be closed at 21375.19

etc, etc, etc all the way down to the final

Sample Data.xlsx

KGT · Accepted Answer

Without building out a full solution, which would take time that I don't have right now, these are some of the techniques that might get you started.

Caution: Beware of combining lines if they're the same price. Although it works for this, it may not work for tax purposes if they have a different exchange etc. And any process like this could also be used for other purposes. We deal with this all the time in Australia when evaluating the cost base of US shares for tax purposes. If all that is the same, then combine away if it makes it easier.

* You may want to reverse the dataset so you can work down, rather than up.
* I would tag the packages (transactions) with identifiers (Preferably a concat of fields and date, but even a recordID would work). This would then be your granularity.
* Running total on [Shares/Par]. Make sure you have enough grouping fields ticked, but not date.
* Don't be afraid to create extra columns, but don't keep extra if they are no use.
* Don't be afraid of building with 50 tools and then refining. This type of process normally has a bit of working out.
* Multi-row formula will be your friend, try to only look at one row before if possible.
* Work out how you are going to represent which package offsets each. Working out the value is one thing, auditability is another.
* This is your input set, but your output data can be totally different. You don't necessarily need one row per transaction, as a CLLFUT may actually be 3 "transactions" as the sale may be coming from 3 different acquisitions.

So, to start might be tagging etc, then running total, then see what can be allocated from the last package. It's only the CLLFUT that have to find a match.

One option is to create 2 columns that represent "Packagenumber", "Amount", then another 2 columns for the next if another allocation is needed, etc. By the time you get to 3-4 of these, you will most likely see a pattern and something that can help you refine that process as it's not going to be the solution (too many columns), but you'll need to represent each package that it came from at some point.

The easy final solution to jump to is a batch macro for each CLLFUT. But that will take a bit of working out to get the counters right and so I would start with the above anyhow.