Generating daily Journal Entries for FIFO system.

Question

I’m managing a FIFO-based investment fund and trying to automate daily journal entries for realized G/L. I use Alteryx to merge transaction reports and Excel (Purchases sheet, column G) to track tranche balances.

Total activity is easy to calculate—but breaking out daily G/L at the investment level is difficult.

My idea:

Calculate the cumulative realized G/L up to each day, then subtract the prior day’s total to isolate the net G/L for that specific day.

Possible method:

Generate a calendar of all dates using Alteryx.

Match each day's transactions (up to that date).

Subtract the previous day’s G/L from the current to get the daily value.

This should create a clear daily G/L time series. Open to other suggestions if there’s a better way.

FIFO System.xlsx

dreldrel · Answer

You create a small trial workflow and see if that is efficient or durable for your case

caltang · Answer

I’m on my phone right now but your way @edolphin98 seems to do the trick. Are you looking for a more efficient method?

Shivangi105949 · Answer

The simplest way to get daily realized gains and losses in a FIFO fund is to look at the cumulative total each day, then subtract the prior day’s number to isolate the net change.

One approach in Alteryx would be:

* Build a full calendar of dates covering your reporting period.

* Join in all transactions up to each date so you can calculate the running realized G/L.

* Use a multi-row formula (or similar) to subtract yesterday’s cumulative value from today’s, which gives you the daily G/L.

This will give you a clean time series of daily realized G/L.

An alternative is to calculate G/L directly at the transaction level and roll those up by date, but if your FIFO logic is already handled in your running balances, the cumulative-minus-prior-day method is usually the most straightforward.