FIFO costs

Hi community,

I am totally stuck with one issue. I already tried several batch macros, iterative macros etc. but cannot find a suitable solution. Other threads also did not have the exact solution for my case.

Hope you guy have an idea

I have one data table containing the transaction history of one stock. (see Input.xlsx)

I manually created the needed output in excel (see Output.xlsx) but was not able to recreate that in Alteryx so far.

Tasks needed:

When stocks are sold, we will need to calculate "Total Costs" using the unit price of the sold stocks and the unit price of buyed stocks according to the first-in first-out method (FIFO). So, if the number of sold units are more then the buyed units from the first transaction, I will need to use the unit price of the historically next buy.

Example:

First Buy -> 10 Units for 1$ each

Second Buy -> 10 Units for 2$ each

Third Buy -> 20 Units for 3$ each

Sell -> 15 Units --> Total Costs = 10 Units x 1$ + 5 Units x 2$ // now there are only 5 Units left from the second buy

Sell -> 15 Units --> Total Costs = 5 Units x 2$ + 10 Units x 3$

It gets more complex with increasing number of transactions. (see my example files)

Any idea how to solve this?

Input.xlsx

Output.xlsx

Iterative Macro

Accepted answers

ScottLewis

An iterative macro solution. It makes the assumption that the records come in time ordered and preserves the order rather than trying to parse the date field for time.

The iteration is basically "Compare the oldest buy row and the oldest sell row. Reduce the remaining volume in both by the smaller of the two, increase the total cost of the sell by that number * the buy unit price. Remove any rows with 0 remaining units from the data."

FIFO Buy Sell.yxmd

FIFO Buy Sell Macro.yxmc

ScottLewis

Back from vacation and having a look at this.

There are two layers to why it didn't work. The first is that we were doing integer math in part of the Macro, which was causing rounding. The 2 units off was the aggregate of all the rounding.

Second layer is that if we do floating point (double) math we still get an error on the order of e-19, which is noise to the amount field but still is !-=0 for the comparison.

Solution is to do fixed decimal math. I chose 19.6, you can muck with that if you want, the fields are set in the join within the macro. Forcing to 6 decimals captures the full detail of your input while avoiding the possible issues with floating point calculations. An alternative if you wanted floating point precision would be to round to some arbitrary decimal point at the end of the macro but I find doing fixed decimal math a more honest version of the same thing. Also, faster.

Attached file works for the sample provided and should work for all the others.

FIFO Buy Sell Macro.yxmc

FIFO Buy Sell.yxmd

All comments

griffinwelsh

This is sort of a brute force method computationally so you may run into performance issues if your unit volumes are large, but this will work.

FIFO.yxmd

griffinwelsh

This is a second option using an iterative macro. This solution is more efficient than the first one. Keep in mind both solutions assume that you don't have simultaneous trades. If you do have multiple trades on the same date you will need to add a time component.

FIFO2.yxzp

ScottLewis

An iterative macro solution. It makes the assumption that the records come in time ordered and preserves the order rather than trying to parse the date field for time.

FIFO Buy Sell.yxmd

FIFO Buy Sell Macro.yxmc

RRW

Thank you very much!! This is helpful!

I noticed one strange issue - it worked for all of my 200 transactions except for one! I attached the Input file for this one.

When I run it through the iterative macro it only shows 10 lines in the output eventhough the input has 11 lines. I checked each iteration and somehow in the last iteration the "remaining units" are not 0 - 2 units are left. I guess its a rounding issue? When using a sum tool over all Buys and Sells it shows the exact same value though.

Any idea or is this a general issue when using iterative macros?

Input.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280